CS61A: Tables

%load_ext sql
%config SqlMagic.style = '_DEPRECATED_DEFAULT'
%sql sqlite:///cs61a.db

Joining Tables

• Queries may span over multiple tables, so we have to join multiple tables into one.
  • We must modify the FROM clause within a select statement to join multiple tables.
• Two tables A & B are joined by a comma (or JOIN) to form all combiations of a row from A and a row from B.

%%sql
CREATE TABLE dogs AS
    SELECT "ace" AS name, "long" AS fur UNION
    SELECT "bella", "short" UNION
    SELECT "charlie", "long" UNION
    SELECT "daisy", "long" UNION 
    SELECT "ellie", "short" UNION
    SELECT "finn", "curly" UNION
    SELECT "ginger", "short" UNION
    SELECT "hank", "curly";

 * sqlite:///cs61a.db
Done.





[]

• Using the parents and the dogs table, we may now manipulate data pertaining to the family structure of dogs, and their fur types at the same time.
• Ex: Select the names of the parents of curly-furred dogs.

%%sql
SELECT parent FROM parents, dogs WHERE child = name AND fur = "curly";

 * sqlite:///cs61a.db
Done.

parent
ellie
daisy

• When we joined the parents and dogs table, we made a new table where each row of parents is joined with every row of dogs, forming a (length of parents) * (length of dogs) long table.
  • To solve problems that require joining tables together, we must find ways to relate the rows of the two tables together and find the relevant entires that match.
  • Match a row from table A with table B.

Implicit & Explicit Join Syntax

• Implicit Syntax: Place all conditions for joining the two tables in the WHERE clause.
  • JOIN will just create rows of all possible combinations of table A and table B’s rows
• Explict Syntax: Use the syntax FROM ____ JOIN ____ ON ____ and pu matching conditions after ON
  • Ex:

%%sql
SELECT parent FROM parents JOIN dogs ON child = name WHERE fur = "curly";

 * sqlite:///cs61a.db
Done.

parent
ellie
daisy

Aliases and Dot Expressions

• We use Dot Expressions to distinguish between two columns that have the same name, and we use Aliases to distinguish between two tables that have the same name.
  • Both of these cases occur when a table is joined with itself.
• EX: Select all pairs of siblings

%%sql
SELECT a.child AS first, b.child AS second -- dot expression is used to select the names of two dogs
    FROM parents AS a, parents AS b     -- Aliasing is used to distinguish between the two tables
    WHERE a.parent = b.parent AND a.child < b.child     -- Check that the two dogs have the same parent and ensure that they are not the same dog + ensure no duplicates

 * sqlite:///cs61a.db
Done.

first	second
bella	charlie
ace	daisy
ace	ginger
daisy	ginger

• Ex: Select all grandparent, grandchild pairs

%%sql
SELECT a.parent AS gp, b.child AS gc
    FROM parents AS a, parents AS b
    WHERE a.child == b.parent

 * sqlite:///cs61a.db
Done.

gp	gc
ellie	ace
ellie	daisy
ellie	ginger
finn	bella
finn	charlie
finn	hank

Joining Multiple Tables

• Multiple tables can be joined to yield all combinations of rows from table.
• Ex: Select all grandparents with the same fur as their grandchildren

%%sql
CREATE TABLE grandparents AS
    SELECT a.parent AS granddog, b.child AS grandpup
        FROM parents AS a, parents AS b
        WHERE a.child = b.parent;

SELECT a.name, b.name
    FROM grandparents AS gp, dogs AS a, dogs AS b
    WHERE gp.granddog = a.name AND gp.grandpup = b.name AND a.fur = b.fur;

 * sqlite:///cs61a.db
Done.
Done.

name	name_1
ellie	ginger
finn	hank

• To figure out which tables must be joined together, we must understand what information we must gather first.
  • What is the grandpup’s fur? (dogs)
  • What is the granddog’s fur? (dogs)
  • Are two dogs grandpups and grandogs? (grandparents)

