Database Management Systems

Chapter 4: Introduction to SQL

Instructor: Khaleel Mershad

[Link]@[Link]
[Link]
CSC 375

Database Management Systems 3ed, R. Ramakrishnan and J. Gehrke 1

 History

 IBM Sequel language developed as part of System R

project at the IBM San Jose Research Laboratory in 1970.
 Renamed Structured Query Language (SQL) in 1973.
 ANSI and ISO standard SQL:
 SQL-86, SQL-89, SQL-92, SQL:1999, SQL:2003, SQL:2006,
SQL:2008, SQL:2011, SQL:2016, SQL:2019, SQL:2023.
 Commercial systems offer most of SQL-92 features, plus
varying feature sets from later standards and special
proprietary features.
 Not all studied examples may work on your particular
DBMS system.

Database Management Systems 3ed, R. Ramakrishnan and J. Gehrke 2

 Introduction

 SQL has always been the standard query language

for relational DBs.
 Most SQL queries take one or more input tables and
return zero or one output table.
 SQL contains many features that go beyond the
expressiveness of traditional queries. For example,
it supports operations such as sorting, aggregation
functions, and set operations.

Database Management Systems 3ed, R. Ramakrishnan and J. Gehrke 3

 Overview

 SQL Command Types

 DDL Basic Queries
 DML Basic Queries
 DQL Basic Queries
 Aggregate Functions

Database Management Systems 3ed, R. Ramakrishnan and J. Gehrke 4

 SQL Command Types

Database Management Systems 3ed, R. Ramakrishnan and J. Gehrke 5

 Types of SQL commands
 A major strength of SQL is that it supports various
operations on the database using a wide set of commands.
 SQL can be broken down into four sub-languages: DDL,
DML, DQL, and DCL.
 DDL (data definition language) – this is used to create and
modify database objects like tables, users, and indices.
 DML (data manipulation language) – this is used to delete, add,
and modify data within databases.
 DCL (data control language) – this is used to control access to
any data within a database.
 DQL (data query language) – this is used to perform queries on
the data and find information, and is composed of
COMMAND statements only.
Database Management Systems 3ed, R. Ramakrishnan and J. Gehrke 6
 Types of SQL commands
 The SQL data-definition language (DDL) allows the
specification of information about relations, including:
 The schema for each relation (table).
 The domain of values associated with each attribute.
 Integrity constraints

 The SQL data manipulation language (DML) is used

to add and modify database data.
 Works on the stored data (instance) but not the schema or
database objects.
 Generally entails inserting, editing (i.e., updating), or
deleting rows in SQL tables.
Database Management Systems 3ed, R. Ramakrishnan and J. Gehrke 7
 Types of SQL commands
 The SQL data-query language (DQL) includes commands
that allow the retrieval of data from a database.
 Data is retrieved but not changed (opposite to DML).
 The principal DQL command is the SELECT query, which
retrieves data from one or more tables.

 The SQL data control language (DCL) is responsible

for all sorts of administrative tasks around the
database.
 Manage users’ access and users’ rights in a database.
 Setting permissions for database users.

Database Management Systems 3ed, R. Ramakrishnan and J. Gehrke 8

 SQL Major Aspects
 SQL contains many advanced operations and features:
 Triggers: Which are actions executed by the DBMS
whenever changes to the database meet conditions specified
in the trigger.
 Transaction Management: It allows users to explicitly control
aspects of how a transaction is to be executed.
 Embedded SQL: Allows SQL code to be called from a host
language (e.g., Java).
 Dynamic SQL: Allows SQL queries to be constructed and
executed at run-time.
 Remote Database Access: Allows connecting client programs
to remote database servers.
And many others ……
Database Management Systems 3ed, R. Ramakrishnan and J. Gehrke 9
 DDL Basic Queries

Database Management Systems 3ed, R. Ramakrishnan and J. Gehrke 10

 Main DDL Commands
 The most basic SQL command is:
CREATE DATABASE [DB_NAME]
which creates a new database in the DBMS.
 After creating a new database, we create the tables (i.e.,
relations) inside it using the command CREATE TABLE:
CREATE TABLE R (A1 D1, A2 D2, ..., An Dn,
(integrity-constraint1),
...,
(integrity-constraintk));
 R is the name of the relation
 Each Ai is an attribute name in the schema of relation R
 Di is the domain of Ai (i.e., the data type of the values of Ai ).

Database Management Systems 3ed, R. Ramakrishnan and J. Gehrke 11

 Examples of Creating Relations in SQL
 S1 can be used to create the “Students” relation.
 S2 can be used to create the “Enrolled” relation.

CREATE TABLE Students CREATE TABLE Enrolled

(sid CHAR(20), (sid CHAR(20),
name CHAR(20), cid CHAR(20),
login CHAR(10), grade CHAR(2));
age INTEGER,
S2
gpa REAL);
S1
 The DBMS enforces domain constraints whenever tuples are
added or modified.
Database Management Systems 3ed, R. Ramakrishnan and J. Gehrke 12
 Main Domain Types in SQL
 char(n). Fixed length character string, with user-specified length n.
 varchar(n). Variable length character strings, with user-specified
maximum length n.
 int. Integer (a finite subset of the integers that is machine-
dependent).
 smallint. Small integer (a machine-dependent subset of the integer
domain type).
 numeric(p,d). Fixed point number, with user-specified precision of
p digits, with d digits to the right of decimal point. (ex.,
numeric(3,1), allows 44.5 to be stores exactly, but not 444.5 or 0.32)
 real, double. real can hold a value 4 bytes in size, with 7 digits of
