SQL

Part 4

1
Aggregation Functions

2
Aggregation

• In addition to retrieving data, we can use SQL to summarize data or perform some
computation
• SQL supports operations for computing aggregate values of any column

select aggregation(column)
from table

aggregation = count, max, min, avg, sum

3
Aggregation Functions

select aggregation(column)
from table

COUNT ([DISTINCT] A) - the number of (unique) values in the A column

SUM ([DISTINCT] A) - the sum of all (unique) values in the A column
AVG ([DISTINCT] A) - the average of all (unique) values in the A column
MIN (A) - the minimum value in the A column
MAX (A) - the maximum value in the A column

4
Aggregation - COUNT

Sailors (sid,name,rating,age)
sid name rating age
Find the number of sailors
1 Dusty 7 45
Output 2 Rusty 10 35
select count(*) count 3 Horatio 5 35
from Sailors; 5 4 Horatio 10 18
5 Julius null 25

Find the number of ratings of sailors

select count(rating) count

from Sailors; 4

Find the number of distinct ratings of sailors

select count(distinct rating) count

from Sailors; 3
Aggregation – MIN, MAX

Sailors (sid,name,rating,age)
sid name rating age
Find the minimum rating
1 Dusty 7 45
Output 2 Rusty 10 35
select min(rating) min 3 Horatio 5 35
from Sailors; 5 4 Horatio 10 18
5 Julius null 25

Find the maximum rating of sailors

select max(rating) max

from Sailors; 10

Find the maximum of distinct ratings of sailors Same single value

select max(distinct rating) max

from Sailors; 10
Aggregation – AVG, SUM

Sailors (sid,name,rating,age)
sid name rating age
Find the average rating of sailors
1 Dusty 7 45
Output 2 Rusty 10 35
select avg(rating) avg 3 Horatio 5 35
from Sailors; 8.00 4 Horatio 10 18
5 Julius null 25

Find the sum of ratings of sailors

select sum(rating) sum

from Sailors; 32

Find the sum of distinct ratings of sailors

select sum(distinct rating) sum

from Sailors; 22
 Aggregation - example

Sailors (sid,name,rating,age)
sid name rating age
1 Dusty 7 45
2 Rusty 10 35
3 Horatio 5 35
4 Horatio 10 18
5 Julius null 25

Find the number of sailors, their minimum age and average rating

count min avg

select count(*), min(age), avg(rating)
5 18 8.00
from Sailors;
 Aggregation - example

Sailors (sid,name,rating,age)
sid name rating age
1 Dusty 7 45
2 Rusty 10 35
3 Horatio 5 35
4 Horatio 10 18
5 Julius null 25

Find the name and age of the oldest This is illegal in SQL;
name must be used in an
sailor
aggregate function or must
select name, max(age) appear in the group by clause
from Sailors; (Although some tools allow this,
e.g., sqlite)

select [Link], [Link] name age

from Sailors S Dusty 45
where [Link] = (select max([Link])
from Sailors S2);
 Aggregation – more examples

Sailors (sid,name,rating,age)
What does this query compute? sid name rating age
1 Dusty 7 45
2 Rusty 10 35
select count(*)
3 Horatio 5 35
from (select rating from sailors); 4 Horatio 10 18
5 Julius null 25

Answer:
count
5

WARNING !

All aggregate functions except COUNT(*) will ignore

NULL values when computing their results.
 Aggregation – more examples

Find the names of sailors who are older than the oldest sailor with a raiting of 10

Sailors (sid,name,rating,age)
select [Link] sid name rating age
from Sailors S 1 Dusty 7 45
where [Link] > (select max([Link]) name
from Sailors S2 2 Rusty 10 35
Dusty
where [Link]=10); 3 Horatio 5 35
4 Horatio 10 18
5 Julius null 25
Can also be written as

select [Link]
from Sailors S
where [Link] > all (select [Link] Not Preferred:
from Sailors S2
where [Link]=10); ALL query is more
error prone – one
could easily (and
incorrectly) use
ANY instead of ALL
 Question

Which answer is correct?

Sailors (sid,name,rating,age)
sid name rating age
select rating, min(age)
1 Dusty 7 45
from Sailors
2 Rusty 10 35
where age > 35
3 Horatio 5 35
group by rating;
4 Horatio 10 18
5 Julius null 25

