This exercise is a continuation of Exercise 9. We use a subquery to generate a table and then use UPDATE to change rows related to the subquery. In the code, we first create a temporary table of pet ids of all pets owned by Zed. We then set all their names to “Zed’s Pet” using UPDATE/SET/WHERE IN.
Write an SQL that only renames dead pets I own to “Zed’s Dead Pet”.
Go to the SQL As Understood By SQLite page and start reading through the docs for CREATE TABLE, DROP TABLE, INSERT,DELETE, SELECT, and UPDATE.
Try out some of the interesting things you find in these docs, and take notes on things you don’t understand so you can research them more later.
Things I don’t understand: CREATE INDEX, CREATE TRIGGER, CREATE VIEW, CREATE VIRTUAL TABLE, DETACH DATABASE, INDEXED BY, DROP TRIGGER, DROP INDEX, DROP VIEW, VACUUM, RELEASE SAVEPOINT.
Many of the commands are related in concepts (For example, what is a savepoint? –> Release savepoint, savepoint.)
For exercise 9, we move to the Update part of CRUD. We change the names for Zed and his pet unicorn. Extra Credit
Use UPDATE to change my name back to “Zed” by my person.id.
Write an UPDATE that renames any dead animals to “DECEASED”. If you try to say they are “DEAD” it’ll fail because SQL will think you mean ‘set it to the column named “DEAD”‘, which isn’t what you want. Since none of the pets are dead, I first update Drogon’s dead status to true. I can then select all of the pets who are dead and rename them to “DECEASED”.
Try using a subquery with this just like with DELETE. I used a subquery to get a table of pet names where the pet age was less than 10. I then used UPDATE to set their names to Young One.
In this exercise, we learn how to delete rows by creating a temporary table through SELECT and using WHERE IN or WHERE NOT IN the temporary table. Extra Credit
Practice writing SELECT commands and then put them in a DELETE WHERE IN to remove those records found. Try deleting any dead pets owned by you.
Do the inverse and delete people who have dead pets. I added a Tabby cat and a new person Charlie Brown. Charlie Brown owns Mr. Whiskers the tabby cat who is unfortunately dead. 😦 I then delete any people who own dead pets, only Charlie Brown fits the bill and is deleted. Finally, I delete the relationship from the person_pet table.
Do you really need to delete dead pets? Why not just remove their relationship in person_pet and mark them dead? Write a query that removes dead pets from person_pet.
Since I didn’t have any dead pets left, I first added Mr. Jingles as my dead pet. (I added him as a pet, and then added our relationship in the person_pet table.) Initially the person_pet table shows that I own three pets, Mr. Jingles, Drogon, and Jaws. Nobody owns Mr. Whiskers.
I then deleted any dead pets from person_pet. The person_pet relationship between me and Mr. Jingles is removed from the table.
This exercise is a good brain workout, like solving mini puzzles!
In this exercise, we select all the dead pets from the table and delete them. In our example, only the robot is deleted. We then add back the robot, this time with a dead status of false. The robot lives!
Go through the output from your run and make sure you know what table is produced for which SQL commands and how they produced that output. 1) The first command selects the name and age columns for the pets that are dead. Only Gigantor shows up in the table.
2) The second command deletes the row where the pet has a 1 for the dead column. Gigantor is removed.
3) The third command selects all the columns from the pet table. We see that Fluffy and Drogon are left and we see all their information. 4) We then insert a row into pet, putting Gigantor back, with false for the dead column.
5) We select all the columns from the pet table and see that Gigantor has been added back. (Revived with ‘dead’ now 0!)
Combine all of ex2.sql through ex7.sql into one file and redo the above script so you just run this one new file to recreate the database. I added all the ex2.sql to ex7.sql into a new file named ex7b.sql!
At the top of this new .sql file, add DROP TABLE commands to drop the tables you’re about to recreate. Now your script can run without you needing to rm ex3.db. You’ll need to go look up the syntax for DROP TABLE. DROP TABLE IF EXISTS tablename; drop table completely removes the named table from the database. The table cannot be recovered.
I added DROP TABLE IF EXISTS at the top of the file for all three of the tables person, pet, and person_pet.
Add onto the script to delete other pets and insert them again with new values. Remember that this is not how you normally update records and is only for the exercise.
In this exercise, I learned how to select from multiple tables. Using the person, pet, and person_pet tables created previously, we can select all the pets owned by one person.
This may be a mind blowing weird way to look at data if you already know a language like Python or Ruby. Take the time to model the same relationships using classes and objects then map it to this setup.
In Ruby, person and pet would both be their own classes. A person has a first_name, last_name, id, and age. A pet has an id, name, age, and ‘dead’ status. To show that a person has-a pet, we would add a person_pet property to a person and specify the value as the pet they have.
Do a query that finds your pets you’ve added thus far.
I’ve only added one pet, Drogon.
Change the queries to use your person.id instead of the person.name like I’ve been doing.
We have added three rows into the person_pet table. Person with id 0 (in the person table) has two pets, the pet with id 0 and the pet with id 1 (in the pets table). Person with id 1 (That’s me!) has one pet, the pet with id 2 in the pets table. In SQL, comments are noted using /* comment in here */, like in CSS. Extra Credit
Add the relationships for you and your pets. Added on lines 8-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? Yes, a pet can be owned by more than one person. Logically it makes sense, because a family pet would be owned by all the people in that family. Similarly, a house can also be owned by more than one person.
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? It is better to put the pets into a separate table from the person table. If we put the pet_id in the person table, a pet cannot have more than one owner. (Since the pet id is a primary key, it cannot be repeated in the column.) We would only be able to link one pet_id with one person_id if the pet_id was in the person table.
For Exercise 2Creating a Multi-Table Database, we create two tables and a relation (a table that links tables together).
The person_pet table is the relation, it is the “link” between person table and the pet table. For example, if person with id 3 (in the person table) has a pet with an id of 10 in the pet table, we would add a row to the person_pet table with person_id of 3 and pet_id of 10.
After writing this code, I ran it by typing sqlite3 ex2.db < ex2.sql on the command line. Then I opened the databse with sqlite3 ex2.db, and then used .schema to dump it. On the command line, a “dot command”, is interpreted by the sqlite3 program. (Other times, lines are just read and passed to the SQLite library for execution.) The .schema command works as below:
.schema ?TABLE? Show the CREATE statements
If TABLE specified, only show tables matching LIKE pattern TABLE.
Since we did not specify the table, all the CREATE statements are shown.
We can confirm the output matches what we typed in the ex2.sql file.
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?
You could create a column in person_pet with the name pet_id, and have integer values for that column. This column would have the id (from the pet table) of the pet that person owns.
An implication of this would be that each person could only have one pet. (since each row would only have one value in the pet_id column)
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? Yes, you can have many rows in the person_pet table. If cat lady has a person_id of 5, we can just add a new row for each cat she owns. For example row 1 would be person_id 5 with pet_id 1, row 2 would be person_id 5 with pet_id 2, row 3 would be person_id 5 and pet_id 3… all the way to pet_id 50.
Create another table for the cars people might own, and create its corresponding relation table. I created a table named cars, it has 3 columns: id, type (although manufacturer might be better), color, and manufacture year. The person_car table has two columns: person_id and car_id. These values correspond to the id values in the person table and the car table.
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. Here is Datatypes in SQLite3.
Affinity is an interesting aspect, with type conversions happening. I like the name BLOB.