precision. double can hold a value 8 bytes in size, with machine
dependent precision.
 float(n). Floating point number, with user-specified precision of at
least n digits. The default value of n is 53
Database Management Systems 3ed, R. Ramakrishnan and J. Gehrke 13
 Other Domain Types
 bit. can take either 0 or 1 as a value.
 date. a calendar date, containing four digit year, month, and day of
the month (stored as YYYY-MM-DD).
 time. the time of the day in hours, minutes, and seconds.
 datetime. specifies a date and time with fractional seconds. It
supports dates in the form year-month-day
hours:minutes:seconds. The default value is 1900-01-01 00:00:00.
The time is based on 24-hour clock.
 timestamp. stores number of seconds passed since the Unix epoch
(1970-01-01).
 year. stores year in a 2-digit or 4-digit format. Range 1901 to 2155 in
4-digit format. Range 70 to 69, representing 1970 to 2069.
 binary(n). fixed-length with a maximum length of 8,000 bytes.
 varbinary(n). variable-length binary data with a maximum length
of 8,000 bytes.
Database Management Systems 3ed, R. Ramakrishnan and J. Gehrke 14
 Integrity Constraints in SQL
 Domain constraints
 NOT NULL: Ensures that a column cannot have a NULL value.
 UNIQUE: Ensures that all values in a column are different.
 PRIMARY KEY (A1, ..., An ): Uniquely identifies each row in a
table.
 FOREIGN KEY (Ai, ..., Aj ) REFERENCES R (Am, ..., An ):
Prevents actions that would destroy links between tables.
 CHECK: Ensures that the values in a column satisfy a specific
condition.
 DEFAULT: Sets a default value for a column if no value is
specified.
 CREATE INDEX: Used to create and retrieve data from the
database very quickly (discussed later).
Database Management Systems 3ed, R. Ramakrishnan and J. Gehrke 15
 Integrity Constraints in SQL
Example:
CREATE TABLE Enrolls (
id CHAR(5),
cid VARCHAR(20),
semester VARCHAR(20) NOT NULL,
capacity INT DEFAULT 30,
crn VARCHAR(10) UNIQUE,
grade CHAR(2),
edate DATE,
PRIMARY KEY (id, cid),
FOREIGN KEY (cid) REFERENCES course(cid),
UNIQUE (cid, semester),
CHECK (capacity<=30)
);

Database Management Systems 3ed, R. Ramakrishnan and J. Gehrke 16

 Integrity Constraints in SQL
 PRIMARY KEY and UNIQUE constraints ensure that the
corresponding attribute(s) cannot be NULL.
 UNIQUE means that the corresponding attribute(s) is a
candidate key.
 For example, UNIQUE (cid, semester) means that the
combination of cid and semester cannot be the same in any two
tuples, which makes (cid, semester) a candidate key.
 FOREIGN KEY constraint links the two attributes or set of
attributes together. For example, we cannot add a new
“Enrolls” tuple in which the cid value does not exist in the
course table (the DBMS will reject the insertion).
 What happens to the cid in “Enrolls” if we delete or update the
corresponding cid in course?
Database Management Systems 3ed, R. Ramakrishnan and J. Gehrke 17
 Enforcing Referential Integrity in SQL
 Referential Integrity options in SQL create a link between two
tables. The DBMS will not allow any modification or change to
any of the two tables unless it satisfies the integrity constraint.
 It can be used by adding the keyword ON to the FOREIGN KEY
constraint followed by an SQL operation (UPDATE or
DELETE), then followed by an action to be taken by the DBMS.
 The action can be: NO ACTION, CASCADE, SET NULL, or SET
DEFAULT.
 For example:
FOREIGN KEY (sid) REFERENCES Students(sid)
ON DELETE CASCADE
ON UPDATE SET DEFAULT

Database Management Systems 3ed, R. Ramakrishnan and J. Gehrke 18

 Enforcing Referential Integrity in SQL
 The actions specified in the Foreign Key constraint will be taken by the
DBMS when deleting or updating the parent table’s values.
 NO ACTION: When the ON UPDATE or ON DELETE clauses are set to NO
ACTION, the performed update or delete operation in the parent table will
fail with an error.
 CASCADE: Setting the ON UPDATE or ON DELETE clauses to CASCADE,
the same action performed on the referenced values of the parent table will
be reflected to the related values in the child table. For example, if the
referenced value (i.e., tuple) is deleted in the parent table, all related rows in
the child table are also deleted.
 SET NULL: With this option, if the referenced values in the parent table are
deleted or modified, all related values in the child table are set to NULL.
 SET DEFAULT: Using the SET DEFAULT option of the ON UPDATE and
ON DELETE clauses specifies that, if the referenced values in the parent
table are updated or deleted, the related values in the child table with
FOREIGN KEY columns will be set to its default value.
Database Management Systems 3ed, R. Ramakrishnan and J. Gehrke 19
 DDL Table Commands
 DROP TABLE: The schema information and the tuples inside
it are deleted.
DROP TABLE Students;
 Some DBMSs reject this command if the table is not empty.
 ALTER TABLE: Modify the table design.
 Add, delete, or modify columns.
 Add and drop various constraints.
ALTER TABLE Students
ADD {COLUMN} gender CHAR(1) {[FIRST | AFTER]
Name}; (COLUMN, FIRST, AFTER are optional)
 All existing tuples in the relation are assigned NULL as the
