Bright Tutorials Bright Tutorials
CBSE Class 11 Informatics Practices Question 63 of 87

Structured Query Language (SQL) — Question 24

Back to all questions
24
Question

Question 18

Write SQL commands for (i) to (v) on the basis of relation given below:

Table: BOOKS

book_idBook_nameauthor_namePublishersPriceTypeqty
k0001Let us CY. KanetkarEPB450Comp15
p0001GenuineJ. MukhiFIRST PUBL.755Fiction24
m0001Mastering C++K.R. VenugopalEPB165Comp60
n0002VC++ advanceP. PurohitTDH250Comp45
k0002Programming with PythonSanjeevFIRST PUBL.350Fiction30

(i) To show the books of FIRST PUBL. written by J. Mukhi.

(ii) To display cost of all the books published for FIRST PUBL.

(iii) Depreciate the price of all books of EPB publishers by 5%.

(iv) To display the Book_Name and price of the books more than 3 copies of which have been issued.

(v) To show the details of the book with quantity more than 30.

Answer

(i)

SELECT * 
FROM BOOKS 
WHERE Publishers = 'FIRST PUBL.' AND author_name = 'J. Mukhi';
Output
+---------+-----------+-------------+-------------+--------+---------+-----+
| book_id | Book_name | author_name | Publishers  | Price  | Type    | qty |
+---------+-----------+-------------+-------------+--------+---------+-----+
| p0001   | Genuine   | J. Mukhi    | FIRST PUBL. | 755.00 | Fiction |  24 |
+---------+-----------+-------------+-------------+--------+---------+-----+

(ii)

SELECT SUM(Price) AS TotalCost 
FROM BOOKS 
WHERE Publishers = 'FIRST PUBL.';
Output
+-----------+
| TotalCost |
+-----------+
|   1105.00 |
+-----------+

(iii)

UPDATE BOOKS 
SET Price = Price - (Price * 0.05)
WHERE Publishers = 'EPB';

(iv)

SELECT Book_name, Price 
FROM BOOKS 
WHERE qty > 3;
Output
+-------------------------+--------+
| Book_name               | Price  |
+-------------------------+--------+
| Let us C                | 427.50 |
| Programming with Python | 350.00 |
| Mastering C++           | 156.75 |
| VC++ advance            | 250.00 |
| Genuine                 | 755.00 |
+-------------------------+--------+

(v)

SELECT * 
FROM BOOKS 
WHERE qty > 30;
Output
+---------+---------------+----------------+------------+--------+------+-----+
| book_id | Book_name     | author_name    | Publishers | Price  | Type | qty |
+---------+---------------+----------------+------------+--------+------+-----+
| m0001   | Mastering C++ | K.R. Venugopal | EPB        | 156.75 | Comp |  60 |
| n0002   | VC++ advance  | P. Purohit     | TDH        | 250.00 | Comp |  45 |
+---------+---------------+----------------+------------+--------+------+-----+