Skip to content

bloominstituteoftechnology/SQL

Folders and files

NameName
Last commit message
Last commit date

Latest commit

 

History

3 Commits
 
 
 
 

Repository files navigation

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?
    • 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.

Releases

No releases published

Packages

No packages published