RDBMS example of book and dept many to one relationship

Q.) Book( bno, bname, pudname, price)

        Department( dno, dname)

        Relationships between book and department is many to one.
   
        Constraints:-primary key,  Price should bo>0.

SQL> select * from department_1;
    DNO DNAME
---------- ------------------------------
       101 computer
       102 science
       103 arts
       104 MBA
       105 MSC
SQL> select * from books_1;
 BNO     BNAME   PUBNAME    PRICE  DNO
-------   -------------  ---------------   --------- --------
201      C++              nirali               150      101
1202    numerical     BPB                180      102
             method  
1203     RDBMS     vision               140      101


  BNO     BNAME   PUBNAME    PRICE  DNO
-------   -------------  ---------------   --------- --------

1204    data structure       nirali          145       104

1205     social sic         vision            120      105
1206       POM              BPB             130      103

6 rows selected.
************************ ** ** ******************************************

a).create or replace a PL/SQL function to return total expenditure on books of a given department.

SQL>
  1  create or replace function disb(x in varchar2)return number
  2  is
  3    c number(5);
  4  begin
  5    select sum(b.price) into c from department_1 d,books_1 b
  6    where d.dno=b.dno and d.dname=x;
  7    return(c);
  8* end;
SQL> /
Function created.

SQL>
  1  declare
  2     dn department_1.dname%type;
  3     t number(10);
  4  begin
  5     dn:='&dn';
  6     t:=disb(dn);
  7     dbms_output.put_line('total expenditure on books of a'||' '||dn||' '||'is:-'||t);
  8* end;
SQL> /
Enter value for dn: computer
old   5:    dn:='&dn';
new   5:    dn:='computer';
total expenditure on books of a computer is:-290

PL/SQL procedure successfully completed.


************************************************************************
************************************************************************

b). write a cursor to display details of all books brought for a ‘computer’ department.

SQL>
  1  declare
  2    cursor c8 is select b.bno,b.bname,b.pubname,b.price from department_1 d,
  3    books_1 b where d.dno=b.dno and d.dname='computer';
  4  begin
  5    for x in c8
  6    loop
  7      dbms_output.put_line(x.bno||' '||x.bname||' '||x.pubname||' '||x.price);
  8    end loop;
  9* end;
SQL> /
1201 C++ nirali 150
1203 RDBMS vision 140

PL/SQL procedure successfully completed.


************************************************************************