Book and Author Table Having Many To Many Relationship. Consider the Following Entities and Their Relationship. Book(bno,bname,pubname,price) Author(ano,aname) Constraints: Primary Key aname and pubname should not be Null.

SQL> create table book1

  2  (bno number primary key,

  3  bname varchar(30),

  4  pubname varchar(30) NOT NULL,

  5  price number);


Table created.


SQL> create table author

  2  (ano number primary key,

  3  aname varchar(20) NOT NULL);


Table created.


SQL> create table book_author

  2  (bno number references book1,

  3  ano number references author);


Table created.


SQL> select * from book1;


       BNO BNAME                          PUBNAME                             PRICE

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

         1 Core Java                               APB                                   500

         2 C Programming                     APB                                   600

         3 RDBMS                                LMD                                   650

         4 Accounting Management       Nirali                                400

         5 Visual Basic                          Vision                                560

         6 C++                                       APB                                   600

         7 Numerical Methods               Nirali                                400


7 rows selected.


SQL> select * from author;


       ANO ANAME

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

       101 Kanetkar

       102 Korth

       103 Umakant S S

       104 Siddiqui


SQL> select * from book_author;


       BNO        ANO

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

         1        101

         2        101

         3        102

         4        104

         5        102

         6        103

         7        103


7 rows selected.


a) Create or Replace PL/SQL Procedure To Display Details of all Books Written By 'Kanetkar'.


SQL> set serveroutput on;

SQL> create or replace procedure p6

  2  as

  3  cursor c6 is

  4  select bname,pubname,price from book1,author,book_author

  5  where book1.bno=book_author.bno

  6  and author.ano=book_author.ano

  7  and aname='Kanetkar';

  8  y c6%rowtype;

  9  begin

 10  dbms_output.put_line('Bname    Pubname  Price');

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

 12  for y in c6

 13  loop

 14  dbms_output.put_line(y.bname ||'    '|| y.pubname ||'    '|| y.price);

 15  end loop;

 16  end p6;

 17  /


Procedure created.


SQL> execute p6;

Bname    Pubname  Price

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

Core Java    APB    500

C Programming    APB    600


PL/SQL procedure successfully completed.


b) Create or Replace a Trigger That Restricts Insertion or Updation of Books Having Price Less Than 0.


SQL> create or replace trigger  t6

  2  before

  3  insert or update

  4  on book1

  5  for each row

  6  begin

  7  if :new.price<=0 then

  8  raise_application_error(-20008,'Price of The Book Should Be Greater Than 0');

  9  end if;

 10  end;

 11  /


Trigger created.


SQL> insert into book1 values(8,'OB','BPB',0);

insert into book1 values(8,'OB','BPB',0)

            *

ERROR at line 1:

ORA-20008: Price of The Book Should Be Greater Than 0

ORA-06512: at "SYSTEM.T6", line 3

ORA-04088: error during execution of trigger 'SYSTEM.T6'