Review

SQL

1
 Purposes of SQL

• Data Manipulation Language (DML)

– Querying: SELECT-FROM-WHERE
– Modifying: INSERT/DELETE/UPDATE

• Data Definition Language (DDL)

– CREATE/ALTER/DROP

2
 The SQL Query Language

• To find all 18 year old students, we can write:

all attributes

SELECT * sid name login age gpa

FROM Students S 53666 Jones jones@cs 18 3.4
WHERE [Link]=18
53688 Smith smith@ee 18 3.2

• To find just names and logins, replace the first line:

SELECT [Link], [Link]

3
 Querying Multiple Relations
• What does the following SELECT [Link], [Link]
query compute? FROM Students S, Enrolled E
WHERE [Link]=[Link] AND [Link]=“A”
Enrolled
Given the following instances of sid cid grade
Enrolled and Students: 53831 Carnatic101 C
Students
53831 Reggae203 B
53650 Topology112 A
sid name login age gpa
53666 History105 B
53666 Jones jones@cs 18 3.4
we get: ??
53688 Smith smith@eecs 18 3.2
53650 Smith smith@math 19 3.8

4
 Querying Multiple Relations
• What does the following SELECT [Link], [Link]
query compute? FROM Students S, Enrolled E
WHERE [Link]=[Link] AND [Link]=“A”
Enrolled
Given the following instances of sid cid grade
Enrolled and Students: 53831 Carnatic101 C
Students
53831 Reggae203 B
53650 Topology112 A
sid name login age gpa
53666 History105 B
53666 Jones jones@cs 18 3.4
we get:
53688 Smith smith@eecs 18 3.2
53650 Smith smith@math 19 3.8 [Link] [Link]
Smith Topology112

5
 Creating Relations in SQL
CREATE TABLE Students
• Creates the “Students” relation
(sid CHAR(20),
– the type (domain) of each field is name CHAR(20),
specified login CHAR(10),
– enforced by the DBMS whenever tuples age INTEGER,
are added or modified gpa REAL)

CREATE TABLE Enrolled

• As another example, the (sid CHAR(20),
“Enrolled” table holds information cid CHAR(20),
about courses that students take grade CHAR(2))

sid name login age gpa sid cid grade

53666 Jones jones@cs 18 3.4 53831 Carnatic101 C
53688 Smith smith@eecs 18 3.2 53831 Reggae203 B
53650 Smith smith@math 19 3.8 53650 Topology112 A
53666 History105 B
Students
Enrolled
6
 Destroying and Altering Relations
DROP TABLE Students

• Destroys the relation Students

– The schema information and the tuples are deleted.

ALTER TABLE Students

ADD COLUMN firstYear: integer

• The schema of Students is altered by adding

a new field; every tuple in the current
instance is extended with a NULL value in
the new field.

7
 Adding and Deleting Tuples

• Can insert a single tuple using:

INSERT INTO Students (sid, name, login, age, gpa)
VALUES (53688, ‘Smith’, ‘smith@ee’, 18, 3.2)

• Can delete all tuples satisfying some

condition (e.g., name = Smith):
DELETE
FROM Students S
WHERE [Link] = ‘Smith’

8
 Integrity Constraints (ICs)

• IC: condition that must be true for any instance of the database
– e.g., domain constraints
– ICs are specified when schema is defined
– ICs are checked when relations are modified

• A legal instance of a relation is one that satisfies all specified ICs

– DBMS will not allow illegal instances

• If the DBMS checks ICs, stored data is more faithful to real-world

meaning
– Avoids data entry errors, too!

9
 Keys in a Database

• Key / Candidate Key

• Primary Key
• Foreign Key

• Primary key attributes are underlined in a schema

– Person(pid, address, name)
– Person2(address, name, age, job)

10
 Primary Key Constraints
• A set of fields is a key for a relation if :
1. No two distinct tuples can have same values in all key fields, and
2. This is not true for any subset of the key

• If there are > 1 keys for a relation, one of the keys is chosen
(by DBA = DB admin) to be the primary key
– E.g., sid is a key for Students
– The set {sid, gpa} is a superkey.

• Any possible benefit to refer to a tuple using primary key

(than any key)?

11
 Primary and Candidate Keys in SQL
• Possibly many candidate keys
– specified using UNIQUE
– one of which is chosen as the primary key.

