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