SQL: Part I

Introduction to Database Management

CS348 Fall 2022
SQL
• SQL: Structured Query Language
• Pronounced “S-Q-L” or “sequel”
• The standard query language supported by most DBMS
• A brief history
• IBM System R
• ANSI SQL96
• ANSI SQL89
• ANSI SQL92 (SQL2)
• ANSI SQL99 (SQL3)
• ANSI SQL 2003 (added OLAP, XML, etc.)
• ANSI SQL 2006 (added more XML)
• ANSI SQL 2008, …
2
SQL
• Data-definition language (DDL): define/modify
schemas, delete relations

this
• Data-manipulation language (DML): query
information, and insert/delete/modify tuples week

• Integrity constraints: specify constraints that the

data stored in the database must satisfy

• Intermediate/Advanced topics: (next week)

• E.g., triggers, views, indexes, programming, recursive
queries
3
 User (uid int, name string, age int, pop float)
Group (gid string, name string)
DDL Member (uid int, gid string)

• CREATE TABLE table_name

(…, column_name column_type, …);
CREATE TABLE User(uid DECIMAL(3,0), name VARCHAR(30), age DECIMAL
(2,0), pop DECIMAL(3,2));
CREATE TABLE Group (gid CHAR(10), name VARCHAR(100));
CREATE TABLE Member (uid DECIMAL (3,0), gid CHAR(10));

• DROP TABLE table_name; How does it

DROP TABLE User; work with
DROP TABLE Group; MySQL?
DROP TABLE Member;

-- everything from -- to the end of line is ignored.

-- SQL is insensitive to white space.
-- SQL is insensitive to case (e.g., ...CREATE... is
-- equivalent to ...create...).
4
Basic queries for DML: SFW statement
• SELECT 𝐴! , 𝐴" , …, 𝐴#
FROM 𝑅! , 𝑅" , …, 𝑅$
WHERE 𝑐𝑜𝑛𝑑𝑖𝑡𝑖𝑜𝑛;

• Also called an SPJ (select-project-join) query

• Corresponds to (but not really equivalent to)

relational algebra query:
𝜋%!,%",…,%# 𝜎()#*+,+)# 𝑅! ×𝑅" × ⋯×𝑅$

5
 User (uid int, name string, age int, pop float)
Examples Group (gid string, name string)
Member (uid int, gid string)

• List all rows in the User table

SELECT * FROM User;

• * is a short hand for “all columns”

• List name of users under 18 (selection, projection)
SELECT name FROM User where age <18;

• When was Lisa born?

SELECT 2021-age FROM User where name = ‘Lisa’;

• SELECT list can contain expressions

• Can also use built-in functions such as SUBSTR, ABS, etc.
• String literals (case sensitive) are enclosed in single
quotes
6
 User (uid int, name string, age int, pop float)
Example: join Group (gid string, name string)
Member (uid int, gid string)

• List ID’s and names of groups with a user whose

name contains “Simpson”
SELECT [Link], [Link]
FROM User, Member, Group
WHERE [Link] = [Link]
AND [Link] = [Link]
AND ….;

7
 User (uid int, name string, age int, pop float)
Example: join Group (gid string, name string)
Member (uid int, gid string)

• List ID’s and names of groups with a user whose

name contains “Simpson”
SELECT [Link], [Link]
FROM User, Member, Group
WHERE [Link] = [Link]
AND [Link] = [Link]
AND [Link] LIKE ‘%Simpson%’;

• LIKE matches a string against a pattern

• % matches any sequence of zero or more characters
• Okay to omit table_name in table_name.column_name if
column_name is unique

8
 User (uid int, name string, age int, pop float)
Example: rename Group (gid string, name string)
Member (uid int, gid string)

• ID’s of all pairs of users that belong to one group

• Relational algebra query:
𝜋!! .#$%,!" .#$%
𝜌!! 𝑀𝑒𝑚𝑏𝑒𝑟 ⋈!! .'$%(!" .'$% ∧ !! .#$%*!" .#$% 𝜌!" 𝑀𝑒𝑚𝑏𝑒𝑟

• SQL (not exactly):

