Fundamentals of Database

Systems
(6th Edition)

2018-2019 Academic Year

Department of Computer Engineering and Information Technology
 The Relational Data Model
and SQL
Chapter-5: More SQL: Complex Queries, Triggers,
Views, and Schema Modification

Lectured by
Daw Honey Htun
Assistant Lecturer (Ph.D, Thesis)
Department of Computer Engineering and Information Technology
Comparison Operators
 '=' (equal to),
 '<>' or '!=' (not equal to),
 '>' (greater than),
 '<' (less than),
 '>=' (greater than or equal to),
 '<=' (less than or equal to), to compare two numbers.

mysql> SELECT name, price FROM products WHERE price < 1.0;
mysql> SELECT name, quantity FROM products WHERE quantity <=
2000;
mysql> SELECT name, price FROM products WHERE productCode =
'PEN';
String Pattern Matching- Like or Not Like
 Can perform pattern matching using operator LIKE (or NOT LIKE)
with wildcard characters.
 The wildcard '_' matches any single character; '%' matches any
number of characters (including zero).
 For example,
 'abc%' matches strings beginning with 'abc';
 '%xyz' matches strings ending with 'xyz';
 '%aaa%' matches strings containing 'aaa';
 '___' matches strings containing exactly three characters; and
 'a_b%' matches strings beginning with 'a', followed by any single
character, followed by 'b', followed by zero or more characters.
-- "name" begins with 'PENCIL'
mysql> SELECT name, price FROM products WHERE name LIKE
'PENCIL%';
-- "name" begins with 'P', followed by any two characters,
-- followed by space, followed by zero or more characters
mysql> SELECT name, price FROM products WHERE name LIKE
'P__ %';
Arithmetic & Logical Operators
 Perform arithmetic operations on numeric fields using arithmetic
operators
 + Addition
 - Subtraction
 * Multiplication
 / Division
 DIV Integer Division
 % Modulus (Remainder)

 Can combine multiple conditions with Boolean operators AND,

OR, XOR.
 Can also invert a condition using operator NOT.
 In, Not In
 Can select from members of a set with IN (or NOT IN) operator.
Between, Not Between
 To check if the value is within a range, use BETWEEN ... AND ... operator.
 Order By Clause
 Can order the rows selected using ORDER BY clause, with the following syntax:

SELECT ...
FROM tableName
WHERE criteria
ORDER BY columnA ASC|DESC, columnB ASC|DESC,
...
 Limit
 A SELECT query on a large database may produce many rows.
 Could use the LIMIT clause to limit the number of rows displayed.
 Skip
 could specify the number of rows to be skipped, followed by the
number of rows to be displayed in the LIMIT clause,

 -- Skip the first two rows and display the next 1 row
mysql> SELECT * FROM products ORDER BY price LIMIT 2, 1;
 Alias- AS
 Could use the keyword AS to define an alias for an identifier (such as
column name, table name).
 The alias will be used in displaying the name.
 It can also be used as reference.
 For example,

mysql> SELECT productID AS ID, productCode AS Code,

name AS Description, price AS `Unit Price`
FROM products
ORDER BY ID;
 Function CONCAT
 can also concatenate a few columns as one (e.g., joining the last name and first
name) using function CONCAT().
mysql> SELECT CONCAT(productCode, ' - ', name) AS `Product
Description`, price FROM products;
USE OF DISTINCT
 SQL does not treat a relation as a set; duplicate tuples can appear
 To eliminate duplicate tuples in a query result, the keyword
DISTINCT is used
 For example, the result of Q11 may have duplicate SALARY
values whereas Q11A does not have any duplicate values

 Q11: SELECT SALARY

FROM EMPLOYEE;

Q11A: SELECT DISTINCT SALARY
FROM EMPLOYEE;
Slide 8-17
NESTING OF QUERIES
 A complete SELECT query, called a nested query , can be specified
within the WHERE-clause of another query, called the outer query

 Query 1: Retrieve the name and address of all employees who work
for the 'Research' department.

Q1: SELECT FNAME, LNAME, ADDRESS

FROM EMPLOYEE
WHERE DNO IN (SELECT DNUMBER
FROM DEPARTMENT
WHERE DNAME='Research' );

Slide 8-18
NESTING OF QUERIES (cont.)

 The nested query selects the number of the 'Research' department.

 The outer query select an EMPLOYEE tuple if its DNO value is in the
result of either nested query.

 The comparison operator IN compares a value v with a set (or multi-

set) of values V, and evaluates to TRUE if v is one of the elements in
V.

