Learn SQL the Hard Way – Exercise 10 Updating Complex Data

Here is my work for Exercise 10.

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

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.

• 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

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

  

  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!

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

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 owned by me and dead. (Only Paws fits the bill.)

  

  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.

  

  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

  

  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

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

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

 

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

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

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”

  

  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

 

Learn SQL the Hard Way – Exercise 5 Selecting Data

Here is my work for Exercise 5 Selecting Data.

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

  

  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

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.

 

 

Learn SQL the Hard Way – Exercise 2 Creating a Multi-Table Database

For Exercise 2 Creating a Multi-Table Database, we create two tables and a relation (a table that links tables together).

Exercise 2 code

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.

The CREATE statements

We can confirm the output matches what we typed in the ex2.sql file.

Extra Credit

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

  

  Three tables and two relation tables.

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

Learn SQL the Hard Way Exercise 1

I’m still working on the final exercise for Learn Ruby the Hard Way Exercise. However, I thought I would start learning a bit of SQL as well. I finished Codecademy’s SQL course. As usual, Codecademy’s format made it super easy to go through many concepts in a short amount of time. However, with the handholding, it’s definitely easy to have a false sense of understanding. I decided to try Zed’s new book, Learn SQL the Hard Way.
In Exercise 1, you download sqlite 3 and create a simple table.

Creating a table with SQL

CREATE TABLE is a clause. A clause, also referred to as a command, performs a specific task in SQL. By convention, clauses are all upper-case.
person is the table name. This is the table that the command is applied to.
Lines 2-6 contain parameters. These are passed to the clause as arguments. Here, we have a list of column names and the associated data type.
First column in the table has a name of id, and contains integers. This is a special column because a PRIMARY KEY column: 1) does not have null values 2) has unique values (every value in this column is unique, there are no duplicates).
Second column name is first_name, and contains text values. Third column name is last_name and contains text values. The age column contains integers. And finally, the email column contains text values.
To run this, we go into Terminal and type sqlite3 ex1.db < ex1.sql, this creates a file ex1.db, we can check the existence by using the ls command. (This lists all the files in the directory.)
Note: SQL is not case-sensitive, so if we change capitals to lowercase in this file, it will still work.