Please complete the following steps to set up some projects dependencies for this Unit.
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.
- 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, includingsqlite3.exe
. - Copy
sqlite3.exe
into yourjeopardy
folder.
- You already have SQLite installed and don't have to do anything!
- Install the
sqlite3
package through your package manager.
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
.
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
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
.
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?
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:
[email protected]
.