• “For a given student and course, CREATE TABLE Enrolled

there is a single grade.” (sid CHAR(20)
cid CHAR(20),
grade CHAR(2),
PRIMARY KEY ???)

12
 Primary and Candidate Keys in SQL
• Possibly many candidate keys
– specified using UNIQUE
– one of which is chosen as the primary key.

CREATE TABLE Enrolled

• “For a given student and course,
(sid CHAR(20)
there is a single grade.” cid CHAR(20),
grade CHAR(2),
PRIMARY KEY (sid, cid) )

13
 Primary and Candidate Keys in SQL
• Possibly many candidate keys
– specified using UNIQUE
– one of which is chosen as the primary key.
CREATE TABLE Enrolled
(sid CHAR(20)
• “For a given student and course, there is a cid CHAR(20),
single grade.”
grade CHAR(2),
PRIMARY KEY (sid, cid) )
• vs.
CREATE TABLE Enrolled
• “Students can take only one course, and (sid CHAR(20)
receive a single grade for that course; further,
cid CHAR(20),
no two students in a course receive the same
grade CHAR(2),
grade.”
PRIMARY KEY ???,
UNIQUE ??? )
14
 Primary and Candidate Keys in SQL
• Possibly many candidate keys
– specified using UNIQUE
– one of which is chosen as the primary key.
CREATE TABLE Enrolled
(sid CHAR(20)
• “For a given student and course, there is a cid CHAR(20),
single grade.”
grade CHAR(2),
• vs. PRIMARY KEY (sid,cid) )

• “Students can take only one course, and CREATE TABLE Enrolled
receive a single grade for that course; further, (sid CHAR(20)
no two students in a course receive the same
cid CHAR(20),
grade.”
grade CHAR(2),
PRIMARY KEY sid,
UNIQUE (cid, grade))
15
 Primary and Candidate Keys in SQL
• Possibly many candidate keys
– specified using UNIQUE
– one of which is chosen as the primary key.
CREATE TABLE Enrolled
(sid CHAR(20)
• “For a given student and course, there is a cid CHAR(20),
single grade.”
grade CHAR(2),
• vs. PRIMARY KEY (sid,cid) )

• “Students can take only one course, and CREATE TABLE Enrolled
receive a single grade for that course; further, (sid CHAR(20)
no two students in a course receive the same
cid CHAR(20),
grade.”
grade CHAR(2),
PRIMARY KEY sid,
• Used carelessly, an IC can prevent the storage
UNIQUE (cid, grade))
of database instances that arise in practice!
16
 Foreign Keys, Referential Integrity
• Foreign key : Set of fields in one relation that is used to
`refer’ to a tuple in another relation
– Must correspond to primary key of the second relation
– Like a `logical pointer’

• E.g. sid is a foreign key referring to Students:

– Enrolled(sid: string, cid: string, grade: string)
– If all foreign key constraints are enforced, referential
integrity is achieved
– i.e., no dangling references

17
 Foreign Keys in SQL
• Only students listed in the Students relation should be
allowed to enroll for courses
CREATE TABLE Enrolled
(sid CHAR(20), cid CHAR(20), grade CHAR(2),
PRIMARY KEY (sid,cid),
FOREIGN KEY (sid) REFERENCES Students )

Enrolled
Students
sid cid grade
sid name login age gpa
53666 Carnatic101 C
53666 Jones jones@cs 18 3.4
53666 Reggae203 B
53650 Topology112 A 53688 Smith smith@eecs 18 3.2
53666 History105 B 53650 Smith smith@math 19 3.8

18
 Enforcing Referential Integrity
• Consider Students and Enrolled
– sid in Enrolled is a foreign key that references Students.

• What should be done if an Enrolled tuple with a non-existent

student id is inserted?
– Reject it!

• What should be done if a Students tuple is deleted?

– Three semantics allowed by SQL
1. Also delete all Enrolled tuples that refer to it (cascade delete)
2. Disallow deletion of a Students tuple that is referred to
3. Set sid in Enrolled tuples that refer to it to a default sid
4. (in addition in SQL): Set sid in Enrolled tuples that refer to it to a special
value null, denoting `unknown’ or `inapplicable’

• Similar if primary key of Students tuple is updated

19
 Referential Integrity in SQL
