Learn SQL The Hard Way A Learn Code The Hard Way Book

[next] [prev] [prev-tail] [tail] [up]

Chapter 3
Exercise 2: Creating A Multi-Table Database

Creating one table isn't too useful. I want you to now make 3 tables that you can store data into:

Source 3: ex2.sql
1  CREATE TABLE person (
2      id INTEGER PRIMARY KEY,
3      first_name TEXT,
4      last_name TEXT,
5      age INTEGER
6  );
7  
8  CREATE TABLE pet (
9      id INTEGER PRIMARY KEY,
10      name TEXT,
11      breed TEXT,
12      age INTEGER,
13      dead INTEGER
14  );
15  
16  CREATE TABLE person_pet (
17      person_id INTEGER,
18      pet_id INTEGER
19  );

In this file you are making tables for two types of data, and then "linking" them together with a third table. People call these "linking" tables "relations", but very pedantic people with no lives call all tables "relations" and enjoy confusing people who just want to get their jobs done. In my book, tables that have data are "tables", and tables that link tables together are called "relations".

There isn't anything new here, except when you look at person_pet you'll see that I've made two columns: person_id and pet_id. How you would link two tables together is simply insert a row into person_pet that had the values of the two row's id columns you wanted to connect. For example, if person contained a row with id=20 and pet had a row with id=98, then to say that person owned that pet, you would insert person_id=20, pet_id=98 into the person_pet relation (table).

We'll get into actually inserting data like this in the next few exercises.

3.1 What You Should See

You run this SQL script in the same way as before, but you specify ex2.db instead of ex1.db. As usual there's no output, but this time I want you to open the database and use the .schema command to dump it:

Source 4: ex2.sql Output
  sqlite> .schema
  CREATE TABLE person (
      id INTEGER PRIMARY KEY,
      first_name TEXT,
      last_name TEXT,
      age INTEGER
  );
  CREATE TABLE person_pet (
      person_id INTEGER,
      pet_id INTEGER
  );
  CREATE TABLE pet (
      id INTEGER PRIMARY KEY,
      name TEXT,
      breed TEXT,
      age INTEGER,
      dead INTEGER
  );
  sqlite>

The "schema" should match what you typed in.

3.2 Extra Credit

  1. In these tables I made a 3rd relation table to link them. How would you get rid of this relation table person_pet and put that information right into person? What's the implication of this change?
  2. If you can put one row into person_pet, can you put more than one? How would you record a crazy cat lady with 50 cats?
  3. Create another table for the cars people might own, and create its corresponding relation table.
  4. Search for "sqlite3 datatypes" in your favorite search engine and go read the "Datatypes In SQLite Version 3" document. Take notes on what types you can use and other things that seem important. We'll cover more later.

3.3 Portability Notes

Databases have a lot of options for specifying the keys in these relations, but for now we'll keep it simple.

[next] [prev] [prev-tail] [front] [up]

Take An Online Video Course

I will be offering this book as a video course soon. Stay tuned.