[Courses] [Postgresql] Week 5: Even More SQL

[Michelle Murrain]

Hi all,

Here are this week's assignments. Have fun!

Next week is much more PG-specific, we'll talk about PG's permission 
systems and system catalogs.

And please come for the chat tomorrow: 1:00 EST / 18:00 UTC on #courses 
at irc.linuxchix.org

Peace,
Michelle

=== Week 5: Even More SQL ===

Last week, we did a quick overview of creating databases and tables, 
inserting data, and selecting data. SQL is a very rich language, and 
there is a lot to learn about it - it's impossible to cover very much in 
this course.

Quick review:

CREATE DATABASE database_name; - this will create a database called 
"database_name" using the standard template (template1)

CREATE TABLE table_name (field_name1 type, field_name2 type, field_name3 
type); - this will create a table.

INSERT INTO table_name (field_name1, field_name2, field_name3) VALUES 
('foo','bar','235'); - this inserts a row into the table

SELECT * FROM table_name; - this will select all fields and all rows in 
a table.

(A note about SELECT * vs SELECT ALL - there have been changes in the 
SQL syntax of postgresql, and I was incorrect, as many of you already 
found, about the select statement. The proper form to select all fields 
from a table is to use SELECT * not SELECT ALL)

SELECT * FROM table_name where field_name1='foo'; - this will select all 
fields from the table where the value of field_name is equal to 'foo'.

We'll talk more about selects some when we talk about operators later in 
the course.

**Even More SQL**

The thing I will focus on today is relationships, and how to create, and 
select from related tables.

Using relationships make databases very powerful. I mentioned 
normalization last time - that is a way to make sure that the data 
stored in relational databases is easy to retrieve and analyze.

Here's an example of some tables that are related, and how to get data 
out of them.

Lets take an example database for a bookstore. 4 tables: books, 
customers, orders, order details.

Books have the following data:

^ book_id  ^ title ^ author ^ cost |
| 1 | The Golden Compass | Phillip Pullman | 10 |
| 2 | The Bible          |   god           | 15 |
| 3 | Linux Cookbook     | Carla Schroeder | 35 |
| 4 | GIMP               | Akkana Peck     | 35 |
| 5 | The Hobbit         | J.R.R. Tolkien  | 15 |

Customers:

^ customer_id  ^ customer_last  ^ customer_first  ^ city |
|1| Murrain   | Michelle | Shelburne Falls |
|2| Schmo     | Joe      | New York        |
|3| Sunflower | Penelope | San Francisco   |

Orders:

^ order_id  ^ customer_id ^ date |
|   1        |   2        |2008-01-02|
|   2        |   1        |2008-01-02|
|   3        |   3        |2008-01-03|
|   4        |   1        |2008-01-04|

Order details:

^ order_detail_id ^ order_id ^ book_id |
|     1            |    1     |   4    |
|     2            |    1     |   5    |
|     3            |    1     |   2    |
|     4            |    2     |   3    |
|     5            |    2     |   4    |
|     6            |    3     |   1    |
|     7            |    3     |   2    |

order_id in the order_details table is called a "foreign key". As is 
customer_id in the orders table.

So, now the question. How do you reconstruct an order?

SELECT * from customers, books, orders, order details where 
customers.customer_id=orders.customer_id and 
order_details.order_id=orders.order_id and 
books.book_id=order_details.book_id and order_id='1';

You likely wouldn't want to do this - there will be fields in all of 
those tables you wouldn't want to display. In addition, this would spit 
out the customer name and city for each book they ordered. More likely, 
in an application, you'd first reconstruct the basics of the order:

SELECT customer_first,customer_last,city,date from orders,customers 
where orders.customer_id=customers.customer_id and order_id='1';

Then, get the list of books;

SELECT name,author from books, order_details where 
books.book_id=order_details.book_id and order_id='1';

When you use the construction: orders.customer_id=customers.customer_id 
you are asking the database system to match records, and only return 
those that match. This is the same as what is called an "inner join".

You could find all the orders by using:

SELECT customer_first,customer_last,city,date FROM orders INNER JOIN 
customers ON orders.customer_id = customers.order_id;

You could also find all customers, even if they didn't have an order by 
using what is called a "Left join" (or "left outer join":

SELECT customer_first,customer_last,city,date LEFT JOIN orders ON 
orders.customer_id = customers.order_id;

A "right join" or "right outer join" would do the reverse - list orders, 
even if there were no customers.


**Assignments**

You wanted to sponsor a screening of the film "The Golden Compass" at a 
theatre in NY. What SQL statement would you use to find all of the 
people who had bought that book who lived in NY?

Create your own set of related tables. Try inner and outer joins. What 
have you learned.

Extra credit:

What's a cross join, and when would that be useful?
What's a full outer join?

-- 
Michelle Murrain
Coordinator, Nonprofit Open Source Initiative
http://nosi.net

Skype: pearlbear
AIM: pearlbear0