A B C
rating min rating min rating min
7 45 7 45 7 45
10 null 10 null
5 null 5 null
null null

C
12
 aggregate_function_name( [ALL | DISTINCT] expression )

Function Usage
AVG(expression) Computes the average value of a column given by
expression
CORR(dependent, independent) Computes a correlation coefficient

COUNT(expression) Counts the rows defined by the expression

COUNT(*) Counts all rows in the specified table or view

COVAR_POP(dependent, Computes population covariance

independent)
COVAR_SAMP(dependent, Computes sample covariance
independent)
CUME_DIST(value_list) WITHIN Computes the relative rank of a hypothetical row
GROUP (ORDER BY sort_list) within a group of rows, where the rank is equal to the
number of rows less than or equal to the hypothetical
row divided by the number of rows in the group
DENSE_RANK(value_list) WITHIN Generates a dense rank (no ranks are skipped) for a
GROUP (ORDER BY sort_list) hypothetical row (value_list) in a group of rows
generated by GROUP BY
 aggregate_function_name( [ALL | DISTINCT] expression )

Function Usage
MIN(expression) Finds the minimum value in a column given by
expression
MAX(expression) Finds the maximum value in a column given by
expression
PERCENT_RANK(value_list) WITHIN Generates a relative rank for a hypothetical row by
GROUP (ORDER BY sort_list) dividing that row’s rank less 1 by the number of
rows in the group
PERCENTILE_CONT(percentile) WITHIN Generates an interpolated value that, if added to
GROUP (ORDER BY sort_list) the group, would correspond to the percentile
given
PERCENTILE_DISC(percentile) WITHIN Returns the value with the smallest cumulative
GROUP (ORDER BY sort_list) distribution value greater than or equal to
percentile
RANK(value_list) WITHIN GROUP (ORDER Generates a rank for a hypothetical row
BY sort_list) (value_list) in a group of rows generated by
GROUP BY
 aggregate_function_name( [ALL | DISTINCT] expression )

Function Usage

REGR_AVGX(dependent, independent) Computes the average of the independent

variable
REGR_AVGY(dependent, independent) Computes the average of the dependent
variable
REGR_COUNT(dependent, independent) Counts the number of pairs remaining in the
group after any pair with one or more NULL
values has been eliminated
REGR_INTERCEPT(dependent, independent) Computes the y-intercept of the least-squares-
fit linear equation
REGR_R2(dependent, independent) Squares the correlation coefficient

REGR_SLOPE(dependent, independent) Determines the slope of the least-squares-fit

linear equation
REGR_SXX(dependent, independent) Sums the squares of the independent variables

REGR_SXY(dependent, independent) Sums the products of each pair of variables

REGR_SYY(dependent, independent) Sums the squares of the dependent variables

 aggregate_function_name( [ALL | DISTINCT] expression )

Function Usage

STDDEV_POP(expression) Computes the population standard deviation of all

expression values in a group
STDDEV_SAMP(expression) Computes the sample standard deviation of all expression
values in a group
SUM(expression) Computes the sum of the column values given by
expression
VAR_POP(expression) Computes the population variance of all expression values
in a group
VAR_SAMP(expression) Computes the sample standard deviation of all expression
values in a group
Group by and Having clauses

17
Aggregate Functions vs Group-by and Having Clauses

• So far, we have applied aggregate functions to all rows in a relation

• Often, we want to apply these aggregate functions to a number of groups
within a relation, where the number of groups is not known in advance
• SQL offers the group-by and having operators

18
 The need for Group-by

Find the age of the youngest sailor for each rating level

Sailors (sid,name,rating,age)
select min(age) select min(age) select min(age) sid name rating age
from Sailors from Sailors from Sailors
where rating = 1; where rating = 2; where rating = 3;
1 Dusty 7 45
2 Rusty 10 35
3 Horatio 5 35
… 4
5
Horatio
Julius
10
null
18
25

select min(age) select min(age) select min(age)

from Sailors from Sailors from Sailors
where rating = 8; where rating = 9; where rating = 10;

• What if there were 100 values (groups) for rating?

• What if we didn’t know how many ratings there
were?
 Group-by and Having Clauses