SELECT [Link] AS uid1, [Link] AS uid2
SELECT [Link] AS uid1, [Link] AS uid2
FROM Member
FROM MemberASASm1,
m1,Member ASm2
Member AS m2
WHERE [Link]
WHERE = [Link]
[Link] = [Link]
[Link] > [Link];
[Link] > [Link];

• AS keyword is completely optional

9
A more complicated example
• Names of all groups that Lisa and Ralph are both in

Tip: Write the FROM clause first, then WHERE, and

then SELECT
User (uid int, name string, age int, pop float)
Group (gid string, name string)
Member (uid int, gid string)

10
A more complicated example
• Names of all groups that Lisa and Ralph are both in
SELECT [Link]
FROM User u1, …, Member m1, …
WHERE [Link] = 'Lisa' AND …
AND [Link] = [Link] AND …
AND …;

User (uid int, name string, age int, pop float)

Group (gid string, name string)
Member (uid int, gid string)

11
A more complicated example
• Names of all groups that Lisa and Ralph are both in
SELECT [Link]
FROM User u1, User u2, Member m1, Member m2, …
WHERE [Link] = 'Lisa' AND [Link] = ‘Ralph’
AND [Link] = [Link] AND [Link]=[Link]
AND …;

User (uid int, name string, age int, pop float)

Group (gid string, name string)
Member (uid int, gid string)

12
A more complicated example
• Names of all groups that Lisa and Ralph are both in
SELECT [Link]
FROM User u1, User u2, Member m1, Member m2, Group g
WHERE [Link] = 'Lisa' AND [Link] = ‘Ralph’
AND [Link] = [Link] AND [Link]=[Link]
AND [Link] = [Link] AND [Link] = [Link];

User (uid int, name string, age int, pop float)

Group (gid string, name string)
Member (uid int, gid string)

13
Why SFW statements?
• Many queries can be written using only selection,
projection, and cross product (or join)

• These queries can be written in a canonical form

which is captured by SFW:
𝜋- 𝜎. 𝑅! × ⋯×𝑅$

• Example: 𝜋+.,,-.. 𝑅 ⋈/! 𝑆 ⋈/" 𝜋 0.1 𝜎/# 𝑇

= 𝜋+.,,-..,0.1 𝜎/! ∧/" ∧/# 𝑅×𝑆×𝑇

14
Set versus bag
User age
10
uid name age pop
𝜋!"# 𝑈𝑠𝑒𝑟 8
142 Bart 10 0.9
…
123 Milhouse 10 0.2
857 Lisa 8 0.7
Set
456 Ralph 8 0.3 • No duplicates
… … … … • Relational model and algebra use set
semantics

SELECT age age Bag

FROM User; 10 • Duplicates allowed
8 • Number of duplicates is significant
8
• SQL uses bag semantics by default
8
…
15
A case for bag semantics
• Efficiency
• Saves time of eliminating duplicates

• Which one is more useful?

SELECT age
𝜋!"# 𝑈𝑠𝑒𝑟 FROM User;

• The first query just returns all possible user ages

• The second query returns the user age distribution

• Besides, SQL provides the option of set semantics

with DISTINCT keyword

16
Forcing set semantics
• ID’s of all pairs of users that belong to one group
SELECT [Link] AS uid1, [Link] AS uid2
FROM Member AS m1, Member AS m2
WHERE [Link] = [Link]
AND [Link] > [Link];

àSay Lisa and Ralph are in both the book club and the
student government, they id pairs will appear twice

• Remove duplicate (uid1, uid2) pairs from the output

SELECT DISTINCT [Link] AS uid1, [Link] AS uid2
FROM Member AS m1, Member AS m2
WHERE [Link] = [Link];
AND [Link] > [Link];
17
Semantics of SFW
• SELECT [DISTINCT] 𝐸! , 𝐸" , …, 𝐸#
FROM 𝑅! , 𝑅" , …, 𝑅$
WHERE 𝑐𝑜𝑛𝑑𝑖𝑡𝑖𝑜𝑛;
• For each 𝑡! in 𝑅! :
For each 𝑡" in 𝑅" : … …
For each 𝑡$ in 𝑅$ :
If 𝑐𝑜𝑛𝑑𝑖𝑡𝑖𝑜𝑛 is true over 𝑡! , 𝑡" , …, 𝑡$ :
Compute and output 𝐸! , 𝐸" , …, 𝐸# as a row
If DISTINCT is present
Eliminate duplicate rows in output
• 𝑡! , 𝑡" , …, 𝑡$ are often called tuple variables

