Homework 2:  Writing SQL queries

Database Systems
Spring, 2020
Due:  February 20 (11:59 pm)

Write SQL queries to access the information in the moma and imdb databases.  You can use the "show" command to get a list of the tables in each database, and the "describe" command to get a list of the columns in each table.

Note:  Don't use subqueries or grouping in your solutions.

moma:

1.  Display the name, gender, and bio of all French artists who died between 1991 and 1995 (inclusive), in alphabetical order by name.

2.  Display the name and bio of all artists whose first name is 'Luis', in order by year of birth.

3.  Display the title, workdate, medium and dimensions of all paintings with a height of at least 350 cm.

4.  Display the artist's name, title, and workdate of all bronze sculptures sculpted by French sculptors.

5.  Who has collaborated with George Maciunas on a print or sculpture?  List the names of the collaborators and whether they collaborated on a print or a sculpture; if they collaborated on both, list them twice.

imdb:

6.  Find all movies whose title is in the form "Once Upon a Time in ..." (such as Once Upon a Time in Hollywood, etc.).  Show the title and year, ordered by year.

7.  Find all TV series with an episode entitled 'Love Thy Neighbor'.  Show the name of the series, and the year that the episode aired.

8.  Find all Action-Adventure-Comedy movies made in the 1990s.  Use the genres table to find them.  Show the title and year of each one, ordered by title.

9.  Find all movies in which John Ford directed John Wayne.  Show the title and year of each movie, ordered by year.

10.  Find the episode title, episode year, and the role played by Virginia Gregg for all episodes of “Dragnet 1967” in which she appeared.


11-12.  Two additional queries of your choice.  (For each of these, use comment lines to express in English what the query is doing.)  Try to think of interesting queries.

Test your queries by running them on MySQL.  Please write each of your 12 queries in a separate .sql file, named p1.sql, p2.sql, etc.

Use handin to submit a folder containing the .sql files containing your queries.  The course number is 311 and the assignment number is 2.