SLIDE 1
61A Lecture 32 Announcements Joining Tables Reminder: John the - - PowerPoint PPT Presentation
61A Lecture 32 Announcements Joining Tables Reminder: John the - - PowerPoint PPT Presentation
61A Lecture 32 Announcements Joining Tables Reminder: John the Patriotic Dog Breeder E isenhower F illmore A braham D elano G rover B arack C linton H erbert 4 Reminder: John the Patriotic Dog Breeder E isenhower select "abraham" as
SLIDE 2
SLIDE 3
Joining Tables
SLIDE 4
Reminder: John the Patriotic Dog Breeder
4
Delano Herbert Clinton Abraham Barack Fillmore Eisenhower Grover
SLIDE 5
Reminder: John the Patriotic Dog Breeder
4
Delano Herbert Clinton Abraham Barack Fillmore Eisenhower Grover
select "abraham" as parent, "barack" as child union select "abraham" , "clinton" union select "delano" , "herbert" union select "fillmore" , "abraham" union select "fillmore" , "delano" union select "fillmore" , "grover" union select "eisenhower" , "fillmore";
SLIDE 6
Reminder: John the Patriotic Dog Breeder
4
Delano Herbert Clinton Abraham Barack Fillmore Eisenhower Grover
create table parents as select "abraham" as parent, "barack" as child union select "abraham" , "clinton" union select "delano" , "herbert" union select "fillmore" , "abraham" union select "fillmore" , "delano" union select "fillmore" , "grover" union select "eisenhower" , "fillmore";
SLIDE 7
Reminder: John the Patriotic Dog Breeder
4
Delano Herbert Clinton Abraham Barack Fillmore Eisenhower Grover
create table parents as Parent Child abraham barack abraham clinton delano herbert fillmore abraham fillmore delano fillmore grover eisenhower fillmore Parents: select "abraham" as parent, "barack" as child union select "abraham" , "clinton" union select "delano" , "herbert" union select "fillmore" , "abraham" union select "fillmore" , "delano" union select "fillmore" , "grover" union select "eisenhower" , "fillmore";
SLIDE 8
Joining Two Tables
5
SLIDE 9
Joining Two Tables
Two tables A & B are joined by a comma to yield all combos of a row from A & a row from B
5
SLIDE 10
Joining Two Tables
Two tables A & B are joined by a comma to yield all combos of a row from A & a row from B
5
A
create table dogs as select "abraham" as name, "long" as fur union
SLIDE 11
Joining Two Tables
Two tables A & B are joined by a comma to yield all combos of a row from A & a row from B
5
A B
create table dogs as select "abraham" as name, "long" as fur union select "barack" , "short" union
SLIDE 12
Joining Two Tables
Two tables A & B are joined by a comma to yield all combos of a row from A & a row from B
5
A B C
create table dogs as select "abraham" as name, "long" as fur union select "barack" , "short" union select "clinton" , "long" union
SLIDE 13
Joining Two Tables
Two tables A & B are joined by a comma to yield all combos of a row from A & a row from B
5
A D B C
create table dogs as select "abraham" as name, "long" as fur union select "barack" , "short" union select "clinton" , "long" union select "delano" , "long" union
SLIDE 14
Joining Two Tables
Two tables A & B are joined by a comma to yield all combos of a row from A & a row from B
5
E A D B C
create table dogs as select "abraham" as name, "long" as fur union select "barack" , "short" union select "clinton" , "long" union select "delano" , "long" union select "eisenhower" , "short" union
SLIDE 15
Joining Two Tables
Two tables A & B are joined by a comma to yield all combos of a row from A & a row from B
5
E F A D B C
create table dogs as select "abraham" as name, "long" as fur union select "barack" , "short" union select "clinton" , "long" union select "delano" , "long" union select "eisenhower" , "short" union select "fillmore" , "curly" union
SLIDE 16
Joining Two Tables
Two tables A & B are joined by a comma to yield all combos of a row from A & a row from B
5
E F A D G B C
create table dogs as select "abraham" as name, "long" as fur union select "barack" , "short" union select "clinton" , "long" union select "delano" , "long" union select "eisenhower" , "short" union select "fillmore" , "curly" union select "grover" , "short" union
SLIDE 17
Joining Two Tables
Two tables A & B are joined by a comma to yield all combos of a row from A & a row from B
5
E F A D G B C H
create table dogs as select "abraham" as name, "long" as fur union select "barack" , "short" union select "clinton" , "long" union select "delano" , "long" union select "eisenhower" , "short" union select "fillmore" , "curly" union select "grover" , "short" union select "herbert" , "curly";
SLIDE 18
Joining Two Tables
Two tables A & B are joined by a comma to yield all combos of a row from A & a row from B
5
E F A D G B C H
create table dogs as select "abraham" as name, "long" as fur union select "barack" , "short" union select "clinton" , "long" union select "delano" , "long" union select "eisenhower" , "short" union select "fillmore" , "curly" union select "grover" , "short" union select "herbert" , "curly"; create table parents as select "abraham" as parent, "barack" as child union select "abraham" , "clinton" union ...;
SLIDE 19
Joining Two Tables
Two tables A & B are joined by a comma to yield all combos of a row from A & a row from B
5
E F A D G B C H
create table dogs as select "abraham" as name, "long" as fur union select "barack" , "short" union select "clinton" , "long" union select "delano" , "long" union select "eisenhower" , "short" union select "fillmore" , "curly" union select "grover" , "short" union select "herbert" , "curly"; create table parents as select "abraham" as parent, "barack" as child union select "abraham" , "clinton" union ...; Select the parents of curly-furred dogs
SLIDE 20
Joining Two Tables
Two tables A & B are joined by a comma to yield all combos of a row from A & a row from B
5
E F A D G B C H
create table dogs as select "abraham" as name, "long" as fur union select "barack" , "short" union select "clinton" , "long" union select "delano" , "long" union select "eisenhower" , "short" union select "fillmore" , "curly" union select "grover" , "short" union select "herbert" , "curly"; create table parents as select "abraham" as parent, "barack" as child union select "abraham" , "clinton" union ...; Select the parents of curly-furred dogs select parent from parents, dogs where child = name and fur = "curly";
SLIDE 21
Joining Two Tables
Two tables A & B are joined by a comma to yield all combos of a row from A & a row from B
5
E F A D G B C H
create table dogs as select "abraham" as name, "long" as fur union select "barack" , "short" union select "clinton" , "long" union select "delano" , "long" union select "eisenhower" , "short" union select "fillmore" , "curly" union select "grover" , "short" union select "herbert" , "curly"; create table parents as select "abraham" as parent, "barack" as child union select "abraham" , "clinton" union ...; Select the parents of curly-furred dogs select parent from parents, dogs where child = name and fur = "curly";
SLIDE 22
Joining Two Tables
Two tables A & B are joined by a comma to yield all combos of a row from A & a row from B
5
E F A D G B C H
create table dogs as select "abraham" as name, "long" as fur union select "barack" , "short" union select "clinton" , "long" union select "delano" , "long" union select "eisenhower" , "short" union select "fillmore" , "curly" union select "grover" , "short" union select "herbert" , "curly"; create table parents as select "abraham" as parent, "barack" as child union select "abraham" , "clinton" union ...; Select the parents of curly-furred dogs select parent from parents, dogs where child = name and fur = "curly"; (Demo)
SLIDE 23
Aliases and Dot Expressions
SLIDE 24
Joining a Table with Itself
7
E F A D G B C H
SLIDE 25
Joining a Table with Itself
Two tables may share a column name; dot expressions and aliases disambiguate column values
7
E F A D G B C H
SLIDE 26
Joining a Table with Itself
Two tables may share a column name; dot expressions and aliases disambiguate column values
7
E F A D G B C H
select [columns] from [table] where [condition] order by [order];
SLIDE 27
Joining a Table with Itself
Two tables may share a column name; dot expressions and aliases disambiguate column values
7
E F A D G B C H
select [columns] from [table] where [condition] order by [order]; [table] is a comma-separated list of table names with optional aliases
SLIDE 28
Joining a Table with Itself
Two tables may share a column name; dot expressions and aliases disambiguate column values
7
E F A D G B C H
select [columns] from [table] where [condition] order by [order]; [table] is a comma-separated list of table names with optional aliases Select all pairs of siblings
SLIDE 29
Joining a Table with Itself
Two tables may share a column name; dot expressions and aliases disambiguate column values
7
E F A D G B C H
select [columns] from [table] where [condition] order by [order]; [table] is a comma-separated list of table names with optional aliases 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;
SLIDE 30
Joining a Table with Itself
Two tables may share a column name; dot expressions and aliases disambiguate column values
7
E F A D G B C H
select [columns] from [table] where [condition] order by [order]; [table] is a comma-separated list of table names with optional aliases 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;
SLIDE 31
Joining a Table with Itself
Two tables may share a column name; dot expressions and aliases disambiguate column values
7
E F A D G B C H
select [columns] from [table] where [condition] order by [order]; [table] is a comma-separated list of table names with optional aliases 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; First Second barack clinton abraham delano abraham grover delano grover
SLIDE 32
Example: Grandparents
Which select statement evaluates to all grandparent, grandchild pairs?
8
E F A D G B C H
select a.grandparent, b.child from parents as a, parents as b where b.parent = a.child; select a.grandparent, b.child from parents as a, parents as b where a.parent = b.child; select a.parent, b.child from parents as a, parents as b where b.parent = a.child; select a.parent, b.child from parents as a, parents as b where a.parent = b.child; None of the above 1 2 3 4 5
SLIDE 33
Joining Multiple Tables
9
E F A D G B C H
SLIDE 34
Joining Multiple Tables
Multiple tables can be joined to yield all combinations of rows from each
9
E F A D G B C H
SLIDE 35
Joining Multiple Tables
Multiple tables can be joined to yield all combinations of rows from each
9
E F A D G B C H
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;
SLIDE 36
Joining Multiple Tables
Multiple tables can be joined to yield all combinations of rows from each
9
E F A D G B C H
Select all grandparents with the same fur as their grandchildren 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;
SLIDE 37
Joining Multiple Tables
Multiple tables can be joined to yield all combinations of rows from each
9
E F A D G B C H
Select all grandparents with the same fur as their grandchildren 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; Which tables need to be joined together?
SLIDE 38
Joining Multiple Tables
Multiple tables can be joined to yield all combinations of rows from each
9
E F A D G B C H
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; 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; Which tables need to be joined together?
SLIDE 39
Example: Dog Triples
SLIDE 40
Fall 2014 Quiz Question (Slightly Modified)
11
SLIDE 41
Fall 2014 Quiz Question (Slightly Modified)
Write a SQL query that selects all possible combinations of three different dogs with the same fur and lists each triple in inverse alphabetical order
11
SLIDE 42
Fall 2014 Quiz Question (Slightly Modified)
Write a SQL query that selects all possible combinations of three different dogs with the same fur and lists each triple in inverse alphabetical order
11
create table dogs as select "abraham" as name, "long" as fur union select "barack" , "short" union ...;
SLIDE 43
Fall 2014 Quiz Question (Slightly Modified)
Write a SQL query that selects all possible combinations of three different dogs with the same fur and lists each triple in inverse alphabetical order
11
create table dogs as select "abraham" as name, "long" as fur union select "barack" , "short" union ...; create table parents as select "abraham" as parent, "barack" as child union select "abraham" , "clinton" union ...;
SLIDE 44
Fall 2014 Quiz Question (Slightly Modified)
Write a SQL query that selects all possible combinations of three different dogs with the same fur and lists each triple in inverse alphabetical order
11
E F A D G B C H
create table dogs as select "abraham" as name, "long" as fur union select "barack" , "short" union ...; create table parents as select "abraham" as parent, "barack" as child union select "abraham" , "clinton" union ...;
SLIDE 45
Fall 2014 Quiz Question (Slightly Modified)
Write a SQL query that selects all possible combinations of three different dogs with the same fur and lists each triple in inverse alphabetical order
11
Expected output: delano|clinton|abraham grover|eisenhower|barack
E F A D G B C H
create table dogs as select "abraham" as name, "long" as fur union select "barack" , "short" union ...; create table parents as select "abraham" as parent, "barack" as child union select "abraham" , "clinton" union ...;
SLIDE 46
Fall 2014 Quiz Question (Slightly Modified)
Write a SQL query that selects all possible combinations of three different dogs with the same fur and lists each triple in inverse alphabetical order
11
Expected output: delano|clinton|abraham grover|eisenhower|barack
E F A D G B C H
create table dogs as select "abraham" as name, "long" as fur union select "barack" , "short" union ...; create table parents as select "abraham" as parent, "barack" as child union select "abraham" , "clinton" union ...; (Demo)
SLIDE 47
Numerical Expressions
SLIDE 48
Numerical Expressions
Expressions can contain function calls and arithmetic operators
13
SLIDE 49
Numerical Expressions
Expressions can contain function calls and arithmetic operators
13
select [columns] from [table] where [expression] order by [expression];
SLIDE 50
Numerical Expressions
Expressions can contain function calls and arithmetic operators
13
select [columns] from [table] where [expression] order by [expression]; [expression] as [name], [expression] as [name], ...
SLIDE 51
Numerical Expressions
Expressions can contain function calls and arithmetic operators
13
select [columns] from [table] where [expression] order by [expression]; [expression] as [name], [expression] as [name], ... Combine values: +, -, *, /, %, and, or
SLIDE 52
Numerical Expressions
Expressions can contain function calls and arithmetic operators
13
select [columns] from [table] where [expression] order by [expression]; [expression] as [name], [expression] as [name], ... Combine values: +, -, *, /, %, and, or Transform values: abs, round, not, -
SLIDE 53
Numerical Expressions
Expressions can contain function calls and arithmetic operators
13
select [columns] from [table] where [expression] order by [expression]; [expression] as [name], [expression] as [name], ... Combine values: +, -, *, /, %, and, or Transform values: abs, round, not, - Compare values: <, <=, >, >=, <>, !=, =
SLIDE 54
Numerical Expressions
Expressions can contain function calls and arithmetic operators
13
select [columns] from [table] where [expression] order by [expression]; [expression] as [name], [expression] as [name], ... Combine values: +, -, *, /, %, and, or Transform values: abs, round, not, - Compare values: <, <=, >, >=, <>, !=, = (Demo)
SLIDE 55
String Expressions
SLIDE 56
String Expressions
String values can be combined to form longer strings
15
SLIDE 57
String Expressions
String values can be combined to form longer strings
15
sqlite> select "hello," || " world"; hello, world
SLIDE 58
String Expressions
String values can be combined to form longer strings
15
Basic string manipulation is built into SQL, but differs from Python sqlite> select "hello," || " world"; hello, world
SLIDE 59
String Expressions
String values can be combined to form longer strings
15
sqlite> create table phrase as select "hello, world" as s; Basic string manipulation is built into SQL, but differs from Python sqlite> select "hello," || " world"; hello, world
SLIDE 60
String Expressions
String values can be combined to form longer strings
15
sqlite> create table phrase as select "hello, world" as s; sqlite> select substr(s, 4, 2) || substr(s, instr(s, " ")+1, 1) from phrase; Basic string manipulation is built into SQL, but differs from Python sqlite> select "hello," || " world"; hello, world
SLIDE 61
String Expressions
String values can be combined to form longer strings
15
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 Basic string manipulation is built into SQL, but differs from Python sqlite> select "hello," || " world"; hello, world
SLIDE 62
String Expressions
String values can be combined to form longer strings
15
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 Basic string manipulation is built into SQL, but differs from Python sqlite> select "hello," || " world"; hello, world
SLIDE 63
String Expressions
String values can be combined to form longer strings
15
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 Strings can be used to represent structured values, but doing so is rarely a good idea Basic string manipulation is built into SQL, but differs from Python sqlite> select "hello," || " world"; hello, world
SLIDE 64
String Expressions
String values can be combined to form longer strings
15
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 Strings can be used to represent structured values, but doing so is rarely a good idea Basic string manipulation is built into SQL, but differs from Python sqlite> select "hello," || " world"; hello, world sqlite> create table lists as select "one" as car, "two,three,four" as cdr;
SLIDE 65
String Expressions
String values can be combined to form longer strings
15
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 Strings can be used to represent structured values, but doing so is rarely a good idea Basic string manipulation is built into SQL, but differs from Python sqlite> select "hello," || " world"; hello, world sqlite> create table lists as select "one" as car, "two,three,four" as cdr; sqlite> select substr(cdr, 1, instr(cdr, ",")-1) as cadr from lists;
SLIDE 66
String Expressions
String values can be combined to form longer strings
15
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 Strings can be used to represent structured values, but doing so is rarely a good idea Basic string manipulation is built into SQL, but differs from Python sqlite> select "hello," || " world"; hello, world sqlite> create table lists as select "one" as car, "two,three,four" as cdr; sqlite> select substr(cdr, 1, instr(cdr, ",")-1) as cadr from lists; two
SLIDE 67
String Expressions
String values can be combined to form longer strings
15
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 Strings can be used to represent structured values, but doing so is rarely a good idea Basic string manipulation is built into SQL, but differs from Python sqlite> select "hello," || " world"; hello, world sqlite> create table lists as select "one" as car, "two,three,four" as cdr; sqlite> select substr(cdr, 1, instr(cdr, ",")-1) as cadr from lists; two
SLIDE 68
String Expressions
String values can be combined to form longer strings
15
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 Strings can be used to represent structured values, but doing so is rarely a good idea Basic string manipulation is built into SQL, but differs from Python sqlite> select "hello," || " world"; hello, world sqlite> create table lists as select "one" as car, "two,three,four" as cdr; sqlite> select substr(cdr, 1, instr(cdr, ",")-1) as cadr from lists; two (Demo)
SLIDE 69
Database Management Systems
SLIDE 70
Database Management System Architecture
17
Architecture of a Database System by Hellerstein, Stonebreaker, and Hamilton
SLIDE 71
Query Planning
The manner in which tables are filtered, sorted, and joined affects execution time
18
SLIDE 72
Query Planning
The manner in which tables are filtered, sorted, and joined affects execution time
18
Select the parents of curly-furred dogs: select parent from parents, dogs where child = name and fur = "curly";
SLIDE 73
Query Planning
The manner in which tables are filtered, sorted, and joined affects execution time
18
Select the parents of curly-furred dogs: select parent from parents, dogs where child = name and fur = "curly";
SLIDE 74
Query Planning
The manner in which tables are filtered, sorted, and joined affects execution time
18
Select the parents of curly-furred dogs: select parent from parents, dogs where child = name and fur = "curly";
SLIDE 75
Query Planning
The manner in which tables are filtered, sorted, and joined affects execution time
18
Select the parents of curly-furred dogs: select parent from parents, dogs where child = name and fur = "curly";
SLIDE 76
Query Planning
The manner in which tables are filtered, sorted, and joined affects execution time
18
Select the parents of curly-furred dogs: select parent from parents, dogs where child = name and fur = "curly"; Join all rows of parents to all rows of dogs, filter by child = name and fur = "curly"
SLIDE 77
Query Planning
The manner in which tables are filtered, sorted, and joined affects execution time
18
Select the parents of curly-furred dogs: select parent from parents, dogs where child = name and fur = "curly"; Join all rows of parents to all rows of dogs, filter by child = name and fur = "curly" Join only rows of parents and dogs where child = name, filter by fur = "curly"
SLIDE 78
Query Planning
The manner in which tables are filtered, sorted, and joined affects execution time
18
Select the parents of curly-furred dogs: select parent from parents, dogs where child = name and fur = "curly"; Join all rows of parents to all rows of dogs, filter by child = name and fur = "curly" Join only rows of parents and dogs where child = name, filter by fur = "curly" Filter dogs by fur = "curly", join result with all rows of parents, filter by child = name
SLIDE 79
Query Planning
The manner in which tables are filtered, sorted, and joined affects execution time
18