More SQL

Extended Relational Algebra

Outerjoins,
Grouping/Aggregation
Insert/Delete/Update
1
 The Extended Algebra

δ = eliminate duplicates from bags.

τ = sort tuples.
γ = grouping and aggregation.
Outerjoin : avoids “dangling tuples” =
tuples that do not join with
anything.

2
 Duplicate Elimination
 R1 := δ(R2).
 R1 consists of one copy of each
tuple that appears in R2 one or
more times.

3
 Example: Duplicate
Elimination
R = (A B)
1 2
3 4
1 2

δ(R) = A B
1 2
3 4

4
 Sorting
 R1 := τL (R2).
 L is a list of some of the attributes of R2.
 R1 is the list of tuples of R2 sorted first
on the value of the first attribute on L,
then on the second attribute of L, and
so on.
 Break ties arbitrarily.
 τ is the only operator whose result is
neither a set nor a bag.
5
 Example: Sorting
R= (A B)
1 2
3 4
5 2

τB (R) = [(5,2), (1,2), (3,4)]

6
 Aggregation Operators
 Aggregation operators are not
operators of relational algebra.
 Rather, they apply to entire
columns of a table and produce a
single result.
 The most important examples:
SUM, AVG, COUNT, MIN, and MAX.

7
Example: Aggregation
R= (A B)
1 3
3 4
3 2

SUM(A) = 7
COUNT(A) = 3
MAX(B) = 4
AVG(B) = 3

8
 Grouping Operator
 R1 := γL (R2). L is a list of
elements that are either:
1. Individual (grouping ) attributes.
2. AGG(A ), where AGG is one of the
aggregation operators and A is an
attribute.
• An arrow and a new attribute name
renames the component.

9
 Applying γL(R)
 Group R according to all the
grouping attributes on list L.
 That is: form one group for each distinct
list of values for those attributes in R.
 Within each group, compute AGG(A )
for each aggregation on list L.
 Result has one tuple for each group:
1. The grouping attributes and
2. Their group’s aggregations.
10
 Example:
Grouping/Aggregation
R= (A B C)
1 2 3 Then, average C
4 5 6 within groups:
1 2 5
A B X
γA,B,AVG(C)->X (R) = ?? 1 2 4
First, group R by A and B : 4 5 6
A B C
1 2 3
1 2 5
4 5 6 11
 Outerjoin
 Suppose we join R ⋈C S.
 A tuple of R that has no tuple of S
with which it joins is said to be
dangling.
 Similarly for a tuple of S.
 Outerjoin preserves dangling tuples
by padding them NULL.
12
 Example: Outerjoin

R= (A B) S= (B C)
1 2 2 3
4 5 6 7

(1,2) joins with (2,3), but the other two tuples

are dangling.
R OUTERJOIN S = A B C
1 2 3
4 5 NULL
NULL 6 7
13
Now --- Back to SQL

Each Operation Has a SQL

Equivalent

14
 Outerjoins
 R OUTER JOIN S is the core of an
outerjoin expression. It is modified
by:
1. Optional NATURAL in front of OUTER.
2. Optional ON <condition> after JOIN.
3. Optional LEFT, RIGHT, or FULL before
OUTER. Only one
 LEFT = pad dangling tuples of R only. of these
 RIGHT = pad dangling tuples of S only.
 FULL = pad both; this choice is the default.
15
 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.

16
 Example: Aggregation
 From Sells(Cafe, Drink, price), find
the average price of Coke:
SELECT AVG(price)
FROM Sells
WHERE Drink = ’Coke’;

17
 Eliminating Duplicates in
an Aggregation

 Use DISTINCT inside an aggregation.

 Example: find the number of
different prices charged for Coke:
SELECT COUNT(DISTINCT
price)
FROM Sells
WHERE Drink = ’Coke’; 18
 NULL’s 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.
 Exception: COUNT of an empty set is 0.
19
Example: Effect of NULL’s
SELECT count(*) The number of Cafes
that sell Coke.
FROM Sells
WHERE Drink = ’Coke’;

SELECT count(price) The number of Cafes

that sell Coke at a
FROM Sells known price.

WHERE Drink = ’Coke’;

20
 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.
21
 Example: Grouping
 From Sells(Cafe, Drink, price), find
the average price for each Drink:
SELECT Drink, AVG(price)
FROM Sells
GROUP BY Drink;
Drink AVG(price)
Coke 2.33
… …
22
 Example: Grouping
 From Sells(Cafe, Drink, price) and
Frequents(drinker, Cafe), find for each
drinker the average price of Coke at the
Cafes they frequent:
Compute all
SELECT drinker, AVG(price) drinker-Cafe-
FROM Frequents, Sells price triples
for Coke.
WHERE Drink = ’Coke’ AND
Then group
[Link] = [Link]
them by
GROUP BY drinker; drinker.
23
 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.

24
 Illegal Query Example
 You might think you could find the
Cafe that sells Coke the cheapest
by:
SELECT Cafe, MIN(price)
FROM Sells
WHERE Drink = ’Coke’;
 But this query is illegal in SQL.
