For Exercise 3 Inserting Data, we insert data into the tables we created in the previous exercise.
We insert a row of data into two of the tables. First, we insert Zed Shaw into the person table. He has an id of 0, first_name of Zed, last_name of Shaw, and age of 37. We then insert a Unicorn named Fluffy into the pet table. The boolean value of false is put as 0 for whether Fluffy is dead. Finally, we insert a Robot named Gigantor into the pet table. Poor Gigantor is dead as indicated by the 1 for the “dead” column.
The third data insert does not specify the columns and relies on the implicit order of the columns for inserting the data. This is dangerous, and should be avoided. Why? Because you don’t know which column your statement is accessing, and some databases have unreliable ordering for the columns.
We use sqlite3 and shell redirection “sqlite3 ex3.db < ex2.sql” to create a new database ex3.db. Sqlite 3 is started with a new or existing database, in this case a new one, which gets SQL statements from ex2.sql and is modified accordingly.
Then we, use ex3.sql to insert data into the ex3.db we just created. We use the command -echo so sqlite3 prints out what it is doing.
- Insert yourself and your pets (or imaginary pets like I have).
I added myself a pet dragon!
- 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.
I did not remove the person_pet table.
- 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.
The list of datatypes are here. There are 8 typenames that would result in a TEXT affinity!