REEU: Day 1 Notes — 2023

Loading the database

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

Extracting a few rows from the each of the 6 tables

%%sql
SELECT * FROM titles LIMIT 5;
sql
%%sql
SELECT * FROM episodes LIMIT 5;
sql
%%sql
SELECT * FROM people LIMIT 5;
sql
%%sql
SELECT * FROM ratings LIMIT 5;
sql
%%sql
SELECT * FROM crew LIMIT 5;
sql
%%sql
SELECT * FROM akas LIMIT 5;
sql

We can ask for more than 5 rows too. For instance, here we ask for 15 rows instead of 5 rows.

%%sql
SELECT * FROM titles LIMIT 15;
sql

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

%%sql
SELECT COUNT(*) FROM titles LIMIT 5;
sql
%%sql
SELECT COUNT(*) FROM episodes LIMIT 5;
sql
%%sql
SELECT COUNT(*) FROM people LIMIT 5;
sql

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

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

Say Anything is one of Dr Ward’s favorite movies. We can find it here:

%%sql
SELECT * FROM titles WHERE title_id = 'tt0098258' LIMIT 5;
sql

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

%%sql
SELECT * FROM titles WHERE (primary_title = 'Friends') AND (premiered > 1992) LIMIT 5;
sql

We can investigate how many titles premiered in each year, by grouping things together according to the year that the title premiered, and by ordering the results according to the year that the title premiered. 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(*), premiered FROM titles
GROUP BY premiered ORDER BY premiered DESC LIMIT 20;
sql

The Family Guy premiered in 1999 and ended in 2022.

%%sql
SELECT * FROM titles WHERE title_id = 'tt0182576' LIMIT 5;
sql

Tobey Maguire was born in 1975

%%sql
SELECT * FROM people WHERE person_id = 'nm0001497' LIMIT 5;
sql

Brent Spiner was born in 1949:

%%sql
SELECT * FROM people WHERE person_id = 'nm0000653' LIMIT 5;
sql

Brent Spiner was on the crew for 75 movies and TV shows (this may include individual episodes).

%%sql
SELECT COUNT(*) FROM crew WHERE person_id = 'nm0000653' LIMIT 5;
sql

Jennifer Aniston was born in 1969:

%%sql
SELECT * FROM people WHERE name = 'Jennifer Aniston' LIMIT 5;
sql

There are a total of 8064259 titles in the titles table.

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

There were 8107 people from IMDB born in 1976:

%%sql
SELECT COUNT(*) FROM people WHERE born = 1976 LIMIT 5;
sql

We can get the number of people born in every year from 1976 onwards:

%%sql
SELECT COUNT(*), born FROM people WHERE born >= 1976 GROUP BY born LIMIT 5;
sql

Here are some tvSeries that premiered since the year 2000:

%%sql
SELECT * FROM titles WHERE (premiered >= 2000) AND (type = 'tvSeries') LIMIT 5;
sql

These are the first 5 people in the people table.

%%sql
SELECT * FROM people LIMIT 5;
sql

These are the first 5 episodes in the episodes table.

%%sql
SELECT * FROM episodes LIMIT 5;
sql

These are the first 5 people in the crew table.

%%sql
SELECT * FROM crew LIMIT 5;
sql

Only 3 movies have more than 2 million ratings

%%sql
SELECT * FROM ratings WHERE votes > 2000000 LIMIT 5;
sql

Let’s find how many people were born in each year (after 1850). This is part of Question 1.

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

The Family Guy has 374 episodes.

%%sql
SELECT COUNT(*) FROM episodes WHERE show_title_id = 'tt0182576' LIMIT 5;
sql

These are five of the films where George Lucas was on the crew.

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