TDM 19000: SQL Notes

We only need 2 cores during week 4, when we work with SQL.

Loading the database

%sql sqlite:////anvil/projects/tdm/data/movies_and_tv/imdb2024/imdb2Aug2026.db

Extracting a few rows from the each of the 7 tables

%%sql
SELECT * FROM basics LIMIT 5;
%%sql
SELECT * FROM episode LIMIT 5;
%%sql
SELECT * FROM name LIMIT 5;
%%sql
SELECT * FROM ratings LIMIT 5;
%%sql
SELECT * FROM principals LIMIT 5;
%%sql
SELECT * FROM crew LIMIT 5;
%%sql
SELECT * FROM akas LIMIT 5;

We can see how many rows were in each table, as follows:

%%sql
SELECT COUNT(*) FROM basics LIMIT 5;
%%sql
SELECT COUNT(*) FROM episode LIMIT 5;
%%sql
SELECT COUNT(*) FROM name LIMIT 5;

We can also start to investigate individual people, for instance:

We can look Jack Black up, by his username:

%%sql
SELECT * FROM name WHERE nconst = 'nm0085312' LIMIT 5;

Or we can lookup people by their name directly:

%%sql
SELECT * FROM name WHERE primaryName = 'Jack Black' LIMIT 5;
%%sql
SELECT * FROM name WHERE primaryName = 'Ryan Reynolds' LIMIT 5;
%%sql
SELECT * FROM name WHERE primaryName = 'Hayden Christensen' LIMIT 5;

Community is a show that ran from 2009 to 2015

%%sql
SELECT * FROM basics WHERE tconst = 'tt1439629' LIMIT 5;

Friends is one of Dr Ward’s favorite shows. We can find it here:

%%sql
SELECT * FROM basics WHERE tconst = 'tt0108778' LIMIT 5;

or like this:

%%sql
SELECT * FROM basics WHERE (primarytitle = 'Friends') AND (startYear > 1992) LIMIT 5;

These are the episodes from Friends:

%%sql
SELECT * FROM episode WHERE parentTconst = 'tt0108778' LIMIT 5;

and one particular episode is called "The One Where Chandler Doesn’t Like Dogs"

%%sql
SELECT * FROM basics WHERE tconst = 'tt0583431' LIMIT 5;

That episode is in season 7, episode 8:

%%sql
SELECT * FROM episode WHERE tconst = 'tt0583431' LIMIT 5;

Tobey Maguire was born in 1975

%%sql
SELECT * FROM name WHERE nconst = 'nm0001497' LIMIT 5;

There are a total of 12686436 titles in the basics table.

%%sql
SELECT COUNT(*) FROM basics LIMIT 5;

These are the first 5 people in the name table.

%%sql
SELECT * FROM name LIMIT 5;

These are the first 5 episodes in the episode table.

%%sql
SELECT * FROM episode LIMIT 5;

These are the first 5 people in the crew table.

%%sql
SELECT * FROM crew LIMIT 5;

These are the first 5 people in the principals table.

%%sql
SELECT * FROM principals LIMIT 5;

Only 15 movies have more than 2 million ratings

%%sql
SELECT * FROM ratings WHERE numVotes > 2000000 LIMIT 50;

Let’s find how many people were born in each year (after 1850).

%%sql
SELECT COUNT(*), birthYear FROM name WHERE birthYear > 1850
GROUP BY birthYear LIMIT 200;

There are 743908 titles with rating 7.4 or higher.

%%sql
SELECT COUNT(*) FROM ratings WHERE averageRating >= 7.4 LIMIT 5;

The Family Guy has 508 episodes.

%%sql
SELECT COUNT(*) FROM episode WHERE parentTconst = 'tt0182576' LIMIT 5;

These are five of the films where George Lucas was a director.

%%sql
SELECT * FROM crew WHERE directors = 'nm0000184' LIMIT 5;

Although sometimes he is listed with other directors in a group:

%%sql
SELECT * FROM crew WHERE directors LIKE '%nm0000184%';

