RDBMS examples

Q.3). movie (mvno,mvname,releaseyear)

         Actor (actno,actname)

         Relationships between movie and actor is many to many with descriptive attribute rate of actor for movie.
   
         Constraints:- primary key.


SQL> select * from movie;

 MVNO    MVNAME         RELEASEYEAR
----------  --------------------  -----------
         1     dhoom                       2002
         2     DDLJ                        1999
         3    MBBS                        2000
         4    om kare                     2002
         5    singham                     2011

SQL> select * from actor;

 ACTNO  ACTNAME
----------   ------------------------------
       101    hrithik
       102    amitabh
       103    abhishek
       104    ajay
       105    salaman

SQL> select * from movact;

MVNO  ACTNO    RATE
---------- ---------- ----------
         1        101     500000
         2        102     800000
         3        103     900000
         4        105    1200000
         5        104    1500000
         2        104     700000
         1        101    1200000
7 rows selected.

SQL> set serveroutput on;

a).create or replace a PL/SQL procedure to display details of all movies of actor ‘amitabh’.

SQL>
  1  create or replace procedure showma(t in varchar2)
  2  is cursor c5 is select m.mvno,m.mvname,m.releaseyear
  3     from movie m,actor a,movact ma
  4     where m.mvno=ma.mvno and a.actno=ma.actno and
  5     a.actname=t;
  6  begin
  7     for x in c5 loop
  8       dbms_output.put_line(x.mvno||' '||x.mvname||' '||x.releaseyear);
  9     end loop;
 10* end;
SQL> /

Procedure created.

SQL> declare
  2
  3  begin
  4     showma('amitabh');
  5  end;
  6  /
2 DDLJ 1999

PL/SQL procedure successfully completed.


b).write a cursor to display names of all movies which are released in year 2002.
SQL>
  1  declare
  2    cursor c7 is select mvname from movie
  3    where releaseyear=2002;
  4  begin
  5    for x in c7 loop
  6      dbms_output.put_line(x.mvname);
  7    end loop;
  8* end;
SQL> /
dhoom
om kare

PL/SQL procedure successfully completed.