Intermediate SQL

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

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.

-- Find the title and year of films from the 1990 or 1999 SELECT title, release_year FROM films WHERE (release_year = 1990 OR release_year = 1999) AND (language = 'English' OR language = 'Spanish') AND (gross > 2000000)

films database to get the title and release_year of all Spanish-language films released between 1990 and 2000 (inclusive) with budgets over $100 million.

SELECT title, release_year FROM films WHERE (language = 'Spanish') AND (release_year BETWEEN 1990 AND 2000) AND (budget > 100000000)

SELECT name FROM people -- Select names that don't start with A WHERE name NOT LIKE 'A%';

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) AND -- Filter to English-language films (language = 'English') AND -- Narrow it down to G, PG, and PG-13 certifications (certification IN ('G', 'PG', 'PG-13'))

Select the title of every film that doesn't have a budget associated with it and use the alias no_budget_info.

#-- List all film titles with missing budgets SELECT title AS no_budget_info FROM films WHERE budget IS NULL;

Count the number of films with a language associated with them and use the alias count_language_known.

-- Count the number of films we have language data for SELECT COUNT(*) AS count_language_known FROM films WHERE language IS NOT NULL;

-- Find the latest release_year SELECT MAX(release_year) AS latest_year FROM films

-- Find the duration of the shortest film SELECT MIN(duration) AS shortest_film FROM films

Calculate the average budget from the films table, aliased as avg_budget_thousands, and round to the nearest thousand.

Calculate the percentage of people who are no longer alive and alias the result as percentage_dead.

-- Calculate the percentage of people who are no longer alive SELECT (COUNT(deathdate) * 100.0) / COUNT(*) AS percentage_dead FROM people;

Find how many decades the films table covers by using MIN() and MAX() and alias as number_of_decades.

-- Find the number of decades in the films table SELECT (MAX(release_year) - MIN(release_year)) / 10.0 AS number_of_decades FROM films;

GROUP BY single fields GROUP BY is a SQL keyword that allows you to group and summarize results with the additional use of

aggregate functions. For example, films can be grouped by the certification and language before

counting the film titles in each group. This allows you to see how many films had a particular

certification and language grouping.