• SQL/92 and SQL:1999 support
all 4 options on deletes and CREATE TABLE Enrolled
updates. (sid CHAR(20) DEFAULT ‘000’,
– Default is NO ACTION cid CHAR(20),
(delete/update is grade CHAR(2),
PRIMARY KEY (sid,cid),
rejected) FOREIGN KEY (sid)
– CASCADE (also delete all REFERENCES Students
tuples that refer to ON DELETE CASCADE
deleted tuple) ON UPDATE SET DEFAULT )
– SET NULL / SET DEFAULT (sets
foreign key value of
referencing tuple)
20
 Where do ICs Come From?
• ICs are based upon the semantics of the real-world enterprise
that is being described in the database relations

• Can we infer ICs from an instance?

– We can check a database instance to see if an IC is violated, but we
can NEVER infer that an IC is true by looking at an instance.
– An IC is a statement about all possible instances!
– From example, we know name is not a key, but the assertion that sid is
a key is given to us.

• Key and foreign key ICs are the most common; more general
ICs supported too

38
 Example Instances
• What does the key (sid, bid, day) in Sailor
Reserves mean? sid sname rating age
22 dustin 7 45
31 lubber 8 55
• If the key for the Reserves relation
contained only the attributes (sid, 58 rusty 10 35
bid), how would the semantics
differ? Reserves
sid bid day
22 101 10/10/96
58 103 11/12/96

22
 Basic SQL Query
SELECT [DISTINCT] <target-list>
FROM <relation-list>
WHERE <qualification>

• relation-list A list of relation names

– possibly with a “range variable” after each name
• target-list A list of attributes of relations in relation-list
• qualification Comparisons
– (Attr op const) or (Attr1 op Attr2)
– where op is one of = , <, >, <=, >= combined using AND, OR and NOT
• DISTINCT is an
optional keyword indicating that the answer should not
contain duplicates
– Default is that duplicates are not eliminated!

23
 Can see the next few slides first

Conceptual Evaluation Strategy

SELECT [DISTINCT] <target-list>
FROM <relation-list>
WHERE <qualification>

• Semantics of an SQL query defined in terms of the following

conceptual evaluation strategy:
– Compute the cross-product of <relation-list>
– Discard resulting tuples if they fail <qualifications>
– Delete attributes that are not in <target-list>
– If DISTINCT is specified, eliminate duplicate rows

• This strategy is probably the least efficient way to compute a

query!
– An optimizer will find more efficient strategies to compute the
same answers

24
 Example of Conceptual Evaluation
Sailor
SELECT [Link]
FROM Sailors S, Reserves R sid sname rating age
WHERE [Link]=[Link] AND [Link]=103 22 dustin 7 45
31 lubber 8 55
58 rusty 10 35
Step 1: Form cross product of Sailor and Reserves
Reserves
sid sname rating age sid bid day sid bid day
22 dustin 7 45 22 101 10/10/96 22 101 10/10/96
22 dustin 7 45 58 103 11/12/96 58 103 11/12/96
31 lubber 8 55 22 101 10/10/96
31 lubber 8 55 58 103 11/12/96
58 rusty 10 35 22 101 10/10/96
58 rusty 10 35 58 103 11/12/96

25
 Example of Conceptual Evaluation
Sailor
SELECT [Link]
FROM Sailors S, Reserves R sid sname rating age
WHERE [Link]=[Link] AND [Link]=103 22 dustin 7 45
31 lubber 8 55
58 rusty 10 35
Step 2: Discard tuples that do not satisfy <qualification>
Reserves
sid sname rating age sid bid day sid bid day
22 dustin 7 45 22 101 10/10/96 22 101 10/10/96
22 dustin 7 45 58 103 11/12/96 58 103 11/12/96
31 lubber 8 55 22 101 10/10/96
31 lubber 8 55 58 103 11/12/96
58 rusty 10 35 22 101 10/10/96
58 rusty 10 35 58 103 11/12/96

26
 Example of Conceptual Evaluation
