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

 

Advertisements

2 thoughts on “Learn SQL the Hard Way – Exercise 10 Updating Complex Data

    • Hi Johanna!

      It was originally available on the same site as all the other learn code the hard way online books. Looks like Zed has put it into beta mode and it is now only available through email subscription. It was just a work in progress and not complete when I was going through it. (I pretty much did each exercise as it appeared!)

      Codecademy and code school have some good introduction to SQL courses you can do, or you can try Sql zoo (http://sqlzoo.net/).

      Cheers!

      Like

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