SQL Aggregation and Grouping in Database Systems
CSCE-608 Database Systems
Spring 2024
Instructor: Jianer Chen
Office: PETR 428
Phone: 845-4259
Email: chen@cse.tamu.edu
Notes 12: SQL – Grouping, aggregation, and having
SQL: Structured Query language
a very-high-level language.
*
say “
what to do
” rather than “
how to do it
.”
* avoid a
lot of data-manipulation details
needed in procedural languages like C++
or Java.
Database management system figures
out the “
best
” way to execute queries
* called “
query optimization
”
For both data definition and data
manipulation.
Aggregations
SUM
,
AVG
,
COUNT
,
MIN
, and
MAX
can be
applied to a column in a
SELECT
clause to
produce that aggregation on the column.
Also,
COUNT
(*)
counts the number of
tuples.
Example: From
Sells(bar, beer, price)
,
find
the average price of Bud:
SELECT
AVG
(price)
FROM
Sells
WHERE
beer = ’Bud’;
Eliminating Duplicates in Aggregation
Use
DISTINCT
inside an aggregation.
Example:
find the number of different
prices charged for Bud:
Sells(
bar
,
beer
, price)
S
E
L
E
C
T
C
O
U
N
T
(
D
I
S
T
I
N
C
T
p
r
i
c
e
)
FROM
Sells
WHERE
beer = ’Bud’;
NULL
is Ignored in Aggregation
NULL
never contributes to a sum,
average, or count, and can never be
the minimum or maximum of a
column.
But if there are no non-
NULL
values in
a column, then the result of the
aggregation is
NULL
.
Example: Effect of
NULL
’s
SELECT
count
(*)
FROM
Sells
WHERE
beer = ’Bud’;
SELECT
count
(price)
FROM
Sells
WHER
E
beer = ’Bud’;
The number of bars
that sell Bud.
Sells(
bar
,
beer
, price)
Grouping
We may follow a
SELECT
-
FROM
-
WHERE
expression by
GROUP BY
and
a list of attributes.
The relation that results from the
SELECT
-
FROM
-
WHERE
is grouped
according to the values of all those
attributes, and any aggregation is
applied only within each group.
Example: Grouping
•
From
Sells(bar, beer, price)
,
find the
average price for each beer:
SELECT
beer,
AVG
(price)
FROM
Sells
GROUP BY
beer;
Example: Grouping
•
From
Sells(bar, beer, price)
,
find the
average price for each beer:
SELECT
beer,
AVG
(price)
FROM
Sells
GROUP BY
beer;
Output one tuple
for each group
10
Example: Grouping
From
Sells
and
Frequents
,
find for
each drinker the average price of Bud
at the bars they frequent:
Sells(
bar
,
beer
, price)
Frequents(drinker, bar)
SELECT
drinker
,
AVG
(
price
)
FROM
Frequents
,
Sells
WHERE
beer = ’Bud’
AND
Frequents.bar = Sells.bar
GROUP BY
drinker
;
11
Example: Grouping
From
Sells
and
Frequents
,
find for
each drinker the average price of Bud
at the bars they frequent:
compute
drinker-bar-
price for Bud
tuples first,
then group
by drinker.
Sells(
bar
,
beer
, price)
Frequents(drinker, bar)
SELECT
drinker
,
AVG
(
price
)
FROM
Frequents
,
Sells
WHERE
beer = ’Bud’
AND
Frequents.bar = Sells.bar
GROUP BY
drinker
;
Restriction on
SELECT
Lists
With Aggregation
If any aggregation is used, then each
element of the
SELECT
list must be
either:
1.
Aggregated, or
2.
An attribute on the
GROUP BY
list.
Illegal Query Example
You might think you could find the bar
that sells Bud the cheapest by:
SELECT
bar,
MIN
(price)
FROM
Sells
WHERE
beer = ’Bud’;
But this query is
illegal
in SQL.
Sells(
bar
,
beer
, price)
HAVING
Clauses
HAVING
<condition>
may follow a
GROUP BY
clause.
If so, the condition
applies to each
group
, and groups not satisfying the
condition are eliminated.
Example.
From
Sells
and
Beers
,
find the
average price of those beers that are
either served in at least three bars or are
manufactured by Pete’s.
SELECT
beer,
AVG
(price)
FROM
Sells
GROUP BY
beer
Sells(
bar
,
beer
, price)
Beers(
name
, manf)
Example.
From
Sells
and
Beers
,
find the
average price of those beers that are
either served in at least three bars or are
manufactured by Pete’s.
SELECT
beer,
AVG
(price)
FROM
Sells
GROUP BY
beer
group tuples (bar, beer, price)
in Sells in terms of beer
Sells(
bar
,
beer
, price)
Beers(
name
, manf)
Example.
From
Sells
and
Beers
,
find the
average price of those beers that are
either served in at least three bars or are
manufactured by Pete’s.
SELECT
beer,
AVG
(price)
FROM
Sells
GROUP BY
beer
HAVING
COUNT
(bar) >= 3
beer IN
(SELECT name
FROM Beers
WHERE manf = ’Pete’’s’);
group tuples (bar, beer, price)
in Sells in terms of beer
Sells(
bar
,
beer
, price)
Beers(
name
, manf)
at least 3 bars appear
in the beer group
Example.
From
Sells
and
Beers
,
find the
average price of those beers that are
either served in at least three bars or are
manufactured by Pete’s.
SELECT
beer,
AVG
(price)
FROM
Sells
GROUP BY
beer
HAVING
COUNT
(bar) >= 3
OR
beer IN (SELECT name
FROM Beers
WHERE manf = ’Pete’’s’);
group tuples (bar, beer, price)
in Sells in terms of beer
Sells(
bar
,
beer
, price)
Beers(
name
, manf)
at least 3 bars appear
in the beer group
Example.
From
Sells
and
Beers
,
find the
average price of those beers that are
either served in at least three bars or are
manufactured by Pete’s.
SELECT
beer,
AVG
(price)
FROM
Sells
GROUP BY
beer
HAVING
COUNT
(bar) >= 3
OR
beer IN (
SELECT
name
FROM
Beers
WHERE
manf = ’Pete’’s’
);
group tuples (bar, beer, price)
in Sells in terms of beer
Sells(
bar
,
beer
, price)
Beers(
name
, manf)
at least 3 bars appear
in the beer group
beers made
by Pete’s
Example.
From
Sells
and
Beers
,
find the
average price of those beers that are
either served in at least three bars or are
manufactured by Pete’s.
SELECT
beer,
AVG
(price)
FROM
Sells
GROUP BY
beer
HAVING
COUNT
(bar) >= 3
OR
beer
IN
(
SELECT
name
FROM
Beers
WHERE
manf = ’Pete’’s’);
group tuples (bar, beer, price)
in Sells in terms of beer
Sells(
bar
,
beer
, price)
Beers(
name
, manf)
at least 3 bars appear
in the beer group
beers made
by Pete’s
the beer
is made
by Pete’s
Requirements on HAVING
Conditions
These conditions may refer to any
relation or tuple-variable in the
FROM
clause.
They may refer to attributes of those
relations, as long as the attribute makes
sense within a group; i.e., it is either:
1.
A grouping attribute, or
2.
Aggregated.
Requirements on HAVING
Conditions
It is easier to understand this from an
implementation viewpoint:
SELECT
FROM
WHERE
GROUP BY
HAVING
Requirements on HAVING
Conditions
It is easier to understand this from an
implementation viewpoint:
SELECT
FROM
WHERE
GROUP BY
HAVING
step 1, input
step 5, compute the output
step 2, pick the proper tuples
step 3, group the picked tuples
step 4, pick the proper groups
Database Modifications
A
modification
command does not
return a result (as a query does), but
changes the database in some way.
Database Modifications
A
modification
command does not
return a result (as a query does), but
changes the database in some way.
Three kinds of modifications:
1.
Insert
a tuple or tuples.
2.
Delete
a tuple or tuples.
3.
Update
the value(s) of an existing tuple
or tuples.
Insertion
To insert a single tuple:
INSERT INTO
<relation>
VALUES
(<list of values>);
Insertion
To insert a single tuple:
INSERT INTO
<relation>
VALUES
(<list of values>);
Example:
add to
Likes(drinker, beer)
the fact
that Sally likes Bud.
INSERT
INTO
Likes
VALUES
(’Sally’, ’Bud’);
Likes(
drinker
,
beer
)
Insertion
To insert a single tuple:
INSERT INTO
<relation>
VALUES
(<list of values>);
Example:
add to
Likes(drinker, beer)
the fact
that Sally likes Bud.
INSERT
INTO
Likes
VALUES
(’Sally’, ’Bud’);
We may add a list of attributes to
<relation>
.
Two reasons for doing so:
1.
Forget the order of attributes for the relation.
2.
Don’t have values for all attributes, and want the
system to fill in missing ones with default values.
Likes(
drinker
,
beer
)
Insertion
To insert a single tuple:
INSERT INTO
<relation>
VALUES
(<list of values>);
Example:
add to
Likes(drinker, beer)
the fact
that Sally likes Bud.
INSERT
INTO
Likes
VALUES
(’Sally’, ’Bud’);
We may add a list of attributes to
<relation>
.
Two reasons for doing so:
1.
Forget the order of attributes for the relation.
2.
Don’t have values for all attributes, and want the
system to fill in missing ones with default values.
So another solution for the above example:
Likes(
drinker
,
beer
)
INSERT
INTO
Likes(beer, drinker)
VALUES
(‘Bud’, ’Sally’);
Inserting Many Tuples
We may insert the entire result of a
query into a relation, using the form:
INSERT INTO
<relation>
(<subquery>);
Example.
Using
Frequents
, enter into the
new relation
PotBuddies(name)
all of
Sally’s “potential buddies,” i.e., those
drinkers who frequent at least one bar
that Sally also frequents.
Frequents(
drinker
,
bar
)
Example.
Using
Frequents
, enter into the
new relation
PotBuddies(name)
all of
Sally’s “potential buddies,” i.e., those
drinkers who frequent at least one bar
that Sally also frequents.
INSERT INTO PotBuddies
(SELECT d2.drinker
FROM
Frequents d1, Frequents d2
WHERE
d1.drinker = ’Sally’
AND
d2.drinker <> ’Sally’
AND
d1.bar = d2.bar);
Frequents(
drinker
,
bar
)
1.
find all potential
buddies of Sally by
pairing Sally with those
who frequent the bars
Sally frequents.
Example.
Using
Frequents
, enter into the
new relation
PotBuddies(name)
all of
Sally’s “potential buddies,” i.e., those
drinkers who frequent at least one bar
that Sally also frequents.
INSERT INTO PotBuddies
(SELECT d2.drinker
FROM
Frequents d1, Frequents d2
WHERE
d1.drinker = ’Sally’
AND
d2.drinker <> ’Sally’
AND
d1.bar = d2.bar);
Frequents(
drinker
,
bar
)
1.
find all potential
buddies of Sally by
pairing Sally with those
who frequent the bars
Sally frequents.
(Sally, Joe’s, Tom, Joe’s)
(Sally, Sue’s, Jeff, Sue’s)
(Sally, Sue’s, Mary, Sue’s)
……
Example.
Using
Frequents
, enter into the
new relation
PotBuddies(name)
all of
Sally’s “potential buddies,” i.e., those
drinkers who frequent at least one bar
that Sally also frequents.
INSERT INTO PotBuddies
(
SELECT
d2.drinker
FROM
Frequents d1, Frequents d2
WHERE
d1.drinker = ’Sally’
AND
d2.drinker <> ’Sally’
AND
d1.bar = d2.bar);
Frequents(
drinker
,
bar
)
1.
find all potential
buddies of Sally by
pairing Sally with those
who frequent the bars
Sally frequents.
2.
collect
the drinkers
(Sally, Joe’s, Tom, Joe’s)
(Sally, Sue’s, Jeff, Sue’s)
(Sally, Sue’s, Mary, Sue’s)
……
Example.
Using
Frequents
, enter into the
new relation
PotBuddies(name)
all of
Sally’s “potential buddies,” i.e., those
drinkers who frequent at least one bar
that Sally also frequents.
INSERT INTO PotBuddies
(
SELECT
d2.drinker
FROM
Frequents d1, Frequents d2
WHERE
d1.drinker = ’Sally’
AND
d2.drinker <> ’Sally’
AND
d1.bar = d2.bar);
Frequents(
drinker
,
bar
)
1.
find all potential
buddies of Sally by
pairing Sally with those
who frequent the bars
Sally frequents.
2.
collect
the drinkers
Tom
Jeff
Mary
…
Example.
Using
Frequents
, enter into the
new relation
PotBuddies(name)
all of
Sally’s “potential buddies,” i.e., those
drinkers who frequent at least one bar
that Sally also frequents.
INSERT INTO
PotBuddies
(
SELECT
d2.drinker
FROM
Frequents d1, Frequents d2
WHERE
d1.drinker = ’Sally’
AND
d2.drinker <> ’Sally’
AND
d1.bar = d2.bar);
Frequents(
drinker
,
bar
)
1.
find all potential
buddies of Sally by
pairing Sally with those
who frequent the bars
Sally frequents.
2.
collect
the drinkers
3.
add the drinkers
to PotBuddies
Tom
Jeff
Mary
…
Example.
Using
Frequents
, enter into the
new relation
PotBuddies(name)
all of
Sally’s “potential buddies,” i.e., those
drinkers who frequent at least one bar
that Sally also frequents.
INSERT INTO
PotBuddies
(
SELECT
d2.drinker
FROM
Frequents d1, Frequents d2
WHERE
d1.drinker = ’Sally’
AND
d2.drinker <> ’Sally’
AND
d1.bar = d2.bar);
Frequents(
drinker
,
bar
)
1.
find all potential
buddies of Sally by
pairing Sally with those
who frequent the bars
Sally frequents.
2.
collect
the drinkers
3.
add the drinkers
to PotBuddies
PotBuddies
Deletion
To delete tuples satisfying a condition
from some relation:
DELETE FROM
<relation>
WHERE
<condition>;
Deletion
To delete tuples satisfying a condition
from some relation:
DELETE FROM
<relation>
WHERE
<condition>;
Example.
Delete from
Likes
the fact
that Sally likes Bud:
DELETE FROM
Likes
WHERE
drinker = ’Sally’
AND
beer = ’Bud’;
Likes(
drinker
,
beer
)
Deletion
To delete tuples satisfying a condition
from some relation:
DELETE FROM
<relation>
WHERE
<condition>;
Example.
Delete from
Likes
the fact
that Sally likes Bud:
DELETE FROM
Likes
WHERE
drinker = ’Sally’
AND
beer = ’Bud’;
To make the relation
Likes
empty:
DELETE FROM
Likes
Note that no WHERE clause is needed
Likes(
drinker
,
beer
)
Example: Delete Many Tuples
Delete from
Beers(name, manf)
all
beers for which there is another beer
by the same manufacturer.
DELETE FROM
Beers b
WHERE EXISTS
(
SELECT
name
FROM
Beers
WHERE
manf = b.manf
AND
name <> b.name);
Example: Delete Many Tuples
Delete from
Beers(name, manf)
all
beers for which there is another beer
by the same manufacturer.
Beers with the same manufacturer
and a different name from the name
of the beer represented by tuple b.
DELETE FROM
Beers b
WHERE EXISTS
(
SELECT
name
FROM
Beers
WHERE
manf = b.manf
AND
name <> b.name);
Semantics of Deletion
Suppose
Anheuser-Busch
makes only
Bud
and
Bud Lite
.
Delete from
Beers(name, manf)
all
beers for which there is another
beer by the same manufacturer.
Semantics of Deletion
Suppose
Anheuser-Busch
makes only
Bud
and
Bud Lite
.
If we come to the tuple
b
for
Bud
first.
Delete from
Beers(name, manf)
all
beers for which there is another
beer by the same manufacturer.
Semantics of Deletion
Suppose
Anheuser-Busch
makes only
Bud
and
Bud Lite
.
If we come to the tuple
b
for
Bud
first.
The subquery is nonempty, because of the
Bud Lite
tuple, so we delete
Bud
.
Delete from
Beers(name, manf)
all
beers for which there is another
beer by the same manufacturer.
Semantics of Deletion
Suppose
Anheuser-Busch
makes only
Bud
and
Bud Lite
.
If we come to the tuple
b
for
Bud
first.
The subquery is nonempty, because of the
Bud Lite
tuple, so we delete
Bud
.
Delete from
Beers(name, manf)
all
beers for which there is another
beer by the same manufacturer.
Semantics of Deletion
Suppose
Anheuser-Busch
makes only
Bud
and
Bud Lite
.
If we come to the tuple
b
for
Bud
first.
The subquery is nonempty, because of the
Bud Lite
tuple, so we delete
Bud
.
(?)
?
Delete from
Beers(name, manf)
all
beers for which there is another
beer by the same manufacturer.
Semantics of Deletion
Suppose
Anheuser-Busch
makes only
Bud
and
Bud Lite
.
If we come to the tuple
b
for
Bud
first.
The subquery is nonempty, because of the
Bud Lite
tuple, so we delete
Bud
.
(?)
Now, when
b
is the tuple for
Bud Lite
,
do we
delete that tuple too?
?
?
Delete from
Beers(name, manf)
all
beers for which there is another
beer by the same manufacturer.
Semantics of Deletion
Suppose
Anheuser-Busch
makes only
Bud
and
Bud Lite
.
If we come to the tuple
b
for
Bud
first.
The subquery is nonempty, because of the
Bud Lite
tuple, so we delete
Bud
.
(?)
Now, when
b
is the tuple for
Bud Lite
,
do we
delete that tuple too?
Answer:
we
do
delete
Bud Lite
as well.
Delete from
Beers(name, manf)
all
beers for which there is another
beer by the same manufacturer.
Semantics of Deletion
Suppose
Anheuser-Busch
makes only
Bud
and
Bud Lite
.
If we come to the tuple
b
for
Bud
first.
The subquery is nonempty, because of the
Bud Lite
tuple, so we delete
Bud
.
(?)
Now, when
b
is the tuple for
Bud Lite
,
do we
delete that tuple too?
Answer:
we
do
delete
Bud Lite
as well.
Reason:
Deletion proceeds in two stages:
Delete from
Beers(name, manf)
all
beers for which there is another
beer by the same manufacturer.
Semantics of Deletion
Suppose
Anheuser-Busch
makes only
Bud
and
Bud Lite
.
If we come to the tuple
b
for
Bud
first.
The subquery is nonempty, because of the
Bud Lite
tuple, so we delete
Bud
.
(?)
Now, when
b
is the tuple for
Bud Lite
,
do we
delete that tuple too?
Answer:
we
do
delete
Bud Lite
as well.
Reason:
Deletion proceeds in two stages:
--
Mark all tuples for which
WHERE
condition holds.
Delete from
Beers(name, manf)
all
beers for which there is another
beer by the same manufacturer.
Semantics of Deletion
Suppose
Anheuser-Busch
makes only
Bud
and
Bud Lite
.
If we come to the tuple
b
for
Bud
first.
The subquery is nonempty, because of the
Bud Lite
tuple, so we delete
Bud
.
(?)
Now, when
b
is the tuple for
Bud Lite
,
do we
delete that tuple too?
Answer:
we
do
delete
Bud Lite
as well.
Reason:
Deletion proceeds in two stages:
--
Mark all tuples for which
WHERE
condition holds.
-- Delete the marked tuples.
Delete from
Beers(name, manf)
all
beers for which there is another
beer by the same manufacturer.
Updates
To change certain attributes in certain
tuples of a relation:
UPDATE
<relation>
SET
<list of attribute assignments>
WHERE
<condition on tuples>;
Change Fred’s phone number to 555-1212:
UPDATE
Drinkers
SET
phone = ’555-1212’
WHERE
name = ’Fred’;
Drinkers(
name
, addr, phone)
Updates
To change certain attributes in certain
tuples of a relation:
UPDATE
<relation>
SET
<list of attribute assignments>
WHERE
<condition on tuples>;
Change Fred’s phone number to 555-1212:
UPDATE
Drinkers
SET
phone = ’555-1212’
WHERE
name = ’Fred’;
Make $4 the maximum price for beer
(make updates for several tuples):
UPDATE
Sells
SET
price = 4.00
WHERE
price > 4.00;
Drinkers(
name
, addr, phone)
Sells(
bar
,
beer
, price)
SQL offers powerful aggregation functions like SUM, AVG, COUNT, MIN, and MAX to perform calculations on column data efficiently. By utilizing DISTINCT and GROUP BY clauses, you can manipulate and organize your data effectively in database systems while handling NULL values appropriately.
Download Presentation
Please find below an Image/Link to download the presentation.
The content on the website is provided AS IS for your information and personal use only. It may not be sold, licensed, or shared on other websites without obtaining consent from the author.If you encounter any issues during the download, it is possible that the publisher has removed the file from their server.
You are allowed to download the files provided on this website for personal or commercial use, subject to the condition that they are used lawfully. All files are the property of their respective owners.
The content on the website is provided AS IS for your information and personal use only. It may not be sold, licensed, or shared on other websites without obtaining consent from the author.
E N D
Presentation Transcript
CSCE-608 Database Systems Spring 2024 Instructor: Jianer Chen Office: PETR 428 Phone: 845-4259 Email: chen@cse.tamu.edu Notes 12: SQL Grouping, aggregation, and having
SQL: Structured Query language a very-high-level language. * say what to do rather than how to do it. * avoid a lot of data-manipulation details needed in procedural languages like C++ or Java. Database management system figures out the best way to execute queries * called query optimization For both data definition and data manipulation.
Aggregations SUM, AVG, COUNT, MIN, and MAX can be applied to a column in a SELECT clause to produce that aggregation on the column. Also, COUNT(*) counts the number of tuples. Example: From Sells(bar, beer, price), find the average price of Bud: SELECT AVG(price) FROM Sells WHERE beer = Bud ;
Sells(bar, beer, price) Eliminating Duplicates in Aggregation Use DISTINCT inside an aggregation. Example: find the number of different prices charged for Bud: SELECT COUNT(DISTINCT price) FROM Sells WHERE beer = Bud ;
NULL is Ignored in Aggregation NULL never contributes to a sum, average, or count, and can never be the minimum or maximum of a column. But if there are no non-NULL values in a column, then the result of the aggregation is NULL.
Sells(bar, beer, price) Example: Effect of NULL s SELECT count(*) FROM Sells WHERE beer = Bud ; The number of bars that sell Bud. SELECT count(price) FROM Sells WHERE beer = Bud ; The number of bars that sell Bud at a known price.
Grouping We may follow a SELECT-FROM- WHERE expression by GROUP BY and a list of attributes. The relation that results from the SELECT-FROM-WHERE is grouped according to the values of all those attributes, and any aggregation is applied only within each group.
Example: Grouping From Sells(bar, beer, price), find the average price for each beer: SELECT beer, AVG(price) FROM Sells GROUP BY beer;
Example: Grouping From Sells(bar, beer, price), find the average price for each beer: SELECT beer, AVG(price) FROM Sells GROUP BY beer; Output one tuple for each group
Sells(bar, beer, price) Frequents(drinker, bar) Example: Grouping From Sells and Frequents, find for each drinker the average price of Bud at the bars they frequent: SELECT drinker, AVG(price) FROM Frequents, Sells WHERE beer = Bud AND Frequents.bar = Sells.bar GROUP BY drinker; 10
Sells(bar, beer, price) Frequents(drinker, bar) Example: Grouping From Sells and Frequents, find for each drinker the average price of Bud at the bars they frequent: compute drinker-bar- price for Bud tuples first, then group by drinker. SELECT drinker, AVG(price) FROM Frequents, Sells WHERE beer = Bud AND Frequents.bar = Sells.bar GROUP BY drinker; 11
Restriction on SELECT Lists With Aggregation If any aggregation is used, then each element of the SELECT list must be either: 1. Aggregated, or 2. An attribute on the GROUP BY list.
Sells(bar, beer, price) Illegal Query Example You might think you could find the bar that sells Bud the cheapest by: SELECT SELECT bar, bar, MIN MIN(price) FROM FROM Sells Sells WHERE WHERE beer = Bud ; beer = Bud ; But this query is illegal in SQL. (price)
HAVING Clauses HAVING <condition> may follow a GROUP BY clause. If so, the condition applies to each group, and groups not satisfying the condition are eliminated.
Sells(bar, beer, price) Beers(name, manf) Example. From Sells and Beers, find the average price of those beers that are either served in at least three bars or are manufactured by Pete s. SELECT beer, AVG(price) FROM Sells GROUP BY beer
Sells(bar, beer, price) Beers(name, manf) Example. From Sells and Beers, find the average price of those beers that are either served in at least three bars or are manufactured by Pete s. group tuples (bar, beer, price) in Sells in terms of beer SELECT beer, AVG(price) FROM Sells GROUP BY beer
Sells(bar, beer, price) Beers(name, manf) Example. From Sells and Beers, find the average price of those beers that are either served in at least three bars or are manufactured by Pete s. group tuples (bar, beer, price) in Sells in terms of beer SELECT beer, AVG(price) FROM Sells GROUP BY beer HAVING COUNT(bar) >= 3 (SELECT name FROM Beers WHERE manf = Pete s ); at least 3 bars appear in the beer group beer IN
Sells(bar, beer, price) Beers(name, manf) Example. From Sells and Beers, find the average price of those beers that are either served in at least three bars or are manufactured by Pete s. group tuples (bar, beer, price) in Sells in terms of beer SELECT beer, AVG(price) FROM Sells GROUP BY beer HAVING COUNT(bar) >= 3 OR beer IN (SELECT name FROM Beers WHERE manf = Pete s ); at least 3 bars appear in the beer group
Sells(bar, beer, price) Beers(name, manf) Example. From Sells and Beers, find the average price of those beers that are either served in at least three bars or are manufactured by Pete s. group tuples (bar, beer, price) in Sells in terms of beer SELECT beer, AVG(price) FROM Sells GROUP BY beer HAVING COUNT(bar) >= 3 OR beer IN (SELECT name FROM Beers WHERE manf = Pete s ); beers made by Pete s at least 3 bars appear in the beer group
Sells(bar, beer, price) Beers(name, manf) Example. From Sells and Beers, find the average price of those beers that are either served in at least three bars or are manufactured by Pete s. group tuples (bar, beer, price) in Sells in terms of beer SELECT beer, AVG(price) FROM Sells GROUP BY beer HAVING COUNT(bar) >= 3 OR beer IN (SELECT name FROM Beers WHERE manf = Pete s ); beers made by Pete s at least 3 bars appear in the beer group the beer is made by Pete s
Requirements on HAVING Conditions These conditions may refer to any relation or tuple-variable in the FROM clause. They may refer to attributes of those relations, as long as the attribute makes sense within a group; i.e., it is either: 1. A grouping attribute, or 2. Aggregated.
Requirements on HAVING Conditions It is easier to understand this from an implementation viewpoint: SELECT FROM WHERE GROUP BY HAVING
Requirements on HAVING Conditions It is easier to understand this from an implementation viewpoint: SELECT FROM WHERE GROUP BY HAVING step 4, pick the proper groups step 5, compute the output step 1, input step 2, pick the proper tuples step 3, group the picked tuples
Database Modifications A modification command does not return a result (as a query does), but changes the database in some way.
Database Modifications A modification command does not return a result (as a query does), but changes the database in some way. Three kinds of modifications: 1. Insert a tuple or tuples. 2. Delete a tuple or tuples. 3. Update the value(s) of an existing tuple or tuples.
Insertion To insert a single tuple: INSERT INTO <relation> VALUES (<list of values>);
Likes(drinker, beer) Insertion To insert a single tuple: INSERT INTO <relation> VALUES (<list of values>); Example: add to Likes(drinker, beer) the fact that Sally likes Bud. INSERT INTO Likes VALUES( Sally , Bud );
Likes(drinker, beer) Insertion To insert a single tuple: INSERT INTO <relation> VALUES (<list of values>); Example: add to Likes(drinker, beer) the fact that Sally likes Bud. INSERT INTO Likes VALUES( Sally , Bud ); We may add a list of attributes to <relation>. Two reasons for doing so: 1. Forget the order of attributes for the relation. 2. Don t have values for all attributes, and want the system to fill in missing ones with default values.
Likes(drinker, beer) Insertion To insert a single tuple: INSERT INTO <relation> VALUES (<list of values>); Example: add to Likes(drinker, beer) the fact that Sally likes Bud. INSERT INTO Likes VALUES( Sally , Bud ); We may add a list of attributes to <relation>. Two reasons for doing so: 1. Forget the order of attributes for the relation. 2. Don t have values for all attributes, and want the system to fill in missing ones with default values. So another solution for the above example: INSERT INTO Likes(beer, drinker) VALUES( Bud , Sally );
Inserting Many Tuples We may insert the entire result of a query into a relation, using the form: INSERT INTO <relation> (<subquery>);
Frequents(drinker, bar) Example. Using Frequents, enter into the new relation PotBuddies(name) all of Sally s potential buddies, i.e., those drinkers who frequent at least one bar that Sally also frequents.
Frequents(drinker, bar) Example. Using Frequents, enter into the new relation PotBuddies(name) all of Sally s potential buddies, i.e., those drinkers who frequent at least one bar that Sally also frequents. 1. find all potential buddies of Sally by pairing Sally with those who frequent the bars Sally frequents. INSERT INTO PotBuddies (SELECT d2.drinker FROM Frequents d1, Frequents d2 WHERE d1.drinker = Sally AND d2.drinker <> Sally AND d1.bar = d2.bar);
Frequents(drinker, bar) Example. Using Frequents, enter into the new relation PotBuddies(name) all of Sally s potential buddies, i.e., those drinkers who frequent at least one bar that Sally also frequents. 1. find all potential buddies of Sally by pairing Sally with those who frequent the bars Sally frequents. INSERT INTO PotBuddies (SELECT d2.drinker FROM Frequents d1, Frequents d2 WHERE d1.drinker = Sally AND d2.drinker <> Sally AND d1.bar = d2.bar); (Sally, Joe s, Tom, Joe s) (Sally, Sue s, Jeff, Sue s) (Sally, Sue s, Mary, Sue s)
Frequents(drinker, bar) Example. Using Frequents, enter into the new relation PotBuddies(name) all of Sally s potential buddies, i.e., those drinkers who frequent at least one bar that Sally also frequents. 1. find all potential buddies of Sally by pairing Sally with those who frequent the bars Sally frequents. 2. collect the drinkers INSERT INTO PotBuddies (SELECT d2.drinker FROM Frequents d1, Frequents d2 WHERE d1.drinker = Sally AND d2.drinker <> Sally AND d1.bar = d2.bar); (Sally, Joe s, Tom, Joe s) (Sally, Sue s, Jeff, Sue s) (Sally, Sue s, Mary, Sue s)
Frequents(drinker, bar) Example. Using Frequents, enter into the new relation PotBuddies(name) all of Sally s potential buddies, i.e., those drinkers who frequent at least one bar that Sally also frequents. 1. find all potential buddies of Sally by pairing Sally with those who frequent the bars Sally frequents. 2. collect the drinkers INSERT INTO PotBuddies (SELECT d2.drinker FROM Frequents d1, Frequents d2 WHERE d1.drinker = Sally AND d2.drinker <> Sally AND d1.bar = d2.bar); Tom Jeff Mary
Frequents(drinker, bar) Example. Using Frequents, enter into the new relation PotBuddies(name) all of Sally s potential buddies, i.e., those drinkers who frequent at least one bar that Sally also frequents. 1. find all potential buddies of Sally by pairing Sally with those who frequent the bars Sally frequents. 2. collect the drinkers INSERT INTO PotBuddies (SELECT d2.drinker FROM Frequents d1, Frequents d2 WHERE d1.drinker = Sally AND d2.drinker <> Sally AND d1.bar = d2.bar); Tom Jeff Mary 3. add the drinkers to PotBuddies
Frequents(drinker, bar) Example. Using Frequents, enter into the new relation PotBuddies(name) all of Sally s potential buddies, i.e., those drinkers who frequent at least one bar that Sally also frequents. PotBuddies 1. find all potential buddies of Sally by pairing Sally with those who frequent the bars Sally frequents. 2. collect the drinkers d2.drinker INSERT INTO PotBuddies (SELECT d2.drinker FROM Frequents d1, Frequents d2 WHERE d1.drinker = Sally AND d2.drinker <> Sally AND d1.bar = d2.bar); Tom Jeff Mary 3. add the drinkers to PotBuddies
Deletion To delete tuples satisfying a condition from some relation: DELETE FROM <relation> WHERE <condition>;
Likes(drinker, beer) Deletion To delete tuples satisfying a condition from some relation: DELETE FROM <relation> WHERE <condition>; Example. Delete from Likes the fact that Sally likes Bud: DELETE FROM Likes WHERE drinker = Sally AND beer = Bud ;
Likes(drinker, beer) Deletion To delete tuples satisfying a condition from some relation: DELETE FROM <relation> WHERE <condition>; Example. Delete from Likes the fact that Sally likes Bud: DELETE FROM Likes WHERE drinker = Sally AND beer = Bud ; To make the relation Likes empty: DELETE FROM Likes Note that no WHERE clause is needed
Example: Delete Many Tuples Delete from Beers(name, manf) all beers for which there is another beer by the same manufacturer. DELETE FROM Beers b WHERE EXISTS ( SELECT name FROM Beers WHERE manf = b.manf AND name <> b.name);
Example: Delete Many Tuples Delete from Beers(name, manf) all beers for which there is another beer by the same manufacturer. DELETE FROM Beers b WHERE EXISTS ( SELECT name FROM Beers WHERE manf = b.manf AND name <> b.name); Beers with the same manufacturer and a different name from the name of the beer represented by tuple b.
name manf Semantics of Deletion Bud Anheuser-Busch Bud Lite Anheuser-Busch Suppose Anheuser-Busch makes only Bud and Bud Lite. Delete from Beers(name, manf) all beers for which there is another beer by the same manufacturer.
name manf Semantics of Deletion Bud Anheuser-Busch Bud Lite Anheuser-Busch Suppose Anheuser-Busch makes only Bud and Bud Lite. If we come to the tuple b for Bud first. Delete from Beers(name, manf) all beers for which there is another beer by the same manufacturer.
name manf Semantics of Deletion Bud Anheuser-Busch Bud Lite Anheuser-Busch Suppose Anheuser-Busch makes only Bud and Bud Lite. If we come to the tuple b for Bud first. The subquery is nonempty, because of the Bud Lite tuple, so we delete Bud. Delete from Beers(name, manf) all beers for which there is another beer by the same manufacturer.
name manf Semantics of Deletion Bud Anheuser-Busch Bud Lite Anheuser-Busch Suppose Anheuser-Busch makes only Bud and Bud Lite. If we come to the tuple b for Bud first. The subquery is nonempty, because of the Bud Lite tuple, so we delete Bud. Delete from Beers(name, manf) all beers for which there is another beer by the same manufacturer.
name manf Semantics of Deletion ? Bud Anheuser-Busch Bud Lite Anheuser-Busch Suppose Anheuser-Busch makes only Bud and Bud Lite. If we come to the tuple b for Bud first. The subquery is nonempty, because of the Bud Lite tuple, so we delete Bud. (?) Delete from Beers(name, manf) all beers for which there is another beer by the same manufacturer.
name manf Semantics of Deletion ? Bud Anheuser-Busch Bud Lite Anheuser-Busch Suppose Anheuser-Busch makes only Bud and Bud Lite. If we come to the tuple b for Bud first. The subquery is nonempty, because of the Bud Lite tuple, so we delete Bud. (?) Now, when b is the tuple for Bud Lite, do we delete that tuple too? ? Delete from Beers(name, manf) all beers for which there is another beer by the same manufacturer.
name manf Semantics of Deletion Bud Anheuser-Busch Bud Lite Anheuser-Busch Suppose Anheuser-Busch makes only Bud and Bud Lite. If we come to the tuple b for Bud first. The subquery is nonempty, because of the Bud Lite tuple, so we delete Bud. (?) Now, when b is the tuple for Bud Lite, do we delete that tuple too? Answer: we do delete Bud Lite as well. Delete from Beers(name, manf) all beers for which there is another beer by the same manufacturer.
name manf Semantics of Deletion Bud Anheuser-Busch Bud Lite Anheuser-Busch Suppose Anheuser-Busch makes only Bud and Bud Lite. If we come to the tuple b for Bud first. The subquery is nonempty, because of the Bud Lite tuple, so we delete Bud. (?) Now, when b is the tuple for Bud Lite, do we delete that tuple too? Answer: we do delete Bud Lite as well. Reason: Deletion proceeds in two stages: Delete from Beers(name, manf) all beers for which there is another beer by the same manufacturer.
