Art Institute of Chicago

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 indexes that will speed up their queries.

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/5/chicago-optimized.zip

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

Then execute

unzip chicago-optimized.zip

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

rm chicago-optimized.zip

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

Now type

cd chicago-optimized

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

chicago-optimized/ $

If all was successful, you should execute

ls

and see a database named chicago.db.

Review

To create an index, use the following syntax:

CREATE INDEX name
ON table (column);

where name is your index’s name, table is the table on which you’d like to create your index, and column the column you’d like to include in your index.

You can drop (i.e., remove) an index by using:

DROP INDEX name

It’s possible to make indexes with more than one column, too:

CREATE INDEX name
ON table (column0, column1, ...);

Specification

Write SQL statements to create indexes that will help museum researchers and visitors find the artwork they’re looking for even faster.

Part 1

Create an index to help researchers look up artwork by type.
- A sample query looks like this:
```
 SELECT * FROM "artworks"
 WHERE "type" = 'Painting';
```
Create an index to help researchers look up artists by name.
- A sample query looks like this:
```
 SELECT * FROM "artists"
 WHERE "name" = 'Sakai Hoitsu';
```
Create an index to help researchers look up artwork by year.
- A sample query looks like this:
```
 SELECT * FROM "artworks"
 WHERE "year" = 2000;
```

Part 2

Drop any indexes you’ve created prior to these practice problems.

Create an index to help researchers look up artwork titles by year.
- A sample query looks like this:
```
 SELECT "title" FROM "artworks"
 WHERE "year" = 2000;
```
Create an index to help researchers look up artist names by birth year.
- A sample query looks like this:
```
 SELECT "name" FROM "artists"
 WHERE "birth_date" = 1511;
```

Part 3

Drop any indexes you’ve created prior to these practice problems.

Create an index to help researchers look up the titles of artworks created by an artist with a given name.

A sample query looks like this:

 SELECT "title" FROM "artworks"
 WHERE "id" IN (
  SELECT "artwork_id" FROM "created"
  WHERE "artist_id" IN (
      SELECT "id" FROM "artists"
      WHERE "name" = 'Claude Monet'
  )
 );