Movie Theatre Database

List show time of a movie. Input includes movie title and a date.

a. First check whether movie title matches any movie, the title can be part of the full movie title.

b. Find all show time on that day for movies matching the input title. Print cinema name, start time, format, and whether it is full. Order results by movie title, cinema, format, and start time.

Answer:

set serveroutput on;

create or replace procedure list_show_times (v_title in varchar, v_release_date in date)

is

r_count number;

cin_name varchar(20);

start_time varchar(20);

m_format varchar(20);

full_sh varchar(20);

Begin

select count(*) into r_count from movie, show_times, cinema

where movie.movie_id = show_times.movie_id and movie_title like ('%'||v_title||'%') and movie_release_date = v_release_date;

if r_count > 0 then

dbms_output.put_line('No movie found');

else

select cinema_name, show_start_time, movie_format, full_show into cin_name, start_time, m_format, full_sh

from cinema, movie, show_times

where movie.movie_id = show_times.movie_id and cinema.cinema_id = show_times.cinema_id and movie_title like ('%'||v_title||'%')

and movie_release_date = v_release_date;

end if;

end;