Tables - Mon, Nov 18

Joining Tables

Two tables & are joined by a comma to yield all possible combinations of rows from & rows from .

CREATE TABLE dogs...;
CREATE TABLE parents...;

-- Select the parents of curly-furred dogs
SELECT parent FROM parents, dogs WHERE child = name AND fur = "curly";

For a full example of joins, see the lecture slides.

Joining a Table with Itself

Two tables may share a column name; dot expressions and aliases disambiguate column values.

SELECT [cols] FROM [tbl] WHERE [expr] ORDER BY [expr];

[table] is a comma-separated list of table name with optional aliases. Using the parents table:

-- Select all pairs of siblings
SELECT a.child AS first, b.child AS second
	FROM parents AS a, parents AS b
	WHERE a.parent = b.parent AND a.child < b.child;

The AND clause at the end ensures that a pair is not duplicated. The table parents here is being joined with itself, so we distinguish it from itself by assigning one copy of it to a and another copy to b. Then, we use dot expressions to refer to the columns of the specific tables.

By default, the two columns would be named child and child:1. Instead, we use AS first and AS second to simply rename the columns to user-friendly names.

For a full example, see the lecture slides.

Joining Multiple Tables

Yield all combinations of rows from each pair of tables.

CREATE TABLE grandparents AS
	SELECT a.parent AS grandog, b.child AS granpup
		FROM parents AS a, parents AS b
		WHERE b.parent = a.child;

Select all grandparents with the same fur as their grandchildren:

SELECT grandog FROM grandparents, dogs AS c, dogs AS d
	WHERE grandog = c.name AND
		  granpup = d.name AND
		  c.fur = d.fur;

Numerical Expressions

SELECT [cols] FROM [tbl] WHERE [expr] ORDER BY [expr];

Where [cols] can be of the form [expr] AS [name], [expr] AS name, ....

Each [expr] can combine values using arithmetic operators, transform them using functions like abs or round, or compare values using comparison operators.

String Expressions

Strings can be combined:

sqlite> SELECT "hello," || " world";
hello, world

Strings can be manipulated:

sqlite> CREATE TABLE phrase AS SELECT "hello, world" AS s;
sqlite> SELECT substr(s, 4, 2) || substr(s, instr(s, " ") + 1, 1) FROM phrase;
low