Slide 8-19
CORRELATED NESTED QUERIES
 If a condition in the WHERE-clause of a nested query references
an attribute of a relation declared in the outer query , the two
queries are said to be correlated.
 Query 12: Retrieve the name of each employee who has a
dependent with the same first name as the employee.

Q12: SELECT E.FNAME, E.LNAME

FROM EMPLOYEE AS E
WHERE E.SSN IN ( SELECT ESSN
FROM DEPENDENT
WHERE ESSN=E.SSN
AND E.FNAME =
DEPENDENT_NAME);
Slide 8-20
(cont.)

 Q12 may be written as in Q12A.

Q12 A: SELECT E.FNAME, E.LNAME

FROM EMPLOYEE E, DEPENDENT D
WHERE E.SSN=D.ESSN
AND
E.FNAME=D.DEPENDENT_NAME;
Slide 8-21
(cont.)
 The CONTAINS operator compares two sets of values , and
returns TRUE if one set contains all values in the other set.

 Query 3: Retrieve the name of each employee who works on

all the projects controlled by department number 5.

Q3: SELECT FNAME, LNAME

FROM EMPLOYEE
WHERE ( ( SELECTPNO
FROM WORKS_ON
WHERE SSN=ESSN)
CONTAINS
( SELECT PNUMBER
FROM PROJECT
WHERE DNUM=5) ); Slide 8-22
(cont.)
 In Q3, the second nested query, which is not correlated with the
outer query, retrieves the project numbers of all projects
controlled by department 5.

 The first nested query, which is correlated, retrieves the project

numbers on which the employee works, which is different for
each employee tuple because of the correlation.

Slide 8-23
EXISTS FUNCTION

 EXISTS is used to check whether the result of a correlated

nested query is empty (contains no tuples) or not.

Slide 8-24
 Query 12: Retrieve the name of each employee who has a
dependent with the same first name as the employee.

 Q12B: SELECT FNAME, LNAME

FROM EMPLOYEE
WHERE EXISTS ( SELECT *
FROMDEPENDENT
WHERE SSN = ESSN

AND

FNAME = DENDENT_NAME); Slide 8-25

 Query 6: Retrieve the names of employees who have no
dependents.

 Q6: SELECT FNAME, LNAME

FROM EMPLOYEE
WHERE NOT EXISTS ( SELECT *
FROM DEPENDENT
WHERE SSN = ESSN );
 EXISTS is necessary for the expressive power of SQL
Slide 8-26
EXPLICIT SETS
 It is also possible to use an explicit (enumerated) set of values in
the WHERE-clause rather than a nested query.

 Query 13: Retrieve the social security numbers of all

employees who work on project number 1, 2, or 3.

 Q13: SELECT DISTINCT ESSN

FROM WORKS_ON
WHERE PNO IN (1, 2, 3); Slide 8-27
Comparison with NULL
 Represents a missing value

 Three Meanings:
 Unknown value
 Unavailable or withheld value
 Not applicable attribute

 SQL allows queries to check whether an attribute value is

NULL
 Is Null, Is Not Null
Q: Retrieve the names of all employees who don’t have supervisors.
Select Fname, Lname
From Employee
Where Super_ssn IS NULL;

 Using comparison operator (such as = or <>) to check for NULL is a

mistake - a very common mistake.
 For example,
SELECT * FROM products WHERE productCode = NULL;
Joined Relations Features
 Can specify a "joined relation" in the FROM-clause

 Allows the user to specify different types of joins

 "theta" JOIN,

 NATURAL JOIN,

 OUTER JOIN (LEFT OUTER JOIN, RIGHT OUTER JOIN, FULL OUTER
JOIN),

 INNER JOIN (EQUI JOIN),

 CROSS JOIN (Cartesian Product). Slide 8-30

 Examples:
Q8: SELECT E.FNAME, E.LNAME, S.FNAME, S.LNAME
FROM EMPLOYEE AS E, EMPLOYEE AS S
WHERE E.SUPERSSN=S.SSN;
can be written as:
( All employees are required)
Q8: SELECT E.FNAME, E.LNAME, S.FNAME, S.LNAME
FROM ( EMPLOYEE AS E LEFT OUTER JOIN
EMPLOYEE AS S
ON E.SUPERSSN=S.SSN); Slide 8-31
 Q1: SELECT FNAME, LNAME, ADDRESS
FROM EMPLOYEE, DEPARTMENT
WHERE DNUMBER=DNO AND DNAME='Research' ;
can be written as:
 Q1: SELECT FNAME, LNAME, ADDRESS
