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

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

Chapter 4
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:

Source 5: ex3.sql
1  INSERT INTO person (id, first_name, last_name, age)
2      VALUES (0, "Zed", "Shaw", 37);
3  
4  INSERT INTO pet (id, name, breed, age, dead)
5      VALUES (0, "Fluffy", "Unicorn", 1000, 0);
6  
7  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.

4.1 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:

Source 6: ex3.sql Output
  $ 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.

4.2 Extra Credit

  1. Insert yourself and your pets (or imaginary pets like I have).
  2. 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.
  3. 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.

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

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

Take An Online Video Course

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