Bright Tutorials Bright Tutorials
CBSE Class 12 Computer Science Question 68 of 91

Relational Database and SQL — Question 29

Back to all questions
29
Question

Question 22

Write SQL commands for (i) to (vi) on the basis of relations given below:

Table: BOOKS

Book_IDBook_nameAuthor_namePublishersPriceTypeqty
K0001Let us CY. KanetkarEPB450Prog15
P0001Computer NetworksB. AgarwalFIRST PUBL755Comp24
M0001Mastering C++K.R. VenugopalEPB165Prog60
N0002VC++ advanceP. PurohitTDH250Prog45
K0002Programming with PythonSanjeevFIRST PUBL350Prog30
L02Computer Science with PythonSumita AroraDhanpat rai655Prog16
L04Computer Science with PythonPreeti AroraSultan chand550Prog20
L05Concise MathematicsR.K.BansalSelina600Maths10

Table: ISSUED

Book_IDQty_Issued
L0213
L045
L0521

(i) To show the books of "FIRST PUBL" Publishers written by P.Purohit.

(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 bookname and price of the books, more than 3 copies of which have been issued.

(v) To show total cost of books of each type.

(vi) To show the details of the costliest book.

Answer

(i)

SELECT BOOK_NAME
FROM BOOKS
WHERE PUBLISHERS = "FIRST PUBL" AND AUTHOR_NAME = "P.PUROHIT";

There is no output produced because there are no books published by "FIRST PUBL" and written by "P.Purohit" in the table BOOKS.

(ii)

SELECT SUM(Price * qty) AS Cost
FROM BOOKS
WHERE Publishers = 'FIRST PUBL'
GROUP BY publishers;
Output
+-------+
| Cost  |
+-------+
| 28620 |
+-------+

(iii)

UPDATE BOOKS 
SET Price = Price - (Price * 5/100)  
WHERE Publishers = 'EPB';

(iv)

SELECT b.Book_name, b.Price
FROM BOOKS b, ISSUED i 
WHERE b.Book_ID = i.Book_ID and i.Qty_Issued > 3;
Output
+------------------------------+-------+
| Book_name                    | Price |
+------------------------------+-------+
| Computer Science with python |   655 |
| Computer Science with python |   550 |
| Concise Mathematics          |   600 |
+------------------------------+-------+

(v)

SELECT Type, SUM(Price * qty) AS Total_Cost
FROM BOOKS
GROUP BY Type;
Output
+-------+------------+
| Type  | Total_Cost |
+-------+------------+
| Prog  |      59070 |
| Maths |       6000 |
| Comp  |      18120 |
+-------+------------+

(vi)

SELECT *
FROM BOOKS
WHERE Price = (SELECT MAX(Price) FROM BOOKS);
Output
+---------+-------------------+-------------+------------+-------+------+-----+
| book_id | book_name         | author_name | publishers | price | type | qty |
+---------+-------------------+-------------+------------+-------+------+-----+
| P001    | Computer Networks | B.Agarwal   | FIRST PUBL |   755 | Comp |  24 |
+---------+-------------------+-------------+------------+-------+------+-----+