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 Publ	650	Fiction	20
T0001	First C++	Brain Brooke	EPB	350	Text	10
T0002	C++ Brain	A. W. Rossaine	TDH	350	Text	15
F0002	Thunder	Annaborts	First Publ	750	Fiction	50

 Ans.
 create table library1

      ( Book_Id char(6),

         Book_Name char(10),

         Author_Name char(15),

         Publishers char(10),

         Price decimal,

         Type char(8),

          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','First C++','Brian Broke','EPB',350.00,'Text',10); 

Insert into library values('T0002','C++ Brain','A.W. Rosssain','TDH',350.00,'Text',15);

Insert into library values('F0002','Thundes','Annaborts','First Pub',750.00,'Fiction',50);

Table:ISSUED

Book_Id	Quantity_Issued
T0001	4
C0001	5
F0001	2

Ans: create table Issued

         (

             Book_Id char(8),

             Quantity_Issued integer

          );

Insert into Issued values ('T0001',4);
Insert into Issued values ('C0001',4);
Insert into Issued values ('F0001',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.Book_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 Library;

Ans:  5

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

Ans: 750

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

Ans:  Fast Cook         Lata Kapoor

          First C++         Brain Brake

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

               Ans: 1