RDBMS example of many to many relationship

Q.).    book ( bno, bname, pubname, price)
 
         Author ( ano, aname)
 
         Relationships between book and author is many to many.

         Constraints:-primary key,
                                Aname and pubname should NOT NULL.


SQL> select * from book_1;

       BNO BNAME                PUBNAME                             PRICE
---------- -------------------- ------------------------------ ----------
       101 C++                           nirali                                150
       102 M A/C                      vision                                250
       103 RDBMS                    BPB                                   175
       104 data structre              BPB                                   165
       105 software engineering   nirali                                135

SQL> select * from author;

       ANO ANAME
---------- --------------------
      1201  mr.dewasi
      1202  mr.shiravi b
      1203  mr.pankaj
      1204  kanetkar
      1205  babulal

SQL> select * from bookauth;

    BNO        ANO
---------- ----------
       101       1201
       102       1202
       103       1203
       104       1204
       102       1204
       105       1205
       101       1203

7 rows selected.


************************************************************************
a). create or replace a PL/SQL procedure to display details of all books written by ‘kanetkar’.

SQL>
  1  create or replace procedure disa(t in varchar2)
  2  is cursor ca is select b.bno,b.bname,b.pubname,b.price from book_1 b,
  3     author a, bookauth ba where b.bno=ba.bno and a.ano=ba.ano and
  4     a.aname=t;
  5  begin
  6     for x in ca loop
  7        dbms_output.put_line(x.bno||' '||x.bname||' '||x.pubname||' '||x.price);
  8     end loop;
  9* end;
SQL> /

Procedure created.

SQL>
  1  declare
  2  begin
  3     disa('kanetkar');
  4* end;
  5  /
104 data structre  BPB 165
102 M A/C vision 250

PL/SQL procedure successfully completed.


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

b). create or replace a trigger that restricts insertion or updation of books having price less than 0.

SQL>
  1  create or replace trigger ta1
  2  before insert or update
  3  on book_1
  4  for each row
  5  declare
  6    p book_1.price%type;
  7  begin
  8    p:=:new.price;
  9    if(p<0) then
 10      raise_application_error(-20089,'book price must be>0');
 11    end if;
 12* end;
SQL> /

Trigger created.

SQL> insert into book_1
  2  values(106,'SE','nirali',-45);
insert into book_1
            *
ERROR at line 1:
ORA-20089: book price must be>0
ORA-06512: at "SCOTT.TA1", line 6
ORA-04088: error during execution of trigger 'SCOTT.TA1'


SQL> update book_1
  2  set price=-45
  3  where bno=101;
update book_1
       *
ERROR at line 1:
ORA-20089: book price must be>0
ORA-06512: at "SCOTT.TA1", line 6
ORA-04088: error during execution of trigger 'SCOTT.TA1'

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