SQL Prog-1 Tables Library and Issued

SQL PRACTICALS

1.       Given the following tables for a database LIBRARY :

Book_Id	Book_Name	Author_Name	Publishers	Price	Type	Quantity
C0001	Fast Cook	Lata Kapoor	EPB	355	Cookery	5
F0001	The Tears	William Hopkins	First Publishers	650	Fiction	20
T0001	My First C++	Brain and Brooke	EPB	350	Text	10
T0002	C++ Brainworks	A. W. Rossaine	TDH	350	Text	15
F0002	Thunderbolts	Annaborts	First Publishers	750	Fiction	50

Ans:

 create table library

      ( Book_Id char(10),

         Book_Name char(15),

         Author_Name char(17),

         Publishers char(12),

         Price decimal,

         Type char(10),

          Quantity integer

       );

 Insert into library values (‘C0001’,’Fast Cook’,’Lata Kapoor’,’EPB’,355.00,’Cookery’,5);

 Insert into library values (‘F0001’,’The tears’,’Willian Hopkins’,’First publ.’,650.00,’Fiction’,20);

 Insert into library values(‘T0001’,’My First C++’,’Brian & Broke’,’EPB,350.00,’Text’,15);

Table:ISSUED

Book_Id	Quantity_Issued
T0001	4
C0001	5
F0001	2

Ans: create table Issued

         (

             Book_Id char(6),

             Quantity_Issued integer

          );

Insert into Issued values (‘T0001’,4);

Write SQL queries for (a) to (f):

a)      To show Book name, Author name and Price of books of First Publishers.

Ans: select Book_Name, Author_Name, Price from Library where Publishers = ‘First Publ.’;

b)      To list the names from books of Text type.

Ans: select Book_Name from Library where Type=’Text’;

c)       To display the names and price of all books in ascending order of their price.

Ans: select Book_Name, Price from Library order by Price;

d)      To increase the price of all books of EPB Publishers by 50.

Ans: update Library set price = price + 50 where publishers= ‘EPB’;

e)      To display the Book_Id, Book_Name and Quantity_Issued for all books which have been issued.

Ans: select Library.Book_Id,Library.Book_Name,Issued.Quantity_Issued from Library,Issued where Library.Books_Id = Issued.Book_Id;

f)       To insert a new row in the table ISSUED having the following data : “F0003”,1

Ans: Insert into Issued values (‘F0003,1);

g)      Give the output of the following queries based on the above tables:

                                 i.            Select count(*) from BOOKS;

Ans:  5

                               ii.            Select max(Price) from BOOKS where Quantity>=15;

Ans: 750

                              iii.            Select Book_Name, Author_Name from BOOKS where Publisher=”EPB”;

Ans:  Fast Cook         Lata Kapoor

          My First C++    Brain & Brake

                             iv.            Select count(distinct Publishers) from BOOKS where Price>=400;

Ans: 1