Introductory SQL, Part 2

Create Databases and Select Data

Chapter 3 in the Text Book

©Anne Haxthausen and Flemming Schmidt

These slides have been prepared by Anne Haxthausen, partly reusing/modifying slides by Flemming Schmidt, which
again partly reused some slides by Silberschatz, Korth. Sudarshan, 2010.

3. Introductory SQL 02170 Database Systems 1

 Contents
 University Database (running example)
 SQL Data, more details:
• String Operations (book sec. 3.4.2)
• NULL Values (book sec. 3.6)
 Advanced SQL Queries Using SELECT
• Aggregate Functions + GROUP BY … HAVING … (book sec. 3.7)
• Subqueries (book sec. 3.8, also used in 3.9)
• Scalar subqueries (book sec. 3.8.7)
• Conditions using IN, NOT IN (book sec. 3.8.1)
• Conditions using op ALL, op SOME (book sec. 3.8.2)
• Conditions using EXISTS, NOT EXISTS (book sec. 3.8.3)

 Composite SQL Queries Using UNION, INTERSECT and EXCEPT (book sec. 3.5)
 Demo Exercises & Exercises
3. Introductory SQL Contents 2
 University Database, revisited
 Database example used throughout the textbook and in this course!

1. Database Schema Diagram

2. Database Instance

3. Introductory SQL University Database 3

 Database Schema Diagram from the book, but with new names

3. Introductory SQL University Database 4

 Database Instance (1 of 4)
Instructor Student Advisor

Department

3. Introductory SQL University Database 5

 Database Instance (2 of 4)
Teaches Takes

3. Introductory SQL University Database 6

 Database Instance (3 of 4)
Course PreReq

3. Introductory SQL University Database 7

 Database Instance (4 of 4)
Section TimeSlot

Classroom

Note: When writing and testing SQL queries it

is useful to have a print of the Diagram and the
Database Instance.
3. Introductory SQL University Database 8
 SQL Data Queries
 Basic Query Structure
• SQL Data Query Language provides the ability to query data
• The result of a SQL Query is a table

 A typical basic SQL query has the form:

SELECT A1, A2, ..., An
FROM r1, r2, ..., rm
WHERE P;
• Ai represents an attribute
• ri represents a relation (can e.g. be an id or a [natural] join expression)
• P is a (row) predicate over attribute names. For each row it is either true or false.

3. Introductory SQL Data Queries 9

 String Operations (section 3.4.2)

 Strings are sequences of characters enclosed with quotes

• E.g. 'Anne'
• How to include ' and \ in strings: 'Anne\'s' and 'A\\B'
 SQL supports a variety of string operations such as
• =, <>, <, >: are case sensitive according to the SQL standard, but not in
MySQL and MariaDB, where e.g. 'anne' = 'Anne' evaluates to 1 (true)
• Concatenation, using CONCAT
• Finding string length, extracting substrings, etc, see the DBMS manual.
• Pattern matching, using LIKE

 Examples
SET @TeachingHistory = CONCAT('CS-101', ', ' , 'CS-315' , ', ' , 'CS-347'); # @ defines a variable
SELECT @TeachingHistory;

3. Introductory SQL String Operations 10

 String Operations: Pattern Matching
 LIKE is a string-matching operator for comparisons of character strings:
• Syntax: string-expr LIKE string-pattern
• Can be used where a Boolean expression is expected, e.g. in a WHERE
clause.
• Returns 1 (true) if string-expr matches string-pattern, otherwise 0 (false).
• The string-pattern can use two special characters:
• The % character matches any substring (of 0 - n characters).
• The _ character matches any (single) character.
 Pattern matching examples
• 'Anne' matches 'Anne' , but not 'Hanne'
• 'Intro%' matches any string beginning with “Intro”, e.g. 'Introduction'
• '%duc%' matches any string containing “duc” as a substring”, e.g.
'Introduction'
• '_ _ _' matches any string of exactly three characters, e.g. 'Ann'
• '_ _ _ %' matches any string of at least three characters, e.g. 'Hanne'

3. Introductory SQL String Operations 11

 String Operations: Pattern Matching
 Patterns are case sensitive according to the SQL standard,
but not in MySQL and MariaDB where e.g.
• 'anne' LIKE 'Anne'
will evaluate to 1.
 Query Example using pattern matching: Find the names of all
instructors whose name includes the substring “ri”.
SELECT InstName FROM Instructor
WHERE InstName LIKE '%ri%';

