Databases

Basic SQL

Danilo Montesi
[Link]@[Link]
 SQL

■ At first, an acronym for “Structured Query

Language”, now a “proper noun”
■ Language with many features:
▪ Implements both DDL and DML
■ There is an standard ISO language, but
different DBMSs have their own language
grammar
■ For the moment we’re going to see the
basics of this language
2
 SQL: History

■ Its predecessor was SEQUEL (1974)

■ First implementations were SQL/DS and
Oracle (1981)
■ SQL has a “de facto” standard since 1983
■ Many proposed updates (1986, then 1989,
1992, 1999, 2003, 2006, 2008, …) but still,
DBMSs have their own grammar (see some
comparisons on
[Link]

3
 SQL Improvements: SQL-base

■ SQL-86: first proposed standard. It had

most of the clauses for expressing
queries, but offered a limited support for
creating and updating both schemas and
data
■ SQL-89: Referential Integrity is added

4
 SQL Improvements: SQL-2

SQL-92: mostly backward compatible, has

new features:
■ New functions (e.g., COALESCE,
NULLIF, CASE)
■ 3 usage levels: entry, intermediate, full

5
 SQL Improvements: SQL-3 (1)

Different subversions:
■ SQL:1999: proposes the object-relational,
triggers and external functions
■ SQL:2003: extends the object oriented
model and allows to perform queries in Java
and over semistructured data (XML)

6
 SQL Improvements: SQL-3 (2)

■ SQL:2006: SQL is extended with other

languages (e.g., XQuery) for querying
XML data

■ SQL:2008: some slight edits to the syntax

(e.g., trigger with instead of)

7
 SQL Improvements
Unofficial Official
Name Name Features
SQL-86 Basic keywords
SQL-Base
SQL-89 Referential Integrity
Modello relazionale
SQL-2 SQL-92 New keywords
3 levels: entry, intermediate, full
Relational model with object-oriented
SQL:1999 Structured in different parts
Trigger, external functions, …
The support of the Object-Oriented model is extended
SQL-3 SQL:2003 The no-longer used keywords were removed
Extensions: SQL/JRT, SQL/XML, …
SQL:2006 Extended support for XML data

SQL:2008 Slight edits (e.g., trigger instead of)

8
 Data Definition (1)

CREATE DATABASE:
■ Each newly created database contains
tables, views, triggers and other things
■ For example:
CREATE DATABASE db_name

Please Note:
■ In SQLite sqlite3 db_name.db sqlite3_open_v2(db_name)
■ In Mimer CREATE DATABANK db_name
9
 Data Definition (2)

CREATE SCHEMA:
■ A SQL Schema is identified by a name
and describes the elements belonging
to it (tables, types, constraints, views,
domains, …). The schema will belong
to the user which has typed the
statement
■ For example:
CREATE SCHEMA schema_name
10
 Data Definition (3)

CREATE SCHEMA:
■ Such statement could be even followed
by the AUTHORIZATION keyword, to
indicate a specific user owning the
schema
■ For example:
CREATE SCHEMA schema_name
AUTHORIZATION ‘user_name’
From MySQL 8.0 Reference Manual … “CREATE SCHEMA is
a synonym for CREATE DATABASE” 11
 Data Definition (4)

CREATE TABLE:
■ Specifies a new relation and creates its
empty instance
■ It specifies its attributes (with their
types) and initial constraints

12
 CREATE TABLE: an Example
CREATE TABLE EMPLOYEE (
Number CHARACTER(6) PRIMARY KEY,
Name CHARACTER(20) NOT NULL,
Surname CHARACTER(20) NOT NULL,
Dept CHARACTER(15),
Wage NUMERIC(9) DEFAULT 0,
FOREIGN KEY(Dept) REFERENCES
DEPARTMENT(Dept),
UNIQUE (Surname, Name)
)

13
 (Attribute) Data Types

■ (Attribute) Data Types in SQL correspond

to the domains in the relational calculus
▪ Basic data types (already available)
▪ Custom data types (called “domains”
simple and reusable)

14
 Basic Data Types

■ Character-string: data types are either

fixed length or varying length
■ Numeric, including integer numbers and
different floating points
■ DATE, TIME, INTERVAL
■ Introduced with SQL-3 (SQL:1999):
▪ Boolean
▪ BLOB, CLOB (binary/character large
object): representing huge data
collections (either textual or not)
15
 Custom Data Types

CREATE DOMAIN:
■ Each custom data type could be used
when defining new relations, stating
constraints and default values

16
 CREATE DOMAIN: an Example

CREATE DOMAIN Grade

AS SMALLINT DEFAULT NULL
CHECK ( value >=18 AND value <= 30 )

17
 Table Constraints

■ NOT NULL
■ UNIQUE defining keys
■ PRIMARY KEY: (just one, implies NOT
NULL; DB2 has a non standard behaviour)
■ CHECK, let’s see it later

18
 UNIQUE and PRIMARY KEY

It could be used when:

■ when we define an attribute that
defines the key
■ as a stand-alone element

19
 CREATE TABLE: an Example
CREATE TABLE EMPLOYEE (
Number CHARACTER(6) PRIMARY KEY,
Name CHARACTER(20) NOT NULL,
Surname CHARACTER(20) NOT NULL,
Dept CHARACTER(15),
Wage NUMERIC(9) DEFAULT 0,
FOREIGN KEY(Dept) REFERENCES
DEPARTMENT(Dept),
UNIQUE (Surname, Name)
)

20
 PRIMARY KEY: Other Options

Number CHARACTER(6) PRIMARY KEY

Number CHARACTER(6),
…
PRIMARY KEY (Number)

21
 CREATE TABLE: an Example

CREATE TABLE EMPLOYEE (

Number CHARACTER(6) PRIMARY KEY,
Name CHARACTER(20) NOT NULL,
Surname CHARACTER(20) NOT NULL,
Dept CHARACTER(15),
Wage NUMERIC(9) DEFAULT 0,
FOREIGN KEY(Dept) REFERENCES
DEPARTMENT(Dept),
UNIQUE (Surname, Name)
)
22
 Warning!

Name CHARACTER(20) NOT NULL,

Surname CHARACTER(20) NOT NULL,
UNIQUE (Surname,Name)

Name CHARACTER(20) NOT NULL UNIQUE,

Surname CHARACTER(20) NOT NULL UNIQUE,

■ Are not the same!

23
 Key and Referential Integrity Constraints

■ CHECK, let’s see it later

■ REFERENCES and FOREIGN KEY define
Referential Integrity Constraints
■ They can be defined
▪ Over a single attribute
▪ Over multiple attributes
■ We can define referential triggered
actions when such constraints are
violated
24
 Referential Integrity Constraints (1)

OFFENSES Code Date Officer State Number

34321 95/02/01 3987 IT AG548UK
53524 95/03/04 3295 IT TE395AB
64521 96/04/05 3295 FR ZT395AB
73321 98/02/05 9345 FR ZT395AB

OFFICER Id Surname Name

3987 Rossi Luca
3295 Neri Piero
9345 Neri Mario
7543 Mori Gino

25
 Referential Integrity Constraints (2)

OFFENSES Code Date Officer State Number

34321 95/02/01 3987 IT AG548UK
53524 95/03/04 3295 IT TE395AB
64521 96/04/05 3295 FR ZT395AB
73321 98/02/05 9345 FR ZT395AB

CAR State Number Surname Name

IT AG548UK Verdi Giuseppe
IT TE395AB Verdi Giuseppe
FR ZT395AB Quinault Philippe

26
 CREATE TABLE: an Example

CREATE TABLE OFFENCES (

Code CHARACTER(6) PRIMARY KEY,
Day DATE NOT NULL,
Officer INTEGER NOT NULL
REFERENCES OFFICER(Id),
State CHARACTER(2),
Number CHARACTER(6),
FOREIGN KEY(State, Number)
REFERENCES CAR(State, Number)
)
27
 Referential Triggered Action

■ After each referential constraint, we can

specify a triggered action (delete,update)
to be invoked if the operation is rejected:

ON < DELETE | UPDATE >

ON < CASCADE | SET NULL |
ON < SET DEFAULT | NO ACTION >

28
 Referential Triggered Action: Delete

■ CASCADE: deletes the referencing tuples

■ SET NULL: the value of the deleted
referencing attribute is replaced with
NULL
■ SET DEFAULT: the value of the deleted
referencing attributes is replaced with the
specified default value
■ NO ACTION: no removal is allowed

29
 Referential Triggered Action: Update

■ CASCADE: the value of the referencing

foreign key attributes(s) is updated with
the new value
■ SET NULL: the value of the affected
referencing attribute is replaced with
NULL
■ SET DEFAULT: the value of the affected
referencing attributes is replaced with the
specified default value
■ NO ACTION: no update is allowed
30
 Schema Change Statements

■ ALTER DOMAIN
■ ALTER TABLE
■ DROP DOMAIN
■ DROP TABLE

31
 ALTER DOMAIN

ALTER DOMAIN:
■ Allows to alter previously-defined
domains
■ Such statement has to be used
alongside with those other ones: SET
DEFAULT, DROP DEFAULT, ADD
CONSTRAINT or DROP CONSTRAINT

32
 ALTER DOMAIN: an Example (1)

ALTER DOMAIN Grade SET DEFAULT 30

■ Sets the default Grade to 30
■ Such command is applied only when
the command is invoked and missing
grade value are found

ALTER DOMAIN Grade DROP DEFAULT

■ Removes the default Grade value

33
 ALTER DOMAIN: an Example (2)

ALTER DOMAIN Grade

ADD CONSTRAINT isValid
CHECK (value >=18 AND value <=30)
■ Adds the isValid constraint to the
data type Grade
ALTER DOMAIN Grade DROP CONSTRAINT
isValid
■ Removes constraint associated to
the data type
34
 ALTER TABLE

ALTER TABLE:
■ Performs changes to previously
defined tables
■ Such statement has to be used
alongside with these parameters:
ALTER COLUMN, ADD COLUMN, DROP
COLUMN, DROP CONSTRAINT or ADD
CONSTRAINT

35
 ALTER TABLE: an Example (1)

ALTER TABLE EMPLOYEE

ALTER COLUMN Number SET NOT NULL
■ Number from table EMPLOYEE cannot
have null values

ALTER TABLE EMPLOYEE

ADD COLUMN Level CHARACTER(10)
■ An attribute Level is added to the
table EMPLOYEE
36
 ALTER TABLE: an Example (2)

ALTER TABLE EMPLOYEE

DROP COLUMN Level RESTRICT
■ Removes the attribute Level from
EMPLOYEE only if it doesn’t contain
values
ALTER TABLE EMPLOYEE
DROP COLUMN Level CASCADE
■ Removes the attribute Level from
EMPLOYEE alongside with its values 37
 ALTER TABLE: an Example (3)

ALTER TABLE EMPLOYEE

ADD CONSTRAINT validNum
CHECK (char_length(Number) = 10)
■ Adds the validNum constraint to the
Number attribute from EMPLOYEE
ALTER TABLE EMPLOYEE
DROP CONSTRAINT validNum
■ Removes the previously defined
constraint 38
 DROP DOMAIN

DROP DOMAIN:
■ Removes a user-defined data type

Example:
DROP DOMAIN Grade

39
 DROP TABLE

DROP TABLE:
■ Removes a whole table instance with
its schema and its data

Example:
DROP TABLE OFFENCES

40
 Defining Indices

■ They usually enhance the query time,

relevant for computation efficiency
■ They are defined at the physical level, not
logical
■ In the old days this was also the only way
to define keys
■ CREATE INDEX

41
 CREATE INDEX: an Example

CREATE INDEX idx_Surname

ON OFFICER (Surname)
■ Creates the index idx_Surname on the
attribute Surname from the table
OFFICER

42
 DDL in Practice

■ In many systems and projects, different

tools are used, instead of SQL
statements, in order to define a database
schema (e.g., tools with a graphical user
interface)

43
 SQL: Data Operations

■ Query:
■ SELECT

■ Edit:
■ INSERT, DELETE, UPDATE

44
 How to Interpret the SELECT Clause

3 SELECT Number, Name

1 FROM OFFICER
2 WHERE Surname = ‘Jones’

1 From the table OFFICER

2 Retrieve all the officers having ‘Jones’
as Surname attribute
3 Showing for each tuple both Number and
Name
45
 SELECT: Shortcuts (1)

SELECT *
FROM PEOPLE
WHERE Age < 30

SELECT Name, Age, Income

FROM PEOPLE
WHERE Age < 30

46
 Basic SELECT Statement

SELECT <AttributeList>
FROM <TableList>
[ WHERE <Condition> ]

■ Target list
■ FROM statement
■ WHERE statement

47
 Database Example

PEOPLE MOTHERHOOD FATHERHOOD

Name Age Income Mother Child Father Child
Jim 27 21 Abby Alice Steve Frank
James 25 15 Abby Louis Louis Olga
Alice 55 42 Jesse Olga Louis Phil
Jesse 50 35 Jesse Phil Frank Jim
Phil 26 30 Alice Jim Frank James
Louis 50 40 Alice James
Frank 60 20
Olga 30 41
Steve 85 35
Abby 75 87

48
 Selection and Projection

PEOPLE
Return name and income Name Age Income
of people under 30 yo Jim 27 21
James 25 15
πName, Income (σAge<30 (PEOPLE)) Alice 55 42
Jesse 50 35
SELECT Name, Income Phil 26 30
Louis 50 40
FROM PEOPLE Frank 60 20
WHERE Age < 30 Olga 30 41
Steve 85 35
Abby 75 87

49
 Selection and Projection

Return name and income Name Income

of people under 30 yo Jim 21
James 15
πName, Income (σAge<30 (PEOPLE)) Phil 30

SELECT Name, Income

FROM PEOPLE
WHERE Age < 30

50
SELECT: (Attribute) Renaming

SELECT [Link] AS GivenName,

SELECT [Link] AS Revenue
FROM PEOPLE AS P
WHERE [Link] < 30

Name Income GivenName Revenue

Jim 21 Jim 21
James 15 James 15
Phil 30 Phil 30

51
 Pure Selection

PEOPLE
Provide the Name, Age Name Age Income
and Income of people Jim 27 21
under 30 yo James 25 15
Alice 55 42
σAge<30 (PEOPLE) Jesse 50 35
Phil 26 30
Louis 50 40
SELECT * Frank 60 20
FROM PEOPLE Olga 30 41
WHERE Age < 30 Steve 85 35
Abby 75 87

52
 Selection without Projection

Provide the Name, Age Name Age Income

and Income of people Jim 27 21
under 30 yo James 25 15
Phil 26 30
σAge<30 (PEOPLE)

SELECT *
FROM PEOPLE
WHERE Age < 30

53
 Projection without Selection

PEOPLE
Return the peoples’ name Name Age Income
and income Jim 27 21
James 25 15
πName,Income (PEOPLE) Alice 55 42
Jesse 50 35
SELECT Name, Income Phil 26 30
Louis 50 40
FROM PEOPLE Frank 60 20
Olga 30 41
Steve 85 35
Abby 75 87

54
 Projection without Selection

Return the peoples’ name Name Income

and income Jim 21
James 15
πName,Income (PEOPLE) Alice 42
Jesse 35
SELECT Name, Income Phil 30
Louis 40
FROM PEOPLE Frank 20
Olga 41
Steve 35
Abby 87

55
 SELECT: Shortcuts (2)

Given a relation R(A,B)

SELECT *
FROM R

It corresponds to:
SELECT X.A AS A, X.B AS B
FROM R AS X
WHERE true
56
 Composed Conditions

SELECT *
FROM PEOPLE
WHERE Income>25 AND (Age<30 OR
WHERE Age>60)
PEOPLE
Name Age Income
Phil 26 30
Frank 60 20
Olga 30 41
Steve 85 35

57
 Composed Conditions

SELECT *
FROM PEOPLE
WHERE Income>25 AND (Age<30 OR
WHERE Age>60)

Name Age Income

Phil 26 30
Steve 85 35

58
 LIKE Predicate (1)

■ It returns the people having a name

starting with 'J' and have a 'm' as a third
letter:

SELECT *
FROM PEOPLE
WHERE Name LIKE 'J_m%'

59
 LIKE Predicate (2)

SELECT *
FROM PEOPLE
WHERE Name LIKE 'J_m%'
PEOPLE
Name Age Income
Jim 27 21
James 25 15
Alice 55 42
Jesse 50 35
Phil 26 30
Louis 50 40
Frank 60 20
60
 Handling NULL Values

EMPLOYEE
Number Surname Agency Age
5998 Neri Milan 45
9553 Bruni Milan NULL

■ Return the employees being either

more than 40 yo or NULL value

σ (Age > 40) OR (Age IS NULL) (EMPLOYEE)

61
 Example

■ Return the employees being either more

than 40 yo or NULL value

σ (Age > 40) OR (Age IS NULL) (EMPLOYEE)

SELECT *
FROM EMPLOYEE
WHERE Age>40 OR Age IS NULL

62
Projection (Relational Algebra)
EMPLOYEE
Number Surname Agency Age
7309 Neri Naples 55
5998 Neri Milan 64
9553 Rossi Rome 44
5698 Rossi Rome 64

■ Return the surname and the

agency for all the employees
π Surname,Agency (EMPLOYEE)

63
 Projection (SQL and DISTINCT)

SELECT SELECT DISTINCT

Surname, Agency Surname, Agency
FROM EMPLOYEE FROM EMPLOYEE

Surname Agency Surname Agency

Neri Naples Neri Naples
Neri Milan Neri Milan
Rossi Rome Rossi Rome
Rossi Rome

64
 Select, Project, Join

■ By using only one relation in the FROM

clause, one single SQL query can
express: select, project and rename
■ Using more relations in the FROM clause
we have joins (and cartesian products)

65
 SQL vs Relational Algebra (1)

■ R1(A1,A2) R2(A3,A4)
SELECT DISTINCT R1.A1, R2.A4
FROM R1, R2
WHERE R1.A2 = R2.A3
■ Cartesian products (FROM)
■ Selection (WHERE)
■ Projection (SELECT)

66
 SQL vs Relational Algebra (2)

■ R1(A1,A2) R2(A3,A4)
SELECT DISTINCT R1.A1, R2.A4
FROM R1, R2
WHERE R1.A2 = R2.A3
πA1,A4 (σA2=A3 (R1 ⨝ R2))

67
 SQL: Alias and Renaming

■ Renaming could be required

■ in the cartesian product
■ in the target list
SELECT X.A1 AS B1, ...
FROM R1 AS X, R2 AS Y, R1 AS Z
WHERE X.A2 = Y.A3 AND ...

68
 SQL vs Relational Algebra (3)

SELECT DISTINCT X.A1 AS B1, Y.A4 AS B2

FROM R1 AS X, R2 AS Y, R1 AS Z
WHERE X.A2 = Y.A3 AND Y.A4 = Z.A1

ρB1,B2←A1,A4 (
πA1,A4 (σA2 = A3 AND A4 = C1 (
R1 ⨝ R2 ⨝ ρC1,C2 ← A1,A2 (R1)
πA1,A4 ()
)
)
69
 SQL: Evaluating the Queries

■ SQL is a declarative language. We are

providing the semantics by examples
■ DBMS have query execution plans for
efficient evaluations:
■ Selections are run as soon as possible
■ When possible, join are ran instead of
cartesian products

70
 SQL: Formulating the Queries

■ We don’t necessarily have to write

efficient queries since DBMS embed
query optimizers
■ Hereby it is more important that the
provided queries are easy to understand
(avoiding errors when formulating the
query)

71
 Database Example

PEOPLE MOTHERHOOD FATHERHOOD

Name Age Income Mother Child Father Child
Jim 27 21 Abby Alice Steve Frank
James 25 15 Abby Louis Louis Olga
Alice 55 42 Jesse Olga Louis Phil
Jesse 50 35 Jesse Phil Frank Jim
Phil 26 30 Alice Jim Frank James
Louis 50 40 Alice James
Frank 60 20
Olga 30 41
Steve 85 35
Abby 75 87

72
 Example 1

■ People’s fathers earning more than 20

πFather (FATHERHOOD ⨝Child =Name σIncome>20
(PEOPLE))

■ Same query using SQL:

SELECT DISTINCT Father
FROM PEOPLE, FATHERHOOD
WHERE Name=Child AND Income > 20

73
 Example 2

■ Return the people’s name, income and

their father’s income, where such people
earn more than their fathers
πName, Income, IF (σIncome>IF (ρNF,AF,IF ← Name,Age,Income
(PEOPLE)
⨝NF=Father (FATHERHOOD ⨝ Son=Name PEOPLE)
πName, Income, IF (σIncome>IF ()
πName, Income,
SELECT [Link],
IF
() [Link], [Link]
FROM PEOPLE F, FATHERHOOD, PEOPLE C
WHERE [Link] = Father AND Child = [Link]
WHERE AND [Link] > [Link] 74
 SELECT with Renaming

SELECT [Link] AS Name,

SELECT [Link] AS Income,
SELECT [Link] AS fatherIncome
FROM PEOPLE F, FATHERHOOD, PEOPLE C
WHERE [Link] = Father AND
WHERE Child = [Link] AND
WHERE [Link] > [Link]

75
Using Expressions in the Target List

SELECT Income/2 AS halvedIncome

FROM PEOPLE
WHERE Name = 'Louis'

PEOPLE
Name Age Income halvedIncome
Jim 27 21 20
James 25 15
Alice 55 42
Louis 50 40

76
 JOIN Statement

■ Return each person’s mother and father

■ Implicit JOIN:
SELECT [Link], Father, Mother
FROM MOTHERHOOD M, FATHERHOOD F
WHERE [Link] = [Link]

■ Explicit JOIN:
SELECT Mother, [Link], Father
FROM MOTHERHOOD JOIN FATHERHOOD ON
FROM [Link] = [Link]

77
 SELECT with JOIN: Syntax

SELECT …
FROM LeftTable { … JOIN RightTable
FROM ON joincondition }, …
[ WHERE otherPredicate ]

78
 Example

■ Return name, income and father’s income of

those people having a greater income than their
father’s
SELECT [Link], [Link], [Link]
FROM PEOPLE F, FATHERHOOD, PEOPLE C
WHERE [Link] = Father AND Child = [Link] AND
WHERE [Link] > [Link]
SELECT [Link], [Link], [Link]
FROM (PEOPLE F JOIN FATHERHOOD ON
FROM ([Link] = Father) JOIN PEOPLE C ON
FROM (Child = [Link]
WHERE [Link] > [Link]
79
 Natural Join

πChild,Father,Mother (FATHERHOOD ⨝Child=Name ρName←Child

(MOTHERHOOD))

FATHERHOOD ⨝ MOTHERHOOD

SELECT Mother, [Link], Father

FROM MOTHERHOOD JOIN FATHERHOOD ON
FROM [Link] = [Link]

SELECT Mother, Child, Father

FROM MOTHERHOOD NATURAL JOIN FATHERHOOD

80
 Outer Join

■ With the previous joins, also called inner

joins, some of the tuples could be discarded
from the final result: this happens if they don’t
have a correspondent tuple in the other table
■ In order to avoid this information loss, we can
use:
LEFT/RIGHT/FULL OUTER JOIN
■ When such join is either left or right, the
OUTER keyword could be omitted because left
and right are “outer” by definition
81
 Left (Outer) Join

■ Return the father and the mother, if known

SELECT [Link], Father, Mother
FROM FATHERHOOD LEFT [OUTER] JOIN
FROM MOTHERHOOD ON [Link] =
FROM [Link]
[Link] Father Mother
Frank Steve NULL
Olga Louis Jesse
Phil Louis Jesse
Jim Frank Alice
James Frank Alice
82
 Outer Join

SELECT [Link], Father, Mother

FROM MOTHERHOOD JOIN FATHERHOOD ON
FROM [Link] = [Link]

SELECT [Link], Father, Mother

FROM MOTHERHOOD LEFT OUTER JOIN FATHERHOOD ON
FROM [Link] = [Link]
SELECT [Link], Father, Mother
FROM MOTHERHOOD FULL OUTER JOIN FATHERHOOD ON
FROM [Link] = [Link]
■ What does the last query return?

83
Full Outer Join: an Example

[Link] Father Mother

NULL NULL Abby
NULL NULL Abby
Olga Louis Jesse
Phil Louis Jesse
Jim Frank Alice
James Frank Alice
Frank Steve NULL

The full outer join returns all the tuples that

were excluded on both left and right operand
84
Recap

85
 Sorting the Answer

■ Provide the name and the income of

people being less than 30 yo sorted by
alphabetic order
SELECT Name, Income
FROM PEOPLE
WHERE Age < 30
ORDER BY Name ASC
■ ASC ascending order (default)
■ DESC descending order
86
Sorting the Answer: an Example
PEOPLE
Name Age Income
Jim 27 21
James 25 15
Alice 55 42
Jesse 50 35
Phil 26 30
Louis 50 40
Frank 60 20
Olga 30 41
Steve 85 35
Abby 75 87
87
 Sorting the Answer (1)

SELECT Name, Income SELECT Name, Income

FROM PEOPLE FROM PEOPLE
WHERE Age <= 30 WHERE Age <= 30
ORDER BY Name

Name Income Name Income

Jim 21 James 15
James 15 Jim 21
Phil 30 Phil 30

■ ORDER BY ‘s default sorting order is ascending

88
 Sorting the Answer (2)

SELECT Name, Income SELECT Name, Income

FROM PEOPLE FROM PEOPLE
WHERE Age <= 30 WHERE Age <= 30
ORDER BY Name ASC ORDER BY Name DESC

Name Income Name Income

James 15 Phil 30
Jim 21 Jim 21
Phil 30 James 15

89
 Union, Intersection, Difference

■ The SELECT requires a specific statement

for performing unions:
SELECT …
UNION [ALL]
SELECT …
■ In the result the rows are unique (except
when ALL is used. In this case we have a
multiset union)

90
 Set Union
MOTHERHOOD FATHERHOOD
Mother Child Father Child
Abby Alice Steve Frank
Abby Louis Louis Olga
Jesse Olga Louis Phil
SELECT Child
FROM MOTHERHOOD Child
Alice
UNION
Louis
SELECT Child Olga
FROM FATHERHOOD Frank
Phil
91
 Multiset Union
MOTHERHOOD FATHERHOOD
Mother Child Father Child
Abby Alice Steve Frank
Abby Louis Louis Olga
Jesse Olga Louis Phil
SELECT Child
FROM MOTHERHOOD Child
Alice
UNION ALL
Louis
SELECT Child Olga Olga
FROM FATHERHOOD Frank appears
Olga twice
Phil 92
 Positional Notation (1)

SELECT Father, Child

FROM FATHERHOOD
UNION
SELECT Mother, Child
FROM MOTHERHOOD
■ When two tables have different schema, how
could we resolve the conflict by renaming?
■ Either fictitious or none
■ We always assume the names of the first
operand
■ Merge the conflicting attributes
93
Positional Notation: First Operand
Father Child
Steve Frank
Louis Olge
Louis Phil
Frank Jim
Frank James
Abby Alice
Abby Louis
Jesse Olga
Jesse Phil
Alice Jim
Alice James
94
 Positional Notation (2)

SELECT Father, Child SELECT Father, Child

FROM FATHERHOOD FROM FATHERHOOD
UNION UNION
SELECT Child, Mother SELECT Mother, Child
FROM MOTHERHOOD FROM MOTHERHOOD

■ The resulting tables’ resulting scheme in

both cases is (Father, Child)

95
 Difference

SELECT Name
FROM EMPLOYEE
EXCEPT
SELECT Surname AS Name
FROM EMPLOYEE

■ We could later on express such operator

through nested select queries

96
 Intersection

SELECT Name
FROM EMPLOYEE
INTERSECT
SELECT Surname AS Name
FROM EMPLOYEE

■ It is the same as
SELECT [Link]
FROM EMPLOYEE E, EMPLOYEE F
WHERE [Link] = [Link]
97
 Database Example

PEOPLE MOTHERHOOD FATHERHOOD

Name Age Income Mother Child Father Child
Jim 27 21 Abby Alice Steve Frank
James 25 15 Abby Louis Louis Olga
Alice 55 42 Jesse Olga Louis Phil
Jesse 50 35 Jesse Phil Frank Jim
Phil 26 30 Alice Jim Frank James
Louis 50 40 Alice James
Frank 60 20
Olga 30 41
Steve 85 35
Abby 75 87

98
 Nested Queries

Predicates allow to:

■ compare one (or more, as we will see
later) attributes with the result of a
nested (“sub”) query
■ use the existential quantifier (exists, ∃)

99
 Nested Queries: an Example (1)

■ Provide the name and the income of

Frank’s father
Cartesian product and
WHERE (equi-join)
SELECT Name, Income
FROM PEOPLE, FATHERHOOD
WHERE Name=Father AND Child='Frank'
WHERE clause is true when
SELECT Name, Income subquery result is equal to
FROM PEOPLE Name. Moreover, only one
tuple is produced by the
WHERE Name=(SELECT Father subquery

WHERE Name=(FROM FATHERHOOD

WHERE Name=(WHERE Child='Frank')
100
 Nested Queries: Discussion

■ Nested queries are “less declarative”, but

sometimes more readable since they requires less
variables
■ Nested and non-nested queried could be combined
■ The “subqueries” within nested ones cannot
express set operations (“the union can be
performed within the outer query”); this limitation is
not significative
■ Comparison operators require single values as
operands. A solution is needed to compare a value
with the result of a query (i.e., a relation)
101
 Nested Queries: ANY & ALL

■ Nested queries can be formulated through a

predicate using either ANY or ALL alongside with a
comparison operator (>, <, =, >=, ..), solving the
homogeneity problem

Attribute op ANY( Expr )

■ An outer query tuple is matched if it satisfies the
predicate with respect to any tuples within Expr

Attribute op ALL( Expr )

■ A outer query tuple is matched if it satisfies the
predicate with respect to all tuples within Expr
102
 Nested Queries: IN

Attribute IN( Expr )

■ An outer query tuple is matched if its
values in Attribute is contained within
the elements returned by Expr
■ ANY, ALL and IN can be negated through
using the word NOT before
■ Some interesting equivalences:
■ A IN(Expr) ≡ A = ANY(Expr)
■ A NOT IN(Expr) ≡ A ≠ ALL(Expr)
103
 Nested Queries: an Example (2a)
■ Provide name and income of the fathers’ having child earning
more than 20
SELECT DISTINCT [Link], [Link]
FROM PEOPLE F, FATHERHOOD, PEOPLE C
WHERE [Link] = [Link] AND
WHERE [Link] = [Link] AND [Link] > 20
■ We can rewrite it without DISTINCT, because we will not join
tables so the fathers’ names will not be repeated for each
child:
SELECT Name, Income
FROM PEOPLE
WHERE Name IN (SELECT Father
WHERE Name IN (FROM FATHERHOOD
WHERE Name IN (WHERE Child = ANY (SELECT Name
WHERE Name IN (WHERE Child = ANY (FROM PEOPLE
WHERE Name IN (WHERE Child = ANY (WHERE Income > 20))
104
 Nested Queries: an Example (2b)
■ Provide name and income of the fathers’ having child
earning more than 20
SELECT DISTINCT [Link], [Link]
FROM PEOPLE F, FATHERHOOD, PEOPLE C
WHERE [Link] = Father AND Child = [Link] AND
WHERE [Link] > 20
■ We can rewrite it without DISTINCT:
SELECT Name, Income
FROM PEOPLE
WHERE Name IN (SELECT Father
WHERE Name IN (FROM FATHERHOOD, PEOPLE
WHERE Name IN (WHERE Child=Name AND Income>20)

105
 Nested Queries: an Example (3)
■ Provide name and income of the fathers’ having child
earning more than 20, and provide the child’s income too
SELECT DISTINCT [Link], [Link], [Link]
FROM PEOPLE F, FATHERHOOD, PEOPLE C
WHERE [Link] = Father AND Child = [Link] AND
WHERE [Link] > 20
■ Does the following one provide the same answer?
SELECT Name, Income ANY meaning: clause is
FROM PEOPLE true if Child value is equal
to any of the values
WHERE Name IN (SELECT Father returned by nested query
WHERE Name IN (FROM FATHERHOOD
WHERE Name IN (WHERE Child = ANY (SELECT Name
WHERE Name IN (WHERE Child = ANY (FROM PEOPLE
WHERE Name IN (WHERE Child = ANY (WHERE Income>20))
106
 Nested Queries Visibility
SELECT Name, Income
FROM PEOPLE
WHERE Name IN (SELECT Father
WHERE Name IN (FROM FATHERHOOD
WHERE Name IN (WHERE Child=ANY (SELECT Name
WHERE Name IN (WHERE Child=ANY (FROM PEOPLE
WHERE Name IN (WHERE Child=ANY (WHERE Income>20))
Name
Jim
Name Income Father Alice
Frank 20 Frank Jesse
Louis 40 Louis Phil
Louis 40 Louis Louis
Olga
■ Answer: no, because for each father Steve
we do not view the child’s income Abby
107
 Nested Queries: Considerations

■ Visibility Rules
■ It is not possible to refer to variables
declared within inner blocks
■ If a variable’s name is omitted, we
assume to take the “nearest” declared
one
■ We can refer to a variable defined:
■ within the scope of the query in which it is
defined (i.e., outer blocks)
■ or within the scope of a nested query, at
any level, (i.e., inner block) within it
108
 Semantics of Nested Queries with Variables

■ The inner query is performed one time for

each tuple within the outer query
■ The only way to avoid this is by creating a
view, which, however, modifies the
database schema

109
 Existential Quantification

EXISTS ( Expr )
■ The predicate is true if Expr returns at
least one tuple (i.e., Expr returns a
non-empty set)
■ Typically Expr is a nested query
■ Useful with a linking variable between the
outer query and the nested query

110
 Existential Quantification: an Example (1)

■ People having at least one child

Name column is
SELECT * taken from the
FROM PEOPLE relation declared
in the outer block
WHERE EXISTS (SELECT *
WHERE EXISTS (FROM FATHERHOOD
WHERE EXISTS (WHERE Father = Name)
WHERE OR
WHERE EXISTS (SELECT *
WHERE EXISTS (FROM MOTHERHOOD
WHERE EXISTS (WHERE Mother = Name)

111
 Existential Quantification: an Example (2)

■ Fathers having all their children earning

more than 20
[Link] is
SELECT DISTINCT Father taken from the
FROM FATHERHOOD Z relation obtained
in the outer block
WHERE NOT EXISTS (SELECT *
WHERE NOT EXISTS (FROM FATHERHOOD W,
WHERE NOT EXISTS (FOMR PEOPLE
WHERE NOT EXISTS (WHERE [Link]=[Link]
WHERE NOT EXISTS (WHERE AND [Link]=Name
WHERE NOT EXISTS (WHERE AND Income<=20)

112
 Existential Quantification: Error

■ People of the same age and income

SELECT DISTINCT *
FROM PEOPLE
WHERE EXISTS (SELECT *
WHERE EXISTS (FROM PEOPLE
WHERE EXISTS (WHERE Age=Age AND
WHERE EXISTS (WHERE Income=Income)

Scope rule: Age and Income, without table

reference, implicitly refers to to closest FROM clause

113
 Existential Quantification: Correct

■ People of the same age and income

SELECT DISTINCT *
FROM PEOPLE P
WHERE EXISTS (SELECT *
WHERE EXISTS (FROM PEOPLE
WHERE EXISTS (WHERE [Link]≠Name AND
WHERE EXISTS (WHERE [Link]=Age AND
WHERE EXISTS (WHERE [Link]=Income)

114
 Visibility: Wrong!
SELECT *
FROM EMPLOYEE
WHERE Dept IN (SELECT Name
WHERE Dept IN (FROM DEPARTMENT D1
WHERE Dept IN (WHERE Name='Production')
WHERE OR
WHERE Dept IN (SELECT Name
WHERE Dept IN (FROM DEPARTMENT D2
WHERE Dept IN (WHERE [Link] = [Link])

Wrong because in the last selection the city of

D1 is not visible
115
 Set Difference and Nested Queries

SELECT Name
FROM EMPLOYEE
EXCEPT
SELECT Surname AS Name
FROM EMPLOYEE

SELECT [Link]
FROM EMPLOYEE E
WHERE NOT EXISTS (SELECT *
WHERE NOT EXISTS (FROM EMPLOYEE
WHERE NOT EXISTS (WHERE Surname = [Link])
116
 Nested Queries Positions

■ WHERE clause: standard use, we have

seen several examples so far
■ FROM clause: a new data source (i.e., a
relation) is required, the alternative is to
create a view that, nevertheless, modifies
the database schema
■ SELECT clause: uncommon use, it is
equivalent to a join. It necessarily requires
a tuple as a result
117
 Nested Queries in FROM clause

■ Provide the name and the income of Jim’s

children
SELECT Name, Income
FROM PEOPLE P, (SELECT Child
FROM PEOPLE P, (FROM FATHERHOOD
FROM PEOPLE P, (WHERE Father='Jim') AS
FROM PEOPLE P, (JIMCHILD
WHERE Name = [Link]

118
 Nested Queries in SELECT clause

■ Provide the total shipping charges for

each customer in the customer table
SELECT [Link],
(SELECT SUM(ShipCharge)
FROM ORDERS
WHERE [Link]=[Link])
AS TotalShipCharge
FROM CUSTOMER

The query can be rewritten by simply using the

join between the two tables!
119
 Aggregate Functions

In the target list, we can put expressions

that compute values from a set of tuples
through aggregate functions:

■ Aggr: COUNT | MIN | MAX | AVG | SUM

■ Basic syntax:
Aggr([DISTINCT] *)
Aggr([DISTINCT] Attribute)

120
 Aggregate Functions: COUNT (1)

■ The number of Frank’s children

SELECT COUNT(*) AS NumFrankChildren
FROM FATHERHOOD
WHERE Father = 'Frank'
■ The aggregate function (COUNT) is applied
to the tuples of the following result:
SELECT *
FROM FATHERHOOD
WHERE Father = 'Frank'
121
Aggregate Functions: COUNT (2)

FATHERHOOD
Father Child
Steve Frank
Louis Olga
Louis Phil
Frank Jim
Frank James

NumFrankChildren
2

122
 COUNT DISTINCT
PEOPLE
Name Age Income
Jim 27 30
James 25 24
Alice 55 36
Jesse 50 36

SELECT COUNT(*) COUNT(*)

4
FROM PEOPLE

SELECT COUNT(DISTINCT Income)

FROM PEOPLE COUNT(DISTINCT Income)
3
123
 Some Other Aggregate Functions

■ SUM, AVG, MAX, MIN

■ Average of the income of Frank’s children
SELECT AVG(Income)
FROM PEOPLE JOIN FATHERHOOD ON
FROM Name=Child
WHERE Father='Frank'

124
 COUNT with NULL Values (1)
PEOPLE
Name Age Income
Jim 27 30
James 25 NULL
Alice 55 36
Jesse 50 36

SELECT COUNT(*) COUNT(*)

4
FROM PEOPLE

SELECT COUNT(Income) COUNT(Income)

3
FROM PEOPLE
125
 COUNT with NULL Values (2)
PEOPLE
Name Age Income
Jim 27 21
James 25 NULL
Alice 55 21
Jesse 50 35

SELECT COUNT(DISTINCT Income)

FROM PEOPLE

COUNT(DISTINCT Income)
2

126
 Aggregate Functions and NULLs
PEOPLE
Name Age Income
Jim 27 21
James 25 NULL
Alice 55 21
Jesse 50 35

SELECT AVG(Income) AS AvgInc

FROM PEOPLE

AvgInc
25.6

127
 Aggregate Functions and Target List

■ A wrong query:
SELECT Name, MAX(Income)
FROM PEOPLE
■ Whose the name? We cannot extract the
name having the max income. The Target
List must have all the same types of
attributes
SELECT MIN(Age), MAX(Income)
FROM PEOPLE
128
 Maximum and Nested Queries

■ Return the people having the (same)

maximum income
SELECT *
FROM PEOPLE
WHERE Income = (SELECT MAX(Income)
WHERE Income = (FROM PEOPLE)

129
 Aggregate Functions and Grouping (1)

■ Aggregate function can operate over

relations’ groups via the GROUP BY
statement:
GROUP BY AttrList

130
 Aggregate Functions and Grouping (2)

■ The number of the fathers’ children

SELECT Father, COUNT(*) AS NumberOfChildren
FROM FATHERHOOD
GROUP BY Father

FATHERHOOD
Father Child Father NumberOfChildren
Steve Frank Steve 1
Louis Olga Louis 2
Louis Phil Frank 2
Frank Jim
Frank James
131
 GROUP BY: Semantics

1. Perform the query without aggregate

functions and without aggregate
operators
SELECT *
FROM FATHERHOOD
2. Then perform the grouping and apply the
aggregate function over each group

132
 Grouping and Target Lists

Wrong:
SELECT Father, AVG([Link]), [Link]
FROM PEOPLE C JOIN FATHERHOOD ON [Link]=Child
FROM JOIN PEOPLE F ON Father=[Link]
GROUP BY Father
We also need to
group by [Link]
Correct:
SELECT Father, AVG([Link]), [Link]
FROM PEOPLE C JOIN FATHERHOOD ON [Link]=Child
FROM JOIN PEOPLE F ON Father=[Link]
GROUP BY Father, [Link]
133
 Conditions on Groups

■ Provide those fathers whose children

have an average income greater than 25;
return the father and their children’s
average income
SELECT Father, AVG([Link])
FROM PEOPLE C JOIN FATHERHOOD ON
FROM [Link]=Child
GROUP BY Father
HAVING AVG([Link]) > 25
134
 WHERE vs. HAVING

■ Provide the fathers whose children under

30 yo have an average income greater
than 20
SELECT Father, AVG([Link])
FROM PEOPLE C JOIN FATHERHOOD ON
FROM [Link]=Child
WHERE [Link] < 30
GROUP BY Father
HAVING AVG([Link]) > 20
135
 Grouping and NULLs
R A B
1 11
2 11
3 NULL
4 NULL

B COUNT(*) A COUNT(*)
SELECT B, COUNT(*) SELECT A, COUNT(*)
11 2 1 1
FROM R GROUP BY B FROM R GROUP BY A
NULL 2 2 1
3 1
4 1
SELECT A, COUNT(B) A COUNT(B)
FROM R GROUP BY A 1 1
2 1
3 0
4 0
136
 SELECT Syntax: Summary

SELECT AttList1 + Exprs

FROM TableList + Joins
[ WHERE Condition ]
[ GROUP BY AttList2]
[ HAVING AggrCondition]
[ ORDER BY OrderingAttr1]

137
 Updating Operations

■ Such operations are

■ INSERT
■ DELETE
■ UPDATE
■ … of one or more tuples within a table
■ … on the basis of a predicate that may
involve other relations

138
 INSERT

INSERT INTO Table [(AttList)]

VALUES( Vals )

or

INSERT INTO Table [(AttList)]

SELECT ...

139
 INSERT: Examples

INSERT INTO PEOPLE(Name, Age, Income)

VALUES('Jack', 25, 52)
INSERT INTO PEOPLE VALUES('John', 25, 52)
INSERT INTO PEOPLE(Name, Income)
VALUES('Robert', 55)
INSERT INTO PEOPLE(Name)
SELECT Father
FROM FATHERHOOD
WHERE Father NOT IN (SELECT Name
WHERE Father NOT IN (FROM PEOPLE)
140
 INSERT: Discussion

■ The attributes’ and the values’ ordering is

relevant
■ Both lists should have the same number of
arguments
■ If the attribute list is omitted, we assume that all
the attributes are considered and each value
corresponds to a specific attribute as
declared in the relation’s schema
■ If the attribute list does not contain all the
relation’s attributes, either a NULL value or a
default value are emplaced
141
 Deleting Tuples

DELETE FROM Table

[ WHERE Condition ]

142
 Deleting Tuples: some Examples

DELETE FROM PEOPLE

WHERE Age < 35

DELETE FROM FATHERHOOD

WHERE Child NOT IN (SELECT Name
WHERE Child NOT IN (FROM PEOPLE)

DELETE FROM FATHERHOOD

143
 Deleting Tuples: Discussion

■ Removes the tuples satisfying a given

condition
■ It could cause the removal of other tuples (if
the constraints are defined using CASCADE)
■ If no condition is provided, such has to be
intended as WHERE TRUE

144
 Updating Tuples

UPDATE TableName
SET Attribute = < Expr |
SET Attribute = < SELECT … |
SET Attribute = < NULL |
SET Attribute = < DEFAULT >
SE[ WHERE Condition ]

145
 Updating Tuples (1)
PEOPLE
Name Age Income
BEFORE Jim 27 30
James 25 15
Bob 55 36
UPDATE PEOPLE
SET Income = 45
WHERE Name = Bob
PEOPLE
Name Age Income
AFTER Jim 27 30
James 25 15
Bob 55 45
146
 Updating Tuples (2)
PEOPLE
Name Age Income
BEFORE Jim 27 30
James 25 15
Bob 55 36
UPDATE PEOPLE
SET Income = Income*1.1
WHERE Age < 30
PEOPLE
Name Age Income
AFTER Jim 27 33
James 25 16.5
Bob 55 36
147
 Updating Tuples (3)
PEOPLE
Name Age Income
BEFORE Jim 27 30
James 25 15
UPDATE PEOPLE Bob 55 36
SET Income =
(SELECT Income FROM
(PEOPLE WHERE Name=Jim)
WHERE Name = Bob PEOPLE
Name Age Income
AFTER Jim 27 30
James 25 15
Bob 55 30
148
 Updating Tuples (4)
PEOPLE
Name Age Income
BEFORE Jim 27 30
James 25 15
Bob 55 36
UPDATE PEOPLE
SET Income = NULL
WHERE Age < 30
PEOPLE
Name Age Income
AFTER Jim 27 NULL
James 25 NULL
Bob 55 36
149
 Updating Tuples (5)
PEOPLE
Name Age Income
BEFORE Jim 27 30
James 25 15
Bob 55 36
UPDATE PEOPLE
SET Income = DEFAULT
WHERE Age < 30
PEOPLE
Name Age Income
AFTER Jim 27 0
James 25 0
Assuming that in CREATE TABLE
we specified 0 as the DEFAULT Bob 55 36
value for Income 150