Exercise 5: Selecting Data

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:

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.

What You Should See

When you run this with sqlite3 -echo you should get something like the following output:

$ sqlite3 -echo ex3.db < ex5.sql 
SELECT * FROM person;
0|Zed|Shaw|37
SELECT name, age FROM pet;
Fluffy|1000
Gigantor|1
SELECT name, age FROM pet WHERE dead = 0;
Fluffy|1000
SELECT * FROM person WHERE first_name != "Zed";
$

I say "something like" because if you were doing the extra credit this whole time you will have different rows in your database. For example, if you added yourself then you will have some rows listed at the end. In my example above I have nothing returned for the last query because I'm the only person in the person table, and that means no row match the last query's WHERE clause. Study this carefully.

Study Drills

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

Portability Notes

Some databases have additional operators and boolean logic tests, but just stick to the regular ones that you find in most programming languages for now.