FROM ( EMPLOYEE JOIN DEPARTMENT
ON DNUMBER=DNO )
WHERE DNAME='Research’;

or as:

Q1: SELECT FNAME, LNAME, ADDRESS

FROM ( EMPLOYEE NATURAL JOIN ( DEPARTMENT
AS DEPT ( DNAME, DNO, MSSN, MSDATE ) ) )
WHERE DNAME='Research’;

Slide 8-32
Nested Join ( Multiway Join)
 Another Example;
 Q2 could be written as follows; this illustrates multiple joins in the
joined tables.
 Q2: SELECT PNUMBER, DNUM, LNAME, BDATE, ADDRESS
FROM ( PROJECT JOIN DEPARTMENT ON
DNUM=DNUMBER)
JOIN EMPLOYEE ON MGRSSN=SSN) )
WHERE PLOCATION='Stafford’;
Slide 8-33
 AGGREGATE FUNCTIONS
 Include COUNT, SUM, MAX, MIN, and AVG
 Query 15: Find the maximum salary, the minimum salary, and the
average salary among all employees.

Q15: SELECT MAX(SALARY), MIN(SALARY), AVG(SALARY)

FROM EMPLOYEE;

 Some SQL implementations may not allow more than one function in
the SELECT-clause
Slide 8-34
 Query 16: Find the maximum salary, the minimum salary, and
the average salary among employees who work for the
'Research' department.

Q16: SELECT MAX(SALARY), MIN(SALARY), AVG(SALARY)

FROM EMPLOYEE, DEPARTMENT
WHERE DNO=DNUMBER

AND DNAME='Research‘;

Slide 8-35
 Queries 17 and 18: Retrieve the total number of employees in
the company (Q17), and the number of employees in the
'Research' department (Q18).

Q17: SELECT COUNT (*)

FROM EMPLOYEE;

Q18: SELECT COUNT (*)

FROM EMPLOYEE, DEPARTMENT
WHERE DNO=DNUMBER AND
Slide 8-36
DNAME='Research’;
 GROUPING
 In many cases, we want to apply the aggregate functions to
subgroups of tuples in a relation.

 Each subgroup of tuples consists of the set of tuples that have the
same value for the grouping attribute(s).

 The function is applied to each subgroup independently.

 SQL has a GROUP BY-clause for specifying the grouping

attributes, which must also appear in the SELECT-clause.
Slide 8-37
 Query 20: For each department, retrieve the department
number, the number of employees in the department, and their
average salary.

Q20: SELECT DNO, COUNT (*), AVG (SALARY)

FROM EMPLOYEE
GROUP BY DNO;

 A join condition can be used in conjunction with grouping

Slide 8-38
 Query 21: For each project, retrieve the project number,
project name, and the number of employees who work on
that project.

Q21: SELECT PNUMBER, PNAME, COUNT (*)

FROM PROJECT, WORKS_ON
WHERE PNUMBER = PNO
GROUP BY PNUMBER, PNAME;
Slide 8-39
HAVING-CLAUSE
 Sometimes we want to retrieve the values of these functions for
only those groups that satisfy certain conditions.

 The HAVING-clause is used for specifying a selection condition

on groups (rather than on individual tuples).

Slide 8-40
 Query 22: For each project on which more than two
employees work , retrieve the project number, project name,
and the number of employees who work on that project.

Q22: SELECT PNUMBER, PNAME, COUNT (*)

FROM PROJECT, WORKS_ON
WHERE PNUMBER = PNO
GROUP BY PNUMBER, PNAME
HAVINGCOUNT (*) > 2;
Slide 8-41
Constraints as Assertions
 General constraints: constraints that do not fit in the basic SQL
categories

 Mechanism: CREAT ASSERTION

 components include: a constraint name, followed by CHECK,
followed by a condition

Chapter 9-42
Assertions: An Example
 “The salary of an employee must not be greater than the salary of
the manager of the department that the employee works for’’

 CREATE ASSERTION SALARY_CONSTRAINT

CHECK (NOT EXISTS (SELECT *
FROM EMPLOYEE E, EMPLOYEE M, DEPARTMENT D
WHERE E.SALARY > M.SALARY AND
E.DNO=D.NUMBER
AND D.MGRSSN = M.SSN ) );
Chapter 9-43
 SQL Triggers

 Objective: To monitor a database and take action when a condition occurs

 Triggers are expressed in a syntax similar to assertions and include the

following:
 event (e.g., an update operation)
 condition
 action (to be taken when the condition is satisfied)