18
SQL set and bag operations
• Set: UNION, EXCEPT, INTERSECT
• Exactly like set ∪, −, and ∩ in relational algebra
• Duplicates in input tables, if any, are first eliminated
• Duplicates in result are also eliminated (for UNION)

Bag1 Bag2 (SELECT * FROM Bag1) (SELECT * FROM Bag1) (SELECT * FROM Bag1)
UNION EXCEPT INTERSECT
fruit fruit (SELECT * FROM Bag2); (SELECT * FROM Bag2); (SELECT * FROM Bag2);
apple orange fruit fruit fruit
apple orange apple apple orange
orange orange orange

19
SQL set and bag operations
• Set: UNION, EXCEPT, INTERSECT
• Exactly like set ∪, −, and ∩ in relational algebra
• Bag: UNION ALL, EXCEPT ALL, INTERSECT ALL
• Think of each row as having an implicit count (the
number of times it appears in the table)
(SELECT * FROM Bag1)
UNION ALL
(SELECT * FROM Bag2);
Bag1 Bag2
fruit
fruit fruit sum up the counts
apple from two tables
apple apple
apple
apple orange
orange
orange orange
apple
apple: 2 apple: 1 orange apple: 3
orange:1 orange:2 orange:3
orange 20
SQL set and bag operations
• Set: UNION, EXCEPT, INTERSECT
• Exactly like set ∪, −, and ∩ in relational algebra
• Bag: UNION ALL, EXCEPT ALL, INTERSECT ALL
• Think of each row as having an implicit count (the
number of times it appears in the table)
(SELECT * FROM Bag1)
EXCEPT ALL
Bag1 Bag2 (SELECT * FROM Bag2);

fruit fruit fruit proper-subtract

apple apple apple the two counts
apple orange
orange orange

apple: 2 apple: 1 apple: 1

orange:1 orange:2 orange:0
21
SQL set and bag operations
• Set: UNION, EXCEPT, INTERSECT
• Exactly like set ∪, −, and ∩ in relational algebra
• Bag: UNION ALL, EXCEPT ALL, INTERSECT ALL
• Think of each row as having an implicit count (the
number of times it appears in the table)
(SELECT * FROM Bag1)
INTERSECT ALL
Bag1 Bag2 (SELECT * FROM Bag2);
take the
fruit fruit fruit minimum of the
apple apple apple two counts
apple orange orange
orange orange

apple: 2 apple: 1 apple: 1

orange:1 orange:2 orange:1
22
Set versus bag operations
Poke (uid1, uid2, timestamp)
• uid1 poked uid2 at timestamp

Question: How do these two queries differ?

Q1: Q2:
(SELECT uid1 FROM Poke) (SELECT uid1 FROM Poke)
EXCEPT EXCEPT ALL
(SELECT uid2 FROM Poke); (SELECT uid2 FROM Poke);

23
Set versus bag operations
Poke (uid1, uid2, timestamp)
• uid1 poked uid2 at timestamp

Question: How do these two queries differ?

Q1: Q2:
(SELECT uid1 FROM Poke) (SELECT uid1 FROM Poke)
EXCEPT EXCEPT ALL
(SELECT uid2 FROM Poke); (SELECT uid2 FROM Poke);

Users who poked others but Users who poked others

never got poked by others more than others poked them

24
SQL features covered so far
• SELECT-FROM-WHERE statements
• Set and bag operations

FNext: how to nest SQL queries

25
Table subqueries
• Use query result as a table
• In set and bag operations, FROM clauses, etc.

• Example: names of users who poked others more

than others poked them

SELECT DISTINCT name

FROM User,
(SELECT uid1 FROM Poke)
EXCEPT ALL
(SELECT uid2 FROM Poke) AS T
WHERE [Link] = [Link];

26
Scalar subqueries
• A query that returns a single row can be used as a
value in WHERE, SELECT, etc.
• Example: users at the same age as Bart
SELECT *
FROM User,
WHERE age = (SELECT age
FROM User
WHERE name = ‘Bart’);

• When can this query go wrong?

• Return more than 1 row
• Return no rows