25
 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.

26
 Example: HAVING
 From Sells(Cafe, Drink, price) and
Drinks(name, manf), find the
average price of those Drinks that
are either served in at least three
Cafes or are manufactured by
Pete’s.

27
 Solution
Drink groups with at least
SELECT Drink, AVG(price)3Drink
non-NULL Cafes and also
groups where the
FROM Sells manufacturer is Pete’s.

GROUP BY Drink
HAVING COUNT(Cafe) >= 3 OR
Drinks manu-
Drink IN (SELECT name factured by
FROM Drinks Pete’s.

WHERE manf = ’Pete’’s’);

28
 Requirements on HAVING
Conditions
 Anything goes in a subquery.
 Outside subqueries, they may refer
to attributes only if they are either:
1. A grouping attribute, or
2. Aggregated
(same condition as for SELECT
clauses with aggregation).

29
 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.

30
 Insertion
 To insert a single tuple:
INSERT INTO <relation>
VALUES ( <list of values> );
 Example: add to Likes(drinker,
Drink) the fact that Sally likes Coke.
INSERT INTO Likes
VALUES(’Sally’, ’Coke’);
31
 Specifying Attributes in
INSERT
 We may add to the relation name a
list of attributes.
 Two reasons to do so:
1. We forget the standard order of
attributes for the relation.
2. We don’t have values for all attributes,
and we want the system to fill in missing
components with NULL or a default value.

32
 Example: Specifying
Attributes
 Another way to add the fact that
Sally likes Coke to Likes(drinker,
Drink):

INSERT INTO Likes(Drink,

drinker)
VALUES(’Coke’, ’Sally’);
33
 Adding Default Values
 In a CREATE TABLE statement, we
can follow an attribute by DEFAULT
and a value.
 When an inserted tuple has no
value for that attribute, the default
will be used.

34
Example: Default Values
CREATE TABLE Drinkers (
name CHAR(30) PRIMARY KEY,
addr CHAR(50)
DEFAULT ’123 Sesame St.’,
phone CHAR(16)
);

35
 Example: Default Values
INSERT INTO
Drinkers(name)
VALUES(’Sally’);
Resulting tuple:
name address phone
Sally 123 Sesame St NULL

36
 Inserting Many Tuples
 We may insert the entire result of
a query into a relation, using the
form:
INSERT INTO <relation>
( <subquery> );

37
 Example: Insert a
Subquery
 Using Frequents(drinker, Cafe),
enter into the new relation
PotBuddies(name) all of Sally’s
“potential buddies,” i.e., those
drinkers who frequent at least one
Cafe that Sally also frequents.

38
The other Pairs of Drinker
drinker
Solution tuples where the
first is for Sally,
the second is for
someone else,
INSERT INTO PotCokedies and the Cafes are
the same.
(SELECT [Link]
FROM Frequents d1, Frequents d2
WHERE [Link] = ’Sally’ AND
[Link] <> ’Sally’ AND
[Link] = [Link]
);
39
 Deletion
 To delete tuples satisfying a
condition from some relation:
DELETE FROM <relation>
WHERE <condition>;

40
 Example: Deletion
 Delete from Likes(drinker, Drink)
the fact that Sally likes Coke:
DELETE FROM Likes
WHERE drinker = ’Sally’
AND
Drink = ’Coke’;

41
Example: Delete all Tuples
 Make the relation Likes empty:

DELETE FROM Likes;

 Note no WHERE clause needed.

42
 Example: Delete Some
Tuples
 Delete from Drinks(name, manf) all
Drinks for which there is another
Drink by the same manufacturer.
Drinks with the same
DELETE FROM Drinks b manufacturer and
a different name
WHERE EXISTS ( from the name of
the Drink represented
SELECT name FROM Drinks by tuple b.

WHERE manf = [Link] AND

name <> [Link]);
43
 Semantics of Deletion ---
(1)
 Suppose Anheuser-Busch makes only
Coke and Coke Lite.
 Suppose we come to the tuple b for
Coke first.
 The subquery is nonempty, because
of the Coke Lite tuple, so we delete
Coke.
 Now, when b is the tuple for Coke
Lite, do we delete that tuple too? 44
 Semantics of Deletion ---
(2)
 Answer: we do delete Coke Lite
as well.
 The reason is that deletion
proceeds in two stages:
1. Mark all tuples for which the WHERE
condition is satisfied.
2. Delete the marked tuples.

45
 Updates
 To change certain attributes in
certain tuples of a relation:
UPDATE <relation>
SET <list of attribute
assignments>
WHERE <condition on
tuples>;
46
 Example: Update
 Change drinker Fred’s phone
number to 555-1212:

UPDATE Drinkers
SET phone = ’555-1212’
WHERE name = ’Fred’;

47
 Example: Update Several
Tuples
 Make $4 the maximum price for
Drink:

UPDATE Sells
SET price = 4.00
WHERE price > 4.00;

48