Warning

This book is new. If you'd like to go through it, then join the Learn Code Forum to get help while you attempt it. I'm also looking for feedback on the difficulty of the projects. If you don't like public forums, then email help@learncodethehardway.org to talk privately.

Exercise 40: Reading with SQL

Out of the CRUD matrix you only know "Create". You can create tables and you can create rows in those tables. I'll now show you how to "Read," or in the case of SQL, SELECT:

1
2
3
4
5
6
7
SELECT * FROM person;

SELECT name, age FROM pet;

SELECT name, age FROM pet WHERE dead = 0;

SELECT * FROM person WHERE first_name != "Zed";

Here's what each of these lines does:

ex5.sql:1
This says "select all columns from person and return all rows." The format for SELECT is SELECT what FROM tables(s) WHERE (tests), and the WHERE clause is optional. The '*' (asterisk) character is what says you want all columns.
ex5.sql:3
In this one I'm only asking for two columns, name and age, from the pet table. It will return all rows.
ex5.sql:5
Now I'm looking for the same columns from the pet table, but I'm asking for only the rows where dead = 0. This gives me all the pets that are alive.
ex5.sql:7
Finally I'm selecting all columns from person just like in the first line, but now I'm saying only if they do not equal "Zed". That WHERE clause is what determines which rows to return or not.

Select Across Many Tables

Hopefully you're getting your head around selecting data out of tables. Always remember this: SQL ONLY KNOWS TABLES. SQL LOVES TABLES. SQL ONLY RETURNS TABLES. TABLES. TABLES. TABLES. TABLES! I repeat this in this rather crazy manner so that you will start to realize that what you know in programming isn't going to help. In programming you deal in graphs, and in SQL you deal in tables. They're related concepts, but the mental model is different.

Here's an example of where it becomes different. Imagine you want to know what pets Zed owns. You need to write a SELECT that looks in person and then "somehow" finds my pets. To do that you have to query the person_pet table to get the id columns you need. Here's how I'd do it:

1
2
3
4
5
6
SELECT pet.id, pet.name, pet.age, pet.dead
    FROM pet, person_pet, person
    WHERE
    pet.id = person_pet.pet_id AND
    person_pet.person_id = person.id AND
    person.first_name = "Zed";

Now this looks huge, but I'll break it down so you can see it's simply crafting a new table based on data in the three tables and the WHERE clause:

ex6.sql:1
I only want some columns from pet, so I specify them in the select. In the last exercise you used '*' to say "every column" but that's going to be a bad idea here. Instead, you want to be explicit and say what column from each table you want, and you do that by using table.column as in pet.name.
ex6.sql:2
To connect pet to person I need to go through the person_pet relation table. In SQL that means I need to list all three tables after the FROM.
ex6.sql:3
Start the WHERE clause.
ex6.sql:4
First, I connect pet to person_pet by the related id columns pet.id and person_pet.id.
ex6.sql:5
AND I need to connect person to person_pet in the same way. Now the database can search for only the rows where the id columns all match up, and those are the ones that are connected.
ex6.sql:6
AND I finally ask for only the pets that I own by adding a person.first_name test for my first name.

Exercise Challenge

  1. Write a query that finds all pets older than 10 years.
  2. Write a query to find all people younger than you. Do one that's older.
  3. 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.
  4. Do another query that searches for rows using 3 columns and uses both AND and OR operators.
  5. 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.
  6. Do a query that finds your pets you've added thus far.
  7. Change the queries to use your person.id instead of the person.name like I've been doing.
  8. 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.

Further Study

Continue to deep dive into SQLite3 by reading the documentation for the SELECT command at https://sqlite.org/lang_select.html and also read the documentation for the EXPLAIN QUERY PLAN feature at https://sqlite.org/eqp.html. If you ever wonder why SQLite3 did something, EXPLAIN is your answer.

Pre-order Learn More Python The Hard Way

When you pre-order Learn More Python The Hard Way, you'll receive the Python 3 Edition as it's being created. All files are DRM free and you can download them to your computer for offline viewing. Digital Download Only! You do not get a physical book.

$29.00

Buy the Online Course

Or, you can read Learn More Python the Hard Way for free right here, video lectures not included.

Other Buying Options

Other buying options coming soon.

No content available for this exercise. You can view all available downloads at your customer account page.