seinfeld-queries.sql

/*****************************************************************************
 For each of the main four characters, return all episodes without them.
*****************************************************************************/
SELECT first_name, last_name, season_no, episode_no, episode_name
FROM `Character`, Episode E JOIN Season USING(season_id)
WHERE 
  character_id IN (1, 2, 3, 4)
  AND character_id NOT IN (
    SELECT character_id
    FROM Portrays
    WHERE episode_id = E.episode_id
  );

/*****************************************************************************
 Find all episodes with Newman.
*****************************************************************************/
SELECT season_no, episode_no, episode_name
FROM Portrays
  JOIN `Character` USING(character_id)
  JOIN Episode USING(episode_id)
  JOIN Season USING(season_id)
WHERE referred_to_as = 'Newman';

/*****************************************************************************
 List all actors which appeared in episode 1, season 1.
*****************************************************************************/
SELECT first_name, last_name
FROM Portrays
  JOIN Actor USING(actor_id)
  JOIN Person USING(person_id)
  JOIN Episode USING(episode_id)
  JOIN Season USING(season_id)
WHERE episode_no = 1 AND season_no = 1;

/*****************************************************************************
 Find the first epiode in which Uncel Leo appears.
*****************************************************************************/
SELECT season_no, episode_no, episode_name
FROM Portrays
  JOIN `Character` USING(character_id)
  JOIN Episode USING(episode_id)
  JOIN Season USING(season_id)
WHERE referred_to_as = 'Uncle Leo'
ORDER BY aired_on LIMIT 1;

WITH PCES AS (
  SELECT *
  FROM Portrays
    JOIN `Character` USING(character_id)
    JOIN Episode USING(episode_id)
    JOIN Season USING(season_id)
)
SELECT season_no, episode_no, episode_name
FROM PCES
WHERE referred_to_as = 'Uncle Leo'
AND aired_on = (
  SELECT MIN(aired_on) FROM PCES WHERE referred_to_as = 'Uncle Leo'
);

/*****************************************************************************
 Create a table storing the number of episodes in each season.
*****************************************************************************/
SELECT season_no, COUNT(*) as episode_count
-- FROM Season, Episode WHERE Season.season_id = Episode.season_id
-- FROM Season JOIN Episode ON Season.season_id = Episode.season_id
-- GROUP BY Season.season_id;
-- FROM Season JOIN Episode USING(season_id)
FROM Season NATURAL JOIN Episode
GROUP BY season_id;