2004:
5. Give the following table for database a LIBRARY.
TABLE : BOOKS
| BOOK_ID |
BOOK_NAME | AUTHONAME | PUBLISHER | PRICE | TYPE | QUANTITY |
|---|---|---|---|---|---|---|
| F0001 |
The Tears | William Hopkins | First Publ | 750 | Fiction | 10 |
| F0002 |
Thund erbolts | Anna Roberts | First Publ. | 700 | Fiction | 5 |
| T0001 | My first C+
+ |
Brains & Brooke | EPB | 250 | Text | 10 |
| T0002
|
C++ Brain works | A.W.Ros saine | TDH | 325 | Text | 5 |
| C001 |
Fast Cook | Lata Kapoore | EPB | 350 | Cookery | 8 |
TABLE:ISSUED
| BOOK_ID | QUANTITY_ISSUED |
|---|---|
| F0001 | 3 |
| T0001 |
1 |
| C0001 | 5 |
Write SQL queries from b to g.
(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.
Ans: 5
CBSE Computer Science Solved Revision Tour By Mr. Ravi Kiran ( mrkdata@yahoo.com )