27
IN subqueries
• 𝑥 IN (𝑠𝑢𝑏𝑞𝑢𝑒𝑟𝑦) checks if 𝑥 is in the result of
𝑠𝑢𝑏𝑞𝑢𝑒𝑟𝑦

• Example: users at the same age as (some) Bart

SELECT *
FROM User,
WHERE age IN (SELECT age
FROM User
WHERE name = ‘Bart’);

28
EXISTS subqueries
• EXISTS (𝑠𝑢𝑏𝑞𝑢𝑒𝑟𝑦) checks if the result of 𝑠𝑢𝑏𝑞𝑢𝑒𝑟𝑦
is non-empty

• Example: users at the same age as (some) Bart

SELECT *
FROM User AS u,
WHERE EXISTS (SELECT * FROM User
WHERE name = ‘Bart’
AND age = [Link]);

• This happens to be a correlated subquery—a subquery

that references tuple variables in surrounding queries
29
 User (uid int, name string, age int, pop float)
Another example Group (gid string, name string)
Member (uid int, gid string)

• Users who join at least two groups

SELECT * FROM User u
WHERE EXISTS
(SELECT * FROM Member m Use
WHERE uid = [Link] table_name.
column_name
AND EXISTS
notation and AS
(SELECT * FROM Member (renaming) to avoid
WHERE uid = [Link] confusion
AND gid <> [Link]));

• How to find which table a column belongs to?

