Use subqueries and grouping to write some queries on the moma and
imdb databases. Put your solution to each one in a separate
.sql file, as you did for homework 2.
Perform queries 1-5 without using joins. That is, you may
have nested select statements, but no select statement may refer
to more than one table in its from clause.
1. Display the artist's name, title, and workdate of all
bronze sculptures sculpted by French sculptors.
3. Find the objectid, title, classification, medium,
height, width, and area of the painting(s) with the greatest
area. (note: for some artworks, the height and/or width may
be null; you need to exclude these.) (Write two versions of
this query: one using a subquery with an ANY or ALL
operator, and one using the MAX operator.)
4. Find all movies in which John
Ford directed John Wayne. Show the title and year of each
movie, ordered by year.
5. Find all TV series with an episode entitled 'Love Thy Neighbor'. Show the name of the series, and the year that the episode aired.
For queries 5-10, you may use joins, subqueries, or both.
6. For each artwork classification,
find the number of artworks in that classification. Show the
top 15 classifications, ordered from most artworks to least.
7. For each artwork classification, find the average number
of artists per artwork, in alphabetical order by classification.
8. List the pairs of artists who have collaborated on at
least 100 artworks, ordered from most artworks to least.
9. Make a table of the 15 most common movie genres.
For each genre, give the name of the genre and the number of
movies in that genre, in descending order by number of movies.
10. Find the directors that have directed at least 10
science fiction movies. Show the ncode, name, and number of
science fiction movies for each one, in descending order by number
of movies directed.