Creating Tables

In the introduction I said that you can do "Create Read Update Delete" operations to the data inside tables. How do you make the tables in the first place? By doing CRUD on the database schema, and the first SQL statement to learn is CREATE:

1
2
3
4
5
6

CREATE TABLE person (
    id INTEGER PRIMARY KEY,
    first_name TEXT,
    last_name TEXT,
    age INTEGER
);

You could put this all on one line, but I want to talk about each line so it's on multiple ones. Here's what each line does:

ex1.sql:1

The start of the "CREATE TABLE", which gives the name of the table as person. You then put the fields you want inside parenthesis after this setup.

ex1.sql:2

An id column, which will be used to exactly identify each row. The format of a column is NAME TYPE, and in this case I'm saying I want an INTEGER that is also a PRIMARY KEY. Doing this tells SQLite3 to treat this column special.

ex1.sql:3-4

A first_name and a last_name column, which are both of type TEXT.

ex1.sql:5

An age column that is just a plain INTEGER.

ex1.sql:6

Ending of the list of columns with a closing parenthesis and then a semi-colon ';' character.

Creating a Multi-Table Database

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

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19

CREATE TABLE person (
    id INTEGER PRIMARY KEY,
    first_name TEXT,
    last_name TEXT,
    age INTEGER
);

CREATE TABLE pet (
    id INTEGER PRIMARY KEY,
    name TEXT,
    breed TEXT,
    age INTEGER,
    dead INTEGER
);

CREATE TABLE person_pet (
    person_id INTEGER,
    pet_id INTEGER
);

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.

Inserting Data

You have a couple tables to work with, so now I'll have you put some data into them using the INSERT command:

1
2
3
4
5
6
7

INSERT INTO person (id, first_name, last_name, age)
    VALUES (0, "Zed", "Shaw", 37);

INSERT INTO pet (id, name, breed, age, dead)
    VALUES (0, "Fluffy", "Unicorn", 1000, 0);

INSERT INTO pet VALUES (1, "Gigantor", "Robot", 1, 1);

In this file I'm using two different forms of the INSERT command. The first form is the more explicit style and most likely the one you should use. It specifies the columns that will be inserted, followed by VALUES, then the data to include. Both of these lists (column names and values) go inside parentheses and are separated by commas.

The second version on line 7 is an abbreviated version that doesn't specify the columns and instead relies on the implicit order in the table. This form is dangerous since you don't know what column your statement is actually accessing, and some databases don't have reliable ordering for the columns. It's best to only use this form when you're really lazy.

Insert Referential Data

In the last section you filled in some tables with people and pets. The only thing that's missing is who owns what pets and that data goes into the person_pet table like this:

1
2

INSERT INTO person_pet (person_id, pet_id) VALUES (0, 0);
INSERT INTO person_pet VALUES (0, 1);

Again I'm using the explicit format first, then the implicit format. How this works is I'm using the id values from the person row I want (in this case, 0) and the id from the pet rows I want (again, 0 for the Unicorn and 1 for the Dead Robot). I then insert one row into the person_pet relation table for each "connection" between a person and a pet.

Exercise Challenge

1. Make another database but other INTEGER and TEXT fields for other things a person might have.
2. In these tables I made a third 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?
3. 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?
4. Create another table for the cars people might own, and create its corresponding relation table.
5. Search for "sqlite3 datatypes" in your favorite search engine and go read the Data types In SQLite Version 3 document. Take notes on what types you can use and other things that seem important. We'll cover more later.
6. Insert yourself and your pets (or imaginary pets like I have).
7. If you changed the database in the last exercise to not have the person_pet table, then make a new database with that schema and insert the same information into it.
8. Go back to the list of data types and take notes on what format you need for the different types. For example, note how many ways can you write TEXT data.
9. Add the relationships for you and your pets.
10. Using this table, could a pet be owned by more than one person? Is that logically possible? What about the family dog? Wouldn't everyone in the family technically own it?
11. Given the above, and given that you have an alternative design that puts the pet_id in the person table, which design is better for this situation?

Further Study

Read the documentation for the SQLite3 CREATE command at https://sqlite.org/lang_createtable.html and then review as many other CREATE statements as you can. You should also read INSERT documentation at https://sqlite.org/lang_insert.html which should lead you to many other pages to read.