Lecture-4: SQL-II

Course CSB2204: Database Systems

Assoc. Prof. Mohammed Gadelrab

School of Artificial Intelligence & Data Management
Badr University in Assiut (BUA)
Outline

The rename Operation

String Operations

Set Operations, Membership & Comparison

NULL Values

Joined Relations

Spring 2025 BUA-AI & DM School 2

 The Reanme Operation

The SQL allows renaming relations and attributes using the AS clause:
old-name AS new-name

Find the names of all instructors who have a higher salary than some instructor in 'Comp. Sci'.
SELECT DISTINCT T.name
FROM instructor AS T, instructor AS S
WHERE T.salary > S.salary AND S.dept_name = 'Comp. Sci.’

Keyword AS is optional and may be omitted
instructor AS T ≡ instructor T

Spring 2025 BUA-AI & DM School 3

 String Operations

SQL includes a string-matching operator for comparisons on character strings. The operator LIKE uses patterns that are described using two special characters:
 percent ( % ). The % character matches any substring of 0 or more length.
 underscore ( _ ). The _ character matches any single character.
 Patterns are case sensitive.

Find the names of all instructors whose name includes the substring “dar”.
SELECT name
FROM instructor
WHERE name LIKE '%dar%'

This matches any string that contains “dar” substring in the beginning, the middle or the end.

Match a string that contain the % character such as: “100%”, we use the escape character:
LIKE '100 \%'

 In the above example, we use backslash (\) as the escape character.


Pattern matching examples:
 'Intro%' matches any string beginning with “Intro”, such as: Introduction, Intrusive, Intro, but will not match intro, Cintro.
 '%Comp%' matches any string containing “Comp” as a substring, such as: Computer, Computation, Compare, KinaComp but will not match Camp, comp.
 '_ _ _' matches any string of exactly three characters.
 '_ _ _ %' matches any string of at least three characters.

SQL supports a variety of string operations such as

 concatenation (using “||”)

 converting from upper to lower case (and vice versa)

 finding string length, extracting substrings, etc.

Spring 2025 BUA-AI & DM School 4

 Set Operations

Set operations UNION, INTERSECT, and EXCEPT
 Each of the above operations automatically eliminates duplicates

To retain all duplicates use the
 UNION ALL,
 INTERSECT ALL
 EXCEPT ALL

Examples:
 Find courses that ran in Fall 2017 or in Spring 2018
(SELECT course_id FROM section WHERE sem = 'Fall' AND year = 2017)
UNION
(SELECT course_id FROM section WHERE sem = 'Spring' AND year = 2018)
 Find courses that ran in Fall 2017 and in Spring 2018
(SELECT course_id FROM section WHERE sem = 'Fall' AND year = 2017)
INTERSECT
(SELECT course_id FROM section WHERE sem = 'Spring' AND year = 2018)
 Find courses that ran in Fall 2017 but not in Spring 2018
(SELECT course_id FROM section WHERE sem = 'Fall' AND year = 2017)
EXCEPT
(SELECT course_id FROM section WHERE sem = 'Spring' AND year = 2018)
Spring 2025 BUA-AI & DM School 5
 Set Membership

Find courses offered in Fall 2017 and in Spring 2018
SELECT DISTINCT course_id
FROM section
WHERE semester = 'Fall' AND year= 2017 AND
course_id IN (SELECT course_id
FROM section
WHERE semester = 'Spring' AND year= 2018);

Find courses offered in Fall 2017 but not in Spring 2018
SELECT DISTINCT course_id
FROM section
WHERE semester = 'Fall' AND year= 2017 AND
course_id NOT IN (SELECT course_id
FROM section
WHERE semester = 'Spring' AND year= 2018);

Spring 2025 BUA-AI & DM School 6

 Set Membership: IN & NOT IN

Name all instructors whose name is neither “Mozart” nor Einstein”
SELECT DISTINCT name
FROM instructor
WHERE name NOT IN ('Mozart', 'Einstein')

Find the total number of (distinct) students who have taken course sections taught by the
instructor with ID 10101
SELECT count (DISTINCT ID)
FROM takes
WHERE (course_id, sec_id, semester, year) IN
(SELECT course_id, sec_id, semester, year
FROM teaches
WHERE teaches.ID= 10101);
Spring 2025 BUA-AI & DM School 7
 Set Membership: EXISTS & NOT EXISTS

Check whether the result of a correlated nested query is empty or not. They are Boolean functions that return a
TRUE or FALSE resul.

Yet another way of specifying the query “Find all courses taught in both the Fall 2017 semester and in the Spring
2018 semester”
SELECT course_id
FROM section AS S
WHERE semester = 'Fall' AND year = 2017 AND
EXISTS (SELECT *
FROM section AS T
WHERE semester = 'Spring' AND year= 2018
AND S.course_id = T.course_id);

Find all students who have taken all courses offered in the Biology department:
SELECT distinct S.ID, S.name
FROM student AS S
WHERE NOT EXISTS ( (SELECT course_id
FROM course
WHERE dept_name = 'Biology')
EXCEPT
(SELECT T.course_id
FROM takes AS T
WHERE S.ID = T.ID));

First nested query lists all courses offered in Biology.

Second nested query lists all courses a particular student took.

Spring 2025 BUA-AI & DM School 8

 Set Comparison: SOME & ALL