3. Introductory SQL String Operations 12

 NULL Values in Expressions (section 3.6)
 A row can have a NULL value for some attributes.
• NULL signifies an unknown value or that a value does not exist.

 Arithmetic Expressions containing NULL return NULL.

• Example: 5 + NULL returns NULL

 The predicate IS NULL can be used to check for Null.

• Example: Find all instructors whose salary is unknown.
SELECT InstName FROM Instructor WHERE Salary IS NULL;
 The predicate IS NOT NULL can be used to check for not Null

3. Introductory SQL NULL 13

 NULL and Three Valued Logic
 Comparison Expressions containing NULL return UNKNOWN
• Examples:
5 < NULL, NULL <> NULL, and NULL = NULL all evaluate to UNKNOWN
 Three-valued (conditional) logic using the truth value UNKNOWN
OR FALSE TRUE UNKNOWN AND FALSE TRUE UNKNOWN
FALSE FALSE TRUE UNKNOWN FALSE FALSE FALSE FALSE
TRUE TRUE TRUE TRUE TRUE FALSE TRUE UNKNOWN
UNKNOWN UNKNOWN TRUE UNKNOWN UNKNOWN FALSE UNKNOWN UNKNOWN

NOT FALSE TRUE UNKNOWN

Result TRUE FALSE UNKNOWN

 A WHERE or HAVING clause predicate is treated as FALSE, if it evaluates to

UNKNOWN
 The predicate IS [NOT] UNKNOWN:
• “P IS UNKNOWN” evaluates to TRUE, if predicate P evaluates to
UNKNOWN

3. Introductory SQL NULL 14

 Treatment of NULL By SELECT DISTINCT
 SELECT DISTINCT … FROM … removes duplicate rows.
• When comparing two tuples, it treats NULL as being equal to NULL (although
NULL = NULL returns UNKNOWN).
• Example 1: If there are two rows (1, NULL) and (1, NULL), one of them are
removed.
• Example 2: If there are two rows (1, 2) and (1, NULL), both are kept.

3. Introductory SQL NULL 15

 Aggregate Functions (section 3.7)
 These functions operate on the set of values in a column of a
given attribute A and return a value:
AVG(A): Average of values in the A-column
MIN(A): Minimum of values in the A-column
MAX(A): Maximum of values in the A-column
SUM(A): Sum of values in the A-column
COUNT(A): Number of values in the A-column
 They can be used in SELECT clauses and HAVING clauses.
 Example:
SELECT AVG(Salary), MIN(Salary), Max(Salary), SUM(Salary), COUNT(Salary)
FROM Instructor;

 COUNT(*): counts the number of rows in a relation/table.

 Example: SELECT COUNT(*) FROM Course; COUNT(*)
13
3. Introductory SQL Aggregate Functions 16
 NULL Values and Aggregates
 Find the total of all instructor salaries
SELECT SUM(Salary) FROM Instructor;
• This statement ignores NULL amounts and sums the rest.
• Result is NULL, only if ALL salaries are NULL.

 AVG(A), MIN(A), MAX(A), SUM(A), and COUNT(A) ignore

rows where A is NULL.
 What if an A column only has NULL values or is empty?
• COUNT(A) returns 0,
• MIN(A), MAX(A), SUM(A), and AVG(A) return NULL
 COUNT(*) does not ignore rows with NULL values.

3. Introductory SQL Aggregate Functions 17

 Duplicates and Aggregates
 Aggregate functions take duplicate values of the aggregate
attribute into account.
 Example: Find the average salary of instructors in the
Computer Science department
SELECT AVG(Salary) FROM Instructor
WHERE DeptName='Comp. Sci.';

 To ignore duplicate values, use the DISTINCT keyword.

 Example: Find the total number of instructors who teach a
course in the Spring 2010 semester
SELECT COUNT(DISTINCT InstID) FROM Teaches
WHERE Semester='Spring' AND StudyYear=2010;

3. Introductory SQL Aggregate Functions 18

 GROUP BY
 Find the name and average instructor salary for each
department:
SELECT * FROM Instructor ORDER BY DeptName;
SELECT DeptName, AVG(Salary)
FROM Instructor
GROUP BY DeptName;

3. Introductory SQL Aggregate Functions 19

 Aggregation with GROUP BY
 Study the table Testscores and possible Aggregates
SELECT * FROM Testscores;

SELECT Student,
COUNT(Score) AS n,
SUM(Score) AS Total,
AVG(Score) AS Average,
MIN(Score) AS Lowest,
MAX(Score) AS Highest
FROM Testscores GROUP BY Student;

