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.

Note: When using sample databases such as those that contain course data, you have read-only access. You can run queries, but cannot make any changes such as adding, deleting, or modifying the data (e.g., creating tables, views, etc.).

Take Notes

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

Add your notes here

SQL SELECT Statement: Used to retrieve data from one or more tables.

Filtering Data: The WHERE clause allows you to filter records based on specific conditions.

Limiting Results: The LIMIT clause restricts the number of rows returned.

Joining Tables: Use JOIN to combine rows from two or more tables based on a related column.

Aggregations: Functions like COUNT(), AVG(), and SUM() help summarize data.

Sorting Results: The ORDER BY clause sorts the result set by one or more columns.

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

Find in the hidden cell below some exercises to explore the data and practice your skills:

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.
Spinner
DataFrameas
df1
variable
-- 1. Titles in the reviews table with an IMDB score higher than 8.5
SELECT f.title
FROM cinema.reviews r
JOIN cinema.films f ON r.film_id = f.id
WHERE r.imdb_score > 8.5;

-- 2. All titles from Germany released after 2010 from the films table
SELECT title
FROM cinema.films
WHERE country = 'Germany'
  AND release_year > 2010;

-- 3. Count of all movies by country using the films table
SELECT country, COUNT(*) AS movie_count
FROM cinema.films
GROUP BY country
ORDER BY movie_count DESC;