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;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;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';Friends has 235 episodes.
%%sql
SELECT COUNT(*) FROM episodes WHERE show_title_id = 'tt0108778';Downton Abbey has 52 episodes.
%%sql
SELECT COUNT(*) FROM episodes WHERE show_title_id = 'tt1606375';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';Tobey Maguire is a member of 154 crews.
%%sql
SELECT COUNT(*) FROM crew WHERE person_id = 'nm0001497';Jennifer Aniston is a member of 992 crews.
%%sql
SELECT COUNT(*) FROM crew WHERE person_id = 'nm0000098';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;There are 1166146 titles with 50000 or fewer ratings.
%%sql
SELECT COUNT(*) FROM ratings WHERE votes <= 50000;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';Mark Hamill was born in 1951.
%%sql
SELECT * FROM people WHERE person_id = 'nm0000434';Jennifer Aniston was born in 1969.
%%sql
SELECT * FROM people WHERE person_id = 'nm0000098';