Sailor
SELECT [Link]
FROM Sailors S, Reserves R sid sname rating age
WHERE [Link]=[Link] AND [Link]=103 22 dustin 7 45
31 lubber 8 55
58 rusty 10 35
Step 3: Select the specified attribute(s)
Reserves
sid sname rating age sid bid day sid bid day
22 dustin 7 45 22 101 10/10/96 22 101 10/10/96
22 dustin 7 45 58 103 11/12/96 58 103 11/12/96
31 lubber 8 55 22 101 10/10/96
31 lubber 8 55 58 103 11/12/96
58 rusty 10 35 22 101 10/10/96
58 rusty 10 35 58 103 11/12/96

27
 A Note on “Range Variables”
• Really needed only if the same relation appears twice
in the FROM clause
– sometimes used as a short-name
• The previous query can also be written as:

SELECT [Link]
FROM Sailors S, Reserves R It is good style,
however, to use
WHERE [Link]=[Link] AND bid=103
range variables
always!
OR SELECT sname
FROM Sailors, Reserves
WHERE [Link]=[Link]
AND bid=103

28
 Joins
sid sname rating age
• Condition/Theta-Join 22 dustin 7 45

• Equi-Join 31 lubber 8 55
58 rusty 10 35
• Natural-Join
• (Left/Right/Full) Outer-Join sid bid day
22 101 10/10/96
58 103 11/12/96

29
 Condition/Theta Join
SELECT * sid sname rating age
FROM Sailors S, Reserves R 22 dustin 7 45
WHERE [Link]=[Link] and age >= 40
31 lubber 8 55
58 rusty 10 35
Form cross product, discard rows that do not satisfy the condition

sid sname rating age sid bid day sid bid day
22 dustin 7 45 22 101 10/10/96 22 101 10/10/96
22 dustin 7 45 58 103 11/12/96 58 103 11/12/96
31 lubber 8 55 22 101 10/10/96
31 lubber 8 55 58 103 11/12/96
58 rusty 10 35 22 101 10/10/96
58 rusty 10 35 58 103 11/12/96

30
 Equi Join
SELECT * sid sname rating age
FROM Sailors S, Reserves R 22 dustin 7 45
WHERE [Link]=[Link] and age = 45
31 lubber 8 55
A special case of theta join 58 rusty 10 35
Join condition only has equality predicate =

sid sname rating age sid bid day sid bid day
22 dustin 7 45 22 101 10/10/96 22 101 10/10/96
22 dustin 7 45 58 103 11/12/96 58 103 11/12/96
31 lubber 8 55 22 101 10/10/96
31 lubber 8 55 58 103 11/12/96
58 rusty 10 35 22 101 10/10/96
58 rusty 10 35 58 103 11/12/96

31
 Natural Join
SELECT * sid sname rating age
FROM Sailors S NATURAL JOIN Reserves R 22 dustin 7 45
31 lubber 8 55
A special case of equi join 58 rusty 10 35
Equality condition on ALL common predicates (sid)
Duplicate columns are eliminated
sid sname rating age bid day sid bid day
22 dustin 7 45 101 10/10/96 22 101 10/10/96
22 dustin 7 45 103 11/12/96 58 103 11/12/96
31 lubber 8 55 101 10/10/96
31 lubber 8 55 103 11/12/96
58 rusty 10 35 101 10/10/96
58 rusty 10 35 103 11/12/96

32
 Outer Join
SELECT [Link], R. bid sid sname rating age
FROM Sailors S LEFT OUTER JOIN Reserves R 22 dustin 7 45
ON [Link]=[Link]
31 lubber 8 55
58 rusty 10 35
Preserves all tuples from the left table whether or not there is a match
if no match, fill attributes from right with null
Similarly RIGHT/FULL outer join sid bid day
22 101 10/10/96
sid bid 58 103 11/12/96
22 101
31 null
58 103

33
 Expressions and Strings
SELECT [Link], age1=[Link]-5, 2*[Link] AS age2
FROM Sailors S
WHERE [Link] LIKE ‘B_%B’

• Illustrates use of arithmetic expressions and string pattern matching

• Find triples (of ages of sailors and two fields defined by expressions)
for sailors
– whose names begin and end with B and contain at least three characters
• LIKE is used for string matching. `_’ stands for any one character
and `%’ stands for 0 or more arbitrary characters
– You will need these often

34
 Find sid’s of sailors who’ve reserved a red or a
