QUESTION 4

Question 4
Screen(screen_id,location ,seating_cap)
Movie(movie_id,movie_name,date_of_release)
Current(screen_id,movie_id,date_of_arrival,date_of_closure)
Value of screen_id must start with letters ‘S’.
Attribute location can be any one of ‘ FF’, ‘SF’, or ‘TF’.
Date_of_arrival must be less than date_of_closure.

CREATE TABLE

Screen

create table screen

(

screen_id varchar2(3) primary key,

location varchar2(3) not null check(location in('FF','SF','TF')),

          seating_cap number(3) not null check(seating_cap>0),

constraint sid check(screen_id like ('S%')),

);

Movie

Create table movie

(

movie_id varchar2(3) primary key,

          movie_name varchar2(20) unique,

date_of_release date not null

);

Current1

create table current1

(

screen_id varchar2(3) references SCREEN(screen_id),

          movie_id varchar2(6) references MOVIE(movie_id),

          date_of_arrival date not null,

          date_of_closure date not null,

          constraint dtc check(date_of_arrival<date_of_closure)

);

Solve the following queries based on the above schema:

1. Get the name of movie which has run the longest in the multiplex so far.
   
   select a.movie_name from movie a,current1 b  where (date_of_closure - date_of_arrival) =(select max(date_of_closure - date_of_arrival) from current1) and a.movie_id=b.movie_id;
   
2. Get the average duration of a movie on screen number ‘S4’.
   
   select screen_id, avg(date_of_closure-date_of_arrival) avg_duration from current1 where screen_id='S3' group by screen_id;
   
3. Get the details of movie that closed on date 24-november-2004.
   
   select a.movie_id, a.movie_name,a.date_of_release from current1 b, movie a where date_of_closure='27-Oct-10' and a.movie_id=b.movie_id;
   
   
   
4. 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.
   
   select * from movie where TO_CHAR(date_of_release,'ww')=3 and movie_name=’hulchul’;
   
5. Get the full outer join of the relations screen and current.
   
   select * from screen a, current1 b where a.screen_id(+)=b.screen_id(+);