Doctor and Hospital Table Have Many To Many Relationship. Consider the Following Entities and Their Relationship. Doctor(dno,dname,dcity) Hospital(hno,hname,hcity) Constraints: Primary Key dcity and hcity should not be Null.

 SQL> create table doctor

  2  (dno number primary key,

  3  dname varchar(20),

  4  dcity varchar(20) NOT NULL);


Table created.


SQL> create table hospital

  2  (hno number primary key,

  3  hname varchar(30),

  4  hcity varchar(20) NOT NULL);


Table created.


SQL> create table doctor_hospital

  2  (dno number references doctor,

  3  hno number references hospital);


Table created.


SQL> select * from doctor;


       DNO DNAME                DCITY

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

         1 Dr Roy                  Pune

         2 Dr Apte               Pune

         3 Dr Mishra            Mumbai

         4 Dr Raman             Delhi

         5 Dr Chowdary        Mumbai

         6 Dr Sharma            Pune


6 rows selected.


SQL> select * from hospital;


       HNO HNAME                          HCITY

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

       101 Sayadri                             Pune

       102 Sanchiti                            Pune

       103 City Hospital                  Mumbai

       104 Rustom Hospital                Delhi


SQL> select * from doctor_hospital;


       DNO        HNO

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

         1        101

         2        101

         3        102

         3        103

         4        103

         5        104

         6        101

         6        102


8 rows selected.


a) Create or Replace a PL/SQL Procedure to Display Details of all Hospitals in 'Pune'.


SQL> set serveroutput on;

SQL> create or replace procedure p8

  2  as

  3  cursor c8 is

  4  select * from hospital

  5  where hcity='Pune';

  6  y c8%rowtype;

  7  begin

  8  dbms_output.put_line('HNO   HNAME   HCITY');

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

 10  for y in c8

 11  loop

 12  dbms_output.put_line(y.hno ||'  '|| y.hname ||'  '|| y.hcity);

 13  end loop;

 14  end p8;

 15  /


Procedure created.


SQL> execute p8;

HNO   HNAME   HCITY

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

101  Sayadri  Pune

102  Sanchiti  Pune


PL/SQL procedure successfully completed.


b) Write a Cursor To List All Hospitals and There Doctor Details.


SQL> declare

  2  cursor c8 is

  3  select hname,doctor.dno,dname,dcity from doctor,hospital,doctor_hospital

  4  where doctor.dno=doctor_hospital.dno and

  5  hospital.hno=doctor_hospital.hno;

  6  y c8%rowtype;

  7  begin

  8  dbms_output.put_line('HNAME      DNO DNAME           DCITY');

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

 10  for y in c8

 11  loop

 12  dbms_output.put_line(y.hname ||'      '|| y.dno ||'       '|| y.dname ||'        '|| y.dcity);

 13  end loop;

 14  end;

 15  /

HNAME        DNO       DNAME         DCITY

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

Sayadri        1       Dr Roy        Pune

Sayadri        2       Dr Apte       Pune

City Hospital  3       Dr Mishra     Mumbai

Sanchiti       3       Dr Mishra     Mumbai

City Hospital  4       Dr Raman      Delhi

Rustom Hospital5       Dr Chowdary   Mumbai

Sanchiti       6       Dr Sharma     Pune

Sayadri        6       Dr Sharma     Pune


PL/SQL procedure successfully completed.