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

 

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s