SQL

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.

The SELECT clause refers to the columns to use.
FROM clauses give the tables to use.
WHERE clauses give restrictions limiting the rows chosen, generally using boolean conditions.

1 BBC: comments:
Lists are surrounded by parentheses, not square brackets of Python.
There is no assignment, 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.