Find the age of the youngest sailor for each rating level
Sailors (sid,name,rating,age)
select rating, min(age) sid name rating age
from Sailors 1 Dusty 7 45
group by rating; 2 Rusty 10 35
3 Horatio 5 35
4 Horatio 10 18
5 Julius null 25
Answer:
rating min
7 45
A single value 10 18
per group 5 35
null 25
Group-by and Having Clauses

select [distinct] select-list

from from-list
where qualification
group by grouping-list
having group-qualification
 Group-by and Having Clauses

Find the age of the youngest sailor for each rating level
Sailors (sid,name,rating,age)
Answer: sid name rating age
select rating, min(age) rating min 1 Dusty 7 45
from Sailors 7 45 2 Rusty 10 35
group by rating; 10 18 3 Horatio 5 35
5 35 4 Horatio 10 18
null 25 5 Julius null 25

Find the age of the youngest sailor for each rating level over 35

Answer:
rating min
select rating, min(age)
7 45
from Sailors
where rating > 5 10 18

group by rating;
 Group-by and Having Clauses

Find the age of the youngest sailor for each rating level
Sailors (sid,name,rating,age)
sid name rating age
select rating, min(age) 1 Dusty 7 45
from Sailors 2 Rusty 10 35
group by rating; 3 Horatio 5 35
4 Horatio 10 18
5 Julius null 25

Find the age of the youngest sailor for each rating level, such that there are at least two sailors
with that rating

select rating, min(age)

from Sailors Answer:
group by rating rating min
having count(*) > 1; having applies to each 10 18
group separately
Aggregation – Evaluation Steps

Sailors (sid,name,rating,age)
sid name rating age
22 Dusty 7 45
29 Brutus 1 33
31 Lubber 8 55.5
32 Andy 8 25.5
58 Rusty 10 35.0
64 Horatio 7 35
71 Zorba 10 16
74 Horatio 9 35
85 Art 3 25.5
95 Bob 3 63.5
96 Frodo 3 25.5
Aggregation – Evaluation Steps

Sailors (sid,name,rating,age) Find the youngest sailor who is eligible to vote

sid name rating age (at least 18) for each rating level with at least
22 Dusty 7 45 two such sailors
29 Brutus 1 33
31 Lubber 8 55.5
32 Andy 8 25.5
58 Rusty 10 35.0
64 Horatio 7 35 select rating, min(age)
71 Zorba 10 16 from Sailors
74 Horatio 9 35 where age >= 18
85 Art 3 25.5 group by rating
95 Bob 3 63.5 having count(*) > 1
96 Frodo 3 25.5
Evaluation – Step 1

Sailors (sid,name,rating,age) Find the youngest sailor who is eligible to vote

sid name rating age (at least 18) for each rating level with at least
22 Dusty 7 45
two such sailors
29 Brutus 1 33
31 Lubber 8 55.5
32 Andy 8 25.5
58 Rusty 10 35.0 select rating, min(age)
64 Horatio 7 35 from Sailors
where age >= 18
71 Zorba 10 16 group by rating
74 Horatio 9 35 having count(*) > 1

85 Art 3 25.5
95 Bob 3 63.5
96 Frodo 3 25.5

Step 1 –
• We compute the cartesian product of all tables in the from clause
• Here we have only one table, so we move on to the next step
Evaluation – Step 2

Sailors (sid,name,rating,age) Find the youngest sailor who is eligible to vote

sid name rating age (at least 18) for each rating level with at least
22 Dusty 7 45
two such sailors
29 Brutus 1 33
31 Lubber 8 55.5
32 Andy 8 25.5
58 Rusty 10 35.0 select rating, min(age)
64 Horatio 7 35 from Sailors
where age >= 18
71 Zorba 10 16 group by rating
74 Horatio 9 35 having count(*) > 1;

85 Art 3 25.5 sid name rating age

95 Bob 3 63.5 22 Dusty 7 45
96 Frodo 3 25.5 29 Brutus 1 33
31 Lubber 8 55.5
32 Andy 8 25.5
58 Rusty 10 35.0
Step 2 – 64 Horatio 7 35
• We evaluate the where clause 74 Horatio 9 35
• We eliminate the row of Zorba 85 Art 3 25.5
95 Bob 3 63.5
96 Frodo 3 25.5
Evaluation – Step 3

