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

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

BOOKS

 
 
 
 
 
	 
Book-id
 
	 
Book_name
 
	 
Author_name
 
	 
Publishers
 
	 
Price
 
	 
Type
 
	 
Qty
 
 
 
	 
k0001
 
	 
Let us C
 
	 
Sanjay Mukherjee 
 
	 
EPB
 
	 
450
 
	 
Comp
 
	 
15
 
 
 
	 
p0001.
 
	 
Genuine
 
	 
J. Mukhi
 
	 
FIRST PUBL.
 
	 
755
 
	 
Fiction
 
	 
24
 
 
 
	 
m0001
 
	 
Mastering C++
 
	 
Kantar
 
	 
EPB
 
	 
165
 
	 
Comp
 
	 
60
 
 
 
	 
n0002
 
	 
VC++ advance
 
	 
P. Purohit
 
	 
TDH
 
	 
250
 
	 
Comp
 
	 
45
 
 
 
	 
k0002
 
	 
Programming with Python 
 
	 
Sanjeev
 
	 
FIRST PUBL.
 
	 
350
 
	 
Fiction
 
	 
30
 
 
 
 

ISSUED

 
 
 
 
 
	 
Book_ID
 
	 
Qty_Issued
 
 
 
	 
L02
 
	 
13
 
 
 
	 
L04
 
	 
5
 
 
 
	 
L05
 
	 
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 display the BOOK_NAME 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 * from BOOKS where Author_name = “P.Purohit” and Publishers = “FIRST PUBL.”;

(ii)
Select Price from BOOKS where Publishers = “FIRST PUBL.”;

(iii)
Select * , Price * 0.95 from BOOKS ;

(iv)
Select Book_name, Price from BOOKS, ISSUED where Book-id.BOOKS = Book-id.ISSUED and Qty_Issued > 3 ;

(v)
Select sum(price) from BOOKS group by Type ;

(vi)
Select * from BOOKS having Price = MAX(Price) ;