1.Write the query below to show the movie title that has the longest runtime in each year...
Question:
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?
Expert Answer:
I see you have a set of SQL tasks that seems to be part of an assignment or practice exercise focused on writing queries and understanding query optimization To address this Ill provide SQL queries an... View the full answer