We can investigate how many titles started in each year, by grouping things together according to the year that the title started, and by ordering the results according to the year that the title started. The "desc" specifies that we want the results in descending order, i.e., with the largest result first (where "largest" means the "last year", because we are ordering by the years).

%%sql
SELECT COUNT(*), startYear FROM basics
GROUP BY startYear ORDER BY startYear DESC LIMIT 20;

The Family Guy started in 1999 and does not have its ending year in 2022 listed.

%%sql
SELECT * FROM basics WHERE tconst = 'tt0182576' LIMIT 5;

If you want to find the first five Comedies, you can find the ones where the genres are like Comedy, possibly with some other characters before and after:

%%sql
SELECT * FROM basics WHERE genres LIKE '%Comedy%' LIMIT 5;

Similarly, you can find actors and actresses with Audrey in their name:

%%sql
SELECT * FROM name WHERE primaryName LIKE '%Audrey%' LIMIT 5;

Find and print the tconst, averageRating, and number of votes (numVotes) for all movies that received at least 2 million votes.

In a second query (and new cell), use the information you found in the previous query to identify the primarytitle of these movies.

These are the movies with at least 2 million votes:

%%sql
SELECT * FROM ratings WHERE numVotes >= 2000000 LIMIT 50;

and then we can lookup some of their titles, like this:

%%sql
SELECT * FROM basics WHERE tconst = 'tt0111161' OR tconst = 'tt0468569' OR tconst = 'tt1375666' LIMIT 50;

Later, we will learn an easier way to find the titles of the movies, by learning how to JOIN the information in two or more tables.

How many actors have lived to be more than 115 years old? Find the names, birth years, and death years for all actors and actresses who lived more than 115 years.

We use the condition that deathYear-birthYear is bigger than 115

%%sql
SELECT *, deathYear - birthYear FROM name WHERE deathYear-birthYear > 115 AND deathYear IS NOT NULL AND birthYear IS NOT NULL AND deathYear > 0 AND birthYear > 0 AND birthYear <> '' AND deathYear <> '' LIMIT 20;

Now we can use the COUNT function to see that there are 11 such actors who lived more than 115 years.

%%sql
SELECT COUNT(*), deathYear - birthYear FROM name WHERE deathYear-birthYear > 115 AND deathYear IS NOT NULL AND birthYear IS NOT NULL AND deathYear > 0 AND birthYear > 0 AND birthYear <> '' AND deathYear <> '' LIMIT 10;

Use the ratings table to discover how many films have a rating of at least 8 and at least 50000 votes. In a separate cell, show 15 rows with this property.

We can use conditions to ensure that rating and number of votes are large enough, and then we can display 15 such results.

%%sql
SELECT * FROM ratings WHERE (averageRating >= 8) AND (numVotes >= 50000) LIMIT 15;

Then we can use the COUNT function to see that there are 1035 such titles altogether.

%%sql
SELECT COUNT(*) FROM ratings WHERE (averageRating >= 8) AND (numVotes >= 50000) LIMIT 15;

Find the primarytitle of every movie that is over 2 hours long or that started after 1990. Order the result from newest start year to oldest, and limit the output to 15 movies. Make sure startYear and runtimeMinutes are not NULL. After displaying these 15 movies, run the query again in a second cell, but this time only display the number of such movies.

We just add the conditions to the query about the basics table.

%%sql
SELECT * FROM basics WHERE (titleType == 'movie') AND (runtimeMinutes IS NOT NULL) AND (startYear IS NOT NULL) AND startYear > 0 AND runtimeMinutes > 0 AND startYear <> '' AND runtimeMinutes <> '' AND ((runtimeMinutes > 120) OR (startYear > 1990)) ORDER BY startYear DESC LIMIT 15;

Now we can find the total number of such movies, using the COUNT:

%%sql
SELECT COUNT(*) FROM basics WHERE (titleType == 'movie') AND (runtimeMinutes IS NOT NULL) AND (startYear IS NOT NULL) AND startYear > 0 AND runtimeMinutes > 0 AND startYear <> '' AND runtimeMinutes <> '' AND ((runtimeMinutes > 120) OR (startYear > 1990)) ORDER BY startYear DESC LIMIT 15;

This can be a helpful time to mention the concept of order of operations