green boat Sailors (sid, sname, rating, age)
Reserves(sid, bid, day)
Boats(bid, bname, color)
• Assume a Boats relation SELECT [Link]
FROM Sailors S, Boats B, Reserves R
• UNION: Can be used to
WHERE [Link]=[Link] AND [Link]=[Link]
compute the union of any AND ([Link]=‘red’ OR [Link]=‘green’)
two union-compatible sets of
tuples
– can themselves be the result of
SELECT [Link]
SQL queries
FROM Sailors S, Boats B, Reserves R
• If we replace OR by AND in the WHERE [Link]=[Link] AND [Link]=[Link]
first version, what do we get? AND [Link]=‘red’
UNION
• Also available: EXCEPT (What SELECT [Link]
do we get if we replace UNION FROM Sailors S, Boats B, Reserves R
by EXCEPT?) WHERE [Link]=[Link] AND [Link]=[Link]
AND [Link]=‘green’
35
 Sailors (sid, sname, rating, age)
Find sid’s of sailors who’ve reserved Reserves(sid, bid, day)
a red and a green boat Boats(bid, bname, color)

36
 Sailors (sid, sname, rating, age)
Find sid’s of sailors who’ve reserved Reserves(sid, bid, day)
a red and a green boat Boats(bid, bname, color)

SELECT [Link]
FROM Sailors S, Boats B1, Reserves R1,
• INTERSECT: Can be used to Boats B2, Reserves R2
compute the intersection of WHERE [Link]=[Link] AND [Link]=[Link]
AND [Link]=[Link] AND [Link]=[Link]
any two union-compatible AND ([Link]=‘red’ AND [Link]=‘green’)
sets of tuples.
– Included in the SQL/92 SELECT [Link] Key field!
standard, but some systems FROM Sailors S, Boats B, Reserves R
don’t support it WHERE [Link]=[Link] AND [Link]=[Link]
AND [Link]=‘red’
INTERSECT
SELECT [Link]
FROM Sailors S, Boats B, Reserves R
WHERE [Link]=[Link] AND [Link]=[Link]
AND [Link]=‘green’

37
 Nested Queries
Find names of sailors who’ve reserved boat #103:
SELECT [Link] Sailors (sid, sname, rating, age)
FROM Sailors S Reserves(sid, bid, day)
WHERE [Link] IN (SELECT [Link] Boats(bid, bname, color)
FROM Reserves R
WHERE [Link]=103)

• A very powerful feature of SQL:

– a WHERE/FROM/HAVING clause can itself contain an SQL query
• To find sailors who’ve not reserved #103, use NOT IN.
• To understand semantics of nested queries, think of a
nested loops evaluation
– For each Sailors tuple, check the qualification by computing the
subquery
38
 Nested Queries with Correlation
Find names of sailors who’ve reserved boat #103:
SELECT [Link]
FROM Sailors S
WHERE EXISTS (SELECT *
FROM Reserves R
WHERE [Link]=103 AND [Link]=[Link])

• EXISTS is another set comparison operator, like IN

• Illustrates why, in general, subquery must be re-
computed for each Sailors tuple

39
 Nested Queries with Correlation
Find names of sailors who’ve reserved boat #103
at most once:
SELECT [Link]
FROM Sailors S
WHERE UNIQUE (SELECT [Link]
FROM Reserves R
WHERE [Link]=103 AND [Link]=[Link])

• If UNIQUE is used, and * is replaced by [Link], finds

sailors with at most one reservation for boat #103
– UNIQUE checks for duplicate tuples

40
 More on Set-Comparison Operators
• We’ve already seen IN, EXISTS and UNIQUE
• Can also use NOT IN, NOT EXISTS and NOT UNIQUE.
• Also available: op ANY, op ALL, op IN
– where op : >, <, =, <=, >=
• Find sailors whose rating is greater than that of some
sailor called Horatio
– similarly ALL SELECT *
FROM Sailors S
WHERE [Link] > ANY (SELECT [Link]
FROM Sailors S2
WHERE [Link]=‘Horatio’)
41
 Aggregate Operators
COUNT (*)
COUNT ( [DISTINCT] A)
Check yourself:
What do these queries compute? SUM ( [DISTINCT] A)
AVG ( [DISTINCT] A)
MAX (A)
MIN (A)
SELECT COUNT (*)
single column
FROM Sailors S
SELECT [Link]
SELECT AVG ([Link]) FROM Sailors S
FROM Sailors S WHERE [Link]= (SELECT MAX([Link])
WHERE [Link]=10 FROM Sailors S2)