SELECT COUNT(*) FROM Testscores;

3. Introductory SQL Aggregate Functions 20

 HAVING
 Find the name and average salary for those departments
whose average salary is greater than 65000.
SELECT DeptName, AVG(Salary) FROM Instructor
GROUP BY DeptName HAVING AVG(Salary) > 65000;

• Note: Predicates in the HAVING clause are applied after the formation
of groups, whereas predicates in the WHERE clause are applied before
forming groups.
So, WHERE is working on each Row and HAVING on each Group result.

3. Introductory SQL Aggregate Functions 21

 GROUP BY
 Typical form
SELECT attribute-list1, aggregations
FROM … WHERE P1
GROUP BY attribute-list2 HAVING P2

• Attributes in attribute-list1 must be present in attribute-list2.

• Usually attribute-list1 = attribute-list2.
• In P2 there can be aggregations (like in the SELECT clause).
Attributes that appear in P2 without being aggregated over,
must be present in attribute-list2.

Introductory SQL Aggregate Functions 22

 More General SQL Queries
 A more general form of SELECT queries:
SELECT attributes
FROM r1, r2, ..., rm [WHERE P1]
[GROUP BY group-spec [HAVING P2]]
[ORDER BY order-spec];

 Meaning:
1. Calculate the relation r represented by r1, r2, ..., rm and remove
rows from r not satisfying P1.
2. Arrange the selected rows into groups having the same values
for group-spec and remove groups not satisfying P2.
3. For each group calculate the attributes: this gives one
tuple/row for each group.
4. Order the rows according to the order-spec.

2. Introductory SQL 23
 Subqueries (section 3.8)
 What:
• A SELECT statement is said to be a subquery, if it occurs nested
inside another statement.
 Where:
• A SELECT statement can be used to represent a relation in the
WHERE, FROM and HAVING clauses of another (outer) SELECT
statement.
 Examples:
• Will be shown on the following slides.
 Purpose:
• They provide alternative ways to perform operations that would
otherwise require complex joins and unions.

3. Introductory SQL Subqueries 24

 Scalar Subqueries (section 3.8.7)
 If a subquery returns a 1x1 relation then it is said to be a scalar subquery.
 A scalar subquery can be used where a single value is expected (in the
SELECT, WHERE, FROM and HAVING clauses). Example:
• SELECT InstName FROM Instructor
WHERE Salary > (SELECT AVG(Salary) FROM Instructor);
• For an instance, this corresponds to:
AVG(Salary)
… FROM WHERE Salary > '74833.333333'

InstName
Wu
Einstein
Gold
Katz

• Result table: ------------------------------> Singh

Brandt
Kim
3. Introductory SQL Subqueries 25
 Set Membership Conditions: IN and NOT IN (section 3.8.1)
 IN and NOT IN can be used in a WHERE/HAVING clause to form a
condition.
 Typical forms:
• SELECT…, Ai,… FROM …
WHERE Ai [NOT] IN (value1, value2, ...);
• SELECT…, Ai,… FROM …
WHERE Ai [NOT] IN (SELECT Bj FROM … WHERE …); #often Ai = Bj
 Example: select the names of instructors whose name is neither Mozart,
Einstein:
• SELECT DISTINCT InstName FROM Instructor
WHERE InstName NOT IN ('Mozart', 'Einstein') ;
 Example: Find courses offered both in Fall 2009 and in Spring 2010
SELECT DISTINCT CourseID FROM Section
WHERE
Semester='Fall' AND StudyYear=2009 AND
CourseID IN
(SELECT CourseID FROM Section WHERE Semester='Spring' AND StudyYear=2010);

3. Introductory SQL 26
 Conditions Using ALL and SOME (section 3.8.2)

 Can be used in a WHERE/HAVING clause to form a

condition.
 Typical forms:
• A op ALL (SELECT A FROM … WHERE …);
• A op SOME (SELECT A FROM … WHERE …);
where op can be:     
 It checks whether A op v is true for all/some of the values v in
the column specified by the SELECT.

3. Introductory SQL 27
 SOME
 Find names of instructors with salary greater than that of
some (at least one) instructor in the Finance department.
SELECT InstName FROM Instructor
WHERE Salary > SOME (SELECT Salary FROM Instructor
WHERE DeptName = 'Finance');
 An instance of this corresponds to: Salary
SELECT InstName FROM WHERE Salary > SOME 90000
80000

InstName
Wu
 Which gives: Einstein
Gold
Brandt

