Exercise 3: 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:

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 parenthesis 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.

What You Should See

I'm going to reuse the ex2.sql file from the previous exercise to recreate the database so you can put data into it. This is what it looks like when I run it:

$ sqlite3 ex3.db < ex2.sql
$ sqlite3 -echo ex3.db < ex3.sql 
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 the first line I just make ex3.db from the ex2.sql file. Then I add the -echo argument to sqlite3 so that it prints out what it is doing. After that the data is in the database and ready to query.

WARNING: One more time for the win. On Windows PowerShell the < (redirect input from file) is not implemented. Every time you see me do sqlite3 ex1.db < ex3.sql you should ignore me and do sqlite3 ex3.db -init ex3.sql. This will load your file and drop you right into the sqlite3 shell. Type .quit to exit the shell after you're done playing with the results.

Study Drills

• Insert yourself and your pets (or imaginary pets like I have).

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

• Go back to the list of data types and take notes on what format you need for the different types. For example, how many ways can you write TEXT data.

Portability Notes

As I mentioned in the last exercise, database vendors tend to add lock-in to their platforms by extending or altering the data types used. They'll subtly make their TEXT columns a little different here, or their DATETIME columns are called TIMESTAMP and take a different format. Watch out for this when you use a different database.