/*
Examples using the movie databases
Description and downloads available from https://www.imdb.com/interfaces/
The schemas are defined in movies.sql
*/

-- * Some general observations about the data -----------------------------------

-- How many people are there in the movie database?
SELECT COUNT(*) FROM Name_basics;

-- How many titles are there in the movie database?
SELECT COUNT(*) FROM Title_basics;

-- How many rows in the table that connects people and movies?
SELECT COUNT(*) FROM Title_principals;

-- What kind of titles are there in the movie database?
SELECT DISTINCT titleType FROM Title_basics;

-- How many movie titles are there in the movie database?
SELECT COUNT(*) FROM Title_basics WHERE titleType='movie';

-- How many movies per year are there?
SELECT startYear,COUNT(*)
FROM Title_basics
WHERE titleType='movie'
GROUP BY startYear;


-- * Queries involving a single table -------------------------------------------

-- Is this type of query quick or slow?
SELECT * FROM Name_basics WHERE primaryName = 'Tom Cruise';

-- nconst is the primary key, so this should be quick
SELECT * FROM Name_basics WHERE nconst>='nm0000120' AND nconst<='nm0000140';

SELECT * FROM Name_basics WHERE nconst<='nm0000099';

-- Can the primary key index be used for this type of queries?
SELECT * FROM Name_basics WHERE nconst LIKE 'nm00000%';

-- Find 'Mission: Impossible' movies and TV series, order by start year
SELECT tconst,startYear,endYear,titleType,originalTitle
FROM Title_Basics
WHERE titleType IN ('movie','tvSeries')
  AND originalTitle LIKE 'Mission: Impossible%';
ORDER BY startYear
-- Gives an idea why tconst was added

-- Find people with the same last name as me
SELECT nconst,primaryName
FROM Name_basics
WHERE primaryName LIKE '%Hallgren';

-- * Queries involving more than one table --------------------------------------

-- Find movies and TV series in which 'Frida Hallgren' has appeared
SELECT t.startYear,t.endYear,t.titleType,t.originalTitle
FROM Title_Principals as p,Name_basics AS n,Title_Basics AS t
  -- ^ This cartesian product has 9e6*5.5e6*32e6 = 1.6e21 rows
WHERE t.titleType IN ('movie','tvSeries')
  AND n.primaryName='Frida Hallgren'
  AND n.nconst = p.nconst
  AND t.tconst = p.tconst ;

-- Which well-known TV shows lasted 10 years or more?
-- (TV shows that have not ended yet are not included)
SELECT startYear,endYear,endYear-startYear,originalTitle
FROM Title_basics NATURAL JOIN Title_ratings
WHERE titleType='tvSeries'
  AND endYear-startYear>=10
  AND numVotes>=5000
ORDER BY endYear-startYear;
