Question

Write SQL Commands for the following queries (i) to (v) based on the relation 5
BOOKS and ISSUED.
TABLE:
AuthorName
Book Id
C0001
Books
Publishers
Price
Qty
Book Name
Cook well
Pascal
Natasha
EPB
400
Type
Cookery
Text
5
T0001
Rehman
TDH
670
10
T0002
Java
Abhinav
GMT
750
Text
9
F0001
Darkness
Sunita
EPB
250
Fiction
8
C0002
Dessert
JPH
900
Sandeep
Amrita
15
F0002
Cookery
Fiction
Wisdom
GMT
390
18
Table : Issued
BookId
Qty_Issued
T0001
5
F0001
7.
C0001
3
F0002
15
T0002
6
(1) To show Book name, Price and Qty_Issued for all books which have been
issued.
(ii) To increase the price of all books of EPB Publisher by 100.
() To display the Book name and price from the table Role in dandi​

Answer 1

Answer:

(b)To show Book name, Author name and Price of books of EPB publisher.

Ans: select Book_name,Author_name, price from books where Publisher =”EPB”

(c) To list the names of the books of FICTIONS type.

Ans: Select Book_name from books where type=”FICTION”

(d) To display the names and prices of the books in descending order of their price.

Ans: Select Book_name, price from books order by price desc;

(e) To increase the price of all books of First Pub.by 50.

Ans: update books set price= price+50 where publishers = “First Publ”

(f) To Display the Book_ID, Book_Name and Quantity Issued for all books Which have been issued.

Ans:Select Book_ID, Book_Name, Quantity_Issued from Books,Issued where Books.BookId=Issued.BookId;

(g) To insert a new row in the table Issued having the following data: “F0002”,4

Ans: insert into Issued values(“F0002”,4)

(h) Give the output of the following queries on the above tables:

(i) Select Count(Distinct Publishers) From Books

Ans: 3

(ii) Select Sum(Price) From Books Where Quantity>5 Ans: 1350.

(iii) Select Book_Name,Author_Name From Books Where Price<500.

Ans: Book_Name Author_Name My First C++ Brian & Brooks C++ Brainworks A.W.Rossaine Fast Cook Lata Kapoor.

(iv) Select Count(*) From Books.

Explanation:

hope this will help you