Find names of instructors with salary greater than that of some (at least one) instructor in the Biology department.
SELECT distinct T.name
FROM instructor AS T, instructor AS S
WHERE T.salary > S.salary AND S.dept name = 'Biology';
 Same query using > SOME clause
SELECT name
FROM instructor
WHERE salary > SOME (SELECT salary
FROM instructor
WHERE dept name = 'Biology');

Find the names of all instructors whose salary is greater than the salary of all instructors in the Biology department.
SELECT name
FROM instructor
WHERE salary > ALL (SELECT salary
FROM instructor
WHERE dept name = 'Biology');

Spring 2025 BUA-AI & DM School 9

 Equivalent Queries

Different queries that provide same results.

ATTENTION: Equivalent queries may have different performance

Examples:

Find courses offered in both Fall 2017 and in Spring 2018 
You can formulate equivalent queries
1) (SELECT course_id FROM section WHERE sem = 'Fall' AND year = 2017)
INTERSECT
in several ways. For example: each
(SELECT course_id FROM section WHERE sem = 'Spring' AND year = 2018) group of the following can be used to
2) SELECT course_id provide same results:
FROM section AS S
WHERE semester = 'Fall' AND year = 2017 AND
EXISTS (SELECT *
 INTERSECTS, AND EXISTS, AND IN
FROM section AS T
WHERE semester = 'Spring' AND year= 2018  UNION, OR EXISTS, OR IN
AND S.course_id = T.course_id);
 EXCEPT, AND NOT EXISTS, AND

Find courses offered in Fall 2017 but not in Spring 2018
NOT IN
1) (SELECT course_id FROM section WHERE sem = 'Fall' AND year = 2017)
EXCEPT
(SELECT course_id FROM section WHERE sem = 'Spring' AND year = 2018)
2) SELECT DISTINCT course_id
FROM section
WHERE semester = 'Fall' AND year= 2017 AND
course_id NOT IN (SELECT course_id
FROM section
WHERE semester = 'Spring' AND year= 2018);

Spring 2025 BUA-AI & DM School 10

 NULL Values

It is possible for tuples to have a null value, denoted by NULL, 
The predicate in a WHERE clause can involve
for some of their attributes. Boolean operations (AND, OR, NOT); thus the

NULL signifies an unknown value or that a value does not definitions of the Boolean operations need to be
exist.
extended to deal with the value UNKNOWN.

The result of any arithmetic expression involving NULL is
NULL  AND:
 Example: 5 + NULL returns NULL  (true AND unknown) = unknown,

SQL treats as unknown the result of any comparison involving (false AND unknown) = false,
a null value (other than predicates is null and is not null).
(unknown AND unknown) = unknown
 Example: 5 < null or null <> null or null = null
 OR:

The predicate IS NULL can be used to check for null values.

Example: Find all instructors whose salary is null.  (unknown OR true) = true,
SELECT name (unknown OR false) = unknown
(unknown OR unknown) = unknown
FROM instructor

Results of WHERE clause predicate is treated as
WHERE salary IS NULL
false if it evaluates to unknown

The predicate IS NOT NULL succeeds if the value on which it
is applied is not null.

Spring 2025 BUA-AI & DM School 11

 Joined Relations

Join operations take two relations and return as a result another relation.

A join operation is a Cartesian product which requires that tuples in the two relations match
(under some condition). It also specifies the attributes that are present in the result of the join.

The join operations are typically used as subquery expressions in the FROM clause

Various types of join:
 NATURAL JOIN
 INNER JOIN
 Various types of OUTER JOIN (LEFT, RIGHT, FULL)

NATURAL JOIN on two relations R and S
 No join condition specified.
 Is equivalent to an implicit EQUIJOIN condition for each pair of attributes with same name from R and
S.

Spring 2025 BUA-AI & DM School 12

 Natural JOIN

Natural join matches tuples with the same values for all common attributes, and retains only
one copy of each common column.

The FROM clause can have multiple relations combined using natural join:
SELECT A1, A2, … An
FROM r1 NATURAL JOIN r2 NATURAL JOIN .. NATURAL JOIN rn
WHERE P ;

Example: List the names of students along with the course ID of the courses that they
registered:
SELECT name, course_id
FROM students, takes
WHERE student.ID = takes.ID;
 Same (equivalent) query in SQL with “natural join” construct
SELECT name, course_id
FROM student NATURAL JOIN takes;

Spring 2025 BUA-AI & DM School 13

Example: Student & Takes

Spring 2025 BUA-AI & DM School 14

Example: Natural Join Result

Spring 2025 BUA-AI & DM School 15

 NATURAL JOIN with USING Clause

Beware of unrelated attributes with same name which get equated incorrectly

To avoid the danger of equating attributes erroneously, we can use the “USING” keyword that
allows us to specify exactly which columns should be equated.

Query example
 SELECT name, title
FROM (student NATURAL JOIN takes) JOIN course USING (course_id);

Spring 2025 BUA-AI & DM School 16

 Self Join

Imagine a relation emp-super

Find the supervisor of “Bob”
SELECT E.supervisor

FROM emp-super AS E

WHERE E.person=”Bob”;

Find the supervisor of the supervisor of “Bob”
SELECT E2.supervisor

FROM emp-super AS E1, emp-super AS E2

WHERE E1.person=”Bob” AND E1.supervisor= E2.person;


Can you find ALL the supervisors (direct and indirect) of “Bob” using JOIN? (Homework)

Spring 2025 BUA-AI & DM School 17

 ???

BUA-AI & DM School 18