SQL

See the Lambda page on Relational Databases for more information. (Note that page is for PostgreSQL, but the SQL information is valid here.)

SQLite

SQLite is a popular, simple SQL database.

You can launch into a memory-only DB by running:

sqlite3

You can specify a persistent DB file with:

sqlite3 mydatabase.db

When you get to the prompt, you can type .help for commands.

Some helpful ones:

• .mode column turn on column output for SELECT.
• .header on turn on column headers for SELECT.
• .read filename execute the SQL in filename.
• .open dbname re-open a memory-only DB to a persistent file.
• .quit exit SQLite. (Note that if you're using a memory-only DB, all data is lost at this point.)

Another potentially useful third-party tool is DB Browser for SQLite, a GUI-based SQLite viewer and data manipulator that can also run SQL queries.

Create a Music Database

Make an albums table to hold album information:

CREATE TABLE album (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    title VARCHAR(128) NOT NULL,
    release_year INTEGER
);

Make an artists table to hold artist information:

CREATE TABLE artist (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    name VARCHAR(128) NOT NULL
);

Exercises, Day 1

Before you begin, look at the queries in setup.sql to get a hint as to the column names in the following tables. We'll use setup.sql later.

• Create a table called track that holds information about a music track. It should contain:

  • An autoincrementing id
  • A title (of type VARCHAR, probably)
  • A reference to an id in table album (the album the track is on). This should be a foreign key.

• Create a table called artist_album to connect artists to albums. (Note that an artist might have several albums and an album might be created by multiple artists.)

  • Use foreign keys for this, as well.

• Run the queries in the file setup.sql. This will populate the tables.

  • Fix any errors at this point by making sure your tables are correct.
  • DROP TABLE can be used to delete a table so you can recreate it with CREATE TABLE.

• Write SQL SELECT queries that:

  • Show all albums.
  • Show all albums made between 1975 and 1990.
  • Show all albums whose names start with Super D.
  • Show all albums that have no release year.

• Write SQL SELECT queries that:

  • Show all track titles from Super Funky Album.

  • Same query as above, but rename the column from title to Track_Title in the output.

  • Select all album titles by Han Solo.

  • Select the average year all albums were released.

  • Select the average year all albums by Leia and the Ewoks were released.

  • Select the number of artists.

  • Select the number of tracks on Super Dubstep Album.

Exercises, Day 2

Create a database for taking notes.

• What are the columns that a note table needs?

• If you have a timestamp field, how do you auto-populate it with the date?

• A note should have a foreign key pointing to an author in an author table.

• What columns are needed for the author table?

Write queries that:

• Insert authors to the author table.

• Insert notes to the note table.

• Select all notes by an author's name.

• Select author for a particular note by note ID.

• Select the names of all the authors along with the number of notes they each have. (Hint: GROUP BY.)

• Delete authors from the author table.

  Note that SQLite doesn't enforce foreign key constrains by default. You have to enable them by running PRAGMA foreign_keys = ON; before your queries.

  • What happens when you try to delete an author with an existing note? In my case nothing, since I enabled foreign keys before trying this. I don't think I'd want to prevent this behaviour ^^
  • How can you prevent this?

Submit a file notes.sql with the queries that build (CREATE TABLE/INSERT) and query the database as noted above.