Think Summer: Day 1 Notes — 2023
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;sqlWe 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;sqlWe 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;sqlFriends 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;sqlWe 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;sqlThe Family Guy premiered in 1999 and ended in 2022.
%%sql
SELECT * FROM titles WHERE title_id = 'tt0182576' LIMIT 5;sqlThere are a total of 8064259 titles in the titles table.
%%sql
SELECT COUNT(*) FROM titles LIMIT 5;sqlOnly 3 movies have more than 2 million ratings
%%sql
SELECT * FROM ratings WHERE votes > 2000000 LIMIT 5;sqlLet’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