1.Write the query below to show the movie title that has the longest runtime in each year from 2000 to 2010

select to_char(movie_release_date, 'yyyy') as year, movie_name, movie_runtime 

from cinema.movie 

where extract(year from movie_release_date) between 2000 and 2010 and (extract(year from movie_release_date), movie_runtime) in 

(select extract(year from movie_release_date), max(movie_runtime) from cinema.movie group by extract(year from movie_release_date))

order by year;

 

1.Write the query below to show the movie title that has the longest runtime in each year from 2000 to 2010

select to_char(movie_release_date, 'yyyy') as year, movie_name, movie_runtime 

from cinema.movie 

where extract(year from movie_release_date) between 2000 and 2010 and (extract(year from movie_release_date), movie_runtime) in 

(select extract(year from movie_release_date), max(movie_runtime) from cinema.movie group by extract(year from movie_release_date))

order by year;

 

2.(ASSESSED) Write a query using correlated subquery to get the same output as No. 1.

1.Run the execution plan and take the screenshot of the output 

2.Compare the query cost of No. 1 and No. 2. Which query is more efficient and why?

 

3.The query below shows the total amount spent by customer named 'Aaron Anthon' in 'Lunar Drive-In' cinema

 

select c.cust_id, sum(sale_total_price)

from cinema.customer c join cinema.sale s on c.cust_id = s.cust_id 

join cinema.movie_cinema mc on s.movie_id = mc.movie_id and s.cinema_id = mc.cinema_id 

join cinema.cinema ci on ci.cinema_id = mc.cinema_id 

where cinema_name = 'Lunar Drive-In' and cust_name = 'Aaron Anthon';

4..Write another query that produces the same output as No.3 using subqueries to avoid join operation 

1.Run the execution plan and take the screenshot of the output 

2.Compare the query cost of No. 3 and No. 4. Can we conclude that using subquery is always more efficient than using join operation?

 

5.This query is to display the customer name, movie_name and the date of purchase for all customers who live in STONY CREEK (Note: it uses cross join)

select cust_name, movie_name, sale_date

from cinema.customer c 

cross join cinema.sale s 

cross join cinema.movie m 

where cust_suburb = 'STONY CREEK';

 

6.Write another query to get the same output using inner join.

1.Run the execution plan and take the screenshot of the output 

2.Compare the query cost of No. 5 and No. 6. Which query is more efficient and why?.

 

For Task 7-10, we will need a much bigger table to illustrate the query performance. Create a table using the script below

create table cinema.sample as select * from 
((select review_id, review_score, review_date from cinema.review) a
cross join (select * from cinema.sale) b);

once the table is created, check that you have 4665633 rows

select count(*) from cinema.sample;

7. Using sample table and movie table, this query returns all movie name that have been reviewed between 1995 and 2010

select distinct movie_name
from cinema.sample s join cinema.movie m on s.movie_id = m.movie_id
where extract(year from review_date) between 1995 and 2010
order by movie_name;

8.(ASSESSED) Write another query to get the same output as above using "with" statement 

1.Run the execution plan and take the screenshot of the output

2.Compare the query cost of No. 7 and No. 8. Which query is more efficient and why?

REVIEW Preview id review_score De * review_date F movie_id CUSTOMER * cust_id cust_name * cust_address . cust_suburb cust_postcode . * cust_state . cust_contact number cust_gender cust_dob HI- Psale_id F F * SALE sale_date cust_id *movie_id sale_number_of_tickets sale_unit_price sale_total_price * cinema_id mode_id staff_no STAFF P staff_no * staff name -IH staff_gender P * # MOVIE movie_id movie_name * movie release_date movie_spoken_language. movie_runtime - ||+ MOVIE CINEMA PF movie_id PF cinema_id P mode_id * 04 BOOKING MODE mode_description MOVIE GENRE PF movie_id PF genre_id MOVIE COMPANY PF movie_id PF company_id DO CINEMA * P cinema id cinema_name cinema_address HI- * cinema_suburb cinema_postcode cinema_state +H P . GENRE genre_id genre_description PRODUCTION COMPANY Do HHp company_id company_name company address CINEMA_RATING P rating id rating_score rating_date cinema_id