• Start with the immediately surrounding query
• If not found, look in the one surrounding that; repeat if
necessary
30
Quantified subqueries
• Universal quantification (for all):
• … WHERE 𝑥 𝑜𝑝 ALL(𝑠𝑢𝑏𝑞𝑢𝑒𝑟𝑦) …
• True iff for all 𝑡 in the result of 𝑠𝑢𝑏𝑞𝑢𝑒𝑟𝑦, 𝑥 𝑜𝑝 𝑡
SELECT *
FROM User
WHERE pop >= ALL(SELECT pop FROM User);
• Existential quantification (exists):
• … WHERE 𝑥 𝑜𝑝 ANY(𝑠𝑢𝑏𝑞𝑢𝑒𝑟𝑦) …
• True iff there exists some 𝑡 in 𝑠𝑢𝑏𝑞𝑢𝑒𝑟𝑦 result s.t. 𝑥 𝑜𝑝 𝑡
SELECT *
FROM User
WHERE NOT
(pop < ANY(SELECT pop FROM User);
31
More ways to get the most popular
• Which users are the most popular?
Q1. SELECT *
FROM User
WHERE pop >= ALL(SELECT pop FROM User);

Q2. SELECT *
FROM User EXISTS or IN?
WHERE NOT
(pop < ANY(SELECT pop FROM User);

Q3. SELECT * Q4. SELECT * FROM User

FROM User AS u WHERE uid NOT [EXISTS or IN?]
WHERE NOT [EXITS or IN?] (SELECT [Link]
(SELECT * FROM User FROM User AS u1, User AS u2
WHERE pop > [Link]); WHERE [Link] < [Link]);
32
SQL features covered so far
• SELECT-FROM-WHERE statements
• Set and bag operations
• Subqueries
• Subqueries allow queries to be written in more
declarative ways (recall the “most popular” query)
• But in many cases, they don’t add expressive power

FNext: aggregation and grouping

33
Aggregates
• Standard SQL aggregate functions: COUNT, SUM,
AVG, MIN, MAX

• Example: number of users under 18, and their

average popularity
• COUNT(*) counts the number of rows
SELECT COUNT(*), AVG(pop)
FROM User
WHERE age <18;

34
Aggregates with DISTINCT
• Example: How many users are in some group?

SELECT COUNT(*)
FROM (SELECT DISTINCT uid FROM Member);

Is equivalent to

SELECT COUNT(DISTINCT uid)

FROM Member;

35
Grouping
• SELECT … FROM … WHERE …
GROUP BY list_of_columns;

• Example: compute average popularity for

each age group
SELECT age, AVG(pop)
FROM User
GROUP BY age;

36
Example of computing GROUP BY
SELECT age, AVG(pop) FROM User GROUP BY age;

uid name age pop

142 Bart 10 0.9
Compute GROUP BY: group
857 Lisa 8 0.7 rows according to the values
123 Milhouse 10 0.2 of GROUP BY columns
456 Ralph 8 0.3
uid name age pop
142 Bart 10 0.9
Compute SELECT 123 Milhouse 10 0.2
for each group 857 Lisa 8 0.7
456 Ralph 8 0.3
age avg_pop
10 0.55
8 0.50

37
Semantics of GROUP BY
SELECT … FROM … WHERE … GROUP BY …;
1. Compute FROM (×)
2. Compute WHERE (𝜎)
3. Compute GROUP BY: group rows according to the
values of GROUP BY columns
4. Compute SELECT for each group (𝜋)
• For aggregation functions with DISTINCT inputs, first
eliminate duplicates within the group
FNumber of groups =
number of rows in the final output

38
Aggregates with no GROUP BY
• An aggregate query with no GROUP BY clause =
all rows go into one group
SELECT AVG(pop)
SELECT FROM
AVG(pop) FROMUser;
User;

Group all rows Aggregate over

into one group the whole group
uid name age pop uid name age pop
142 Bart 10 0.9 142 Bart 10 0.9 avg_pop
857 Lisa 8 0.7 857 Lisa 8 0.7 0.525
123 Milhouse 10 0.2 123 Milhouse 10 0.2
456 Ralph 8 0.3 456 Ralph 8 0.3

39
Restriction on SELECT
• If a query uses aggregation/group by, then every
column referenced in SELECT must be either
• Aggregated, or
• A GROUP BY column
Why?
FThis restriction ensures that any SELECT expression
produces only one value for each group

RO NG!
SELECT uid, age FROM User GROUP BY age; W

SELECT uid, MAX(pop) FROM User;

RO NG!
W

40
HAVING
• Used to filter groups based on the group properties
(e.g., aggregate values, GROUP BY column values)

• SELECT … FROM … WHERE … GROUP BY …

HAVING 𝑐𝑜𝑛𝑑𝑖𝑡𝑖𝑜𝑛;
1. Compute FROM (×)
2. Compute WHERE (𝜎)
3. Compute GROUP BY: group rows according to the
values of GROUP BY columns
4. Compute HAVING (another 𝜎 over the groups)
5. Compute SELECT (𝜋) for each group that passes
HAVING
41
HAVING examples
• List the average popularity for each age group with
more than a hundred users
SELECT age, AVG(pop)
FROM User
GROUP BY age
HAVING COUNT(*)>100;

• Can be written using WHERE and table subqueries

SELECT [Link], [Link]
FROM (SELECT age, AVG(pop) AS apop, COUNT(*) AS gsize
FROM User GROUP BY age) AS T
WHERE [Link]>100;

42
HAVING examples
• Find average popularity for each age group over 10
SELECT age, AVG(pop)
FROM User
GROUP BY age
HAVING age >10;

• Can be written using WHERE without table subqueries

SELECT age, AVG(pop)

FROM User
WHERE age >10
GROUP BY age;

43
SQL features covered so far
• SELECT-FROM-WHERE statements
• Set and bag operations
• Subqueries
• Aggregation and grouping
• More expressive power than relational algebra

FNext: ordering output rows

44
ORDER BY
• SELECT [DISTINCT] …
FROM … WHERE … GROUP BY … HAVING …
ORDER BY output_column [ASC|DESC], …;

• ASC = ascending, DESC = descending

• Semantics: After SELECT list has been computed

and optional duplicate elimination has been carried
out, sort the output according to ORDER BY
specification

45
ORDER BY example
• List all users, sort them by popularity (descending)
and name (ascending)

SELECT uid, name, age, pop

FROM User
ORDER BY pop DESC, name;

• ASC is the default option

• Strictly speaking, only output columns can appear in
ORDER BY clause (although some DBMS support more)
• Can use sequence numbers instead of names to refer to
output columns: ORDER BY 4 DESC, 2;

46
SQL features covered so far
• Query
• SELECT-FROM-WHERE statements
• Set/bag (DISTINCT, UNION/EXCEPT/INTERSECT (ALL))
• Subqueries (table, scalar, IN, EXISTS, ALL, ANY)
• Aggregation and grouping (GROUP BY, HAVING)
• Ordering (ORDER)
• Outerjoins (and Nulls)
• Modification Lecture 4
• INSERT/DELETE/UPDATE
• Constraints

47