SLIDE 1
61A Lecture 34 Announcements Integer Examples (continued) A Very - - PowerPoint PPT Presentation
61A Lecture 34 Announcements Integer Examples (continued) A Very - - PowerPoint PPT Presentation
61A Lecture 34 Announcements Integer Examples (continued) A Very Interesting Number The mathematician G. H. Hardy once remarked to the mathematician Srinivasa Ramanujan... 4 A Very Interesting Number The mathematician G. H. Hardy once
SLIDE 2
SLIDE 3
Integer Examples (continued)
SLIDE 4
A Very Interesting Number
The mathematician G. H. Hardy once remarked to the mathematician Srinivasa Ramanujan...
4
SLIDE 5
A Very Interesting Number
The mathematician G. H. Hardy once remarked to the mathematician Srinivasa Ramanujan...
4
(Demo)
SLIDE 6
Aggregation
SLIDE 7
Aggregate Functions
6
SLIDE 8
Aggregate Functions
So far, all SQL expressions have referred to the values in a single row at a time
6
SLIDE 9
Aggregate Functions
So far, all SQL expressions have referred to the values in a single row at a time
6
select [columns] from [table] where [expression] order by [expression]; [expression] as [name], [expression] as [name], ...
SLIDE 10
Aggregate Functions
So far, all SQL expressions have referred to the values in a single row at a time
6
select [columns] from [table] where [expression] order by [expression]; [expression] as [name], [expression] as [name], ... An aggregate function in the [columns] clause computes a value from a group of rows
SLIDE 11
Aggregate Functions
So far, all SQL expressions have referred to the values in a single row at a time
6
select [columns] from [table] where [expression] order by [expression]; [expression] as [name], [expression] as [name], ... An aggregate function in the [columns] clause computes a value from a group of rows create table animals as
SLIDE 12
Aggregate Functions
So far, all SQL expressions have referred to the values in a single row at a time
6
select [columns] from [table] where [expression] order by [expression]; [expression] as [name], [expression] as [name], ... An aggregate function in the [columns] clause computes a value from a group of rows create table animals as select "dog" as kind, 4 as legs, 20 as weight union
SLIDE 13
Aggregate Functions
So far, all SQL expressions have referred to the values in a single row at a time
6
select [columns] from [table] where [expression] order by [expression]; [expression] as [name], [expression] as [name], ... An aggregate function in the [columns] clause computes a value from a group of rows create table animals as select "dog" as kind, 4 as legs, 20 as weight union select "cat" , 4 , 10 union
SLIDE 14
Aggregate Functions
So far, all SQL expressions have referred to the values in a single row at a time
6
select [columns] from [table] where [expression] order by [expression]; [expression] as [name], [expression] as [name], ... An aggregate function in the [columns] clause computes a value from a group of rows create table animals as select "dog" as kind, 4 as legs, 20 as weight union select "cat" , 4 , 10 union select "ferret" , 4 , 10 union
SLIDE 15
Aggregate Functions
So far, all SQL expressions have referred to the values in a single row at a time
6
select [columns] from [table] where [expression] order by [expression]; [expression] as [name], [expression] as [name], ... An aggregate function in the [columns] clause computes a value from a group of rows create table animals as select "dog" as kind, 4 as legs, 20 as weight union select "cat" , 4 , 10 union select "ferret" , 4 , 10 union select "parrot" , 2 , 6 union
SLIDE 16
Aggregate Functions
So far, all SQL expressions have referred to the values in a single row at a time
6
select [columns] from [table] where [expression] order by [expression]; [expression] as [name], [expression] as [name], ... An aggregate function in the [columns] clause computes a value from a group of rows create table animals as select "dog" as kind, 4 as legs, 20 as weight union select "cat" , 4 , 10 union select "ferret" , 4 , 10 union select "parrot" , 2 , 6 union select "penguin" , 2 , 10 union
SLIDE 17
Aggregate Functions
So far, all SQL expressions have referred to the values in a single row at a time
6
select [columns] from [table] where [expression] order by [expression]; [expression] as [name], [expression] as [name], ... An aggregate function in the [columns] clause computes a value from a group of rows create table animals as select "dog" as kind, 4 as legs, 20 as weight union select "cat" , 4 , 10 union select "ferret" , 4 , 10 union select "parrot" , 2 , 6 union select "penguin" , 2 , 10 union select "t-rex" , 2 , 12000;
SLIDE 18
Aggregate Functions
So far, all SQL expressions have referred to the values in a single row at a time
6
select [columns] from [table] where [expression] order by [expression]; [expression] as [name], [expression] as [name], ... An aggregate function in the [columns] clause computes a value from a group of rows create table animals as select "dog" as kind, 4 as legs, 20 as weight union select "cat" , 4 , 10 union select "ferret" , 4 , 10 union select "parrot" , 2 , 6 union select "penguin" , 2 , 10 union select "t-rex" , 2 , 12000; kind legs weight dog 4 20 cat 4 10 ferret 4 10 parrot 2 6 penguin 2 10 t-rex 2 12000 animals:
SLIDE 19
Aggregate Functions
So far, all SQL expressions have referred to the values in a single row at a time
6
select [columns] from [table] where [expression] order by [expression]; [expression] as [name], [expression] as [name], ... An aggregate function in the [columns] clause computes a value from a group of rows create table animals as select "dog" as kind, 4 as legs, 20 as weight union select "cat" , 4 , 10 union select "ferret" , 4 , 10 union select "parrot" , 2 , 6 union select "penguin" , 2 , 10 union select "t-rex" , 2 , 12000; select max(legs) from animals; kind legs weight dog 4 20 cat 4 10 ferret 4 10 parrot 2 6 penguin 2 10 t-rex 2 12000 animals:
SLIDE 20
Aggregate Functions
So far, all SQL expressions have referred to the values in a single row at a time
6
select [columns] from [table] where [expression] order by [expression]; [expression] as [name], [expression] as [name], ... An aggregate function in the [columns] clause computes a value from a group of rows create table animals as select "dog" as kind, 4 as legs, 20 as weight union select "cat" , 4 , 10 union select "ferret" , 4 , 10 union select "parrot" , 2 , 6 union select "penguin" , 2 , 10 union select "t-rex" , 2 , 12000; select max(legs) from animals; kind legs weight dog 4 20 cat 4 10 ferret 4 10 parrot 2 6 penguin 2 10 t-rex 2 12000 animals: max(legs) 4
SLIDE 21
Aggregate Functions
So far, all SQL expressions have referred to the values in a single row at a time
6
select [columns] from [table] where [expression] order by [expression]; [expression] as [name], [expression] as [name], ... An aggregate function in the [columns] clause computes a value from a group of rows create table animals as select "dog" as kind, 4 as legs, 20 as weight union select "cat" , 4 , 10 union select "ferret" , 4 , 10 union select "parrot" , 2 , 6 union select "penguin" , 2 , 10 union select "t-rex" , 2 , 12000; select max(legs) from animals; kind legs weight dog 4 20 cat 4 10 ferret 4 10 parrot 2 6 penguin 2 10 t-rex 2 12000 animals: max(legs) 4 (Demo)
SLIDE 22
Mixing Aggregate Functions and Single Values
7
create table animals as select "dog" as kind, 4 as legs, 20 as weight union select "cat" , 4 , 10 union select "ferret" , 4 , 10 union select "parrot" , 2 , 6 union select "penguin" , 2 , 10 union select "t-rex" , 2 , 12000; kind legs weight dog 4 20 cat 4 10 ferret 4 10 parrot 2 6 penguin 2 10 t-rex 2 12000 animals:
SLIDE 23
Mixing Aggregate Functions and Single Values
An aggregate function also selects a row in the table, which may be meaningful
7
create table animals as select "dog" as kind, 4 as legs, 20 as weight union select "cat" , 4 , 10 union select "ferret" , 4 , 10 union select "parrot" , 2 , 6 union select "penguin" , 2 , 10 union select "t-rex" , 2 , 12000; kind legs weight dog 4 20 cat 4 10 ferret 4 10 parrot 2 6 penguin 2 10 t-rex 2 12000 animals:
SLIDE 24
Mixing Aggregate Functions and Single Values
An aggregate function also selects a row in the table, which may be meaningful
7
select max(weight), kind from animals; create table animals as select "dog" as kind, 4 as legs, 20 as weight union select "cat" , 4 , 10 union select "ferret" , 4 , 10 union select "parrot" , 2 , 6 union select "penguin" , 2 , 10 union select "t-rex" , 2 , 12000; kind legs weight dog 4 20 cat 4 10 ferret 4 10 parrot 2 6 penguin 2 10 t-rex 2 12000 animals:
SLIDE 25
Mixing Aggregate Functions and Single Values
An aggregate function also selects a row in the table, which may be meaningful
7
select max(weight), kind from animals; select min(kind), kind from animals; create table animals as select "dog" as kind, 4 as legs, 20 as weight union select "cat" , 4 , 10 union select "ferret" , 4 , 10 union select "parrot" , 2 , 6 union select "penguin" , 2 , 10 union select "t-rex" , 2 , 12000; kind legs weight dog 4 20 cat 4 10 ferret 4 10 parrot 2 6 penguin 2 10 t-rex 2 12000 animals:
SLIDE 26
Mixing Aggregate Functions and Single Values
An aggregate function also selects a row in the table, which may be meaningful
7
select max(weight), kind from animals; select min(kind), kind from animals; select max(legs), kind from animals; create table animals as select "dog" as kind, 4 as legs, 20 as weight union select "cat" , 4 , 10 union select "ferret" , 4 , 10 union select "parrot" , 2 , 6 union select "penguin" , 2 , 10 union select "t-rex" , 2 , 12000; kind legs weight dog 4 20 cat 4 10 ferret 4 10 parrot 2 6 penguin 2 10 t-rex 2 12000 animals:
SLIDE 27
Mixing Aggregate Functions and Single Values
An aggregate function also selects a row in the table, which may be meaningful
7
select max(weight), kind from animals; select min(kind), kind from animals; select max(legs), kind from animals; select avg(weight), kind from animals; create table animals as select "dog" as kind, 4 as legs, 20 as weight union select "cat" , 4 , 10 union select "ferret" , 4 , 10 union select "parrot" , 2 , 6 union select "penguin" , 2 , 10 union select "t-rex" , 2 , 12000; kind legs weight dog 4 20 cat 4 10 ferret 4 10 parrot 2 6 penguin 2 10 t-rex 2 12000 animals:
SLIDE 28
Mixing Aggregate Functions and Single Values
An aggregate function also selects a row in the table, which may be meaningful
7
select max(weight), kind from animals; select min(kind), kind from animals; select max(legs), kind from animals; select avg(weight), kind from animals; create table animals as select "dog" as kind, 4 as legs, 20 as weight union select "cat" , 4 , 10 union select "ferret" , 4 , 10 union select "parrot" , 2 , 6 union select "penguin" , 2 , 10 union select "t-rex" , 2 , 12000; kind legs weight dog 4 20 cat 4 10 ferret 4 10 parrot 2 6 penguin 2 10 t-rex 2 12000 animals: (Demo)
SLIDE 29
Groups
SLIDE 30
Grouping Rows
9
SLIDE 31
Grouping Rows
Rows in a table can be grouped, and aggregation is performed on each group
9
SLIDE 32
Grouping Rows
Rows in a table can be grouped, and aggregation is performed on each group
9
select [columns] from [table] group by [expression] having [expression]; [expression] as [name], [expression] as [name], ...
SLIDE 33
Grouping Rows
Rows in a table can be grouped, and aggregation is performed on each group
9
select [columns] from [table] group by [expression] having [expression]; [expression] as [name], [expression] as [name], ... The number of groups is the number of unique values of an expression
SLIDE 34
Grouping Rows
Rows in a table can be grouped, and aggregation is performed on each group
9
select [columns] from [table] group by [expression] having [expression]; [expression] as [name], [expression] as [name], ... kind legs weight dog 4 20 cat 4 10 ferret 4 10 parrot 2 6 penguin 2 10 t-rex 2 12000 animals: The number of groups is the number of unique values of an expression
SLIDE 35
Grouping Rows
Rows in a table can be grouped, and aggregation is performed on each group
9
select [columns] from [table] group by [expression] having [expression]; [expression] as [name], [expression] as [name], ... kind legs weight dog 4 20 cat 4 10 ferret 4 10 parrot 2 6 penguin 2 10 t-rex 2 12000 animals: The number of groups is the number of unique values of an expression select legs, max(weight) from animals group by legs;
SLIDE 36
Grouping Rows
Rows in a table can be grouped, and aggregation is performed on each group
9
select [columns] from [table] group by [expression] having [expression]; [expression] as [name], [expression] as [name], ... kind legs weight dog 4 20 cat 4 10 ferret 4 10 parrot 2 6 penguin 2 10 t-rex 2 12000 animals: The number of groups is the number of unique values of an expression select legs, max(weight) from animals group by legs;
SLIDE 37
Grouping Rows
Rows in a table can be grouped, and aggregation is performed on each group
9
select [columns] from [table] group by [expression] having [expression]; [expression] as [name], [expression] as [name], ... kind legs weight dog 4 20 cat 4 10 ferret 4 10 parrot 2 6 penguin 2 10 t-rex 2 12000 animals: The number of groups is the number of unique values of an expression select legs, max(weight) from animals group by legs; legs=4
SLIDE 38
Grouping Rows
Rows in a table can be grouped, and aggregation is performed on each group
9
select [columns] from [table] group by [expression] having [expression]; [expression] as [name], [expression] as [name], ... kind legs weight dog 4 20 cat 4 10 ferret 4 10 parrot 2 6 penguin 2 10 t-rex 2 12000 animals: The number of groups is the number of unique values of an expression select legs, max(weight) from animals group by legs; legs=4
SLIDE 39
Grouping Rows
Rows in a table can be grouped, and aggregation is performed on each group
9
select [columns] from [table] group by [expression] having [expression]; [expression] as [name], [expression] as [name], ... kind legs weight dog 4 20 cat 4 10 ferret 4 10 parrot 2 6 penguin 2 10 t-rex 2 12000 animals: The number of groups is the number of unique values of an expression select legs, max(weight) from animals group by legs; legs=4 legs=2
SLIDE 40
Grouping Rows
Rows in a table can be grouped, and aggregation is performed on each group
9
select [columns] from [table] group by [expression] having [expression]; [expression] as [name], [expression] as [name], ... kind legs weight dog 4 20 cat 4 10 ferret 4 10 parrot 2 6 penguin 2 10 t-rex 2 12000 animals: The number of groups is the number of unique values of an expression select legs, max(weight) from animals group by legs; legs=4 legs=2 legs max(weight) 4 20 2 12000
SLIDE 41
Grouping Rows
Rows in a table can be grouped, and aggregation is performed on each group
9
select [columns] from [table] group by [expression] having [expression]; [expression] as [name], [expression] as [name], ... kind legs weight dog 4 20 cat 4 10 ferret 4 10 parrot 2 6 penguin 2 10 t-rex 2 12000 animals: The number of groups is the number of unique values of an expression select legs, max(weight) from animals group by legs; legs=4 legs=2 legs max(weight) 4 20 2 12000 (Demo)
SLIDE 42
Selecting Groups
Rows in a table can be grouped, and aggregation is performed on each group
10
select [columns] from [table] group by [expression] having [expression]; [expression] as [name], [expression] as [name], ... kind legs weight dog 4 20 cat 4 10 ferret 4 10 parrot 2 6 penguin 2 10 t-rex 2 12000 animals:
SLIDE 43
Selecting Groups
Rows in a table can be grouped, and aggregation is performed on each group
10
select [columns] from [table] group by [expression] having [expression]; [expression] as [name], [expression] as [name], ... kind legs weight dog 4 20 cat 4 10 ferret 4 10 parrot 2 6 penguin 2 10 t-rex 2 12000 animals: A having clause filters the set of groups that are aggregated
SLIDE 44
Selecting Groups
Rows in a table can be grouped, and aggregation is performed on each group
10
select [columns] from [table] group by [expression] having [expression]; [expression] as [name], [expression] as [name], ... kind legs weight dog 4 20 cat 4 10 ferret 4 10 parrot 2 6 penguin 2 10 t-rex 2 12000 animals: A having clause filters the set of groups that are aggregated select weight/legs, count(*) from animals group by weight/legs having count(*)>1;
SLIDE 45
Selecting Groups
Rows in a table can be grouped, and aggregation is performed on each group
10
select [columns] from [table] group by [expression] having [expression]; [expression] as [name], [expression] as [name], ... kind legs weight dog 4 20 cat 4 10 ferret 4 10 parrot 2 6 penguin 2 10 t-rex 2 12000 animals: A having clause filters the set of groups that are aggregated select weight/legs, count(*) from animals group by weight/legs having count(*)>1; weight/legs=5
SLIDE 46
Selecting Groups
Rows in a table can be grouped, and aggregation is performed on each group
10
select [columns] from [table] group by [expression] having [expression]; [expression] as [name], [expression] as [name], ... kind legs weight dog 4 20 cat 4 10 ferret 4 10 parrot 2 6 penguin 2 10 t-rex 2 12000 animals: A having clause filters the set of groups that are aggregated select weight/legs, count(*) from animals group by weight/legs having count(*)>1; weight/legs=5 weight/legs=2
SLIDE 47
Selecting Groups
Rows in a table can be grouped, and aggregation is performed on each group
10
select [columns] from [table] group by [expression] having [expression]; [expression] as [name], [expression] as [name], ... kind legs weight dog 4 20 cat 4 10 ferret 4 10 parrot 2 6 penguin 2 10 t-rex 2 12000 animals: A having clause filters the set of groups that are aggregated select weight/legs, count(*) from animals group by weight/legs having count(*)>1; weight/legs=5 weight/legs=2 weight/legs=2
SLIDE 48
Selecting Groups
Rows in a table can be grouped, and aggregation is performed on each group
10
select [columns] from [table] group by [expression] having [expression]; [expression] as [name], [expression] as [name], ... kind legs weight dog 4 20 cat 4 10 ferret 4 10 parrot 2 6 penguin 2 10 t-rex 2 12000 animals: A having clause filters the set of groups that are aggregated select weight/legs, count(*) from animals group by weight/legs having count(*)>1; weight/legs=5 weight/legs=2 weight/legs=2 weight/legs=3
SLIDE 49
Selecting Groups
Rows in a table can be grouped, and aggregation is performed on each group
10
select [columns] from [table] group by [expression] having [expression]; [expression] as [name], [expression] as [name], ... kind legs weight dog 4 20 cat 4 10 ferret 4 10 parrot 2 6 penguin 2 10 t-rex 2 12000 animals: A having clause filters the set of groups that are aggregated select weight/legs, count(*) from animals group by weight/legs having count(*)>1; weight/legs=5 weight/legs=2 weight/legs=2 weight/legs=3 weight/legs=5
SLIDE 50
Selecting Groups
Rows in a table can be grouped, and aggregation is performed on each group
10
select [columns] from [table] group by [expression] having [expression]; [expression] as [name], [expression] as [name], ... kind legs weight dog 4 20 cat 4 10 ferret 4 10 parrot 2 6 penguin 2 10 t-rex 2 12000 animals: A having clause filters the set of groups that are aggregated select weight/legs, count(*) from animals group by weight/legs having count(*)>1; weight/legs=5 weight/legs=2 weight/legs=2 weight/legs=3 weight/legs=5 weight/legs=6000
SLIDE 51
Selecting Groups
Rows in a table can be grouped, and aggregation is performed on each group
10
select [columns] from [table] group by [expression] having [expression]; [expression] as [name], [expression] as [name], ... kind legs weight dog 4 20 cat 4 10 ferret 4 10 parrot 2 6 penguin 2 10 t-rex 2 12000 animals: A having clause filters the set of groups that are aggregated select weight/legs, count(*) from animals group by weight/legs having count(*)>1; weight/legs count(*) 5 2 2 2 weight/legs=5 weight/legs=2 weight/legs=2 weight/legs=3 weight/legs=5 weight/legs=6000
SLIDE 52
Selecting Groups
Rows in a table can be grouped, and aggregation is performed on each group
10
select [columns] from [table] group by [expression] having [expression]; [expression] as [name], [expression] as [name], ... kind legs weight dog 4 20 cat 4 10 ferret 4 10 parrot 2 6 penguin 2 10 t-rex 2 12000 animals: A having clause filters the set of groups that are aggregated select weight/legs, count(*) from animals group by weight/legs having count(*)>1; weight/legs count(*) 5 2 2 2 weight/legs=5 weight/legs=2 weight/legs=2 weight/legs=3 weight/legs=5 weight/legs=6000
SLIDE 53
Selecting Groups
Rows in a table can be grouped, and aggregation is performed on each group
10
select [columns] from [table] group by [expression] having [expression]; [expression] as [name], [expression] as [name], ... kind legs weight dog 4 20 cat 4 10 ferret 4 10 parrot 2 6 penguin 2 10 t-rex 2 12000 animals: A having clause filters the set of groups that are aggregated select weight/legs, count(*) from animals group by weight/legs having count(*)>1; weight/legs count(*) 5 2 2 2 weight/legs=5 weight/legs=2 weight/legs=2 weight/legs=3 weight/legs=5 weight/legs=6000
SLIDE 54
Discussion Question
What's the maximum difference between leg count for two animals with the same weight?
11
SLIDE 55
Select Grammar
SLIDE 56
SQL Select Statements
13
SLIDE 57
SQL Select Statements
13
...
SLIDE 58
SQL Select Statements
13
http://www.sqlite.org/lang_select.html ... ...
SLIDE 59
SQL Select Statements
13
http://www.sqlite.org/lang_select.html ... ...
SLIDE 60
SQL Select Statements
13
http://www.sqlite.org/lang_select.html ... ...
SLIDE 61
SQL Select Statements
13
http://www.sqlite.org/lang_select.html ... ...
SLIDE 62
SQL Select Statements
13
http://www.sqlite.org/lang_select.html ... ...
SLIDE 63
SQL Select Statements
13
http://www.sqlite.org/lang_select.html ... ...
SLIDE 64
SQL Select Statements
13
http://www.sqlite.org/lang_select.html ... ...
SLIDE 65
SQL Select Statements
13
Union http://www.sqlite.org/lang_select.html ... ...
SLIDE 66
SQL Select Statements
13