Skip to content

Intermediate SQL

Here you can access every table used in the course. To access each table, you will need to specify the cinema schema in your queries (e.g., cinema.reviews for the reviews table.

Take Notes

Add notes about the concepts you've learned and SQL cells with queries you want to keep.

Add your notes here

Spinner
DataFrameas
df
variable
-- Add your own queries here
SELECT *
FROM cinema.reviews
LIMIT 5

Explore Datasets

Use the descriptions, films, people, reviews, and roles tables to explore the data and practice your skills!

  • Which titles in the reviews table have an IMDB score higher than 8.5?
  • Select all titles from Germany released after 2010 from the films table.
  • Calculate a count of all movies by country using the films table.
SQL Syntax used in Final Intermediate course:
    
FINAL Exercise for Intermediate SQL				
"All together now
It's time to use much of what you've learned in one query! This is good preparation for using SQL in the real world where you'll often be asked to write more complex queries since some of the basic queries can be answered by playing around in spreadsheet applications.

In this exercise, you'll write a query that returns the average budget and gross earnings for films each year after 1990 if the average budget is greater than 60 million.

This will be a big query, but you can handle it!"				
				
Select the release_year for each film in the films table, filter for records released after 1990, and group by release_year.				
SELECT release_year				
FROM films				
GROUP BY release_year				
HAVING release_year >1990;				
				
Modify the query to include the average budget aliased as avg_budget and average gross aliased as avg_gross for the results we have so far.				
				
SELECT release_year, AVG(budget) AS avg_budget, AVG(gross) AS avg_gross				
FROM films				
WHERE release_year > 1990				
GROUP BY release_year;				
				
Modify the query once more so that only years with an average budget of greater than 60 million are included.				
SELECT release_year, AVG(budget) AS avg_budget, AVG(gross) AS avg_gross				
FROM films				
--WHERE release_year > 1990				
GROUP BY release_year				
-- Modify the query to see only years with an avg_budget of more than 60 million				
HAVING AVG(budget)>60000000;				
				
Finally, order the results from the highest average gross and limit to one.				
SELECT release_year, AVG(budget) AS avg_budget, AVG(gross) AS avg_gross				
FROM films				
WHERE release_year > 1990				
GROUP BY release_year				
HAVING AVG(budget) > 60000000				
-- Order the results from highest to lowest average gross and limit to one				
ORDER BY avg_gross DESC				
LIMIT 1;