Sailors (sid,name,rating,age) Find the youngest sailor who is eligible to vote

sid name rating age (at least 18) for each rating level with at least
22 Dusty 7 45 two such sailors
29 Brutus 1 33
31 Lubber 8 55.5
32 Andy 8 25.5
58 Rusty 10 35.0
select rating, min(age)
64 Horatio 7 35 from Sailors
9 where age >= 18
74 Horatio 35
group by rating
85 Art 3 25.5 having count(*) > 1
95 Bob 3 63.5 rating age

96 Frodo 3 25.5 7 45
1 33
8 55.5
8 25.5
10 35.0
Step 3 – 7 35
• We eliminate unwanted columns – we keep 9 35
only columns in the select clause, the group-by 3 25.5
clause and the having clause 3 63.5
• We eliminate the columns sid and name 3 25.5
Evaluation – Step 4

Find the youngest sailor who is eligible to vote

rating age (at least 18) for each rating level with at least
7 45 two such sailors
1 33
8 55.5
8 25.5
10 35.0
select rating, min(age)
7 35 from Sailors
9 35 where age >= 18
group by rating
3 25.5 rating age
having count(*) > 1
3 63.5 1 33
3 25.5 3 25.5

Note the duplicate 3 25.5

3 63.5
7 45
7 35

Step 4 – 8 55.5
8
• We sort the table in groups according to the 25.5
9 35
columns in the group by clause
10 35
• We sort the table by rating
Evaluation – Step 5

Find the youngest sailor who is eligible to vote

rating age (at least 18) for each rating level with at least
1 33 two such sailors
3 25.5
3 25.5
3 63.5
7 45
select rating, min(age)
7 35 Eliminate from Sailors
8 55.5 where age >= 18
group by rating
8 25.5 having count(*) > 1
rating age
9 35
3 25.5
10 35
3 25.5
3 63.5
7 45
7 35
8 55.5
Step 5 – 8 25.5
• We apply the group qualification
• We eliminate groups that do not have count(*) > 1, that is the
group of 1, 9 and 10
Evaluation – Step 6

Find the youngest sailor who is eligible to vote

rating age (at least 18) for each rating level with at least
3 25.5 Note the two such sailors
3 25.5 duplicate
3 63.5
7 45
7 35
select rating, min(age)
8 55.5 from Sailors
where age >= 18
8 25.5 group by rating
having count(*) > 1

rating age
3 25.5
7 35
8 25.5
Step 6 –
• We apply the select clause and generate one answer row for each
group
• We find the min(age) for each rating
Having Clause

• The having clause operates at the group level

• The having clause specifies a condition that must be specified by all
qualifying groups using operators such as =, >, etc.
having count(*) > 2
having count(customer_id) > 20
having sum(amount) > 1000
etc.
• However, there are two special operators every and any that specify a
condition to be satisfied by all (every) or some (any) members of the
group
having every ( [Link] < 60)
having some ([Link] < 60) Not
implemented
by all vendors
Group-by and Having Clauses – Rule #1
select [distinct] select-list
from from-list
where qualification
group by grouping-list
having group-qualification

Rule 1: a query that uses aggregate operators must use only aggregate
operators in the select clause, unless the query contains a group by clause

select rating, min(age)

from Sailors;
Not Valid

select rating, min(age)

from Sailors Valid
group by rating;
Group-by and Having Clauses – Rule #2
select [distinct] select-list
from from-list
where qualification
group by grouping-list
having group-qualification

Rule 2: all non-aggregate columns in the select clause must appear in the
group by clause

select rating, name, min(age)

from Sailors
group by rating;
Not Valid

select rating, name, min(age)

from Sailors
group by rating, name;
Valid
Group-by and Having Clauses – Rule #3
select [distinct] select-list
from from-list
where qualification
group by grouping-list
having group-qualification

Rule 3: only non-aggregate columns used in the group by may appear as

non-aggregate columns in the having clause

select min(age)
from Sailors
group by rating Not Valid
having name =‘Horatio’;

select min(age)
from Sailors
group by rating, name
Valid
having name = ‘Horatio’;
 Group-by and Having Clauses Examples