3. Introductory SQL 28
 SOME conditions
 A op SOME r v  r : (A op v )
Where r is a relation with one attribute and
op can be:     

0
(5 < SOME 5 ) = true
6
0
(5 < SOME 5 ) = false

0
(5 = SOME 5 ) = true

0
(5  SOME 5 ) = true (since 0  5)
(= SOME)  IN
However, ( SOME)  NOT IN

3. Introductory SQL 29
 ALL conditions
 A op ALL r v  r : (A op v)

0
(5 < ALL 5 ) = false
6
6
(5 < ALL 10 ) = true

4
(5 = ALL 5 ) = false

4
(5  ALL 6 ) = true

( ALL)  NOT IN
But: (= ALL)  IN

3. Introductory SQL 30
Conditions Using EXISTS and NOT EXISTS (section 3.8.3)

 Can be used in a WHERE/HAVING clause to form a condition:

• [NOT] EXISTS (SELECT … FROM … WHERE …);
 It checks whether the relation is [not] non-empty
 Example: Find all courses taught in both the Fall 2009
semester and in the Spring 2010 semester.
• SELECT CourseID FROM Section AS S
WHERE Semester='Fall' AND StudyYear=2009 AND
EXISTS (SELECT *
FROM Section AS T
WHERE Semester = ’Spring’ AND StudyYear= 2010
AND S.CourseId = T.CourseId);

3. Introductory SQL 31
 Compound Queries Using UNION, INTERSECT & EXCEPT (section 3.5)
 Set Operations UNION, INTERSECT & EXCEPT have
• Arguments: two SELECT statements (denoting two relations R and S).
• R and S must be compatible: Number and types of the attributes of R and S must be the same.
• Result: is the relation consisting of the union/intersection and set difference of the
tuples in R and S.
• UNION and INTERSECT removes duplicates in the result.
• EXCEPT removes duplicates in its arguments before the operation is done.

 Applications of set operations constitute a query (like SELECT statements do).

 They can’t appear inside a SELECT statement.
 Set INTERSECT and EXCEPT are implemented in MariaDB, but not in MYSQL,
where they instead be expressed by nested subqueries.
3. Introductory SQL Set Operations 32
 UNION, INTERSECT and EXCEPT

 Find courses offered in Fall 2009 or in Spring 2010

(SELECT CourseID FROM Section WHERE Semester='Fall' AND StudyYear=2009)
UNION
(SELECT CourseID FROM Section WHERE Semester='Spring' AND StudyYear = 2010);
R S R UNION S R INTERSECT S R EXCEPT S
CS-101 CS-101 CS-101
CS-347 FIN-201 CS-347
PHY-101 MU-199 PHY-101
HIS-351 FIN-201
CS-319 MU-199
CS-319 HIS-351
CS-315 CS-319
CS-315

3. Introductory SQL Set Operations 33

 INTERSECT and EXCEPT
 Find courses offered both in Fall 2009 and in Spring 2010
(SELECT CourseID FROM Section WHERE Semester='Fall' AND StudyYear=2009)
INTERSECT
(SELECT CourseID FROM Section WHERE Semester='Spring' AND StudyYear = 2010);
can be expressed by:
SELECT DISTINCT CourseID FROM Section
WHERE Semester='Fall' AND StudyYear=2009 AND CourseID
IN (SELECT CourseID FROM Section WHERE Semester='Spring' AND StudyYear=2010);

 Find courses offered in Fall 2009 but not in Spring 2010

(SELECT CourseID FROM Section WHERE Semester='Fall' AND StudyYear=2009)
EXCEPT
(SELECT CourseID FROM Section WHERE Semester='Spring' AND StudyYear = 2010);
can be expressed by:
SELECT DISTINCT CourseID FROM Section
WHERE Semester='Fall' AND StudyYear=2009 AND CourseID
NOT IN (SELECT CourseID FROM Section WHERE Semester='Spring' AND StudyYear=2010);

3. Introductory SQL Set Operations 34

 Summary
• Data Queries:
• SELECT ... FROM … WHERE … GROUP BY … HAVING … ORDER BY …
• … UNION …,
• … INTERSECT …,
• … EXCEPT …

Readings
• In Database Systems Concepts please complete reading Chapter 3
• Pay special attention to the Summary

3. Introductory SQL 02170 Database Systems - Lecture 3 35

 Demo Exercises

Demo Exercises clarify ideas

and concepts from the Lecture
to provide you with good
Database Skills.

Do the Demo Exercises.

