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

Not all servers for the site may be working.  In the upper left corner change MySQL to Oracle or .... if the first is not working.

On SQL.net:  Go through these tutorials:

1. to get started.  Introduction:  SQL statements have various clauses.  They can be spread over many lines.  Except inside quotes, case does not matter. 
1  BBC:  comments:
Lists are surrounded by parentheses, not square brackets of Python.
There is no assignemnt, so regular mathematical = sign is used to check for equality.
In LIKE expressions, the wildcard % can appear in more than on place. LIKE '%X%' would choose a word that has an X anywhere inside it.
The exercises are good warmups (not to pass in).

2: more BBC more select practice

3:   Nobel; HW includes 3,4

Skip tutorial 4, 5 for this homework (sum and count)

Joins

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.

6:  Join: Look at JOIN syntax, HW includes  7

7:  two joins:  do 8, 9 (10 extra credit)

NOTE:  The notation used for joining three tables in MySQL in the video is not working for the other databases.   Try this notation, with two separate ON clauses:

You can join more than two tables with the FROM clause:

   FROM table1 JOIN table2  ON table1.columnName = table2.columnName
      JOIN table3 ON table2.columnName = table3.columnName

Here table 2 is related to both table 1 and 3.  Join the first two tables with an ON clause, and then join that result to the third table with a JOIN and ON clause.  The dotted form is not needed in the column name if the column name is unique among the tables.

Homework administrative details are here.