Think Summer: Subquery notes about how to use the results of one query within another query — 2022

%sql sqlite:////anvil/projects/tdm/data/movies_and_tv/imdb.db

We can find the information from the episodes of Friends:

%%sql
SELECT * FROM episodes WHERE show_title_id = 'tt0108778' LIMIT 5;

and then we can use this query as a subquery, to select the season numbers from the episodes of Friends:

%%sql
SELECT s.season_number
FROM (SELECT * FROM episodes WHERE show_title_id = 'tt0108778') AS s LIMIT 5;

Here is another example. We can find the movies from 1989:

%%sql
SELECT * FROM titles WHERE (premiered = '1989') AND (type = 'movie') LIMIT 5;

and then we can use this query as a subquery, to find the average length of movies from 1989, given in minutes:

%%sql
SELECT AVG(s.runtime_minutes)
FROM (SELECT * FROM titles WHERE (premiered = '1989') AND (type = 'movie')) AS s LIMIT 5;