SELECT COUNT (DISTINCT [Link]) SELECT AVG ( DISTINCT [Link])

FROM Sailors S FROM Sailors S
WHERE [Link]=‘Bob’ WHERE [Link]=10
42
 Motivation for Grouping
• So far, we’ve applied aggregate operators to all
(qualifying) tuples
– Sometimes, we want to apply them to each of several groups
of tuples
• Consider: Find the age of the youngest sailor for each
rating level
– In general, we don’t know how many rating levels exist, and
what the rating values for these levels are!
– Suppose we know that rating values go from 1 to 10; we can
write 10 queries that look like this (need to replace i by num):
SELECT MIN ([Link])
For i = 1, 2, ... , 10: FROM Sailors S
WHERE [Link] = i
43
 Queries With GROUP BY and HAVING
SELECT [DISTINCT] target-list
FROM relation-list
WHERE qualification
GROUP BY grouping-list
HAVING group-qualification

• The target-list contains

– (i) attribute names
– (ii) terms with aggregate operations (e.g., MIN ([Link]))
• The attribute list (i) must be a subset of grouping-list
– Intuitively, each answer tuple corresponds to a group, and these attributes
must have a single value per group
– Here a group is a set of tuples that have the same value for all attributes in
grouping-list
44
Find age of the youngest sailor with age >= 18, for each
rating with at least 2 such sailors.
Sailors instance:
SELECT [Link], MIN ([Link]) AS minage
FROM Sailors S sid sname rating age
WHERE [Link] >= 18 22 dustin 7 45.0
GROUP BY [Link]
29 brutus 1 33.0
HAVING COUNT (*) > 1
31 lubber 8 55.5
32 andy 8 25.5
58 rusty 10 35.0
64 horatio 7 35.0
rating minage 71 zorba 10 16.0
Answer relation: 3 25.5 74 horatio 9 35.0
7 35.0 85 art 3 25.5
8 25.5 95 bob 3 63.5
96 frodo 3 25.5
45
Find age of the youngest with age >= 18, for each rating
with at least 2 such sailors. SELECT [Link], MIN
Step 1: Form the cross product: FROM clause ([Link]) AS minage
(some attributes are omitted for simplicity) FROM Sailors S
WHERE [Link] >= 18
rating age
GROUP BY [Link]
7 45.0 HAVING COUNT (*) > 1
1 33.0
8 55.5
8 25.5
10 35.0
7 35.0
10 16.0
9 35.0
3 25.5
3 63.5
3 25.5
46
Find age of the youngest with age >= 18, for each rating
with at least 2 such sailors. SELECT [Link], MIN
Step 2: Apply WHERE clause ([Link]) AS minage
FROM Sailors S
rating age rating age WHERE [Link] >= 18
7 45.0 7 45.0 GROUP BY [Link]
1 33.0 HAVING COUNT (*) > 1
1 33.0
8 55.5 8 55.5
8 25.5 8 25.5
10 35.0 10 35.0
7 35.0 7 35.0
10 16.0 10 16.0
9 35.0 9 35.0
3 25.5 3 25.5
3 63.5 3 63.5
3 25.5 3 25.5
47
Find age of the youngest with age >= 18, for each rating
with at least 2 such sailors. SELECT [Link], MIN
Step 3: Apply GROUP BY according to the listed attributes ([Link]) AS minage
FROM Sailors S
rating age rating age WHERE [Link] >= 18
rating age
7 45.0 7 45.0 GROUP BY [Link]
1 33.0 HAVING COUNT (*) > 1
1 33.0 1 33.0
3 25.5
8 55.5 8 55.5
3 63.5
8 25.5 8 25.5
3 25.5
10 35.0 10 35.0
7 45.0
7 35.0 7 35.0
10 16.0 7 35.0
10 16.0
9 35.0 8 55.5
9 35.0
3 25.5 3 25.5 8 25.5
3 63.5 3 63.5 9 35.0
3 25.5 3 25.5 10 35.0
48
Find age of the youngest with age >= 18, for each rating
with at least 2 such sailors.
Step 4: Apply HAVING clause SELECT [Link], MIN
The group-qualification is applied to eliminate some groups ([Link]) AS minage
FROM Sailors S
rating age rating age WHERE [Link] >= 18
rating age
7 45.0 7 45.0 GROUP BY [Link]
1 33.0 HAVING COUNT (*) > 1
1 33.0 1 33.0
3 25.5
8 55.5 8 55.5
3 63.5
8 25.5 8 25.5
3 25.5
10 35.0 10 35.0
7 45.0
7 35.0 7 35.0
10 16.0 7 35.0
10 16.0
9 35.0 8 55.5
9 35.0
3 25.5 3 25.5 8 25.5
3 63.5 3 63.5 9 35.0
3 25.5 3 25.5 10 35.0
49
Find age of the youngest with age >= 18, for each rating
with at
Step 5: Apply SELECT clause
least 2 such sailors. SELECT [Link], MIN
Apply the aggregate operator ([Link]) AS minage
At the end, one tuple per group FROM Sailors S
rating age WHERE [Link] >= 18
rating age rating age
GROUP BY [Link]
7 45.0 7 45.0 1 33.0 HAVING COUNT (*) > 1
1 33.0 1 33.0 3 25.5
8 55.5 8 55.5 3 63.5
8 25.5 8 25.5 3 25.5
10 35.0 7 45.0 rating minage
10 35.0
3 25.5
7 35.0 7 35.0 7 35.0
7 35.0
10 16.0 10 16.0 8 55.5
8 25.5
9 35.0 9 35.0 8 25.5
3 25.5 3 25.5 9 35.0
3 63.5 3 63.5 10 35.0
3 25.5 3 25.5
50
Nulls and Views in SQL

