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 integrations 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
-- 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.
Practice with COUNT() As you've seen, COUNT(*) tells you how many records are in a table. However, if you want to count the number of non-missing values in a particular field, you can call COUNT() on just that field.
Let's get some practice with COUNT()! You can look at the data in the tables throughout these exercises by clicking on the table name in the console.
Count the total number of records in the people table, aliasing the result as count_records.
-- Count the number of records in the people table SELECT COUNT(*) AS count_records FROM people;
Count the number of records with a birthdate in the people table, aliasing the result as count_birthdate.
-- Count the number of birthdates in the people table SELECT COUNT(birthdate) AS count_birthdate FROM people;
Count the records for languages and countries in the films table; alias as count_languages and count_countries. -- Count the records for languages and countries represented in the films table SELECT COUNT(films.language) AS count_languages, COUNT(films.country) AS count_countries FROM films;
SELECT DISTINCT
Often query results will include many duplicate values. You can use the DISTINCT keyword to select the unique values from a field.
This might be useful if, for example, you're interested in knowing which languages are represented in the films table. See if you can find out what countries are represented in this table with the following exercises.
Return the unique countries represented in the films table using DISTINCT. -- Return the unique countries from the films table SELECT DISTINCT country FROM films;
Return the number of unique countries represented in the films table, aliased as count_distinct_countries. -- Count the distinct countries from the films table SELECT COUNT(DISTINCT films.country) AS count_distinct_countries FROM films;
Combining filtering and selecting
Time for a little challenge. So far, your SQL vocabulary from this course includes COUNT(), DISTINCT, LIMIT, WHERE, OR, AND, BETWEEN, LIKE, NOT LIKE, and IN. In this exercise, you will try to use some of these together. Writing more complex queries will be standard for you as you become a qualified SQL programmer.
As this query will be a little more complicated than what you've seen so far, we've included a bit of code to get you started. You will be using DISTINCT here too because, surprise, there are two movies named 'Hamlet' in this dataset!
Follow the instructions to find out what 90's films we have in our dataset that would be suitable for English-speaking teens.
Count the unique titles from the films database and use the alias provided. Filter to include only movies with a release_year from 1990 to 1999, inclusive. Add another filter narrowing your query down to English-language films. Add a final filter to select only films with 'G', 'PG', 'PG-13' certifications.
-- Count the unique titles SELECT COUNT(DISTINCT title) AS nineties_english_films_for_teens FROM films -- Filter to release_years to between 1990 and 1999 WHERE (release_year BETWEEN 1990 AND 1999) -- Filter to English-language films AND (language = 'English') -- Narrow it down to G, PG, and PG-13 certifications AND (certification IN ('G', 'PG', 'PG-13'))