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;