51
 Null Values
• Field values in a tuple are sometimes
– unknown, e.g., a rating has not been assigned, or
– inapplicable, e.g., no spouse’s name
– SQL provides a special value null for such situations.

52
 Standard Boolean 2-valued logic
• True = 1, False = 0
• Suppose X = 5
– (X < 100) AND (X >= 1) is T ∧ T = T
– (X > 100) OR (X >= 1) is F ∨ T = T
– (X > 100) AND (X >= 1) is F ∧ T = F
– NOT(X = 5) is ¬T = F

• Intuitively,
– T = 1, F = 0
– For V1, V2 ∈ {1, 0}
– V1 ∧ V2 = MIN (V1, V2)
– V1 ∨ V2 = MAX(V1, V2)
– ¬(V1) = 1 – V1

53
 2-valued logic does not work for nulls

• Suppose rating = null, X = 5

• Is rating>8 true or false?
• What about AND, OR and NOT connectives?
– (rating > 8) AND (X = 5)?
• What if we have such a condition in the
WHERE clause?

54
 3-Valued Logic For Null
• TRUE (= 1), FALSE (= 0), UNKNOWN (= 0.5)
– unknown is treated as 0.5

• Now you can apply rules from 2-valued logic!

– For V1, V2 ∈ {1, 0, 0.5}
– V1 ∧ V2 = MIN (V1, V2)
– V1 ∨ V2 = MAX(V1, V2)
– ¬(V1) = 1 – V1

• Therefore,
– NOT UNKNOWN = UNKNOWN
– UNKNOWN OR TRUE = TRUE
– UNKNOWN AND TRUE = UNKNOWN
– UNKNOWN AND FALSE = FALSE
– UNKNOWN OR FALSE = UNKNOWN
55
 New issues for Null
• The presence of null complicates many issues. E.g.:
– Special operators needed to check if value IS/IS NOT NULL
– Be careful!
– “WHERE X = NULL” does not work!
– Need to write “WHERE X IS NULL”
• Meaning of constructs must be defined carefully
– e.g., WHERE clause eliminates rows that don’t evaluate to true
– So not only FALSE, but UNKNOWNs are eliminated too
– very important to remember!
• But NULL allows new operators (e.g. outer joins)
• Arithmetic with NULL
– all of +, -, *, / return null if any argument is null
• Can force ”no nulls” while creating a table
– sname char(20) NOT NULL
– primary key is always not null
56
 Aggregates with NULL
sid sname rating age • What do you get for
22 dustin 7 45 • SELECT count(*) from R1?
31 lubber 8 55
• SELECT count(rating) from R1?
58 rusty 10 35
R1

57
 Aggregates with NULL
