Art Institute of Chicago

Art Institute of Chicago Logo

Problem to Solve

The Art Institute of Chicago is home to hundreds of thousands of artworks and other objects of cultural significance.

Help museum patrons and workers filter through the Institute’s vast collections by creating several different views of its data.

Distribution Code

Download the distribution code

Log into cs50.dev, click on your terminal window, and execute cd by itself. You should find that your terminal window’s prompt resembles the below:

$

Next execute

wget https://cdn.cs50.net/sql/2025/spring/sections/4/chicago.zip

in order to download a ZIP called chicago.zip into your codespace.

Then execute

unzip chicago.zip

to create a folder called chicago. You no longer need the ZIP file, so you can execute

rm chicago.zip

and respond with “y” followed by Enter at the prompt to remove the ZIP file you downloaded.

Now type

cd chicago

followed by Enter to move yourself into (i.e., open) that directory. Your prompt should now resemble the below.

chicago/ $

If all was successful, you should execute

ls

and see a database named chicago.db.

Review

Remember that a view is really just a saved query! To create a view, use the following syntax:

CREATE VIEW "NAME" AS
SELECT ...

where "NAME" is the name of your view and ... is the rest of your query.

If curious, here’s the full syntax for views from the SQLite documentation.

Specification

Write SQL statements to create views of the Institute’s vast collection of artworks and objects. In particular, create views for each of the following cases:

  1. A photography exhibition is in the works. Help the museum find possible photographs to exhibit.
    • In 1.sql, create a view named photographs that includes all columns from the artworks table. However, only include artworks with type “Photograph.”
  2. A registrarial assistant needs to identify paintings for which the year column is unknown (i.e., NULL).
    • In 2.sql, create a view named undated that includes all columns from the artworks table. However, only include artworks with an unknown year and with a type of “Painting.”
  3. A curator wants a quick way to review all artworks belonging to the modern or contemporary art departments.
    • In 3.sql, create a view named modern_contemporary that includes all columns from the artworks table. However, only include artworks whose department is “Modern Art” or “Contemporary Art”.
  4. Museum administrators want to see how large each department’s collection is, sorted from the largest to the smallest.
    • In 4.sql, create a view named department_counts that shows the total number of artworks in each department.
  5. An intern wants to see which artist(s) created each piece, along with its title and creation year, so they can label items for a new gallery display.
    • In 5.sql, create a view named artworks_with_artists with three columns: one for the artist’s name, one for the artwork’s title, and one for the artwork’s year.
  6. Museum staff need a listing of all artworks created by living artists (i.e., those with no recorded death_date), so they can plan a special outreach event for them.
    • In 6.sql, create a view named living_artists_pieces that shows only those artworks whose creator’s death_date is NULL and whose birth_date is on or after 1920. Include three columns: the artist’s name, the artwork’s title, and the artwork’s year.
  7. The museum wants to celebrate the artists who’ve contributed the most pieces to their collection.
    • In 7.sql, create a view named prolific_artists that shows each artist’s name and the number of artworks they’ve created. Sort the table by the number of artworks created, most to least.

Challenge Questions

  1. A research fellow is studying collaboration trends and needs to see which artworks were produced by more than one artist.
    • In a SQL file of your choice, create a view named multi_artist_artworks that shows each artwork and the number of distinct artists linked to that artwork. Only include rows for artworks that have 2 or more different artists.
  2. The conservation team wants to prioritize outreach to living artists who have multiple pieces in the collection, to discuss restoration and loan requests.
    • In a SQL file of your choice, create a view named prolific_living_artists that shows the total number of artworks each living artist has in the museum. Consider an artist to be living if death_date is NULL and they have a birth_date on or after 1920.