Learn SQL the Hard Way – Exercise 10 Updating Complex Data

Here is my work for Exercise 10.

Exercise 10 code.
Exercise 10 code.

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.

Two pets have their names changed to "Zed's Pet".
Two pets have their names changed to “Zed’s Pet”.

Extra Credit

  • Write an SQL that only renames dead pets I own to “Zed’s Dead Pet”.

    Zed does not have any dead pets so no names will be changed by this command.
    Zed does not have any dead pets so no names will be changed by this command.
  • 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.)

 

Learn SQL the Hard Way – Exercise 9 Updating Data

Here is my work for Exercise 9 Updating Data.

Exercise 9 code
Exercise 9 code

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.

    Changed!
    Changed back! Still hilarious though.
  • 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”.

    Renaming dead pets to "DECEASED".
    Renaming dead pets to “DECEASED”.

    Drogon is now Deceased. :(
    Drogon is now 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.

    Two Young Ones here!
    Two Young Ones here!

Learn SQL the Hard Way – Exercise 8 Deleting Using Other Tables

Here is my work for Exercise 8 Deleting Using Other Tables.

Exercise 8 code
Exercise 8 code

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.

    I added two more pets for myself. Then deleted any pets that were dead.
    I added two more pets for myself. Then deleted any pets that were owned by me and dead. (Only Paws fits the bill.)

    Goodbye Paws :(
    Goodbye Paws 😦
  • 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.

    Deleting any person who owns dead pets.
    Deleting any person who owns dead pets.

    Goodbye Charlie Brown, you were here and gone so quickly...
    Goodbye Charlie Brown, you were here and gone so quickly…
  • 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.

    Deleting any dead pets from person_pet
    Deleting any dead pets from person_pet
    I am no longer connected to Mr. Jingles.
    I am no longer connected to Mr. Jingles.

    This exercise is a good brain workout, like solving mini puzzles!

 

Learn SQL the Hard Way – Exercise 7 Deleting Data

Here is my work for Exercise 7 Deleting Data.

Exercise 7 code
Exercise 7 code

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!

Exercise 7
Exercise 7

Extra Credit

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

    Deleting Fluffy and re-adding Fluffy
    Deleting Fluffy and re-adding Fluffy

 

Learn SQL the Hard Way – Exercise 6 Select Across Many Tables

Here is my work for Exercise 6: Select Across Many Tables.

Exercise 6 code
Exercise 6 code

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.

All the pets Zed owns
All the pets Zed owns

Extra Credit

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

    Using conditions to find all the pets owned by the person named "Stacey"
    Using conditions to find all the pets owned by the person named “Stacey”

    The result, Zed's pet and mine
    The result, Zed’s pets and mine
  • Change the queries to use your person.id instead of the person.name like I’ve been doing.
Selecting by person id instead of name
Selecting by person id instead of name

 

Learn SQL the Hard Way – Exercise 5 Selecting Data

Here is my work for Exercise 5 Selecting Data.

Exercise 5
Exercise 5

In this exercise, we move onto the Read part of CRUD. We make four selections in the exercise code from our database.
Extra Credit

  • Write a query that finds all pets older than 10 years.
  • Write a query to find all people younger than you. Do one that’s older.
  • Write a query that uses more than one test in the WHERE clause using the AND to write it. For example, WHERE first_name = "Zed" AND age > 30.
  • Do another query that searches for rows using 3 columns and uses both AND and OR operators.
    I added the queries below the original exercise.

    Exercise 5 extra credit
    Exercise 5 extra credit
    Extra credit Query results
    Extra credit Query results

    I keep forgetting the semi-colon at the end, a side effect from working through Learn Ruby the Hard Way!

Learn SQL the Hard Way – Exercise 4 Insert Referential Data

In Exercise 4 Insert Referential Data, we add information into our database to make connections between tables.

Exercise 4
Exercise 4

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.