Movie and Actor Table Having Many To Many Relationship. Consider the Following Entities and Their Relationship. Movie(mvno,mvname,releaseyear) Actor(actno,actname) Movie and Actor are Related with Many to Many Relationships with descriptive attribute rate of actor for movie. Constraints: Primary Key

SQL> create table movie

  2  (mvno number primary key,

  3  mvname varchar(20),

  4  releaseyear number check(releaseyear>0));


Table created.


SQL> create table actor

  2  (actno number primary key,

  3  actname varchar(20));


Table created.


SQL> create table movie_actor

  2  (mvno number references movie,

  3  actno number references actor

  4  rate number);


Table created.


SQL> select * from movie;


      MVNO MVNAME               RELEASEYEAR

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

         1 Dabangg                     2012

         2 Race                        2006

         3 Don                         2002

         4 Dhoom                       2002

         5 Bhoothnath                  2007

         6 Ready                       2011

         7 RaOne                       2011

         8 Dhoom2                      2007


8 rows selected.


SQL> select * from actor;


     ACTNO ACTNAME

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

       101 Salman

       102 Shahrukh

       103 Amitabh

       104 Hrithik

       105 Saif


SQL> select * from movie_actor;


      MVNO      ACTNO       RATE

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

         1        101         30

         2        105         15

         3        103         40

         4        104         10

         5        103         50

         6        101         12

         7        102         16

         8        104         19

8 rows selected.


a)Create or Replace a PL/SQL Procedure to Display Details of all Movies of actor 'Amitabh'.


SQL> set serveroutput on;

SQL> create or replace procedure m

  2  as

  3  cursor n is

  4  select movie.mvno,mvname,releaseyear from movie,actor,movie_actor

  5  where movie.mvno=movie_actor.mvno

  6  and actor.actno=movie_actor.actno

  7  and actname='Amitabh';

  8  y n%rowtype;

  9  begin

 10  dbms_output.put_line('MVNO    MVNAME    RELEASEYEAR');

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

 12  for y in n

 13  loop

 14  dbms_output.put_line(y.mvno || '     ' || y.mvname || '     ' || y.releaseyear);

 15  end loop;

 16  end m;

 17  /


Procedure created.


SQL> execute m;

MVNO    MVNAME    RELEASEYEAR

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

3         Don     2002

5     Bhoothnath   2007


PL/SQL procedure successfully completed.


b)Write a Cursor to Display Names of all Movies Which are released in year 2002.


SQL> declare

  2  cursor n is

  3  select mvname from movie

  4  where releaseyear=2002;

  5  h n%rowtype;

  6  begin

  7  dbms_output.put_line('Movie Name Released in 2002');

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

  9  for h in n

 10  loop

 11  dbms_output.put_line(h.mvname);

 12  end loop;

 13  end;

 14  /

Movie Name Released in 2002

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

Don

Dhoom


PL/SQL procedure successfully completed.