What movie has the longest primary title? Answer this question using just SQL.

You can read more about SQLite length

We can use the length function, as follows:

%%sql
SELECT *, length(primarytitle) FROM basics WHERE isAdult = 0 ORDER BY length(primarytitle) DESC LIMIT 5;

What actor has the longest name? Answer this question using just SQL.

%%sql
SELECT *, length(primaryName) FROM name ORDER BY length(primaryName) DESC LIMIT 5;

Avoiding NULL values, and making calculations within our SQL queries

We can start by loading the basics table.

%%sql
SELECT * FROM basics LIMIT 5;

and then making sure that we avoid rows in which startYear is NULL and the rows in which endYear is NULL.

%%sql
SELECT * FROM basics WHERE (startYear IS NOT NULL) AND (endYear IS NOT NULL) AND startYear > 0 AND endYear > 0 AND startYear <> '' AND endYear <> '' LIMIT 5;

Then we can calculate the difference between the year that the show ended and the year that the show started.

%%sql
SELECT *, endYear-startYear FROM basics WHERE (startYear IS NOT NULL) AND (endYear IS NOT NULL) AND startYear > 0 AND endYear > 0 AND startYear <> '' AND endYear <> '' LIMIT 5;

We can given this new variable a name. For instance, we might use mylength to refer to the show’s run on TV (in years). Then we can order the results by mylength in years, given in DESC (descending) order.

%%sql
SELECT *, endYear-startYear AS mylength FROM basics WHERE (startYear IS NOT NULL) AND (endYear IS NOT NULL) AND startYear > 0 AND endYear > 0 AND startYear <> '' AND endYear <> '' ORDER BY mylength DESC LIMIT 5;

For instance, this allows us to see that the show Major League Baseball on NBC was running from 1939 to 2026, for a total of 87 years.

How long was Friends on TV?

We can use the query above as a starting point, just looking up Friends as the title, and seeing which shows with that title were on TV after 1993. We see that Friends was on TV for 10 years.

%%sql
SELECT *, endYear-startYear AS mylength FROM basics
WHERE (startYear IS NOT NULL) AND (endYear IS NOT NULL) AND startYear > 0 AND endYear > 0 AND startYear <> '' AND endYear <> '' AND (primaryTitle = 'Friends') AND (startYear > 1993) LIMIT 5;

How many types of titles are there?

Here are a few of the types of titles

%%sql
SELECT titleType FROM basics LIMIT 5;

There are lots of repeats, so we ask for DISTINCT types, i.e., removing the repetitions.

%%sql
SELECT DISTINCT titleType FROM basics LIMIT 5;

and now we can ask for a few more, i.e., we can increase the limit.

%%sql
SELECT DISTINCT titleType FROM basics LIMIT 100;

Looks like there are 11 types altogether: short, movie, tvShort, tvSeries, tvMovie, tvEpisode, tvMiniSeries, tvSpecial, video, videoGame,

%%sql
SELECT COUNT(DISTINCT titleType) FROM basics LIMIT 100;

How many times did each type occur?

We can group the types and count each of them. For instance, there are 5897385 tvEpisodes and there are 581731 movies.

%%sql
SELECT COUNT(*), titleType FROM basics GROUP BY titleType LIMIT 100;

How many times did each genre occur?

At first, we view the genres as tuples, for instance, Action,Adult is a genre (separated by commas). We can do this the same as we did above, just changing the variable type to the variable genres.

%%sql
SELECT COUNT(*), genres FROM basics GROUP BY genres LIMIT 100;

Now we see that there are 2391 such genres:

%%sql
SELECT COUNT(DISTINCT genres) FROM basics LIMIT 5;

BUT they are grouped together, so we would need to work harder, in order to separate them into separate genres.

How many times has The Awakening been used as a title?

The Awakening has been used 191 times as a title

%%sql
SELECT COUNT(*) FROM basics WHERE primarytitle = 'The Awakening' LIMIT 5;

Now we can learn about how to JOIN the results of queries from two or more tables. Using a JOIN is a powerful way to leverage lots of information from a database, but it takes a little time to set things up properly. First, we revisit a question from yesterday, about the movies that received at least 2 million votes. We want to find the titles of those movies.