sid sname rating age
22 dustin 7 45 • What do you get for
31 lubber 8 55
• SELECT count(*) from R1?
58 rusty 10 35
R1 • SELECT count(rating) from R1?
• Ans: 3 for both

58
 Aggregates with NULL
sid sname rating age
22 dustin 7 45 • What do you get for
31 lubber 8 55
• SELECT count(*) from R1?
58 rusty 10 35
R1 • SELECT count(rating) from R1?
sid sname rating age • Ans: 3 for both
22 dustin 7 45 • What do you get for
31 lubber null 55 • SELECT count(*) from R2?
58 rusty 10 35 • SELECT count(rating) from R2?
R2

59
 Aggregates with NULL
sid sname rating age
22 dustin 7 45 • What do you get for
31 lubber 8 55
• SELECT count(*) from R1?
58 rusty 10 35
R1 • SELECT count(rating) from R1?
sid sname rating age • Ans: 3 for both
22 dustin 7 45 • What do you get for
31 lubber null 55 • SELECT count(*) from R2?
58 rusty 10 35 • SELECT count(rating) from R2?
R2 • Ans: First 3, then 2

60
 Aggregates with NULL
• COUNT, SUM, AVG, MIN, MAX (with or without DISTINCT)
– Discards null values first
– Then applies the aggregate
– Except count(*)
• If only applied to null values, the result is null

sid sname rating age sid sname rating age

22 dustin 7 45 22 dustin null 45
31 lubber null 55 31 lubber null 55
58 rusty 10 35 58 rusty null 35
R2 R3

• SELECT sum(rating) from R2? • SELECT sum(rating) from R3?

• Ans: 17 • Ans: null
61
 Views
• A view is just a relation, but we store a definition, rather than
a set of tuples
CREATE VIEW YoungActiveStudents (name, grade)
AS SELECT [Link], [Link]
FROM Students S, Enrolled E
WHERE [Link] = [Link] and [Link]<21

• Views can be dropped using the DROP VIEW command

• Views and Security: Views can be used to present necessary information

(or a summary), while hiding details in underlying relation(s)
• the above view hides courses “cid” from E

• More on views later in the course

62
Can create a new table from a query
on other tables too

SELECT… INTO.... FROM.... WHERE

SELECT [Link], [Link]

INTO YoungActiveStudents
FROM Students S, Enrolled E
WHERE [Link] = [Link] and [Link]<21

63
 “WITH” clause – very useful!
• You will find “WITH” clause very useful!
WITH Temp1 AS
(SELECT ….. ..),
Temp2 AS
(SELECT ….. ..)
SELECT X, Y
FROM TEMP1, TEMP2
WHERE….

• Can simplify complex nested queries

64
 Overview: General Constraints
CREATE TABLE Sailors
( sid INTEGER,
• Useful when more general ICs sname CHAR(10),
than keys are involved rating INTEGER,
age REAL,
PRIMARY KEY (sid),
• There are also ASSERTIONS to CHECK ( rating >= 1
specify constraints that span AND rating <= 10 )
across multiple tables
CREATE TABLE Reserves
( sname CHAR(10),
• There are TRIGGERS too : bid INTEGER,
day DATE,
procedure that starts
PRIMARY KEY (bid,day),
automatically if specified changes CONSTRAINT noInterlakeRes
occur to the DBMS CHECK (`Interlake’ <>
( SELECT [Link]
FROM Boats B
WHERE [Link]=bid))) 65
 Only FYI, not covered in detail

Triggers
• Trigger: procedure that starts automatically if specified
changes occur to the DBMS
• Three parts:
– Event (activates the trigger)
– Condition (tests whether the triggers should run)
– Action (what happens if the trigger runs)

CREATE TRIGGER youngSailorUpdate

AFTER INSERT ON SAILORS
REFERENCING NEW TABLE NewSailors
FOR EACH STATEMENT
INSERT
INTO YoungSailors(sid, name, age, rating)
SELECT sid, name, age, rating
FROM NewSailors N
WHERE [Link] <= 18

66
 Summary
• SQL has a huge number of constructs and possibilities
– You need to learn and practice it on your own
– Given a problem, you should be able to write a SQL query and verify
whether a given one is correct

• Pay attention to NULLs

• Can limit answers using “LIMIT” or “TOP” clauses

– e.g. to output TOP 20 results according to an aggregate
– also can sort using ASC or DESC keywords

67