value for the new attribute.
Database Management Systems 3ed, R. Ramakrishnan and J. Gehrke 20
 DDL Table Commands
 Removing a column from an existing table:
ALTER TABLE Students DROP COLUMN gender;
 Dropping of attributes is not supported by many DBMSs.
(more command options for ALTER TABLE here:
[Link]
 The ALTER COLUMN command is used to change the
data type of a column in a table.
 The following SQL query changes the data type of the
column named “birthdate” in the “Employees” table to
type year:
ALTER TABLE Employees
ALTER COLUMN birthdate YEAR;
Database Management Systems 3ed, R. Ramakrishnan and J. Gehrke 21
 DDL Table Commands
 The ADD CONSTRAINT command is used to create a
constraint after a table is already created.
ALTER TABLE Persons
ADD CONSTRAINT PK_Person PRIMARY KEY (id, lastname);

ALTER TABLE Employees

ADD CONSTRAINT check_salary
CHECK (salary <=10000);

ALTER TABLE Orders

ADD CONSTRAINT FK_PersonOrder
FOREIGN KEY (PersonID) REFERENCES Persons(PersonID);

Database Management Systems 3ed, R. Ramakrishnan and J. Gehrke 22

 DDL Table Commands
 The DROP CONSTRAINT command is used to delete an
existing constraint in the table.
 In order to use DROP CONSTRAINT , you need to know the
constraint name:
ALTER TABLE Persons
DROP CONSTRAINT PK_Person;
ALTER TABLE Employees
DROP CONSTRAINT check_salary;
ALTER TABLE Orders
DROP CONSTRAINT FK_PersonOrder;
 MySQL uses the corresponding constraint name instead of the
keyword CONSTRAINT (i.e., DROP PRIMARY KEY, DROP
CHECK, etc.)
Database Management Systems 3ed, R. Ramakrishnan and J. Gehrke 23
 DML Basic Queries

Database Management Systems 3ed, R. Ramakrishnan and J. Gehrke 24

 Main DML Commands
Adding and Deleting Tuples
 We can insert a single tuple to an existing relation using
the SQL INSERT INTO command:
 INSERT INTO Instructor VALUES (‘10211’, ’Smith’, ’Biology’,
66000);
 INSERT INTO Instructor(id, dept_name, name, salary) VALUES
(‘10211’, ’Biology’, ’Smith’, 66000);
 We can delete tuples from a relation using the DELETE
FROM command:
 DELETE FROM table_name WHERE [condition];
 DELETE FROM Students; → Deletes all tuples in “Students”.
 DELETE FROM Students WHERE id = ‘70982’; → Deletes the
tuple that contains the id “70982”.
Database Management Systems 3ed, R. Ramakrishnan and J. Gehrke 25
 Main DML Commands
Updating Tuples
 We can modify the values of the existing tuples in a table
using the UPDATE command.
 Similar to the DELETE command, the WHERE statement
can be used in the UPDATE query to update only the rows
that satisfy the condition stated in the WHERE statement.
 If the WHERE statement is omitted, all the tuples in the
table are updated.
UPDATE table_name
SET column1 = value1, ...., columnN = valueN
WHERE [condition];
Database Management Systems 3ed, R. Ramakrishnan and J. Gehrke 26
 Main DML Commands
Updating Tuples
 Example:
UPDATE Student
SET tot_cred = tot_cred + 3, gpa = gpa + 0.15
WHERE id = ‘20110’;
 If a column is used in determining how rows are updated its
old value (before update) is used:
UPDATE Students
SET gpa = gpa – 0.1
WHERE gpa > 3.3;
Powerful variants of the INSERT, DELETE, and UPDATE
commands are available; more about this later
Database Management Systems 3ed, R. Ramakrishnan and J. Gehrke 27
 DQL Basic Queries

Database Management Systems 3ed, R. Ramakrishnan and J. Gehrke 28

 E-R Model for Example

sname rating Age bid bname bcolor

sid
Sailors Boats

Reserves

day

Database Management Systems 3ed, R. Ramakrishnan and J. Gehrke 29

 Example Instances

Boats
Sailors
bid bname color
101 interlake blue
102 interlake red
103 clipper green
104 marine red

Reserves
sid bid day
22 101 10/10/96
58 103 11/12/96
Database Management Systems 3ed, R. Ramakrishnan and J. Gehrke 30
 Basic SQL Select Query Syntax
SELECT [DISTINCT] target-list The result of a
FROM relation-list SELECT query is an
WHERE qualification (Optional) SQL relation table.
 relation-list A list of relation names (with commas or join
keywords 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!
Database Management Systems 3ed, R. Ramakrishnan and J. Gehrke 31
 The SELECT Clause
 The SELECT clause lists the attributes desired in the
result of a query
 Example: find the names of all instructors:
SELECT name
FROM Instructor;
 NOTE: SQL keywords are case
insensitive (i.e., you may use
upper- or lower-case letters.)
 E.g., select ≡ Select ≡ SELECT

Database Management Systems 3ed, R. Ramakrishnan and J. Gehrke 32

 The SELECT Clause
 SQL allows duplicates in relations as well as in query
results.
 To force the elimination of duplicates, add the keyword
DISTINCT after SELECT.
 Find the department names of all instructors, and remove
duplicates
SELECT DISTINCT dept_name
FROM Instructor;
 The keyword ALL specifies that duplicates
should not be removed.
SELECT ALL dept_name
FROM Instructor;
Database Management Systems 3ed, R. Ramakrishnan and J. Gehrke 33
 The SELECT Clause
 The SELECT clause can contain arithmetic expressions that
contain one of the operations: +, –, *, and /, and operate on
constants or attributes.
 The query:
SELECT ID, name, salary/12
FROM Instructor;
would return a relation in which the value of the attribute
salary is divided by 12.
 We can rename “salary/12” using the AS keyword:
SELECT ID, name, salary/12 AS monthly_salary
FROM Instructor;
 An asterisk in the select clause means “all attributes”
SELECT *
FROM Instructor;
Database Management Systems 3ed, R. Ramakrishnan and J. Gehrke 34
 The WHERE Clause
 The WHERE clause specifies conditions that the result must satisfy
 To find the names of all instructors in the ‘Comp. Sci.’ department:
SELECT name
FROM Instructor
WHERE dept_name = ‘Comp. Sci.‘;
 Comparison results can be combined using the logical operators
AND, OR, and NOT
 To find all instructors in the ‘Comp. Sci.’ department with salary
greater than 80000
SELECT name
FROM instructor
WHERE dept_name = ‘Comp. Sci.' AND salary > 80000;
 Comparisons can be applied to the results of arithmetic expressions.
 Other advanced options of the WHERE clause will be discussed later.
Database Management Systems 3ed, R. Ramakrishnan and J. Gehrke 35
 The FROM Clause
 The FROM clause lists the relations involved in the query.
 It is called the Cartesian product or the cross product of
two relations.
 Find the Cartesian product of Instructor and Teaches
SELECT ∗
FROM Instructor, Teaches
 generates every possible Instructor – Teaches pair, with all
attributes from both relations.
 For common attributes (e.g., id), the attributes in the
resulting table are renamed using the relation name (e.g.,
[Link])
 Cartesian product is not very useful as it is, but it becomes
useful when combined with the WHERE clause.
Database Management Systems 3ed, R. Ramakrishnan and J. Gehrke 36
 The Cartesian Product

Database Management Systems 3ed, R. Ramakrishnan and J. Gehrke 37

Conceptual Evaluation of SELECT Query
 The easiest way to understand the operations performed by a
SELECT query is to consider the clauses in this order: FROM,
WHERE, then SELECT.
 Hence, the semantics of a SELECT query are defined in terms
of the following conceptual evaluation strategy:
 Compute the cross-product of the relations in the relation-list.
 Discard all resulting tuples that fail the overall condition
defined in qualifications.
 Delete the attributes that are not in target-list.
 If DISTINCT is specified, eliminate duplicate rows.
 This strategy is typically the least efficient way to compute a
query! An optimizer will find more efficient strategies to
compute the same answer.
Database Management Systems 3ed, R. Ramakrishnan and J. Gehrke 38
 Example of Conceptual Evaluation
 Find the names of all sailors who reserved the boat whose id
is equal to 103.
Reserves
sid bid day
Sailors 22 101 10/10/96
58 103 11/12/96

Sailors
x
Reserves

Database Management Systems 3ed, R. Ramakrishnan and J. Gehrke 39

 Example of Conceptual Evaluation
 Find the names of all sailors who reserved the boat whose id
is equal to 103.
Optional!
SELECT [Link]
FROM Sailors AS S, Reserves AS R
WHERE [Link]=[Link] AND [Link]=103

Database Management Systems 3ed, R. Ramakrishnan and J. Gehrke 40

 A Note on Range Variables
 In the previous slide, S and R are called Range
Variables or Aliases.
 Really needed only if the same relation appears
twice in the FROM clause (i.e., a self-join). The
previous query can also be written as:
SELECT [Link]
FROM Sailors S, Reserves R
WHERE [Link]=[Link] AND bid=103
OR
SELECT sname
FROM Sailors, Reserves
WHERE [Link]=[Link] AND bid=103
Database Management Systems 3ed, R. Ramakrishnan and J. Gehrke 41
 Self-Join

 Find the names of all sailors who have a rating

greater than the rating of any sailor whose age is
less than 50.
SELECT distinct [Link]
FROM Sailors S1, Sailors S2
WHERE [Link] <> [Link] AND [Link]>[Link]
AND [Link]<50

Database Management Systems 3ed, R. Ramakrishnan and J. Gehrke 42

 Effect of DISTINCT
 Find all sailors who have reserved at least one boat:
SELECT [Link]
FROM Sailors S, Reserves R
WHERE [Link]=[Link]

 Would adding DISTINCT to this query make a

difference?
 What is the effect of replacing [Link] by [Link] in the
SELECT clause? Would adding DISTINCT to this
variant of the query make a difference?

Database Management Systems 3ed, R. Ramakrishnan and J. Gehrke 43

 Natural Join
 There are several types of table joins in SQL, the most
used among which is the NATURAL JOIN.
 Natural join matches tuples that have the same values for
all attributes that have the same name in the joined tables,
and retains only one copy of each common column.
 R(a, b, c, d, e)
S(b, d, f, g)
common attributes → (b, d)
R NATURAL JOIN S
First, R cross product S → (a, R.b, c, R.d, e, S.b, S.d, f, g)
Next, select tuples that have (R.b=S.b) AND (R.d=S.d)
Finally, keep only one copy of (R.b, S.b), call it “b”; and
one copy of (R.d, S.d), call it “d”.
Database Management Systems 3ed, R. Ramakrishnan and J. Gehrke 44
 Natural Join

 Find the names of all sailors along with the boat ID of the boats
that each sailor reserved.
 Using Cartesian product:
SELECT sname, bid
FROM Sailor, Reserves
WHERE [Link] = [Link];
 Using Natural Join:
SELECT sname, bid
FROM Sailor NATURAL JOIN Reserves;

Database Management Systems 3ed, R. Ramakrishnan and J. Gehrke 45

 Natural Join
SELECT sname, bid
FROM Sailor NATURAL JOIN Reserves;

sname bid
dustin 101
rusty 103

Database Management Systems 3ed, R. Ramakrishnan and J. Gehrke 46

 Natural Join
 Other types of table joins exist (such as outer join), we will
discuss them later.
 Danger in Natural Join: beware of unrelated attributes with
same name which get equated incorrectly.
 Example
 R(a, b, c, d, e)
S(b, d, f, g)
common attributes → (b, d)
 Suppose that we want to join R and S based on “b” only.
 In such case, R NATURAL JOIN S will produce wrong results.
 Hence, we must use:
FROM R, S
WHERE R.b=S.b
Database Management Systems 3ed, R. Ramakrishnan and J. Gehrke 47
 Theta Joins
 Find the names of all sailors along with the boat ID of
the boats that each sailor reserved.
 Another method to execute this query is to use the
keywords “JOIN … USING”, which are used to
specify a special type of join that is done on specific
attributes that the user specifies:
SELECT sname, bid
FROM Sailor JOIN Reserves USING (sid);

Database Management Systems 3ed, R. Ramakrishnan and J. Gehrke 48

 Theta Joins
 Both relations that are joined using “JOIN … USING”
should have the same name of the attribute on which
they are joined (for example, sid in the previous
query).
 Another method for “Theta Join” is to use the ON
keyword, which allows using a general predicate over
the relations being joined (this is a more general theta
join):
SELECT sname, bid
FROM Sailor JOIN Reserves ON [Link]=[Link];

Database Management Systems 3ed, R. Ramakrishnan and J. Gehrke 49

 Expressions and Strings
 SQL includes a string-matching operator for comparisons
between strings and patterns. The operator LIKE can be
used to compare with patterns that are described using
two special characters:
 Percent ( % ). Matches any string of zero or more characters.
 Underscore ( _ ). Matches any single character.
 Example: Find the age of all sailors whose name begins
and ends with ‘b’ and contains at least three characters.
SELECT [Link]
FROM Sailors S
WHERE [Link] LIKE ‘b_%b’

Database Management Systems 3ed, R. Ramakrishnan and J. Gehrke 50

 Expressions and Strings
 If we want to use one of the special characters as an
ordinary character, we must define an escape character:
 For example, if we want a string to match the string
“100%”, we say:
LIKE ‘100\%’ ESCAPE ‘\’
 In the above, we use the backslash character (\) as the
escape character.
 Another example: LIKE ‘ab\\cd%’ ESCAPE ‘\’ matches
all strings beginning with “ab\cd”.
 SQL allows us to search for mismatches instead of
matches by using the NOT LIKE comparison operator.
 Pattern matching in SQL is case sensitive.
Database Management Systems 3ed, R. Ramakrishnan and J. Gehrke 51
 Ordering the Display of Tuples
 List in alphabetic order the names of all sailors
SELECT DISTINCT sname
FROM Sailor
ORDER BY sname;
 We may specify DESC for descending order or ASC for
ascending order. Note that the ascending order is the default.
 Example: ORDER BY sname DESC
 Can order by multiple attributes
 Example:
SELECT dept_name, name
FROM Instructor
ORDER BY dept_name DESC, name ASC;
Database Management Systems 3ed, R. Ramakrishnan and J. Gehrke 52
 Where Clause Predicates
 SQL includes a BETWEEN comparison operator.
 Example: Find the names of all instructors with salaries
between $90,000 and $100,000 (that is, ≥ $90,000 and ≤ $100,000).
 SELECT name
FROM Instructor
WHERE salary BETWEEN 90000 AND 100000;
 Tuple comparison:
 SELECT name, course_id
SELECT Instructor, Teaches
WHERE ([Link], dept_name) = ([Link], ’Biology’);

Database Management Systems 3ed, R. Ramakrishnan and J. Gehrke 53

 Set Operations

 UNION: Can be used to compute the union of any two

union-compatible sets of tuples (which are themselves
the result of SQL queries).
 Example: Find the sids of sailors who reserved a red
boat or a green boat.
 Without UNION:

SELECT [Link]
FROM Boats B, Reserves R
WHERE [Link]=[Link]
AND ([Link]=‘red’ OR [Link]=‘green’)
Database Management Systems 3ed, R. Ramakrishnan and J. Gehrke 54
 Set Operations

 Using UNION:

SELECT [Link]
FROM Boats B, Reserves R
WHERE [Link]=[Link]
AND [Link]=‘red’
UNION
SELECT [Link]
FROM Boats B, Reserves R
WHERE [Link]=[Link]
AND [Link]=‘green’;

Database Management Systems 3ed, R. Ramakrishnan and J. Gehrke 55

 Set Operations
Example: Find the sids of sailors who reserved both a red
boat and a green boat.

Can we say:
SELECT [Link]
FROM Boats B, Reserves R
WHERE [Link]=[Link]
AND ([Link]=‘red’ AND [Link]=‘green’)

No boat has two colors

→ Result is empty !
Database Management Systems 3ed, R. Ramakrishnan and J. Gehrke 56
 Set Operations
 INTERSECT: Can be used to compute the intersection of
any two union-compatible sets of tuples.
 Included in the SQL/92 standard, but some DBMSs don’t
support it.
 Example: Find the sids of sailors who reserved both a red
boat and a green boat.
 Without INTERSECT :
SELECT [Link] Very
FROM Boats B1, Reserves R1, Inefficient
Boats B2, Reserves R2
WHERE [Link]=[Link]
AND [Link]=[Link] AND [Link]=[Link]
AND ([Link]=‘red’ AND [Link]=‘green’)
Database Management Systems 3ed, R. Ramakrishnan and J. Gehrke 57
 Set Operations

 Using INTERSECT:

SELECT [Link]
FROM Boats B, Reserves R
WHERE [Link]=[Link]
AND [Link]=‘red’
INTERSECT
SELECT [Link]
FROM Boats B, Reserves R
WHERE [Link]=[Link]
AND [Link]=‘green’;

Database Management Systems 3ed, R. Ramakrishnan and J. Gehrke 58

 Set Operations
 EXCEPT: Can be used to compute the Set Difference of any
two compatible sets of tuples (which are themselves the result
of SQL queries).
 Example: Find the sids of sailors who reserved a red boat but
not a green boat.
SELECT [Link]
FROM Boats B, Reserves R
WHERE [Link]=[Link]
AND [Link]=‘red’
EXCEPT
SELECT [Link]
FROM Boats B, Reserves R
WHERE [Link]=[Link]
AND [Link]=‘green’;
Database Management Systems 3ed, R. Ramakrishnan and J. Gehrke 59
 Set Operations
 Set operations UNION, INTERSECT, and EXCEPT
 Each of the above operations automatically eliminates
duplicates
 To retain all duplicates we use the corresponding multiset
versions UNION ALL, INTERSECT ALL, and EXCEPT ALL.

 Suppose a tuple occurs m times in R and n times in S, then, it

occurs:
 m + n times in R UNION ALL S
 min(m,n) times in R INTERSECT ALL S
 0 times if m < n
in R EXCEPT ALL S
(m-n) times if m > n
Database Management Systems 3ed, R. Ramakrishnan and J. Gehrke 60
 Set Operations

 Query: Find the highest rating of all sailors.

 Step 1: Find the ratings that are less than the highest
rating.
 SELECT DISTINCT [Link]
FROM Sailors R, Sailors S
WHERE [Link] < [Link]
 Step 2: Find all the ratings of all sailors
 SELECT DISTINCT rating
FROM Sailors

Database Management Systems 3ed, R. Ramakrishnan and J. Gehrke 61

 Set Operations

 Find the highest rating of all sailors.

 (second query)
EXCEPT
(first query)
(SELECT DISTINCT rating
FROM Sailors)
EXCEPT
(SELECT DISTINCT [Link]
FROM Sailors R, Sailors S
WHERE [Link] < [Link]);

Database Management Systems 3ed, R. Ramakrishnan and J. Gehrke 62

 Null Values
 It is possible for an attribute to have a null value, denoted by
NULL.
 NULL signifies an unknown value or that a value does not
exist.
 The result of any arithmetic expression involving NULL is
NULL.
 For example: 5 + NULL returns NULL.
 The statement IS NULL can be used to check for NULL
values.
 Example: Find all sailors who haven’t been rated yet.
SELECT sname
FROM Sailors
WHERE rating IS NULL
Database Management Systems 3ed, R. Ramakrishnan and J. Gehrke 63
 Null Values and Three Valued Logic

 In SQL, there are three logic values:

TRUE
FALSE
UNKNOWN
 Any comparison with NULL returns UNKNOWN.
 Example:
5 < NULL
NULL <> NULL
NULL = NULL

Database Management Systems 3ed, R. Ramakrishnan and J. Gehrke 64

 Null Values and Three Valued Logic
 Three-valued logic using the value UNKNOWN:
 OR: (UNKNOWN OR TRUE) = TRUE
(UNKNOWN OR FALSE) = UNKNOWN
(UNKNOWN OR UNKNOWN) = UNKNOWN
 AND: (TRUE AND UNKNOWN) = UNKNOWN
(FALSE AND UNKNOWN) = FALSE
(UNKNOWN AND UNKNOWN) = UNKNOWN
 NOT: (NOT UNKNOWN) = UNKNOWN
 For example: if R.A is NULL, then “R.A < 1” as well as
“NOT (R.A < 1)” evaluate to UNKNOWN.
 If the WHERE clause predicate evaluates to either FALSE
or UNKNOWN for a tuple, that tuple is not added to the
result.
Database Management Systems 3ed, R. Ramakrishnan and J. Gehrke 65
 Aggregate Functions

Database Management Systems 3ed, R. Ramakrishnan and J. Gehrke 66

 Aggregate Functions

 These functions operate on the multiset of values of a

column of a relation, and return a single value.
AVG: average value
MIN: minimum value
MAX: maximum value
SUM: sum of values
COUNT: number of values
 The input to SUM and AVG must be a collection of
numbers, but the other operators (MIN, MAX, COUNT)
can operate on collections of non-numeric data types, such
as strings (i.e., char and varchar), as well.
Database Management Systems 3ed, R. Ramakrishnan and J. Gehrke 67
 Aggregate Functions

COUNT (*) The number of rows in the relation

The number of (unique) values in the A
COUNT ([DISTINCT] A)
column
The sum of all (unique) values in the A
SUM ([DISTINCT] A)
column
The average of all (unique) values in the A
AVG ([DISTINCT] A)
column
MAX (A) The maximum value in the A column

MIN (A) The minimum value in the A column

Database Management Systems 3ed, R. Ramakrishnan and J. Gehrke 68

 Aggregate Functions
 Find the total number of COUNT (*)
sailors: COUNT ( [DISTINCT] A)
SUM ( [DISTINCT] A)
SELECT COUNT (*)
AVG ( [DISTINCT] A)
FROM Sailors
MAX (A)
MIN (A)
 Find the average age of all sailors
single column
who have a rating equal to 10.
SELECT AVG ([Link]) AS avg_age
FROM Sailors S
WHERE [Link]=10
SELECT AVG (DISTINCT [Link])
What if we use DISTINCT FROM Sailors S
with this query? WHERE [Link]=10
Database Management Systems 3ed, R. Ramakrishnan and J. Gehrke 69
 Aggregate Functions
 Find the number of different ratings COUNT (*)
of all sailors whose name is ‘Bob’ : COUNT ( [DISTINCT] A)
SUM ( [DISTINCT] A)
SELECT COUNT (DISTINCT [Link]) AVG ( [DISTINCT] A)
FROM Sailors S MAX (A)
WHERE [Link]=‘Bob’ MIN (A)
single column
 Find the names of sailors who have
the maximum rating:
SELECT [Link]
FROM Sailors S
WHERE [Link]= (SELECT MAX([Link])
FROM Sailors S2)

Database Management Systems 3ed, R. Ramakrishnan and J. Gehrke 70

 Aggregate Functions
 Find the name(s) and age(s) of the oldest sailor(s):
SELECT [Link], [Link]
FROM Sailors S
WHERE [Link] =
(SELECT MAX ([Link])
FROM Sailors S2)
 What about this query:
SELECT [Link], MAX ([Link])
FROM Sailors S
If the SELECT clause uses an aggregate operation, then it must
use only aggregate operations unless the query contains a
GROUP BY clause (discussed next).

Database Management Systems 3ed, R. Ramakrishnan and J. Gehrke 71

 GROUP BY and HAVING

 So far, we have applied aggregate operators to all the

tuples of the specified column. Sometimes, we want
to apply them to each of several groups of tuples.
 In other words, we want to group the tuples that have
the same values into a single group, and then apply
the aggregate function per group.

Database Management Systems 3ed, R. Ramakrishnan and J. Gehrke 72

 GROUP BY and HAVING

 Example: 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 (!):

For i = 1, 2, ... , 10: SELECT MIN([Link])

FROM Sailors S
WHERE [Link] = i

Database Management Systems 3ed, R. Ramakrishnan and J. Gehrke 73

 GROUP BY and HAVING

 In general, we don’t know how many rating levels exist,

and what the rating values for these levels are.
 Hence, we will use the GROUP BY clause to group the
tuples that have the same rating together, and then apply
the MIN(age) aggregate function per group.
12 Aggregator Group 1
9 Aggregator Group 2 Relation
11 Aggregator Group 3

SELECT [Link], MIN([Link]) AS min_age

FROM Sailors S
GROUP BY [Link]
Database Management Systems 3ed, R. Ramakrishnan and J. Gehrke 74
 GROUP BY and HAVING

SELECT [Link], MIN([Link]) AS min_age

FROM Sailors S
GROUP BY [Link]

“Every” attribute that appears in the Column-List

“must” also appear in the Grouping-List

Database Management Systems 3ed, R. Ramakrishnan and J. Gehrke 75

 Aggregate Functions – Having Clause

 Having is used to represent a condition that applies to

groups rather than to individual tuples.
 Example: Find the age of the youngest sailor for each rating
level in which the average age of sailors is greater than 40.0.
SELECT [Link], MIN([Link]) AS min_age
FROM Sailors S
GROUP BY [Link]
HAVING AVG([Link])>40.0;

 Note that AVG([Link]) applies in this query to each group

of sailors that have the same rating.

Database Management Systems 3ed, R. Ramakrishnan and J. Gehrke 76

 Aggregate Functions – Having Clause

SELECT [Link], MIN([Link]) AS min_age

FROM Sailors S
GROUP BY [Link]
HAVING AVG([Link])>40.0;

 Expressions in the HAVING clause must have a single

value per group.
 Predicates in the HAVING clause are applied after the
formation of groups whereas predicates in the WHERE
clause are applied before forming groups.

Database Management Systems 3ed, R. Ramakrishnan and J. Gehrke 77

 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 can contain (i) attribute names (ii)
expressions 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. (A
group is a set of tuples that have the same value for all
attributes in grouping-list.)
Database Management Systems 3ed, R. Ramakrishnan and J. Gehrke 78
 Conceptual Evaluation

 The cross-product of relation-list is computed.

 Tuples that fail qualification are discarded.
 `Unnecessary’ attributes are deleted (those that do not
appear in the target-list, grouping-list, or group-
qualification).
 The remaining tuples are partitioned into groups by
the value of attributes in the grouping-list.
 The group-qualification is then applied to eliminate the
groups that don’t satisfy the condition in the group-
qualification.
 One answer tuple is generated per qualifying group.

Database Management Systems 3ed, R. Ramakrishnan and J. Gehrke 79

 Conceptual Evaluation
5 SELECT [DISTINCT] target-list
1 FROM relation-list
2 WHERE qualification
3 GROUP BY grouping-list
4 HAVING group-qualification

? Aggregator Group 1
SELECT
Qualifier
? Aggregator selecting Group 2 FROM
groups WHERE
Group 3

Database Management Systems 3ed, R. Ramakrishnan and J. Gehrke 80

 Example with WHERE and HAVING
 Find the age of the youngest sailor with age ≥ 18, for each rating
that contains at least 2 sailors whose age ≥ 18.
sid sname rating age
SELECT [Link], MIN([Link])
22 dustin 7 45.0
FROM Sailors S 31 lubber 8 55.5
WHERE [Link] >= 18 71 zorba 10 16.0
GROUP BY [Link] 64 horatio 7 35.0
HAVING COUNT (*) > 1 29 brutus 1 33.0
 Only [Link] and [Link] are 58 rusty 10 35.0
mentioned in the SELECT, rating age
GROUP BY or HAVING clauses;
1 33.0
other attributes `unnecessary’. 7 45.0 rating
 2nd column of result is 7 35.0 7 35.0
unnamed. (Use AS to name it.) 8 55.5
10 35.0 Answer relation
Database Management Systems 3ed, R. Ramakrishnan and J. Gehrke 81
 Example with WHERE and HAVING

Find the age of the youngest sailor with age ≥ 18, for each
rating that contains at least 2 sailors whose age ≥ 18.

sid sname rating age

SELECT [Link], MIN([Link])
22 dustin 7 45.0
FROM Sailors S
31 lubber 8 55.5
WHERE [Link] >= 18
71 zorba 10 16.0
GROUP BY [Link]
64 horatio 7 35.0
HAVING COUNT (*) > 1 29 brutus 1 33.0
Step 1: Apply Where clause. 58 rusty 10 35.0

Database Management Systems 3ed, R. Ramakrishnan and J. Gehrke 82

 Example with WHERE and HAVING

Find the age of the youngest sailor with age ≥ 18, for each
rating that contains at least 2 sailors whose age ≥ 18.

rating age
SELECT [Link], MIN([Link])
7 45.0
FROM Sailors S
WHERE [Link] >= 18
8 55.5
GROUP BY [Link]
HAVING COUNT (*) > 1 7 35.0
1 33.0
Step 2: keep only columns that 10 35.0
appear in SELECT, GROUP
BY, or HAVING

Database Management Systems 3ed, R. Ramakrishnan and J. Gehrke 83

 Example with WHERE and HAVING
Find the age of the youngest sailor with age ≥ 18, for each
rating that contains at least 2 sailors whose age ≥ 18.
rating age
1 33.0
SELECT [Link], MIN([Link])
FROM Sailors S 7 45.0
WHERE [Link] >= 18 7 35.0
GROUP BY [Link]
HAVING COUNT (*) > 1 8 55.5
Step 3: sort tuples into groups.
10 35.0

Database Management Systems 3ed, R. Ramakrishnan and J. Gehrke 84

 Example with WHERE and HAVING
Find the age of the youngest sailor with age ≥ 18, for each
rating that contains at least 2 sailors whose age ≥ 18.
rating age
SELECT [Link], MIN([Link])
FROM Sailors S
WHERE [Link] >= 18 7 45.0
GROUP BY [Link] 7 35.0
HAVING COUNT (*) > 1
Step 4: apply having clause to
eliminate groups.

Database Management Systems 3ed, R. Ramakrishnan and J. Gehrke 85

 Example with WHERE and HAVING

Find the age of the youngest sailor with age ≥ 18, for each
rating that contains at least 2 sailors whose age ≥ 18.

SELECT [Link], MIN([Link])

FROM Sailors S
WHERE [Link] >= 18 rating
GROUP BY [Link] 7 35.0
HAVING COUNT (*) > 1
Step 5: generate one answer
tuple for each group.

Database Management Systems 3ed, R. Ramakrishnan and J. Gehrke 86

 Example with WHERE and HAVING

Review

SELECT [Link], Step 4:

MIN ([Link]) Output Result for each group

FROM Sailors S Step 1:

WHERE [Link] >= 18 Build Base Table

GROUP BY [Link] Step 2:

Break Table Into Subgroups

HAVING COUNT (*) > 1 Step 3:

Eliminate Subgroups

Database Management Systems 3ed, R. Ramakrishnan and J. Gehrke 87

 Another Example

 For each red boat, find the number of reservations for

this boat
SELECT [Link], COUNT (*) AS res_count
FROM Boats B, Reserves R
WHERE [Link]=[Link] AND [Link]=‘red’
GROUP BY [Link]

 Can we instead remove [Link]=‘red’ from the

WHERE clause and add a HAVING clause with this
condition?

Database Management Systems 3ed, R. Ramakrishnan and J. Gehrke 88

 Null Values and Aggregates

 Sum of all salaries:

SELECT SUM (salary)
FROM Instructor
 The above statement ignores NULL amounts.
 The result is NULL if there is no non-NULL amount.
 All aggregate operations except COUNT(*) ignore
tuples with NULL values on the aggregated attributes.
 What if the tuple set has only NULL values?
 COUNT returns 0.
 All other aggregates return NULL.

Database Management Systems 3ed, R. Ramakrishnan and J. Gehrke 89

 Summary
 SQL was an important factor in the early acceptance
of the relational model; more natural than earlier,
procedural query languages.
 SQL queries can belong to one of four types: DDL,
DML, DCL, or DQL.
 DDL is concerned with database design.
 DML is related to adding, deleting, or modifying
data in the database tables.
 DCL provides means for data access control.
 DQL focuses on queries that retrieve specific data.

Database Management Systems 3ed, R. Ramakrishnan and J. Gehrke 90