Find the number of reservations for each boat that was reserved at least 3 times.

Reserves (sid, bid,day)

sid bid day
1 101 10/10/12 The question is
1 102 10/10/12 1) Do we have groups? (group by)
1 101 10/7/12
2) Do we qualify the groups or the
records?
2 102 11/9/12
3) Do we qualify the records before the
2 102 7/11/12
groups are created (where) or after
3 101 7/11/12 (select) ?
3 102 7/8/12
4 103 19/9/12
 Group-by and Having Clauses

Find the number of reservations for each boat that was reserved at least 3 times.

Reserves (sid, bid,day) select bid, count(*) as count

sid bid day
from Reserves
1 101 10/10/12 group by bid
1 102 10/10/12 having count(*) >= 3;
1 101 10/7/12
2 102 11/9/12
2 102 7/11/12
3 101 7/11/12
3 102 7/8/12
4 103 19/9/12 bid count
101 3
102 4
 Group-by and Having Clauses Examples

Find the number of boat reservations by each sailor who is at least 20 years old.

The question is
1) Do we have groups? (group by)
Reserves (sid, bid,day) 2) Do we quality the groups or the records?
sid bid day 3) Do we quality the records before the
1 101 10/10/12 groups are created (where) or after
1 102 10/10/12 (select) ?
1 101 10/7/12
2 102 11/9/12
2 102 7/11/12
3 101 7/11/12
Sailors (sid,name,rating,age)
3 102 7/8/12 sid name rating age
1 Dusty 7 45
4 103 19/9/12
2 Rusty 10 35
3 Horatio 5 35
4 Zorba 8 18
5 Julius 25
 Group-by and Having Clauses Examples

Find the number of boat reservations by each sailor who is at least 20 years old.

Reserves (sid, bid,day)

sid bid day select [Link], count(*)
1 101 10/10/12
from Reserves R, Sailors S
where [Link] = [Link] and [Link] > 20
1 102 10/10/12
group by [Link];
1 101 10/7/12
2 102 11/9/12
sid count
2 102 7/11/12
3 101 7/11/12
Sailors (sid,name,rating,age) 1 3
2 2
3 102 7/8/12 sid name rating age
3 2
1 Dusty 7 45
4 103 19/9/12
2 Rusty 10 35
3 Horatio 5 35
4 Zorba 8 18
5 Julius 25
 Group-by and Having Clauses Examples

Find the number of boat reservations by each sailor who has made at least 2
reservations.
The question is
1) Do we have groups? (group by)
Reserves (sid, bid,day) 2) Do we quality the groups or the records?
sid bid day 3) Do we quality the records before the
1 101 10/10/12 groups are created (where) or after
1 102 10/10/12 (select) ?
1 101 10/7/12
2 102 11/9/12
2 102 7/11/12
3 101 7/11/12
Sailors (sid,name,rating,age)
3 102 7/8/12 sid name rating age
1 Dusty 7 45
4 103 19/9/12
2 Rusty 10 35
3 Horatio 5 35
4 Zorba 8 18
5 Julius 25
 Group-by and Having Clauses Examples

Find the number of boat reservations by each sailor who has made at least 2
reservations.

Reserves (sid, bid,day)

sid bid day select sid, count(*)
1 101 10/10/12
from Reserves
group by sid
1 102 10/10/12
having count(*) >= 2;
1 101 10/7/12
2 102 11/9/12
sid count
2 102 7/11/12
3 101 7/11/12
Sailors (sid,name,rating,age) 1 3
2 2
3 102 7/8/12 sid name rating age
3 2
1 Dusty 7 45
4 103 19/9/12
2 Rusty 10 35
3 Horatio 5 35
4 Zorba 8 18
5 Julius 25
 Group-by and Having Clauses Examples

Find the number of reservations for each boat that was reserved at least 3 times.

The question is
Reserves (sid, bid,day) 1) Do we have groups? (group by)
sid bid day
2) Do we quality the groups or the records?
3) Do we quality the records before the
1 101 10/10/12
groups are created (where) or after
1 102 10/10/12
(select) ?
1 101 10/7/12
2 102 11/9/12
2 102 7/11/12
3 101 7/11/12
Sailors (sid,name,rating,age)
3 102 7/8/12 sid name rating age
1 Dusty 7 45
4 103 19/9/12
2 Rusty 10 35
3 Horatio 5 35
4 Zorba 8 18
5 Julius 25
 Group-by and Having Clauses Examples