We will need the basics table and the ratings table.

%%sql
SELECT * FROM basics LIMIT 5;
%%sql
SELECT * FROM ratings LIMIT 5;

Now we join these two tables, and restrict the results to those movies with at least 2000000 votes.

%%sql
SELECT * FROM basics as b JOIN ratings AS r
ON b.tconst = r.tconst WHERE numVotes > 2000000 LIMIT 5;

For instance, in 1940, there was The Great Dictator, with an (average) rating of 8.4. It is a Charlie Chaplin movie that criticizes the dictators of the time, who were becoming very powerful in Europe.

%%sql
SELECT * FROM basics as b JOIN ratings AS r ON b.tconst = r.tconst
 WHERE (b.startYear = 1940) AND (b.titleType = 'movie') ORDER BY r.averageRating DESC LIMIT 5;

How many episodes of Friends were there?

We start by finding the tconst for Friends.

%%sql
SELECT * FROM basics WHERE (primarytitle = 'Friends') AND (startYear > 1992) LIMIT 5;

So now we know that tt0108778 is the parentTconst for Friends.

Now we find the number of episodes per season. To do this, we first find the episodes for Friends.

%%sql
SELECT * FROM episode WHERE parentTconst = 'tt0108778' LIMIT 5;

and then we group them by seasonNumber, to make sure that our results make sense.

%%sql
SELECT COUNT(*), seasonNumber FROM episode WHERE parentTconst = 'tt0108778' GROUP BY seasonNumber;

Season 10 differs from what I expected (I was guessing that there would be 18 episodes), so I checked further on this.

%%sql
SELECT * FROM episode AS e JOIN basics as b ON e.tconst = b.tconst WHERE parentTconst = 'tt0108778' AND seasonNumber = 10 ORDER BY episodeNumber;

OK so they combined The Last One, which is two episodes, into just one listing.

There is probably another pair of episodes that is combined too, because there are 234 episodes listed, although there were actually 236 episodes in the show altogether!

%%sql
SELECT COUNT(*) FROM episode WHERE parentTconst = 'tt0108778';

Who are the actors and actresses in the TV show Friends?

We will need the name table and the crew table.

%%sql
SELECT * FROM name LIMIT 5;
%%sql
SELECT * FROM principals LIMIT 5;

Now we join these two tables together.

%%sql
SELECT * FROM principals AS p JOIN name as n ON p.nconst = n.nconst LIMIT 5;

and now we also join with the basics table, and we focus on the tconst for Friends, which is tt0108778. There are 16 people listed, from the Friends TV show.

%%sql
SELECT * FROM basics as b JOIN principals AS p ON b.tconst = p.tconst
JOIN name as n ON p.nconst = n.nconst
WHERE b.tconst = 'tt0108778' LIMIT 50;

and 14 of them are actors or actresses

%%sql
SELECT * FROM basics as b JOIN principals AS p ON b.tconst = p.tconst
JOIN name as n ON p.nconst = n.nconst
WHERE (b.tconst = 'tt0108778')
AND ((p.category = 'actress') OR (p.category = 'actor')) LIMIT 50;

How many movies has Emma Watson appeared in?

She has appeared in a total of 22 movies.

%%sql
SELECT COUNT(*) FROM basics as b JOIN principals AS p ON b.tconst = p.tconst
 JOIN name as n ON p.nconst = n.nconst
 WHERE (n.primaryName = 'Emma Watson') AND (b.titleType = 'movie') LIMIT 5;

James Caan died in 2022. You can read his Wikipedia page or his IMDB page. What was his highest rated movie?

He appeared in The Godfather, which has a rating of 9.2

%%sql
SELECT * FROM basics as b JOIN principals AS p ON b.tconst = p.tconst
 JOIN name as n ON p.nconst = n.nconst
 JOIN ratings AS r ON b.tconst = r.tconst
 WHERE (n.primaryName = 'James Caan') AND (b.titleType = 'movie') ORDER BY r.averageRating DESC LIMIT 5;

We also have some additional information about SQL posted in our book pages.