Think Summer: Project 1 — 2023

How many people (from the people table) were born in each year? In which year were the most people born?

There are plenty of erroneous years in the born variable, so we limit our results to years after 1750 (we chose this arbitrarily):

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

The most births in any year is 8882 births in the year 1980:

%%sql
SELECT COUNT(*), born FROM people WHERE born >= 1750 GROUP BY born HAVING COUNT(*) > 8800;
sql

How many episodes did the show Sopranos have? Pick another TV show; how many episodes did this show have?

The Sopranos has 86 episodes.

%%sql
SELECT COUNT(*) FROM episodes WHERE show_title_id = 'tt0141842';
sql

Friends has 235 episodes.

%%sql
SELECT COUNT(*) FROM episodes WHERE show_title_id = 'tt0108778';
sql

Downton Abbey has 52 episodes.

%%sql
SELECT COUNT(*) FROM episodes WHERE show_title_id = 'tt1606375';
sql

How many crews include George Lucas? Pick your own favorite director, actor, or actress: How many crews include that person?

George Lucas is a member of 761 crews.

%%sql
SELECT COUNT(*) FROM crew WHERE person_id = 'nm0000184';
sql

Tobey Maguire is a member of 154 crews.

%%sql
SELECT COUNT(*) FROM crew WHERE person_id = 'nm0001497';
sql

Jennifer Aniston is a member of 992 crews.

%%sql
SELECT COUNT(*) FROM crew WHERE person_id = 'nm0000098';
sql

How many titles have 1 million or more ratings? How many titles have 50,000 or fewer ratings?

There are 48 titles with 1 million or more ratings.

%%sql
SELECT COUNT(*) FROM ratings WHERE votes >= 1000000;
sql

There are 1166146 titles with 50000 or fewer ratings.

%%sql
SELECT COUNT(*) FROM ratings WHERE votes <= 50000;
sql

In what year was the actress Gal Gadot born? How about Mark Hamill? How about your favorite actor or actress?

Gal Gadot was born in 1985.

%%sql
SELECT * FROM people WHERE person_id = 'nm2933757';
sql

Mark Hamill was born in 1951.

%%sql
SELECT * FROM people WHERE person_id = 'nm0000434';
sql

Jennifer Aniston was born in 1969.

%%sql
SELECT * FROM people WHERE person_id = 'nm0000098';
sql