Numerical Expressions

• Expressions can contain function calls and arithmetic operators
• Through expressions, we may:
  • Combine values: +, -, *, /, %, and, or
  • Transform values: abs, round, not
  • Compare values: <, <=, >, >=, <>, !=, =
    • both <> and != means not equal.
• Ex: Cities Table

%%sql
CREATE TABLE cities AS
    SELECT 38 AS latitude, 122 AS longitude, "Berkeley" AS name UNION
    SELECT 42, 71, "Cambridge" UNION
    SELECT 45, 93, "Minneapolis" UNION
    SELECT 33, 117, "San Diego" UNION
    SELECT 26, 80, "Miami" UNION
    SELECT 90, 0, "North Pole";

 * sqlite:///cs61a.db
Done.





[]

• Ex: Create a new table cold that contains all cities with a latitude at or above 43

%%sql

CREATE TABLE cold AS
    SELECT name FROM cities WHERE latitude >= 43;

SELECT name FROM cold;

 * sqlite:///cs61a.db
Done.
Done.

name
Minneapolis
North Pole

• Ex: Create a new table distances that contains the north-south difference of cities

%%sql
CREATE TABLE distances AS
    SELECT a.name AS city_1, b.name AS city_2, 60*(a.latitude-b.latitude) AS distance
    FROM cities AS a, cities AS b
    WHERE a.name > b.name
    ORDER BY distance DESC;

SELECT * FROM distances;

 * sqlite:///cs61a.db
Done.
Done.

city_1	city_2	distance
North Pole	Miami	3840
North Pole	Berkeley	3120
North Pole	Cambridge	2880
North Pole	Minneapolis	2700
Minneapolis	Miami	1140
San Diego	Miami	420
Minneapolis	Berkeley	420
Cambridge	Berkeley	240
Minneapolis	Cambridge	180
San Diego	Berkeley	-300
San Diego	Cambridge	-540
Miami	Berkeley	-720
San Diego	Minneapolis	-720
Miami	Cambridge	-960
San Diego	North Pole	-3420

String Expressions

• String values may be combined to form longer strings
  • This is done through the concatenation symbol ||

%sql SELECT "hello, " || "world";

 * sqlite:///cs61a.db
Done.

"hello, " || "world"
hello, world

• String manipulation is also built into SQL
  • SUBSTR(column, start, length): For each value in the column, start from at the start index (one-based), and capture the next length letters, including the start letter.
  • INSTR(column, string): Returns the index at which a string first appears in another value.

%sql DROP TABLE phrase;

 * sqlite:///cs61a.db
Done.





[]

%sql DROP TABLE phrase

 * sqlite:///cs61a.db
Done.





[]

%%sql
CREATE TABLE phrase AS SELECT "hello, world" AS s;
    SELECT "Bye, world" UNION
    SELECT "Hello, CS61A world";

SELECT SUBSTR(s, 4, 2) || SUBSTR(s, INSTR(s, " ")+1, 1) AS modified FROM phrase;

 * sqlite:///cs61a.db
Done.
Done.
Done.

modified
low

• Strings may also be used to represent structured values.
  • This is not a good idea
• Ex: A linked list representation

%%sql
CREATE TABLE lists AS SELECT "one" AS car, "two,three,four" AS cdr;
SELECT SUBSTR(cdr, 1, instr(cdr, ",")-1) AS cadr FROM lists;

 * sqlite:///cs61a.db
Done.
Done.

cadr
two

• Ex: Nouns table

%%sql 

CREATE TABLE nouns AS
    SELECT "dog" AS phrase UNION
    SELECT "cat" UNION
    SELECT "bird";

SELECT subject.phrase || " chased " || object.phrase AS sentence FROM nouns AS subject, nouns AS object WHERE object.phrase != subject.phrase;

 * sqlite:///cs61a.db
Done.
Done.

sentence
bird chased cat
bird chased dog
cat chased bird
cat chased dog
dog chased bird
dog chased cat