To show the books of FIRST PUBL. Publishers written by P. Purohit.

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

Table:- BOOKS

 
	 
Book_id
 
	 
Book_name
 
	 
author_name
 
	 
Publishers
 
	 
Price
 
	 
Type
 
	 
qty
 
 
 
	 
k0001
 
	 
Let us C
 
	 
Sanjay Mukharjee
 
	 
EPB
 
	 
450
 
	 
Comp
 
	 
15
 
 
 
	 
p0001
 
	 
Genuine
 
	 
J. Mukhi
 
	 
FIRST PUBL. 
 
	 
755
 
	 
Fiction
 
	 
24
 
 
 
	 
m0001
 
	 
Mastering C++
 
	 
Kantkar
 
	 
EPB
 
	 
165
 
	 
Comp
 
	 
60
 
 
 
	 
n0002
 
	 
VC++ advance
 
	 
P. Purohit
 
	 
TDH
 
	 
250
 
	 
Comp
 
	 
45
 
 
 
	 
k0002
 
	 
Programming with Python
 
	 
Sanjeev
 
	 
FIRST PUBL. 
 
	 
350
 
	 
Fiction
 
	 
30
 
 

Table:- ISSUED

 
	 
Book_ID
 
	 
Qty_Issued
 
 
 
	 
L02
 
	 
13
 
 
 
	 
L04
 
	 
5
 
 
 
	 
LO5
 
	 
21
 
 

(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 show total cost of books of each type.

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

Answer :-

(i) Select * from books where author_name = “P. Purohit” and Publishers = “FIRST PUBL” ;
(ii) Select * from books where Publishers = “FIRST PUBL” ;
(iii) update books set price = price * 0.95 where publisher = “EPB” ;
(iv) select sum( price ) , type from books group by type ;
(v) select * from books where ( select max( price ) from books) = price ;