Skip to content

Latest commit

 

History

History
139 lines (92 loc) · 4.08 KB

video1.mkd

File metadata and controls

139 lines (92 loc) · 4.08 KB
Raw

Files for Unit 4

Please complete the following steps to set up some projects dependencies for this Unit.

1. Download the Jeopardy database and Python scripts

Please either clone this repository and navigate to the jeopardy directory, or download these files individually to a jeopardy directory. We'll use them throughout Unit 4.

2. Install SQLite

On Windows

  • Download the SQLite command line tools from http://www.sqlite.org/download.html, using the sqlite-tools link in the "Precompiled Binaries For Windows" section. This will download a directory containing several executables, including sqlite3.exe.
  • Copy sqlite3.exe into your jeopardy folder.

On OSX

  • You already have SQLite installed and don't have to do anything!

On Linux

  • Install the sqlite3 package through your package manager.

3. Create a SQLite database from the database dump

The jeopardy folder contains a file called jeopardy.dump. This is a SQL database dump. We need to turn this database dump into a SQLite database.

Now that you have SQLite installed, create a database from jeopardy.dump by executing the following command at the terminal:

sqlite3 jeopardy.db < jeopardy.dump

This creates a sqlite database called jeopardy.db.

4. Test your setup

At a terminal, start sqlite3 using the jeopardy.db database by running:

sqlite3 jeopardy.db

That should start a sqlite prompt that looks like this:

SQLite version 3.6.12
Enter ".help" for instructions
Enter SQL statements terminated with a ";"
sqlite>

At the sqlite prompt, type .tables and press enter. That should display a list of the tables in this database:

sqlite> .tables
category  clue
sqlite>

Quit sqlite by executing:

sqlite> .quit

5. Practice making SQL queries

Look at the layout of the Jeopardy database

Start sqlite with:

sqlite3 jeopardy.db

Then look at the tables in your database by running the following commands at the sqlite prompt:

  • .table, which will list the tables in the database
  • .schema category, which will show the organization of the category table, including the fields and the data types they store.

It should look like this:

sqlite> .schema category
CREATE TABLE "category" (
    id INTEGER PRIMARY KEY,
    name VARCHAR(255) NOT NULL,
    game INTEGER NOT NULL,
    boardPosition INTEGER
    );

This tells us that the category table has 4 fields: id, name, game, and boardPosition.

Read more about SQL

If you don't have prior experience with SQL, please read these short documents for an introduction:

Then, check your understanding:

  • What tables are in the database?
  • What is a schema?
  • What fields are in the category table?
  • What fields are in the clue table?

Query the database with SELECT

Try running the following queries from the sqlite prompt:

  • SELECT * FROM category;
  • SELECT NAME FROM category;
  • SELECT * FROM clue;
  • SELECT text, answer, value FROM clue;
  • SELECT text, answer, value FROM clue LIMIT 10;

Explore the category and clue tables with your own SELECT queries.

Then, check your understanding:

  • What does * mean in the above queries?
  • What does the LIMIT SQL keyword do?
  • Does case matter when making SQL queries?

Questions? Please don't hesitate to reach out to the author (me, Jessica!) at: jesstess@mit.edu.