COMPUTER SCIENCE STRUCTURED QUERY LANGUAGE

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 ( [email protected] )