Find the number of reservations for each boat that was reserved at least 3 times.

Reserves (sid, bid,day)

sid bid day select sid, count(*)
1 101 10/10/12
from Reserves
group by sid
1 102 10/10/12
having count(*) >= 3;
1 101 10/7/12
2 102 11/9/12
2 102 7/11/12
3 101 7/11/12
Sailors (sid,name,rating,age)
3 102 7/8/12 sid name rating age sid count
1 Dusty 7 45 1 3
4 103 19/9/12
2 Rusty 10 35
3 Horatio 5 35
4 Zorba 8 18
5 Julius 25
 Sorting Results – order by clause

Retrieve all information about reservations, sorted by bid

select *
from Reserves
order by bid;

Retrieve all information about reservations, sorted by bid (descending), with ties
broken by sid (ascending)
select *
from Reserves
order by bid desc, sid;

Retrieve bids of all reserved boats, along with the number of times they were
reserved, and the average sailor rating, sorted by average sailor rating

select [Link], count(*) as num_reservations, avg([Link]) as avg_rating

from Reserves R, Sailors S
where [Link] = [Link]
group by [Link]
order by [Link];
Null Values

45
 Null Values – Comparisons using null

Sailors (sid,name,rating,age) Is (rating = 8) true or false

sid
5
name
Julius
rating
null
age
25
for Julius?

• When we compare a value with null using <, > =, the result is null
• When we compare two null values using <, >, =, the result in also null
• When the result is null, the expression evaluates to false

• SQL provides special comparison operators is null and is not null

– (rating is null) is true for Julius
– (rating is not null) is false for Julius
Null Values – Logical Connectives AND, OR, NOT

Sailors (sid,name,rating,age) Is (age < 40 or Rating = 8) true or false for

sid name rating age Julius?
5 Julius null 25

A B Not A A or B A and B A = B
F F T F F T
F T T T F F
F null T null null null
3-way logic
T F F T F F
T T F T T T
T null F null null null
null F null null null null
null T null null null null
null null null null null null
 SQL operators and null

SQL If one operand is null

Arithetic operators +, -, * and / Returns null if one or both operands are
null
Boolean AND, OR, NOT Uses the 3-way logic in the previous
page
Where clause eliminates rows when the condition
evaluates to null – this has impact on
“exists” and “unique”
null = null Evaluates to null
Duplicates Two records with a null values are not
considered duplicates (an anomaly)
count(*) Counts null as any other value
Min, max, avg, sum Ignore null values
unless all values are null in which case it
evaluates to null
Inner Joins, Outer Joins

49
 Running Example - Join

Student (sid, name,cid) College (cid,name)

sid name cid cid name
1 moe c1 c1 drexel
2 larry c1
c2 temple
3 joe c2
c3 penn
4 alice null

List names of students and names of their colleges

select [Link], [Link] [Link] [Link]

from Student S, College C moe drexel
where [Link] = [Link]; larry drexel
joe temple
Equivalent Join Expressions

select columns
from A, B
where join condition
Some vendors
support only this
form

Alternate syntax

select columns
from A join B on join condition
Different Types of Joins
Join
from A join B

from A inner join B

Left Join
from A left join B

from A left outer join B

Right Join
from A right join B

from A right outer join B

Full Join
from A full join B

from A full outer join B

Equivalent Outer Join Expressions

select columns
from A left outer join B
where join condition

Alternate syntax

select columns
from A left outer join B on join condition

Some vendors
support only this
form
 Running Example – Left Outer Join

Student (sid, name,cid) College (cid,name)

sid name cid cid name
1 moe c1 c1 drexel
2 larry c1
c2 temple
3 joe c2
c3 penn
4 alice null

List names of students and, if they attend a college, list

the names of their colleges too
select [Link], [Link] [Link] [Link]
from Student S left outer join College C moe drexel
on [Link] = [Link]; larry drexel
joe temple
alice null
 Running Example – Right Outer Join

Student (sid, name,cid) College (cid,name)

