Databases and SQL
A database is a collecton of tables. Each table has named column
headings and any number of rows of data associated with the headings.
SQL means structured query language. It is a fairly standardized
way to interact with databases, extracting parts of them.
Go to sqlzoo.net, where we go through some of its Tutorial Sections for descriptions,
examples, and exercises. See notes on this page for some of the
tutorials:
0 to get started
1 (note the syntax summary at the bottom),
1b will be hw! (except 3a),
Skip tutorials 2, 3
4. A join of two table with no additional conditions generates a
large table with
mostly useless data: It has all the columns in each table, and
each row
for the first table is combined with each row of the second table, so
if one table has three row and the other has two rows, the join has
3*2=6 rows.
As in the web examples, the two tiny tables below share a column
containing country abbreviation codes.
leader
| who |
place
|
| Obama |
USA
|
Biden
|
USA |
| Harper |
CAN
|
country
| id |
name |
CAN
|
Canada
|
USA
|
United
States
|
Ths is the full join of the two tables:
leader
JOIN country
| who |
place |
id |
name |
| Obama |
USA |
CAN |
Canada
|
| Obama |
USA |
USA
|
United
States
|
| Biden |
USA |
CAN |
Canada
|
| Biden |
USA |
USA
|
United
States
|
Harper
|
CAN |
CAN |
Canada |
| Harper |
CAN |
USA |
United
States |
Only with an ON clause picking out correspondences beween
columns in one table and another, do you get something more meaningful:
SELECT who, name
FROM leader JOIN country
ON (place=id)
This only selects from the rows where place is the same as id:
| who |
name
|
| Obama |
United
States
|
Biden
|
United
States |
| Harper |
Canada
|
Joins on a pair of columns mostly make sense if there is a unique row
in one table for a particular value used in an ON clause equality. In this case each country
abbreviation corresponds to a unique country.
5. More on joins - working up to three tables. 1a has
total garbage as its initial text. ?? Work in class EXCEPT for 3e
- which is on the SQL hw.
Syntax extension needed: You can join more than two tables with table1
join table2 join table3 .... In the ON clause you must AND
together several equality conditions (one less than the number of
tables, so with three tables you need two equality conditions).