[GTU] SQL & PL/SQL SET - 13

Definition of SET - 13

Movie (movie_id, movie_name, date_of_release)
Screen (screen_id, location, max_capacity)
Current (movie_id,screen_id, date_of_arrival, date_of_closure)

Note:
Value of screen_id must with letter ‘S’.
Screen location can by any one of ‘FF’, ‘SF’, and ‘TF’.
Date_of_arrival must be less than Date_of_closure.
Max_capacity attribute should have a value greater than 0.

Implement the following:
A)

1. Movie ‘Star wars III‘was released in the 7th week of 2005. Find out the date of its
   release considering that a movie releases only on Friday.
2. Get the details of movie that closed on date 15-January-2010.

B)

1. Create a trigger that checks the ‘screen_id’ must start with ‘S’ whenever an insertion
   is tried to be done. Raise a user defined exception if the rule is violated.
2. Create a package for the following :
   Create a procedure to print Movie Name where Movie code is been supplied by the
   user.
   
   

TABLE CREATION

SQL> create table movie
  2  (
  3  movie_id varchar2(3) primary key,
  4  movie_name varchar2(20) NOT NULL,
  5  date_of_release date,
  6  CHECK(movie_id LIKE 'M%')
  7  );

Table created.

SQL> create table screen
  2  (
  3  screen_id varchar2(3) primary key,
  4  location varchar(2) CHECK(location IN('FF','SF','TF')),
  5  max_capacity number(3) CHECK(max_capacity > 0),
  6  CHECK(screen_id LIKE 'S%')
  7  );

Table created.

SQL> create table current1
  2  (
  3  movie_id varchar2(3) REFERENCES movie(movie_id) ON DELETE CASCADE,
  4  screen_id varchar2(3) REFERENCES screen(screen_id) ON DELETE CASCADE,
  5  date_of_arrival date CHECK(to_char(date_of_arrival,'DD-MON-YY') < '12-MAR-12'),
  6  date_of_closure date CHECK(to_char(date_of_closure,'DD-MON-YY') > '12-MAR-12')
  7  );

Table created.

INSERTION IN TABLES

SQL> insert into movie
  2  values('M01','PANSINGH TOMAR','10-MARCH-12');

1 row created.

SQL> insert into movie
  2  values('M02','AGNEEPATH','12-FEB-12');

1 row created.

SQL> insert into movie
  2  values('M03','Kahaani','12-MARCH-12');

1 row created.

SQL> insert into movie
  2  values('M04','Jodi brakers','02-MARCH-12');

1 row created.

SQL> insert into screen
  2  values('S01','FF',200);

1 row created.

SQL> insert into screen
  2  values('S02','SF',150);

1 row created.

SQL> insert into screen
  2  values('S03','TF',100);

1 row created.

SQL> insert into current1
  2  values('M01','S01','09-MARCH-12','20-MARCH-12');

1 row created.

SQL> insert into current1
  2  values('M04','S02','01-MARCH-12','15-JANUARY-12');

1 row created.

SQL> insert into current1
  2  values('M03','S03','11-MARCH-12','25-MARCH-12');

1 row created.

TABLES DISPLAY

SQL> select * from movie;

MOV MOVIE_NAME           DATE_OF_R
--- -------------------- ---------
M01 PANSINGH TOMAR       10-MAR-12
M02 AGNEEPATH            12-FEB-12
M03 Kahaani              12-MAR-12
M04 Jodi brakers         02-MAR-12

SQL> select * from screen;

SCR LO MAX_CAPACITY
--- -- ------------
S01 FF          200
S02 SF          150
S03 TF          100

SQL> select * from current1;

MOV SCR DATE_OF_A DATE_OF_C
--- --- --------- ---------
M01 S01 09-MAR-12 20-MAR-12
M04 S02 01-MAR-12 15-JAN-12
M03 S03 11-MAR-12 25-MAR-12

A. [1]    Movie ‘Star wars III‘was released in the 7th week of 2005. Find out the date of its
release considering that a movie releases only on Friday.

SQL> select next_day(to_date('1-jan-2005','dd-mon-yy'),'friday')+7*7 from dual;

NEXT_DAY(
---------
25-FEB-05

A. [2]    Get the details of movie that closed on date 15-January-2010.

SQL> select * from movie where movie_id IN(select movie_id from current1 where date_of_closure = '15-JANUARY-2012');

MOV MOVIE_NAME           DATE_OF_R
--- -------------------- ---------
M04 Jodi brakers         02-MAR-12

B. [1]     Create a trigger that checks the ‘screen_id’ must start with ‘S’ whenever an insertion
is tried to be done. Raise a user defined exception if the rule is violated.

SQL> create or replace trigger trigger13
  2  BEFORE insert
  3  ON screen
  4  for each row
  5  DECLARE
  6
  7  BEGIN
  8   if (:new.screen_id LIKE 'S%') then
  9    dbms_output.put_line('Data inserted');
 10   else
 11    RAISE_APPLICATION_ERROR(-20005,'Screen id must start with S');
 12   end if;
 13
 14  END;
 15  /

Trigger created.


SQL> insert into screen
  2  values('001','FF',200);
insert into screen
*
ERROR at line 1:
ORA-20005: Screen id must start with S
ORA-06512: at "SCOTT.TRIGGER13", line 7
ORA-04088: error during execution of trigger 'SCOTT.TRIGGER13'

B. [2]     Create a package for the following :
Create a procedure to print Movie Name where Movie code is been supplied by the
user.

SQL>  create or replace package pk13
  2   IS
  3 
  4   procedure p13(xcode IN varchar2);
  5 
  6   end pk13;
  7  /

Package created.

SQL> create or replace package body pk13
  2  IS
  3
  4  procedure p13(xcode IN varchar2)
  5
  6  IS
  7  mname movie.movie_name%type;
  8
  9  BEGIN
 10
 11   select movie_name into mname from movie where movie_id = xcode;
 12
 13   dbms_output.put_line(chr(10) ||' Movie name : '|| mname);
 14
 15  end;
 16  end pk13;
 17  /

Package body created.

SQL> execute pk13.p13('M01');

 Movie name : PANSINGH TOMAR

PL/SQL procedure successfully completed.

---

*this program is only for reference purpose, actual logic may differ from programmer to programmer