sid name cid cid name
1 moe c1 c1 drexel
2 larry c1
c2 temple
3 joe c2
c3 penn
4 alice null

List names of colleges and, if there are students that go

there, list the names of the students too
select [Link], [Link] [Link] [Link]
from Student S right outer join College C moe drexel
on [Link] = [Link]; larry drexel
joe temple
null penn
 Running Example – Full Outer Join

Student (sid, name,cid) College (cid,name)

sid name cid cid name
1 moe c1 c1 drexel
2 larry c1
c2 temple
3 joe c2
c3 penn
4 alice null

List names of colleges and students. If they are

unassociated list them anyway.
select [Link], [Link] [Link] [Link]
from Student S full outer join College C moe drexel
on [Link] = [Link]; larry drexel
joe temple
alice null
null penn
Left Outer Join vs Right Outer Join

Do these two queries generate the same answer or no?

select [Link], [Link]

from Student S right outer join College C
on [Link] = [Link];

select [Link], [Link]

from College C left outer join Student S
on [Link] = [Link];

Answer: Yes
 Outer Join

Student (sid, name,cid) College (cid,name)

sid name cid cid name
1 moe c1 c1 drexel
2 larry c1
c2 temple
3 joe c2
c3 penn
4 alice null

List names of students that do not go to college. [Link]

alice
select [Link]
from Student S left outer join College C on [Link] = [Link]
where [Link] is null;
 Can we write this query using set operators?

Student (sid, name,cid) College (cid,name)

sid name cid cid name
1 moe c1 c1 drexel
2 larry c1
c2 temple
3 joe c2
c3 penn
4 alice null

List names of students that do not go to college. [Link]

alice
select [Link]
from Student S left outer join College C on [Link] = [Link]
where [Link] is null;
select [Link]
from Student S
Also using set except
select [Link]
operators from Student S2, College C
where [Link] = [Link];
 SQL Standard Evolution

Year Name Comments

1986 SQL-86 First formalized by ANSI.

1989 SQL-89 Minor revision that added integrity constraints

1992 SQL-92 Major revision (ISO 9075), Entry Level SQL-92

1999 SQL:1999 Added regular expression matching, recursive queries (e.g. transitive closure),
(aka SQL 3) triggers, support for procedural and control-of-flow statements, non-scalar types
(arrays), and some object-oriented features (e.g. structured types). Support for
embedding SQL in Java (SQL/OLB) and vice versa (SQL/JRT).
2003 SQL:2003 Introduced XML-related features (SQL/XML), window functions, standardized
sequences, and columns with auto-generated values (including identity-columns).
2006 SQL:2006 ISO/IEC 9075-14:2006 defines ways that SQL can be used with XML. It defines ways
of importing and storing XML data in an SQL database, manipulating it within the
database, and publishing both XML and conventional SQL-data in XML form. In
addition, it lets applications integrate queries into their SQL code with XQuery, the
XML Query Language published by the World Wide Web Consortium (W3C), to
concurrently access ordinary SQL-data and XML documents.[34]
2008 SQL:2008 Legalizes ORDER BY outside cursor definitions. Adds INSTEAD OF triggers, TRUNCATE
statement,[35] FETCH clause.
2011 SQL:2011 Adds temporal data (PERIOD FOR)[36] (more information at: Temporal
database#History). Enhancements for window functions and FETCH clause.[37]
2016 SQL:2016 Adds row pattern matching, polymorphic table functions, JSON.

2019 SQL:2019 Adds Part 15, multidimensional arrays (MDarray type and operators).
SQL Standard and Vendor Compatibility

Several reasons for incompatibility between database systems:

• The complexity and size of the SQL standard means that most
implementers do not support the entire standard
• Many database vendors have large existing customer bases; where the
newer version of the SQL standard conflicts with the prior vendor version,
the vendor may be unwilling to break backward compatibility
• Little commercial incentive exists for vendors to make changing database
suppliers easier (see vendor lock-in)
• Clients tend to place other factors such as performance higher in their
priorities than standards conformance

61
Acknowledgements

Some slides in this course are inspired or copied from

• [Link] by Julia Stoyanovich, Drexel U,

Spring 2018
• [Link] by Jiannan Wang - Simon Fraser, Fall
2018

62