Supreme Court Cases

Let’s write a faster database search than our last attempt, this time making use of SQL.

In a file called index.sql, write commands to create indexes on columns that users might frequently search in our database.

Before You Begin

Log into code.cs50.io, 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/2023/winter/labs/1/cases.zip

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

Then execute

unzip cases.zip

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

rm cases.zip

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

Now type

cd cases

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

cases/ $

Execute ls by itself, and you should see a few files:

cases.db  index.sql

If you run into any trouble, follow these same steps again and see if you can determine where you went wrong!

Specification

In index.sql, document the SQL command you used to create an index in cases.db. Remember that you can run the command by opening cases.db in SQLite, using sqlite3 cases.db—just be sure your terminal is in the proper folder!

How to Test

Here’s how to test your code manually, using SQLite’s built-in timer:

  • Open the database with sqlite3 cases.db. Before you run your command to create an index, type .timer on. Now, run a query that might use your index (if, of course, it was applied!). After your query results, you should see: 
    Run Time: real 3.772 user 0.078781 sys 0.064676

    with different timings. Pay attention to the “real” time!

  • Next, apply your index and run the same query from before. You should see an (ideally!) faster runtime than before.
  • If you’d like to check that your index has been applied, try typing .schema at the sqlite prompt. You should see the command you used to create the index.

Acknowledgements

Data courtesy of Washington University in St. Louis, licensed under CC BY-NC 3.0 US. Provided dataset is a subset of the original.