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 5Explore Datasets
Use the descriptions, films, people, reviews, and roles tables to explore the data and practice your skills!
- Which titles in the
reviewstable have an IMDB score higher than 8.5? - Select all titles from Germany released after 2010 from the
filmstable. - Calculate a count of all movies by country using the
filmstable.
Multiple conditions with AND, OR, WHERE, BETWEEN
SELECT *
FROM films
WHERE release_year > 2000
AND release_year < 2010
AND language = 'German';SELECT title, release_year
FROM films
WHERE release_year BETWEEN 1990 AND 2000
AND budget > 100000000
-- Amend the query to include Spanish or French-language films
AND (language = 'Spanish' OR language = 'French');-- Calculate the average budget rounded to the thousands
SELECT ROUND(AVG(budget), -3) AS avg_budget_thousands
FROM filmsIn the real world, every SQL query starts with a business question. Then it is up to you to decide how to write the query that answers the question. Let's try this out.
Which release_year had the most language diversity?
Take your time to translate this question into code. We'll get you started then it's up to you to test your queries in the console.
"Most language diversity" can be interpreted as COUNT(DISTINCT ___). Now over to you.
SELECT DISTINCT release_year, COUNT(DISTINCT language) AS most_lang_diversity
FROM films
GROUP BY release_year
ORDER BY most_lang_diversity ASCUNION exercise
-- Select all fields from economies2015
SELECT *
FROM economies2015
-- Set operation
UNION
-- Select all fields from economies2019
SELECT *
FROM economies2019
ORDER BY code, year;SELECT code, year, unemployment_rate
FROM economies
WHERE income_group = 'High income'
-- Set theory clause
UNION ALL
SELECT country_code, year, fertility_rate
FROM populations
ORDER BY code, year DESC,unemployment_rate DESC;-- Return all cities with the same name as a country
SELECT name
FROM cities
INTERSECT
SELECT name
FROM countries