Solutions can be found on a
later slide.

3. Introductory SQL Demo Exercises 36

 Advanced SQL Queries and Data Manipulation
The following SQL queries should be run
against the University database. 3.1.3 DELETE with NOT IN and nested SELECT
Delete all courses that have never been
3.1.1 SELECT with COUNT aggregation and offered (that is, do not occur in the Section
GROUP BY table).
Find the number of enrolments of each course
section that was offered in Fall 2009.

3.1.2 SELECT with SUM aggregation and

multiple JOINs and GROUP BY
What is the course title and sum of course
credits of the courses taught by instructor
Brandt?

3. Introductory SQL Demo Exercises 37

 Solutions to Demo Exercises

3. Introductory SQL Demo Exercises 38

 Advanced SQL Queries and Data Manipulation
The following SQL queries should be run against
the University database. 3.1.3 DELETE with NOT IN and nested SELECT
Delete all courses that have never been offered
3.1.1 SELECT with COUNT aggregation and (that is, do not occur in the Section table).
GROUP BY
Find the number of enrolments of each course DELETE FROM Course
WHERE CourseID NOT IN
section that was offered in Fall 2009. (Select CourseID FROM Section);
SELECT CourseID, SectionID, Count(StudID)
FROM Takes PS. Run the UniversityDB.sql script again to
WHERE Semester = 'Fall' AND StudyYear = 2009 restore tables to the original instance.
GROUP BY CourseID, SectionID;

3.1.2 SELECT with SUM aggregation and multiple

JOINs and GROUP BY
What is the course title and sum of course credits
of the courses taught by instructor Brandt?

SELECT Title, SUM(Credits)

FROM Instructor
NATURAL JOIN Teaches Title SUM(Credits)
NATURAL JOIN Course Game Design 8
WHERE InstName = 'Brandt'
Image
GROUP BY Title; Processing
3

3. Introductory SQL Demo Exercises 39

 Exercises

Please answer all exercises

to demonstrate your
Database Skills.

MySQL Exercises
Solutions are available at 11:45

3. Introductory SQL Exercises 40

 Advanced SQL Queries and Data Manipulation
Exercises 3.2.4 Advanced WHERE condition using NOT
SQL queries should be run against the IN
University database. Find those students who have not taken a
Re-run the UniversityDB Script to restore course.
tables to initial instances.
Have a print of the University Database 3.2.5 Advanced WHERE condition using ALL
Schema Diagram and the Database Instance Find the name(s) of those department(s)
readily available. which have the highest budget, i.e. a budget
which is higher than or equal to those for all
3.2.1 SELECT with MAX aggregation other departments.
Find the highest salary of any instructor
3.2.6 Advanced WHERE condition using
3.2.2 Scalar Subquery SOME
Find all instructors earning the highest salary. Find the names of those students who have
There might be more than one with the same the same name as some instructor. Use the
salary. SOME operator for this.
Make another statement querying the same,
3.2.3 DELETE using IN but without using SOME.
Delete courses BIO-101 and BIO-301 in the
Takes table.
After this question run the UniversityDB Script
to restore tables to initial instances.
3. Introductory SQL Exercises 41
 Create Table & Advanced SQL Queries
3.2.7 Create and populate a table The Grade-Points earned by a student for a
GradePoints(Grade, Points) to provide a course offering (section) is the number of
conversion from letter grades to numeric credits for the course multiplied by the points
scores such that for the grade that the student received.
SELECT * FROM GradePoints; The Total Grade-Points earned by a student is
gives: the sum of grade points for all courses taken
by the students.

3.2.8 Find the Total Grade-Points

earned by each student who has taken a
course. Hint: use GROUP BY.

This shows that an “A” corresponds to 4 3.2.9 Find the Total Grade-Points Average
points, an “A-“ to 3.7 points, and so on. (GPA)
for each student who has taken a course, that
is, the total grade-points divided by the total
credits for the associated courses. Order the
students by falling averages.
Hint: use GROUP BY.

3. Introductory SQL Demo Exercises 42

 Create Table & Advanced SQL Queries
3.2.10 Query using UNION 3.2.11 Testscores Example
Now modify the queries from the two Create a relation schema Testscores (by a table
previous questions such that students who declaration) and insert values such that
have not taken a course are also included in SELECT * FROM Testscores;
the result. gives:
Hint: use the UNION operator.

Then find the maximal score for each student

who has an average larger than 49.

Then find those students for whom some

score is unknown.

3. Introductory SQL Demo Exercises 43