LIBRARY MANAGEMENT IN SQL

i.Display the library table with the list of fields serial number,book identity number(frregin key),

SQL>create table library(serial number(10),bookid number(10),bookname varchar2(20),foreign key(bookid),author varchar2(20),publication varchar2(20),discountprice varchar2(10),actualprice varchar2(20));

 Table created.

SOL> insert into library  values(‘&serial’,&bookid’,’&bookname’,’&author’, ‘&publication’, ‘&discountprice’, ‘&actualprice’,);

Entert value for serial:1

Entert value for bookid:01

Entert value for bookname:c

Entert value for author:brown dennis

Entert value for publication:tata

Entert value for discountprice:20%

Entert value for actualprice:250

1 row created.

SQL>select * from library;

SERIAL  BOOKID  BOOKNAME  AUTHOR    PUBLICATION DISCOUNTPRICE  ACTUALPRICE

-------- --------  -----------   ---------   -------------  ---------------   ------------------

1                      01              c              brown dennis       tata      20%             250

2          02               c++        hen     peter          tata        20%             350

3          03               java       Hendry  brown     tata        40%             550

4          04               rdbms    sam winsester      tata          -                    -

ii.Display the Book details like Bookid number and name of the book.

SQL>select bookid,bookname from library;

BOOKID  BOOKNAME

-----------  ---------------

01               c              

02               c++          

03               java         

04               rdbms     

iii.Display the name of the book that is discounted by 20 percent.

SQL>select * from library where discountprice=’20%’;

SERIAL  BOOKID  BOOKNAME  AUTHOR  PUBLICATION DISCOUNTPRICE  ACTUALPRICE

-------  --------  ------------  --------- --------------  ----------------   --------------

1              01               c          brown dennis      tata                   20%                 250

2          02              c++       hen     peter         tata                   20%                 350

iv.Display the last name of the author.

SQL>select last(author) as “Last name” from library;

LAST NAME

---------------

Dennis

Peter

Brown

Winsester

v.Display the name of the book ,which is not assigned with the price.

SQL>select * from library where actualprice is Null;

SERIAL  BOOKID  BOOKNAME  AUTHOR  PUBLICATION DISCOUNTPRICE  ACTUALPRICE

-------  --------  ------------  --------- --------------  ----------------   --------------

4          04            rdbms     sam winsester    tata           -                       -

vi.Display the list of  book of a particular author.

SQL>select bookname from library where author name=’sam winsester’;

BOOKNAME

---------------

rdbms        

vii.Display the book details using groupby and order by.

SQL>select bookname from library group by serial;

BOOKNAME 

---------------  

c         

c++          

java            

rdbms     

SQL>select serial,author from library order by serial;

  

SERIAL   AUTHOR 

--------     -----------  

1              brown dennis   

2              hen     peter      

3          Hendry  brown 

4          sam winsester