Book and Department Table Having Many To One Relationship. Consider the Following Entities and Their Relationship. Book(bno,bname,pubname,price) Department(dno,dname) Constraints: Primary Key, Price should be>0

SQL> Create Table Department

    (dno number primary key,

     dname varchar(20));


Table created.


SQL> Create Table Book

    (bno number primary key,

     bname varchar(20),

     pubname varchar(20),

     price number check(price>0),

     dno number references department);


Table created.


SQL> select * from book;


       BNO BNAME                PUBNAME                   PRICE        DNO

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

         1 Advance Java        Kanetkar                    500        101

         2 Visual Basic             BBP                         450        101

         3 AdvertiseTechnqs     DK                          700        105

         4 SalesManagement    VK                          500        102

         5 HR Techniques         DK                          300        104

         6 Inventory                  BBP                         600        103


6 rows selected.


SQL> select * from department;


       DNO DNAME

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

       101 Computer

       102 Sales

       103 Production

       104 HR

       105 Advertisement


a)Create ir Replace a PL/SQL Function To Retun Total Expenditure on Books of Given Department.


SQL> set serveroutput on;

SQL> create or replace function f1

  2  (deptname in varchar2)

  3  return number

  4  as

  5  Expenditure number;

  6  begin

  7  select sum(price) into Expenditure from book,department

  8  where department.dno=book.dno

  9  and dname=deptname;

 10  if SQL%Found then

 11  return Expenditure;

 12  else

 13  return 0;

 14  end if;

 15  end f1;

 16  /


Function created.


**Call Program**

SQL> declare

  2  v_deptname varchar(20);

  3  r number;

  4  begin

  5  v_deptname:=&v_deptname;

  6  r:=f1(v_deptname);

  7  if(r=0)then

  8  dbms_output.put_line('Department Details Does Not Exist.');

  9  else

 10  dbms_output.put_line('The Expenditure on Books of' ||' '|| v_deptname ||' Department is '||r);

 11  end if;

 12  end;

 13  /

Enter value for v_deptname: 'Sales'

old   5: v_deptname:=&v_deptname;

new   5: v_deptname:='Sales';

The Expenditure on Books of Sales Department is 500


PL/SQL procedure successfully completed.


Enter value for v_deptname: 'Computer'

old   5: v_deptname:=&v_deptname;

new   5: v_deptname:='Computer';

The Expenditure on Books of Computer Department is 950


PL/SQL procedure successfully completed.



b) Write a Cursor to Display Details of all Books Brought for a 'Computer Department'.


SQL> declare

  2  cursor c2 is

  3  select bno,bname,pubname,price from book,department

  4  where book.dno=department.dno

  5  and dname='Computer';

  6  n c2%rowtype;

  7  begin

  8  dbms_output.put_line('BookNo  BookName  PubName  Price');

  9  dbms_output.put_line('--------------------------------');

 10  for n in c2

 11  loop

 12  dbms_output.put_line(n.bno || '  ' || n.bname || '  ' || n.pubname || '  ' || n.price);

 13  end loop;

 14  END;

 15  /

BookNo  BookName  PubName  Price

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

1  Advance Java  Kanetkar  500

2  Visual Basic  BBP          450


PL/SQL procedure successfully completed.