Chapter 9-44
SQL Triggers: An Example
 A trigger to compare an employee’s salary to his/her supervisor during
insert or update operations:

 CREATE TRIGGER INFORM_SUPERVISOR

BEFORE INSERT OR UPDATE OF
SALARY, SUPERVISOR_SSN ON EMPLOYEE
FOR EACH ROW
WHEN
(NEW.SALARY> (SELECT SALARY FROM EMPLOYEE
WHERE SSN=NEW.SUPERVISOR_SSN))
INFORM_SUPERVISOR (NEW.SUPERVISOR_SSN,NEW.SSN);
Chapter 9-45
Views in SQL

 A view is a “virtual” table that is derived from other tables.

 Contains no physical data

 Provides an alternative way to look at the data

 Allows full query operations

 A convenience for expressing certain operations

Chapter 9-46
Specification of Views
 SQL command: CREATE VIEW
 a table (view) name
 a possible list of attribute names (for example, when
arithmetic operations are specified or when we want the
names to be different from the attributes in the base
relations)
 a query to specify the table contents
Chapter 9-47
 SQL Views: An Example
 Specify a different WORKS_ON table

 CREATE VIEW WORKS_ON_NEW AS

SELECT FNAME, LNAME, PNAME, HOURS

FROM EMPLOYEE, PROJECT, WORKS_ON

WHERE SSN=ESSN AND PNO=PNUMBER

GROUP BY PNAME; Chapter 9-48

Using a Virtual Table
 We can specify SQL queries on a newly create table (view):

SELECT FNAME, LNAME

FROM WORKS_ON_NEW

WHERE PNAME=‘Seena’;

 When no longer needed, a view can be dropped:

DROP WORKS_ON_NEW;
Chapter 9-49
View Update

 Update on a single view if the view attributes contain the primary

key of base relation and all attributes with NOT NULL constraint
that don’t have default values

 Un-updatable the views defined on multiple tables using joins

 Un-updatable the views defined using grouping and aggregate

functions

Chapter 9-50
 Schema Change Statements in SQL

 Schema evolution commands: used to alter a schema by

adding or dropping tables, attributes, constraints, and other
schema elements.

 Checks must be done to ensure that the changes don’t affect the
rest of DB and make it inconsistent.

Chapter 9-51
 Drop
 Used to drop named schema elements (tables, domains, constraints)

 Two drop behavior options: CASCADE & RESTRICT

 Eg., To remove the COMPANY DB schema, all elements,

DROP SCHEMA COMPANY CASCADE;

To remove the schema only, (if it has no elements)

DROP SCHEMA COMPANY RESTRICT;

Chapter 9-52
*** If there are some elements, DROP command will not be executed.

 If the base relation within a schema and all such constraints, views and
other elements that reference the table are no longer needed, both
relation and definition can be deleted by DROP TABLE command.

Eg., DROP TABLE DEPEDENT CASCADE;

 To drop the table only if it is not referenced in any constraints,

Eg., DROP TABLE DEPENDENT RESTRICT;

 To delete only records but to leave the table definition, DELETE

command should be used. Chapter 9-53
 Alter
 Used to change the definitions of a base relation or of the named
schema elements.

 Alter table actions:

 Adding or dropping a column (attribute)

 Changing a column definition

 Adding or dropping table constraints

Chapter 9-54
 To add an attribute,

ALTER TABLE COMPANY.EMPLOYEE ADD COLUMN Job VARCHAR (12);

 To drop a column, CASCADE & RESTRICT

 ALTER TABLE COMPANY.EMPLOYEE DROP COLUMN Address

CASCADE;

 ALTER TABLE COMPANY.EMPLOYEE DROP COLUMN Address

RESTRICT;
Chapter 9-55
 To alter a column definition,

 ALTER TABLE COMPANY.DEPARTMENT ALTER COLUMN

Mgr_ssn DROP DEFAULT;

 ALTER TABLE COMPANY.DEPARTMENT ALTER COLUMN

Mgr_ssn SET DEFAULT ‘333445555’;

 To drop the constraint,

 ALTER TABLE COMPANY.EMPLOYEE DROP CONSTRAINT

EMPSUPERFK CASCADE;
Chapter 9-56
 To drop the constraint,

 ALTER TABLE COMPANY.EMPLOYEE DROP CONSTRAINT

EMPSUPERFK CASCADE;

 To add the new constraint,

 ALTER TABLE COMPANY.EMPLOYEE ADD CONSTRAINT

EMPSUPERFK CASCADE;

Chapter 9-57
END OF CHAPTER - 5

Chapter 9-58