Faculty of Computer Science and Engineering

Ho Chi Minh City University of Technology

Chapter 4: The SQL Language

Database Systems
(CO2013)
Computer Science Program
Assoc. Prof. Dr. Võ Thị Ngọc Châu
([email protected])
Semester 1 – 2022-2023
 Content
 Chapter 1: An Overview of Database Systems

 Chapter 2: The Entity-Relationship Model

 Chapter 3: The Relational Data Model

 Chapter 4: The SQL Language

 Chapter 5: Relational Database Design

 Chapter 6: Physical Storage and Data Management

 Chapter 7: Database Security

2
Chapter 4: The SQL Language
 4.1. Introduction to the SQL language
 4.2. DDL
 4.3. DML
 4.4. DCL
 4.5. Stored Functions
 4.6. Stored Procedures
 4.7. Triggers
3
Main References
Text:
 [1] R. Elmasri, S. R. Navathe, Fundamentals of Database
Systems- 6th Edition, Pearson- Addison Wesley, 2011.
 R. Elmasri, S. R. Navathe, Fundamentals of Database Systems- 7th
Edition, Pearson, 2016.
References:
[1] S. Chittayasothorn, Relational Database Systems: Language,
Conceptual Modeling and Design for Engineers, Nutcha Printing Co.
Ltd, 2017.
[3] A. Silberschatz, H. F. Korth, S. Sudarshan, Database System
Concepts – 7th Edition, McGraw-Hill, 2020.
[4] H. G. Molina, J. D. Ullman, J. Widom, Database Systems: The
Complete Book - 2nd Edition, Prentice-Hall, 2009.
[5] R. Ramakrishnan, J. Gehrke, Database Management Systems – 4th
Edition, McGraw-Hill, 2018.
[6] M. P. Papazoglou, S. Spaccapietra, Z. Tari, Advances in Object-
Oriented Data Modeling, MIT Press, 2000.
[7]. G. Simsion, Data Modeling: Theory and Practice, Technics
Publications, LLC, 2007. 4
Introduction to the SQL language

SQL – Wikipedia, Accessed 28/09/2020 5

 6
SQL – Wikipedia, Accessed 28/09/2020
Introduction to the SQL language
 SQL = Structured Query Language
 A standard of the American National Standards
Institute (ANSI) in 1986, and of the International
Organization for Standardization (ISO) in 1987
 One of the first commercial languages to utilize the
relational data model
 A declarative non-procedural language (4GL)
supported by existing RDBMSs
 Based on tuple relational calculus, associated with
relational algebra for internal representation,
processing, and optimization
 Specify ―WHAT‖ in data requests on a database
 NOT specify ―HOW‖ to process the data requests 7
SQL vs. The Relational Model
SQL The Relational Model
Table (set if a primary key is
Relation (set)
specified)
Column Attribute (+ attribute values)
Row Tuple
Domain Domain
Data type (Data type)
Primary key Primary key
Uniqueness, NOT NULL Secondary key
Foreign key Foreign key
Defined and enforced with
Semantic constraints
CHECK, ASSERTION, TRIGGER
8
SQL vs. The Relational Algebra
SQL The Relational Algebra
SELECT statement Relational algebra expression
- specifies WHAT - specifies WHAT + HOW
FROM clause (none), x, *, other joins
WHERE clause SELECTION ()
SELECT clause
PROJECTION ()
(+ DISTINCT)
Aggregate functions Aggregate functions
GROUP BY clause <grouping attributes>
HAVING clause SELECTION on 
ORDER BY clause (not available)
UNION, INTERSECT, MINUS , , -

INSERT, DELETE, UPDATE statements (not available) 9

 (DBMS-specific implementation)

SQL Data Types

 Predefined data types
 Character Types: Character fixed (CHAR) (e.g. CHAR(10)), Character Varying
(VARCHAR) (e.g. VARCHAR(20)), Character Large Object (CLOB)
 Binary Types: Binary (BINARY), Binary Varying (VARBINARY), Binary Large Object
(BLOB)
 Numeric Types
 Exact Numeric Types (NUMERIC, DECIMAL, SMALLINT, INTEGER, BIGINT)
 Approximate Numeric Types (FLOAT, REAL, DOUBLE PRECISION)
 Datetime Types (DATE, TIME, TIMESTAMP)
 Interval Type (INTERVAL)
 Boolean (three-valued logic (3VL): true, false, unknown (NULL))
 XML, JSON
 Constructed types
 ARRAY, MULTISET, REF(erence), or ROW
 User-defined types
 comparable to classes in object-oriented language with their own
constructors, observers, mutators, methods, inheritance,
overloading, overwriting, interfaces, … 10
SQL Data Types – 3-Valued Logic
True False Unknown
AND
(T) (F) (U) NOT X

True T F U True F
False F F F False T

Unknown U F U Unknown U

OR True False Unknown Unknown  NULL

True T T T X: True,
False T F U False,
Unknown T U U Unknown
11
Introduction to the SQL language
 Data Definition Language (DDL)
 CREATE, ALTER, DROP
 Data Manipulation Language (DML)
 For queries: SELECT
 For data modifications: INSERT, DELETE, UPDATE
 Data Control Language (DCL)
 For access control: GRANT, REVOKE
 For transaction control: COMMIT, ROLLBACK,
SET AUTOCOMMIT OFF
 SQL: case-insensitive, extended in DBMSs 12
SQL

Simplified
SQL
Statements
(Checked
with each
DBMS)

13
Source: [1]
Data Definition Language
 Purpose
 Create, modify, and remove the constructs at the
structure level of a database
 Named constructs
 Database (schema), tables, types, domains,
constraints, indexes, views, assertions, triggers, …
 DDL statements
 CREATE
 DROP
 ALTER
14
 CREATE SCHEMA
 Starting with SQL2, a schema is defined to group
together tables and other constructs that belong to
the same database application.
 tables, types, constraints, views, domains, indexes,
authorization grants, etc.
 A catalog is a named collection of schemas.
 Database installations typically have a default environment
and schema, so when a user connects and logs in to that
database installation, the user can refer directly to tables
and other constructs within that schema without having to
specify a particular schema name.
 In a catalog, a special schema is INFORMATION_SCHEMA,
which provides information on all the schemas in the catalog
and all the element descriptors in these schemas.
 Schemas within the same catalog can also share certain
elements, such as type and domain definitions. 15
 DBMS-dependent
- MySQL
CREATE SCHEMA + PostgreSQL
...

 An SQL schema is identified by a schema

name and includes an authorization identifier
to indicate the user or account who owns the
schema, and descriptors for each element.
 The privilege to create schemas, tables, and
other constructs must be explicitly granted to
the relevant user accounts by the system
administrator or DBA.
CREATE SCHEMA SchemaName
[AUTHORIZATION AuthorizationIdentifier];
CREATE SCHEMA Company AUTHORIZATION Smith;
CREATE SCHEMA Company; 16
 NOTES on SCHEMAS
The three-schema architecture

the part of the database

that a particular user group
is interested in and hides External Schemas
the rest of the database
from that user group

the structure
of the whole (Relational
database for a database
community of schema)
users

the physical
storage
structure of the
database

- Conceptual schema in the three-schema architecture ≠ the conceptual EER schema in design!!!
- Conceptual schema in the three-schema architecture is in fact the relational database schema.
- External Schemas are supported with CREATE VIEW. 17
 NOTES on SCHEMAS
 MySQL
 CREATE SCHEMA = CREATE DATABASE
 Concepts are equivalent to the standard.
 MS SQL Server
 CREATE DATABASE with more physical properties
 Data/ log files
 dbo (the default schema of the database owner) is a default schema
for each database to group objects of similar scope or ownership.
 Oracle
 CREATE DATABASE with more physical properties
 Data/ log files
 CREATE USER with a schema of the same name created by default
 A user is an account through which we can log in to the database.
 CREATE SCHEMA does not actually create a schema. Oracle
Database automatically creates a schema when a user is created.
This statement populates the schema (the one with the username)
with tables and views and grant privileges on those objects without
having to issue multiple SQL statements in multiple transactions. 18
CREATE TABLE
 A base table is created and actually stored
as a file by a DBMS with CREATE TABLE.
 The attributes in a base table are considered to
be ordered in the sequence in which they are
specified in the CREATE TABLE statement.
 Rows (tuples) are not considered to be ordered
within a table (relation).

 A virtual table is created but may or may

not correspond to an actual physical file
with CREATE VIEW.
19
 CREATE TABLE
CREATE TABLE [SchemaName.]TableName
({columnName dataType [NOT NULL] [UNIQUE] [PRIMARY KEY]
[DEFAULT <value>]
[CHECK searchCondition] [,...]}
[PRIMARY KEY (listOfColumns)] [,…]
{[UNIQUE (listOfColumns)] [,…]}
{[FOREIGN KEY (listOfFKColumns)
REFERENCES [SchemaName.] ParentTableName [(listOfPKColumns)]
[ON UPDATE CASCADE | SET NULL]
[ON DELETE CASCADE | SET NULL] [,…]}
{[CHECK (searchCondition)] [,…] }
{[CONSTRAINT constraintName PRIMARY KEY| UNIQUE| FOREIGN KEY|
CHECK constraint…] [,…]}) 20
The COMPANY database

21
22
23
24
CREATE TABLE - CONSTRAINTS
 Basic constraints are specified as part of
table creation.
 Primary key
 Foreign key (Referential integrity constraint)
 UNIQUE
 NOT NULL
 Attribute defaults
 Restrictions on attribute domains and other
constraints on individual tuples within a table
using the CHECK clause 25
CREATE TABLE - CONSTRAINTS
 Primary key: specified only one for each table
CREATE TABLE DEPARTMENT (
DNAME VARCHAR(15),
DNUMBER INT PRIMARY KEY,
MGRSSN CHAR(9),
MGRSTARTDATE DATE
);
CREATE TABLE WORKS_ON (
ESSN CHAR(9),
PNO INT,
HOURS DECIMAL(3,1),
PRIMARY KEY (ESSN, PNO)
); 26
 CREATE TABLE - CONSTRAINTS
 Foreign key: specified for referential integrity
CREATE TABLE DEPARTMENT (
DNAME VARCHAR(15),
DNUMBER INT PRIMARY KEY,
MGRSSN CHAR(9),
MGRSTARTDATE DATE
);

CREATE TABLE DEPT_LOCATIONS (

DNUMBER INT,
DLOCATION VARCHAR(15),
PRIMARY KEY (DNUMBER, DLOCATION),
FOREIGN KEY (DNUMBER) REFERENCES DEPARTMENT(DNUMBER)
); 27
 CREATE TABLE - CONSTRAINTS
 Foreign key: specified for referential integrity
 The default action for an integrity violation is to reject the
update operation that will cause a violation: RESTRICT.
 Other actions: SET NULL, CASCADE, and SET DEFAULT
 CASCADE ON DELETE is to delete all the referencing tuples,
whereas CASCADE ON UPDATE is to change the value of the
referencing foreign key attribute(s) to the updated (new)
primary key value for all the referencing tuples.

CREATE TABLE DEPT_LOCATIONS (

DNUMBER INT,
DLOCATION VARCHAR(15),
PRIMARY KEY (DNUMBER, DLOCATION),
FOREIGN KEY (DNUMBER) REFERENCES DEPARTMENT(DNUMBER)
ON DELETE CASCADE ON UPDATE CASCADE
); 28
CREATE TABLE - CONSTRAINTS
 UNIQUE: no duplicate values for the attribute
 NOT NULL: no NULL values allowed. NULL is
default with no specification.
CREATE TABLE DEPARTMENT (
DNAME VARCHAR(15) UNIQUE,
DNUMBER INT PRIMARY KEY,
MGRSSN CHAR(9) NOT NULL,
MGRSTARTDATE DATE
);
CREATE TABLE DEPARTMENT (
DNAME VARCHAR(15), …….
UNIQUE (DNAME)
); 29
CREATE TABLE - CONSTRAINTS
 Attribute defaults: a default value for an
attribute by appending DEFAULT <value>
 The default value is included in any new tuple if an
explicit value is not provided for that attribute.
 If no default clause is specified, the default value
is NULL for attributes that do not have the NOT
NULL constraint.
CREATE TABLE WORKS_ON (
ESSN CHAR(9),
PNO INT,
HOURS DECIMAL(3,1) DEFAULT 10.0,
PRIMARY KEY (ESSN, PNO)
); 30
CREATE TABLE - CONSTRAINTS
 Restrictions on attribute domains and other
constraints on individual tuples within a table
using the CHECK clause: row-level constraints
Constraint: Department numbers are restricted to integer
numbers between 1 and 20. Starting date is from 2000-01-01.

CREATE TABLE DEPARTMENT (

DNAME VARCHAR(15) UNIQUE,
DNUMBER INT PRIMARY KEY
CHECK (DNUMBER>0 AND DNUMBER<21),
MGRSSN CHAR(9) NOT NULL,
MGRSTARTDATE DATE
CHECK (MGRSTARTDATE> ‗2000-01-01‘)
); 31
CREATE DOMAIN
 A domain can be declared, and the domain
name can be used with the attribute
specification. CREATE DOMAIN is DBMS-
specifically implemented.
 change the data type for a domain that is used
by numerous attributes in a schema, and
improve schema readability
CREATE DOMAIN DomainName AS DataType
[CHECK (<condition>)];
CREATE DOMAIN SSN_TYPE AS CHAR(9);
CREATE DOMAIN D_NUM AS INTEGER
CHECK (D_NUM > 0 AND D_NUM < 21);
32
CREATE DOMAIN
CREATE DOMAIN SSN_TYPE AS CHAR(9);

CREATE DOMAIN D_NUM AS INTEGER

CHECK (D_NUM > 0 AND D_NUM < 21);

CREATE TABLE DEPARTMENT (

DNAME VARCHAR(15) UNIQUE,
DNUMBER D_NUM PRIMARY KEY,
MGRSSN SSN_TYPE NOT NULL,
MGRSTARTDATE DATE
);

What is difference between CHECK at the attribute level and

CHECK at the domain level?
33
CREATE TABLE - CONSTRAINTS
 Giving
names to constraints:
CONSTRAINT
 This is optional.
 The name is unique within a particular
database schema.
 It is used to identify a particular constraint
in case it must be dropped later and
replaced with another one.
 It is also possible to temporarily defer a
constraint until the end of a transaction.
34
35
 DROP Statement
 Used to drop named schema elements:
schema (database), tables, indexes, functions,
triggers,...
 Two drop behavior options:
 CASCADE: all the elements referencing or contained
inside the one being dropped are also dropped.
 RESTRICT: only the one being dropped if no
reference.

 DROP SCHEMA
DROP SCHEMA Company CASCADE;
DROP SCHEMA Company RESTRICT; 36
DROP Statement
 DROP TABLE:
DROP TABLE Dependent CASCADE;
 CASCADE: all such constraints, views, and other
elements that reference the table are dropped
automatically from the schema along with the
table itself.
DROP TABLE Dependent RESTRICT;
 RESTRICT: dropped if it is not referenced in any
constraints, views, and other elements.
DROP TABLE DEPARTMENT RESTRICT;

Error Code: 1217. Cannot delete or update a parent row:

a foreign key constraint fails (MySQL) 37
ALTER Statement
 Modifications at the structure level:
 ALTER DATABASE
 ALTER TABLE
 ALTER VIEW
 ALTER FUNCTION
 ALTER PROCEDURE
 …

 ALTER TABLE:
 COLUMN: ADD, MODIFY, DROP, …
 CONSTRAINT: ADD, DROP, …
 TABLE PROPERTIES
 … 38
 ALTER Statement
 Add an attribute for keeping track of jobs of
employees to EMPLOYEE table in COMPANY schema
ALTER TABLE Company.Employee ADD (Job VARCHAR(12));
 Job value for each existing row: default value
ALTER TABLE Company.Employee ADD (Job VARCHAR(12)
DEFAULT ‗EMPLOYEE‘);
 What value does each existing row take for attribute Job if?
ALTER TABLE Company.Employee ADD (Job VARCHAR(12)
NOT NULL);
 What happens to two or more existing rows if attribute Job
is not null and unique?
ALTER TABLE Company.Employee ADD (Job VARCHAR(12)
NOT NULL UNIQUE);
39
 ALTER Statement
 Drop a column: similarly to drop a table, CASCADE
or RESTRICT option must be specified

 CASCADE option: all constraints and views referencing the

column are dropped along with the column

 RESTRICT option: successful only if no constraints and

views are referencing the column

ALTER TABLE Company.Employee DROP [COLUMN] Address

CASCADE;

 What if a primary key is dropped?

ALTER TABLE Company.Employee DROP Ssn CASCADE;

40
 ALTER Statement
 Add a constraint to a table. Added constraints
must be satisfied by the current content.
 Primary key
ALTER TABLE Works_on ADD PRIMARY KEY (Ssn, Pnumber);

 Foreign key
ALTER TABLE Works_on ADD CONSTRAINT ssn_fk
FOREIGN KEY (Ssn) REFERENCES Employee (Ssn)
ON DELETE CASCADE ON UPDATE CASCADE;

 Default values
ALTER TABLE Employee ALTER COLUMN Dno SET DEFAULT 1;
 Unique
ALTER TABLE Department ADD UNIQUE (Dname); 41
Data Definition Language
 Drop a constraint
ALTER TABLE Works_on
DROP PRIMARY KEY;

ALTER TABLE Works_on

DROP FOREIGN KEY ssn_fk CASCADE;

ALTER TABLE Employee

ALTER COLUMN Dno DROP DEFAULT;

//Oracle
ALTER TABLE Department
DROP UNIQUE (Dname);

//MySQL – DROP UNIQUE

ALTER TABLE Department
DROP INDEX Dname; 42
 Write DDL
statements
for the
UNIVERSITY
database on
a specific
DBMS. Pay
attention to
the
references
among the
tables and
their other
constraints.
43
SELECT Statement
 SQL has one basic statement for retrieving information from a
database: SELECT statement.
 SELECT statement is not the same as SELECTION operation ()
of the relational algebra.
 Important distinction between the SQL data model and the
formal relational model is that: SQL allows a table to have two
or more tuples that are identical in all their attribute values.
 A SQL table is a multi-set (sometimes called a bag) of tuples, which is not
a set of tuples.
 A SQL table is a set of tuples, like a relation in the relational model, once a
primary key is defined on it.

table 1
SELECT a table or a scalar value
table 2
… statement
table n
44
SELECT Statement

SELECT [DISTINCT | ALL]

{* | [columnExpression [AS newName]] | built-inFunction

[,...] }

[FROM TableName|ViewName [alias] [, ...]]

[WHERE rowCondition]

[GROUP BY columnList]

[HAVING groupCondition]

[ORDER BY columnList | columnPositionList [ASC|DESC]]

45
SELECT Statement
Typical processing of a query block
Execution
Clause Meaning
order
Specifies and joins table(s)/ view(s) to
1 FROM
be used
2 WHERE Filters rows by row-level conditions

Forms groups of rows with the same

3 GROUP BY
grouping column value

4 HAVING Filters groups by group-level conditions

5 SELECT Specifies the output to be returned

6 ORDER BY Specifies the order of the output

46
 The COMPANY
database

Source: [1] 47
SELECT Statement
For each project in Stafford, on which more than two employees
work, retrieve the project name and the number of employees
who work on that project in descending order of the project name.

SELECT PNAME, COUNT (*)

FROM PROJECT JOIN WORKS_ON ON PNUMBER=PNO

WHERE PLOCATION = ‗Stafford‘

GROUP BY PNAME

HAVING COUNT (*) > 2

ORDER BY PNAME DESC;

48
SELECT Statement
FROM PROJECT JOIN WORKS_ON ON PNUMBER=PNO

PNUMBER=PNO

49
SELECT Statement
FROM PROJECT JOIN WORKS_ON ON PNUMBER=PNO
Pname Pnumber Plocation Dnum Essn Pno Hours
ProductX 1 Bellaire 5 123456789 1 32.5
ProductX 1 Bellaire 5 453453453 1 20.0
ProductY 2 Sugarland 5 123456789 2 7.5
ProductY 2 Sugarland 5 453453453 2 20.0
ProductY 2 Sugarland 5 333445555 2 10.0
ProductZ 3 Houston 5 666884444 3 40.0
ProductZ 3 Houston 5 333445555 3 10.0
Computerization 10 Stafford 4 333445555 10 10.0
Computerization 10 Stafford 4 999887777 10 10.0
Computerization 10 Stafford 4 987987987 10 35.0
Reorganization 20 Houston 1 333445555 20 10.0
Reorganization 20 Houston 1 987654321 20 15.0
Reorganization 20 Houston 1 888665555 20 NULL
Newbenefits 30 Stafford 4 999887777 30 30.0
Newbenefits 30 Stafford 4 987987987 30 5.0
Newbenefits 30 Stafford 4 987654321 30 20.0 50
SELECT Statement
WHERE PLOCATION = ‗Stafford‘
Pname Pnumber Plocation Dnum Essn Pno Hours
ProductX 1 Bellaire 5 123456789 1 32.5
ProductX 1 Bellaire 5 453453453 1 20.0
ProductY 2 Sugarland 5 123456789 2 7.5
ProductY 2 Sugarland 5 453453453 2 20.0
ProductY 2 Sugarland 5 333445555 2 10.0
ProductZ 3 Houston 5 666884444 3 40.0
ProductZ 3 Houston 5 333445555 3 10.0
Computerization 10 Stafford 4 333445555 10 10.0
Computerization 10 Stafford 4 999887777 10 10.0
Computerization 10 Stafford 4 987987987 10 35.0
Reorganization 20 Houston 1 333445555 20 10.0
Reorganization 20 Houston 1 987654321 20 15.0
Reorganization 20 Houston 1 888665555 20 NULL
Newbenefits 30 Stafford 4 999887777 30 30.0
Newbenefits 30 Stafford 4 987987987 30 5.0
Newbenefits 30 Stafford 4 987654321 30 20.0 51
SELECT Statement
WHERE PLOCATION = ‗Stafford‘
Pname Pnumber Plocation Dnum Essn Pno Hours
Computerization 10 Stafford 4 333445555 10 10.0
Computerization 10 Stafford 4 999887777 10 10.0
Computerization 10 Stafford 4 987987987 10 35.0
Newbenefits 30 Stafford 4 999887777 30 30.0
Newbenefits 30 Stafford 4 987987987 30 5.0
Newbenefits 30 Stafford 4 987654321 30 20.0

52
 SELECT Statement
GROUP BY PNAME
Pname Pnumber Plocation Dnum Essn Pno Hours
Computerization 10 Stafford 4 333445555 10 10.0
Group 1
Computerization 10 Stafford 4 999887777 10 10.0
Computerization 10 Stafford 4 987987987 10 35.0
Newbenefits 30 Stafford 4 999887777 30 30.0
Group 2 Newbenefits 30 Stafford 4 987987987 30 5.0
Newbenefits 30 Stafford 4 987654321 30 20.0

53
 SELECT Statement
HAVING COUNT (*) > 2
Pname Pnumber Plocation Dnum Essn Pno Hours
Computerization 10 Stafford 4 333445555 10 10.0
Group 1
Computerization 10 Stafford 4 999887777 10 10.0
Computerization 10 Stafford 4 987987987 10 35.0
Newbenefits 30 Stafford 4 999887777 30 30.0
Group 2 Newbenefits 30 Stafford 4 987987987 30 5.0
Newbenefits 30 Stafford 4 987654321 30 20.0

Group 1: COUNT(*) = 3 > 2 => TRUE => group 1 selected

Group 2: COUNT(*) = 3 > 2 => TRUE => group 2 selected

54
 SELECT Statement
SELECT PNAME, COUNT (*)
Pname Pnumber Plocation Dnum Essn Pno Hours
Computerization 10 Stafford 4 333445555 10 10.0
Group 1
Computerization 10 Stafford 4 999887777 10 10.0
Computerization 10 Stafford 4 987987987 10 35.0
Newbenefits 30 Stafford 4 999887777 30 30.0
Group 2 Newbenefits 30 Stafford 4 987987987 30 5.0
Newbenefits 30 Stafford 4 987654321 30 20.0

Group 1: COUNT(*) = 3 > 2 => TRUE => group 1 selected

Group 2: COUNT(*) = 3 > 2 => TRUE => group 2 selected

Pname COUNT(*)
Computerization 3
Newbenefits 3 55
SELECT Statement
ORDER BY PNAME DESC;

Pname COUNT(*)
Computerization 3
Newbenefits 3
Descending
order
Pname COUNT(*)
Newbenefits 3
Computerization 3

56
For each project in Stafford, on which more than two employees
work, retrieve the project name and the number of employees
who work on that project in descending order of the project name.
SELECT PNAME, COUNT (*)
FROM PROJECT JOIN WORKS_ON ON PNUMBER=PNO
WHERE PLOCATION = ‗Stafford‘
GROUP BY PNAME
HAVING COUNT (*) > 2
ORDER BY PNAME DESC;
Query Result
Pname COUNT(*)
Newbenefits 3
Computerization 3

Input Tables

57
 Retrieve all the information of each employee who is with department 4
and salary > 25000 or with department 5 and salary > 30000.
(Dno=4 AND Salary>25000) OR (Dno=5 AND Salary>30000)(EMPLOYEE)

SELECT *
FROM EMPLOYEE
WHERE (Dno = 4 AND Salary > 25000) OR (Dno = 5 AND SALARY > 30000);

* (asterisk): all columns in the previous process are included in the output.
The primary key in the output => the output is a set (relation). 58
Retrieve Ssn, date of birth, and
department number of each employee.
Ssn, Bdate, Dno (EMPLOYEE)

SELECT Ssn, Bdate, Dno

FROM EMPLOYEE;

List all the department numbers of the employees.

Dno (EMPLOYEE)

SELECT DISTINCT Dno

FROM EMPLOYEE; 59
 Return all the combinations of departments and their locations.
DEPARTMENT x DEPT_LOCATIONS
Dname Dnumber Mgr_ssn Mgr_start_date Dnumber Dlocation
Research 5 333445555 1988-05-22 1 Houston
Research 5 333445555 1988-05-22 4 Stafford
Research 5 333445555 1988-05-22 5 Bellaire
SELECT *
Research 5 333445555 1988-05-22 5 Sugarland
Research
FROM DEPARTMENT,
5
DEPT_LOCATIONS;
333445555 1988-05-22 5 Houston
Administration 4 987654321 1995-01-01 1 Houston
Administration 4 987654321 1995-01-01 4 Stafford
Administration 4 987654321 1995-01-01 5 Bellaire
SELECT *
Administration 4 987654321 1995-01-01 5 Sugarland

FROM DEPARTMENT
Administration4 CROSS JOIN
987654321 DEPT_LOCATIONS;
1995-01-01 5 Houston
Headquarters 1 888665555 1981-06-19 1 Houston
Headquarters 1 888665555 1981-06-19 4 Stafford
Headquarters 1 888665555 1981-06-19 5 Bellaire
Headquarters 1 888665555 1981-06-19 5 Sugarland
60
Headquarters 1 888665555 1981-06-19 5 Houston
Return all the combinations of Research department with Houston location.
DEPARTMENT Dname = ‘Research’ AND Dlocation = ‘Houston’ DEPT_LOCATIONS

Dname Dnumber Mgr_ssn Mgr_start_date Dnumber Dlocation

Research 5 333445555 1988-05-22 1 Houston
Research 5 333445555 1988-05-22 5 Houston

SELECT *
FROM DEPARTMENT, DEPT_LOCATIONS;
WHERE Dname = ‗Research‘ AND Dlocation = ‗Houston‘;

SELECT *
FROM DEPARTMENT CROSS JOIN DEPT_LOCATIONS;
WHERE Dname = ‗Research‘ AND Dlocation = ‗Houston‘; 61
Retrieve all the information of each department and its locations.
DEPARTMENT Dnumber = Dnumber DEPT_LOCATIONS

Dname Dnumber Mgr_ssn Mgr_start_date Dnumber Dlocation

Research 5 333445555 1988-05-22 5 Bellaire
Research 5 333445555 1988-05-22 5 Sugarland
Research 5 333445555 1988-05-22 5 Houston
Administration 4 987654321 1995-01-01 4 Stafford
Headquarters 1 888665555 1981-06-19 1 Houston

SELECT *
FROM DEPARTMENT d, DEPT_LOCATIONS dl;
WHERE d.Dnumber = dl.Dnumber;

SELECT *
FROM DEPARTMENT d CROSS JOIN DEPT_LOCATIONS dl;
WHERE d.Dnumber = dl.Dnumber;

SELECT *
FROM DEPARTMENT d JOIN DEPT_LOCATIONS dl ON d.Dnumber = dl.Dnumber;

Ambiguous names => qualified names by [schemaName.]tableName.attributeName

ALIAS => alternative table name => tuple variable for the table 62
The Relational Algebra

Retrieve all the information of each department and its locations.

DEPARTMENT * DEPT_LOCATIONS

Dname Dnumber Mgr_ssn Mgr_start_date Dlocation

Research 5 333445555 1988-05-22 Bellaire
Research 5 333445555 1988-05-22 Sugarland
Research 5 333445555 1988-05-22 Houston
Administration 4 987654321 1995-01-01 Stafford
Headquarters 1 888665555 1981-06-19 Houston

SELECT *
FROM DEPARTMENT NATURAL JOIN DEPT_LOCATIONS;

63
Return the information of all the employees and their departments they manage.
EMPLOYEE Ssn = Mgr_ssn DEPARTMENT

Fname Minit Lname Ssn B Address Sex Salary Super_ Dno Dname Dnumber Mgr_ Mgr_
date ssn ssn start_
date
Franklin T Wong 33344 1955 638 M 40000 888665 5 Resear 5 33344 1988-
5555 -12- Voss, 555 ch 5555 05-22
08 Houst
on, TX
Jennifer S Wallace 98765 1941 291 F 43000 888665 4 Admini 4 98765 1995-
4321 -06- Berry, 555 stration 4321 01-01
20 Bellair
e, TX
James E Borg 88866 1937 450 M 55000 NULL 1 Headqu 1 88866 1981-
5555 -11- Stone, aters 5555 06-09
10 Houst
on, TX

SELECT *
FROM EMPLOYEE, DEPARTMENT;
WHERE Ssn = Mgr_ssn;

SELECT *
FROM EMPLOYEE CROSS JOIN DEPARTMENT;
WHERE Ssn = Mgr_ssn;

SELECT *
FROM EMPLOYEE JOIN DEPARTMENT ON Ssn = Mgr_ssn;
64
 Return the information of all the employees and their departments that they manage if any.
EMPLOYEE Ssn = Mgr_ssn DEPARTMENT

Fname Minit Lname Ssn B Address Sex Salary Super_ Dno Dname Dnumber Mgr_ Mgr_
date ssn ssn start_
date
Franklin T Wong 3334 1955 638 M 40000 888665 5 Resear 5 3334 1988-
4555 -12- Voss, 555 ch 4555 05-22
5 08 Houst 5
on, TX
Jennifer S Wallace 9876 1941 291 F 43000 888665 4 Admini 4 9876 1995-
5432 -06- Berry, 555 stration 5432 01-01
1 20 Bellair 1
e, TX
James E Borg 8886 1937 450 M 55000 NULL 1 Headqu 1 8886 1981-
6555 -11- Stone, aters 6555 06-09
5 10 Houst 5
on, TX
John B Smith 1234 1965 732 M 30000 333445 5 NULL NULL NULL NULL
5678 -01- Fondre 555
9 09 n,
Houst
on, TX
Alicia J Zelaya 9997 … … … … … 4 NULL NULL NULL NULL
7888
8
Ramesh K Narayan … … … … … … 5 NULL NULL NULL NULL
Joyce A English … … … … … … 5 NULL NULL NULL NULL
Ahmad V Jabbar … … … … … … 4 NULL NULL NULL NULL

SELECT *
FROM EMPLOYEE LEFT OUTER JOIN DEPARTMENT ON Ssn = Mgr_ssn; 65
  SEMI-JOIN: T1 T1.X=T2.Y T2

Return the information of all the departments with at least one employee
who has salary > 30000.
DEPARTMENT Dnumber=Dno (Salary>30000(EMPLOYEE))

SELECT Dname, Dnumber, Mgr_ssn, Mgr_start_date

FROM DEPARTMENT JOIN (SELECT * FROM EMPLOYEE WHERE Salary>30000) e
ON Dnumber = Dno;

SELECT *
FROM DEPARTMENT
WHERE Dnumber IN (SELECT Dno FROM EMPLOYEE WHERE Salary > 30000);

SELECT * SEMI-JOIN
(Oracle, MySQL, …)
FROM DEPARTMENT d
(DBMS-specific implementation)
WHERE EXISTS
(SELECT * FROM EMPLOYEE WHERE Dno = d.Dnumber AND Salary > 30000);
66
 EXISTS vs. NOT EXISTS
 EXISTS function returns TRUE or FALSE.
 TRUE: if the nested query result contains at least one tuple

 FALSE: if the nested query result contains no tuples

Retrieve Ssn and name of each employee who has no dependents.

SELECT Ssn, Fname, Lname

FROM EMPLOYEE
WHERE NOT EXISTS (SELECT *
FROM DEPENDENT
WHERE Essn = Ssn);

SELECT Ssn, Fname, Lname

FROM EMPLOYEE
WHERE Ssn NOT IN (SELECT DISTINCT ESsn
FROM DEPENDENT);
67
 EXISTS vs. NOT EXISTS
 EXISTS function returns TRUE or FALSE.
 TRUE: if the nested query result contains at least one tuple

 FALSE: if the nested query result contains no tuples

List Ssn and name of each manager who has at least one dependent.

SELECT Ssn, Fname, Lname

FROM EMPLOYEE
WHERE EXISTS (SELECT *
FROM DEPENDENT
WHERE Essn = Ssn)
AND EXISTS (SELECT *
FROM DEPARTMENT
WHERE Mgr_ssn = Ssn);
68
 ANTI JOIN: T1 X=Y T2

Return the information of all the employees who do not work in any
department which was managed since 1990.

EMPLOYEE Dno=Dnumber (Mgr_start_date>=’1990-01-01’(DEPARTMENT))

SELECT *
FROM EMPLOYEE
WHERE Dno NOT IN ( SELECT Dnumber
FROM DEPARTMENT
WHERE Mgr_start_date >= ‗1990-01-01‘);

69
 IN vs. NOT IN
 IN: comparison operator, which compares a value v
with a set (or multiset) of values V and evaluates to
TRUE if v is one of the elements in V.
 V: a nested query or an explicit set
 = ANY (or = SOME) operator: equivalent to IN operator

 NOT IN: TRUE if v is not any element in V.

List Essns of all employees who work the same (project, hours)
combination on some project that employee whose Ssn is ‗123456789‘
works on

SELECT DISTINCT Essn

FROM WORKS_ON
WHERE (Pno, Hours) IN (SELECT Pno, Hours
FROM WORKS_ON
WHERE Essn = ‗123456789‘);
70
 IN vs. NOT IN
List the project numbers and names of projects that have an employee
with last name ‗Smith‘ involved as worker or as manager of the
department controlling the projects.

SELECT Pnumber, Pname

FROM PROJECT
WHERE Pnumber IN (SELECT DISTINCT Pno
FROM WORKS_ON
WHERE Essn IN (SELECT Ssn
FROM EMPLOYEE
WHERE Lname = ‗Smith‘))
OR Pnumber IN (SELECT Pnumber
FROM PROJECT, DEPARTMENT, EMPLOYEE
WHERE Dnum = Dnumber
AND Mgr_ssn = Ssn
AND Lname = ‗Smith‘);
71
IN vs. NOT IN
 It is also possible to use an explicit
(enumerated) set of values with IN
comparison operator.

Retrieve the SSNs of all employees who work on project

numbers 1, 2, or 3.

SELECT DISTINCT ESSN

FROM WORKS_ON
WHERE PNO IN (1, 2, 3);

SELECT DISTINCT ESSN

FROM WORKS_ON
WHERE PNO = 1 OR PNO = 2 OR PNO = 3;
72
 UNION: R U S
 Union compatibility: R and S has the same
degree and each corresponding pair of attributes
has the same domain.
 Produce a new relation that includes all tuples
that are either in R or in S or in both R and S.
Duplicate tuples are eliminated.
RESULT1 U RESULT2

SELECT * FROM RESULT1

UNION
Check RESULT for

SELECT * FROM RESULT2; UNION ALL? 73

 UNION: R U S

Make a list of all project numbers for projects that involve an

employee whose last name is 'Smith' as a worker or as a
manager of the department that controls the project.

(SELECT DISTINCT PNUMBER

FROM PROJECT, DEPARTMENT, EMPLOYEE
WHERE DNUM=DNUMBER AND MGR_SSN=SSN
AND LNAME='Smith')
UNION
(SELECT DISTINCT PNUMBER
FROM PROJECT, WORKS_ON, EMPLOYEE
WHERE PNUMBER=PNO AND ESSN=SSN
AND LNAME='Smith');
74
 USE IN OPERATOR ALTERNATIVELY
(DBMS-specific implementation)
 INTERSECTION: R  S
 Union compatibility: R and S has the same degree and each
corresponding pair of attributes has the same domain.
 Produce a new relation that includes all tuples that are in
both R and S.

SELECT * FROM STUDENT

STUDENT  INSTRUCTOR
INTERSECT
SELECT * FROM INSTRUCTOR;

Check RESULT for INTERSECT ALL?

(DBMS-specific implementation) 75
 USE NOT IN OPERATOR ALTERNATIVELY
(DBMS-specific implementation)
 DIFFERENCE: R – S (DBMS-specific implementation)

 Union compatibility: R and S has the same degree and each

corresponding pair of attributes has the same domain.
 Produce a new relation that includes all tuples that are in R
but not in S.

STUDENT - INSTRUCTOR INSTRUCTOR - STUDENT

SELECT * FROM STUDENT SELECT * FROM INSTRUCTOR

MINUS MINUS
SELECT * FROM INSTRUCTOR; SELECT * FROM STUDENT; 76
 DIVISION: T(Y) = R(Z) ÷ S(X), where X  Z
 Produce a new relation T each tuple of which appears in R in
combination with every tuple in S => a sequence of , x, –
The projects of all employees Return the employees who worked on
all the projects that Smith worked:
SSN_PNOS ÷ SMITH_PNOS

T1 = Y(R)
T2 = Y(T1 x S – R)
T = T1 – T2

CREATE TABLE T1 AS
SELECT DISTINCT ESSN
FROM SSN_PNOS;
CREATE TABLE T2 AS
SELECT DISTINCT ESSN
FROM ( SELECT * FROM T1, SMITH_PNOS
MINUS
SELECT * FROM SSN_PNOS);
Smith‘s projects
SELECT * FROM T1
MINUS
SELECT * FROM T2; 77
  AGGREGATION: <a grouping attribute list> ℑ <function list> (R)

How many employees work in the company?

ℑ COUNT Ssn (EMPLOYEE)
How many employees work in each department of the company?

Dno ℑ COUNT Ssn (EMPLOYEE)

SELECT COUNT(Ssn) AS COUNT_Ssn SELECT Dno, COUNT(Ssn) AS COUNT_Ssn

FROM EMPLOYEE; FROM EMPLOYEE Dno COUNT_Ssn
COUNT_Ssn 5 4
8
GROUP BY Dno;
4 3
1 1 78
SELECT Statement –
Substring Pattern Matching
 Substring pattern matching: allows comparison
conditions on only parts of a character string, using
the LIKE comparison operator.
 Partial strings are specified by using any of two
reserved characters: % and _ (underscore)
 %: replaces an arbitrary number of zero or more characters
 _ : replaces a single character

SELECT Ssn, Fname, Lname, Address

FROM EMPLOYEE
WHERE Address LIKE ‗%Houston, TX%‘;

SELECT Ssn, Fname, Lname, BDate

FROM Employee
WHERE BDate LIKE ‗_ _ _ _ _ 0 1 _ _ _‘; 79
SELECT Statement –
Arithmetic Operators
 Standard arithmetic operators: +, -, *, /

Show the resulting salaries if every employee

working on ―ProductX‖ project is given 10% raise.

SELECT FNAME, LNAME, 1.1*Salary AS INC_SAL

FROM Employee, Works_on, Project
WHERE SSN=ESSN
AND PNO=PNUMBER
AND PNAME=‗ProductX‘;
80
 SELECT Statement - NULL
 Comparison operators for NULL values
 IS NULL: checks whether an attribute value is NULL

 IS NOT NULL: checks whether an attribute value is NOT NULL

Retrieve Ssn, names, Super_ssn of all employees who do not have supervisors.
SELECT Ssn, Fname, Lname, Super_SSN
FROM EMPLOYEE
WHERE Super_SSN IS NULL;

Retrieve Ssn, Fname, Lname, Super_SSN of all employees

who have supervisors.
SELECT Ssn, Fname, Lname, Super_SSN
FROM EMPLOYEE
WHERE Super_SSN IS NOT NULL; 81
 SELECT Statement –
More Comparison Operators
 =, >, >=, <, <=, <>, BETWEEN
 X BETWEEN v1 AND v2: X >= v1 AND X <= v2

 ANY, SOME, ALL

 The comparison condition (v > ALL V) returns TRUE if the
value v is greater than all the values in the set (or multiset) V.
Retrieve Ssn Fname, Lname, Dnumber, and Salary of each employee in
department 5 whose salary is between $30,000 and $40,000.
SELECT Ssn, Fname, Lname, Dno Dnumber, Salary
FROM EMPLOYEE
WHERE Salary BETWEEN 30000 AND 40000 AND Dno=5;

82
 SELECT Statement –
More Comparison Operators
 =, >, >=, <, <=, <>, BETWEEN
 ANY, SOME, ALL
 The comparison condition (v > ALL V) returns TRUE if the
value v is greater than all the values in the set (or multiset) V.

Retrieve Ssn, Fname, Lname, and Salary of each employee whose salary is
greater than the salary of all employees in department 5.
SELECT Ssn, Fname, Lname, Salary
FROM EMPLOYEE
WHERE Salary > ALL (SELECT Salary FROM Employee WHERE Dno=5);

SELECT Ssn, Fname, Lname, Salary

FROM EMPLOYEE
WHERE Salary >(SELECT MAX(Salary) FROM Employee WHERE Dno=5);
83
 SELECT Statement –
Multiway Join
 It is also possible to nest join specifications; that is, one of the
tables in a join may itself be a joined table.
 Nesting allows the specification of the join of three or more tables
as a single joined table, called a multiway join.
For every project located in 'Stafford', list the project number, the controlling
department number, and the department manager's last name, address, and
birthdate.

SELECT PNUMBER, DNUM, LNAME, BDATE, ADDRESS

FROM PROJECT, DEPARTMENT, EMPLOYEE
WHERE DNUM=DNUMBER AND MGR_SSN=SSN AND PLOCATION='Stafford';

SELECT PNUMBER, DNUM, LNAME, BDATE, ADDRESS

FROM (PROJECT JOIN DEPARTMENT ON DNUM=DNUMBER)
JOIN EMPLOYEE ON MGR_SSN=SSN
WHERE PLOCATION='Stafford';

84
 SELECT Statement –
Nested Queries
 Nested queries: complete [select-from-where-…] query blocks
within another query which is called the outer query.
 These nested queries can also appear in the WHERE clause or
the FROM clause or the SELECT clause or other SQL clauses as
needed.
 If a nested query returns a single attribute and a single tuple,
the result will be a single (scalar) value. In such cases, it is
permissible to use = instead of IN for the comparison operator.
 In general, the nested query will return a table, which is a set
or multiset of tuples.
 SQL allows the use of tuples of values in comparisons by
placing them within parentheses.
List the name of the department where Smith works.
SELECT Dname
FROM DEPARTMENT
WHERE Dnumber IN (SELECT Dno FROM EMPLOYEE WHERE Lname = ‗Smith‘);
85
 SELECT Statement –
Nested Queries
Retrieve Ssn, name, and address of all employees whose salary is greater
than the average salary of the employees in 'Research' department.
SELECT SSN, FNAME, LNAME, ADDRESS
FROM EMPLOYEE
WHERE SALARY > (SELECT AVG(SALARY)
FROM EMPLOYEE
WHERE DNO IN (SELECT DNUMBER
FROM DEPARTMENT
WHERE DNAME='Research'));

For each department that has more than two employees, retrieve the
department number and the number of its employees who are making
more than $30,000.
SELECT Dno, count(*) ENumber
FROM EMPLOYEE
WHERE Salary>30000 AND Dno IN (SELECT Dno
FROM EMPLOYEE
GROUP BY Dno
HAVING count(*) > 2)
86
GROUP BY Dno;
 SELECT Statement –
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.
 A correlated query is the query where the nested query is
evaluated once for each tuple (or combination of tuples) in the
outer query.

List Ssn, name, and department number of each employee who has the salary
greater than the average salary of the employees in his/her department.

SELECT Ssn, Fname, Lname, Dno Dnumber

FROM EMPLOYEE E

WHERE Salary > (SELECT AVG(Salary)

FROM EMPLOYEE

WHERE Dno = E.Dno);

87
 SELECT Statement –
Correlated Nested Queries
Retrieve the name of each employee who has a dependent with the same
first name as the employee.

SELECT FNAME, LNAME

FROM EMPLOYEE AS E
WHERE E.SSN IN (SELECT ESSN
FROM DEPENDENT
WHERE ESSN=E.SSN
AND DEPENDENT_NAME=E.FNAME);

Retrieve Ssn and name of each employee who has two or more
dependents.

SELECT Ssn, Lname, Fname

FROM EMPLOYEE
WHERE (SELECT count(*)
FROM DEPENDENT
WHERE Essn = Ssn) >= 2;
88
 INSERT Statement
 In its simplest form, INSERT is used to add
one row in a table.
 Table name and a list of values for a new row
need to be specified.
 Attribute values must be listed in the same
order as the attributes were specified in the
CREATE TABLE statement.
 NOTE: All the DDL constraints on the table are
checked for any modification on the table.
 A DBMS that fully implements SQL should support
and enforce all the integrity constraints that can be
specified in the DDL. 89
INSERT Statement

INSERT INTO EMPLOYEE

VALUES (‗Richard‘, ‗K‘, ‗Marini‘, ‗653298653‘, ‗1962-12-30‘,
‗98 Oak Forest, Katy, TX‘, ‗M‘, 37000, ‗653298653‘, 4);

90
INSERT Statement
 A second form of the INSERT statement
allows the user to specify explicit attribute
names that correspond to the values
provided in the INSERT command.
 This is useful if a relation has many attributes
but only a few of those attributes are
assigned values in the new tuple.
 The values must include all attributes with NOT
NULL specification and no default value.
 Attributes with NULL allowed or DEFAULT values
are the ones that can be left out.
91
INSERT Statement

INSERT INTO EMPLOYEE (Fname, Lname, Dno, Ssn)

VALUES (‗Peter‘, ‗Pan‘, 1, ‗123123123‘);

92
INSERT Statement
INSERT INTO EMPLOYEE (Fname, Lname, Dno)
VALUES (‗Jack‘, ‗Sparrow‘, 1);
Error Code: 1364. Field 'SSN' doesn't have a default value

INSERT INTO EMPLOYEE (Fname, Lname, Dno, Ssn)

VALUES (‗Jack‘, ‗Sparrow‘, 1, ‗123456789‘);
Error Code: 1062. Duplicate entry '123456789' for key 'PRIMARY'

INSERT INTO EMPLOYEE

VALUES (‗James‘, ‗J‘, ‗Watson‘, ‗653298653‘, ‗1962-12-30‘,
‗98 Oak Forest, Houston, TX‘, ‗M‘, 37000, ‗653298653‘, NULL);
Error Code: 1048. Column 'DNO' cannot be null

INSERT INTO EMPLOYEE (Fname, Lname, Dno, Ssn)

VALUES (‗Jack‘, ‗Sparrow‘, 2, ‗234234234‘);
Error Code: 1452. Cannot add or update a child row: a foreign key constraint fails
(`company`.`employee`, CONSTRAINT `employee_ibfk_2` FOREIGN KEY (`DNO`)
REFERENCES `department` (`DNUMBER`)) 93
INSERT Statement
INSERT INTO EMPLOYEE (Fname, Lname, Dno)
VALUES (‗Jack‘, ‗Sparrow‘, 1);
Error Code: 1364. Field 'SSN' doesn't have a default value

INSERT INTO EMPLOYEE (Fname, Lname, Dno, Ssn)

VALUES (‗Jack‘, ‗Sparrow‘, 1, ‗123456789‘);
Error Code: 1062. Duplicate entry '123456789' for key 'PRIMARY'

INSERT INTO EMPLOYEE

VALUES (‗James‘, ‗J‘, ‗Watson‘, ‗653298653‘, ‗1962-12-30‘,
‗98 Oak Forest, Houston, TX‘, ‗M‘, 37000, ‗653298653‘, NULL);
Error Code: 1048. Column 'DNO' cannot be null

INSERT INTO EMPLOYEE (Fname, Lname, Dno, Ssn)

VALUES (‗Jack‘, ‗Sparrow‘, 2, ‗234234234‘);
Error Code: 1452. Cannot add or update a child row: a foreign key constraint fails
(`company`.`employee`, CONSTRAINT `employee_ibfk_2` FOREIGN KEY (`DNO`)
REFERENCES `department`HOW TO FIX THEM?
(`DNUMBER`)) 94
INSERT Statement
 A variation of the INSERT command inserts
multiple tuples into a table in conjunction
with creating the table and loading it with
the result of a query.
CREATE TABLE WORKS_ON_INFO (
Emp_name VARCHAR(15),
Proj_name VARCHAR(15),
Hours_per_week DECIMAL(3,1)
);

INSERT INTO WORKS_ON_INFO

SELECT E.Lname, P.Pname, W.Hours
FROM PROJECT P, WORKS_ON W, EMPLOYEE E
WHERE P.Pnumber = W.Pno AND W.Essn = E.Ssn;
95
 INSERT Statement
 Another variation for loading data is to create a new table
TNEW that has the same attributes as an existing table T, and
load some of the data currently in T into TNEW. The syntax for
doing this uses the LIKE clause.

CREATE TABLE D5EMPS LIKE EMPLOYEE;

INSERT INTO D5EMPS

SELECT *
FROM EMPLOYEE
WHERE Dno = 5;

? COMPARED with:
CREATE TABLE D5EMPS AS
SELECT *
FROM EMPLOYEE
WHERE Dno = 5; 96
 DELETE Statement
 The DELETE command removes tuples from a relation.
 It includes a WHERE clause, similar to that used in an
SQL query, to select the tuples to be deleted.
 Tuples are explicitly deleted from only one table at a
time. However, the deletion may propagate to tuples
in other relations if referential triggered actions are
specified in the referential integrity constraints of the
DDL.
 A missing WHERE clause specifies that all tuples in the
relation are to be deleted; however, the table remains
in the database as an empty table.
 The DROP TABLE command is used to remove the table definition.
97
 DELETE Statement
Remove from EMPLOYEE table the details of the employees whose last
name is Brown.
DELETE FROM EMPLOYEE
How many rows are deleted?
WHERE LNAME='Brown';
Is any constraint violated?
Remove from EMPLOYEE table the details of the employee whose SSN is
123456789.
DELETE FROM EMPLOYEE
WHERE SSN='123456789';

Remove from EMPLOYEE table the details of all the employees who work in
Research department.
DELETE FROM EMPLOYEE
WHERE DNO IN (SELECT DNUMBER
FROM DEPARTMENT
WHERE DNAME = 'Research');

Remove from EMPLOYEE table the details of all the employees.

DELETE FROM EMPLOYEE; 98
 UPDATE Statement
 The UPDATE command is used to modify attribute
values of one or more selected tuples in a table.
 As in the DELETE command, a WHERE clause in the
UPDATE command selects the tuples to be modified
from a single table.
 Updating a primary key value may propagate to the foreign
key values of tuples in other relations if such a referential
triggered action is specified in the referential integrity
constraints of the DDL.
 To modify multiple relations, several UPDATE commands
must be issued.
 The SET clause in the UPDATE command specifies
the attributes to be modified and their new values.
99
 UPDATE Statement
Change the location and controlling department number
of project number 10 to 'Bellaire' and 5, respectively.

UPDATE PROJECT
SET PLOCATION = 'Bellaire', DNUM = 5
WHERE PNUMBER=10;

Increase 2 hours for all the employees of department 1

working on the projects.

UPDATE WORKS_ON
SET HOURS = HOURS + 2
WHERE ESSN IN (SELECT DISTINCT SSN
FROM EMPLOYEE
WHERE DNO = 1); 100
Data Control Language -
Access Control
 Database access control: restricting data
access and database activities, e.g. restrict
users from querying specified tables or
executing specified database statements.
 Privilege: the right to perform some action,
e.g. run specific SQL statements.
 User: an account through which you can
log in to the database, and to establish the
means by which Oracle Database (DBMS)
permits access by the user
 Role: a named set of privileges that can be
granted to users or to other roles 101
 Data Control Language -
Access Control
 Privilege categories
 System privilege
 This is the right to perform a specific action in the
database, or perform an action on any objects of a
specific type. For example, CREATE USER and CREATE
SESSION are system privileges.

 Object privilege
 This is the right to perform a specified action on an
object, for example, query the employees table.
Privilege types are defined by the database.

Source: Oracle 19c - Real Application Security Administrator's and Developer's Guide 102
 Oracle Database Real Application
Security Components – Oracle 19c

ACL: Access Control List which is a list of

access control entries, which permit or
deny application privileges to one or more
principals. 103
 GRANT Statement
 The GRANT statement provides privileges
for a user to perform specific actions.
Create user psmith as a new user while granting psmith the CREATE
SESSION system privilege.
If a password is specified using the IDENTIFIED BY clause, and the
user name does not exist in the database, then a new user with that
user name and password is created.
GRANT CREATE SESSION TO psmith IDENTIFIED BY password;

Grant the UPDATE, INSERT, and DELETE object privileges for all
columns of the EMPLOYEE table to the user psmith.
GRANT UPDATE, INSERT, DELETE ON EMPLOYEE TO psmith;

The WITH GRANT OPTION clause with the GRANT statement can
enable a grantee, psmith, to grant object privileges to other users.
GRANT SELECT ON EMPLOYEE TO psmith WITH GRANT OPTION;

Source: Oracle 19c – Oracle Database Security Guide 104

 REVOKE Statement
 The REVOKE SQL statement revokes system privileges and roles.
 Any user with the ADMIN option for a system privilege or role can revoke
the privilege or role from any other database user or role.
 The revoker does not have to be the user that originally granted the
privilege or role. Users with GRANT ANY ROLE can revoke any role.
 Can only revoke the privileges that the person who granted the privilege,
directly authorized.
 Cannot revoke grants that were made by other users to whom was granted
the GRANT OPTION.
 However, there is a cascading effect. If the object privileges of the user who
granted the privilege are revoked, then the object privilege grants that were
propagated using the GRANT OPTION are revoked.

Revoke SELECT and INSERT privileges on the EMPLOYEE table from user psmith.
REVOKE SELECT, INSERT ON EMPLOYEE FROM psmith;

Revoke all object privileges for DEPARTMENT table that was originally granted to
the human_resources role.
REVOKE ALL ON DEPARTMENT FROM human_resources;
Source: Oracle 19c – Oracle Database Security Guide 105
 GRANT and REVOKE Statements
-- from user 'root'@'localhost'
CREATE USER 'chau2'@'localhost' IDENTIFIED BY 'chau2';

GRANT ALL PRIVILEGES ON company.* TO 'chau2'@'localhost';

GRANT CREATE, ALTER, SELECT ON test.* TO 'chau2'@'localhost';

SELECT * FROM 'demo_20201003'.'employee';

Error Code: 1142. SELECT command denied to user

'chau2'@'localhost' for table 'employee'

GRANT SELECT ON demo_20201003.* TO 'chau2'@'localhost';

SHOW GRANTS FOR 'chau2'@'localhost';

REVOKE ALL PRIVILEGES ON *.* FROM 'chau2'@'localhost';

Source: a demonstration on MySQL 106

 Data Control Language –
Transaction Control
 Transaction: a logical data processing unit on a
database
 Change a database from a consistent state to another
consistent one
 ACID properties: atomic, consistent, isolated, durable
 Transaction Control Language: a set of commands
which are used to mark the synchronization points
(sync points) of transactions
 COMMIT
 ROLLBACK
 SET AUTOCOMMIT OFF

Source: S. Chittayasothorn, Relational Database Systems: Language, Conceptual Modeling

and Design for Engineers, Nutcha Printing Co. Ltd, 2017. 107
 Data Control Language –
Transaction Control
 COMMIT: confirms the operations in the transaction
have successfully completed. It is the responsibility
of the DBMS to make sure that all those confirmed
operations are permanent.
 ROLLBACK: cancels all the operations from the
previous sync point to the ROLLBACK point.
 SET AUTOCOMMIT OFF: equivalent to ―begin
transaction‖, opposite to SET AUTOCOMMIT ON.
When AUTOCOMMIT is ON, each SQL command is
considered as a separate transaction and is
committed automatically after its execution. To
group several SQL commands into one transaction,
AUTOCOMMIT must be OFF.
Source: S. Chittayasothorn, Relational Database Systems: Language, Conceptual Modeling
and Design for Engineers, Nutcha Printing Co. Ltd, 2017. 108
Database Stored Procedures
and SQL/PSM
 Database program modules—procedures or
functions—that are stored and executed by
the DBMS at the database server.
 These are historically known as database stored
procedures, although they can be functions or
procedures.
 The term used in the SQL standard for stored
procedures is persistent stored modules because
these programs are stored persistently by the
DBMS, similarly to the persistent data stored by
the DBMS.
109
Database Stored Procedures
and SQL/PSM
 Database stored procedures are useful in the
following circumstances:
 If a database program is needed by several applications, it
can be stored at the server and invoked by any of the
application programs. This reduces duplication of effort and
improves software modularity.
 Executing a program at the server can reduce data transfer
and communication cost between the client and server in
certain situations.
 These procedures can enhance the modeling power
provided by views by allowing more complex types of
derived data to be made available to the database users via
the stored procedures. Additionally, they can be used to
check for complex constraints that are beyond the
specification power of assertions and triggers.
110
 Database Stored Procedures
and SQL/PSM
 CREATE, ALTER, and DROP statements create, modify,
and drop a stored procedure or a stored function.
 Functions/procedures can be written in SQL itself, or
in an external programming language (e.g., C, Java).
 Loops, if-then-else, assignment

 Some database systems (e.g., Oracle, MS SQL Server)

support table-valued functions, which can return a
table as a result.
 Functions are parameterized views that generalize the
regular notion of views by allowing parameters.
Source: A. Silberschatz, H. F. Korth, S. Sudarshan, Database System Concepts –
111
6th Edition, McGraw-Hill, 2006.
Stored Functions
 Given a department name, return the number of employees
who work for the corresponding department.

create function dept_count (dept_name varchar (15))

returns int
begin
declare d_count int;
select count(*) into d_count
from employee join department on dno = dnumber
where dname = dept_name;
return d_count;
end

select *
from department
where dept_count (dname) > 10;
112
 Stored Functions

113
Source: a demonstration on MS SQL Server
Stored Functions
 Return the department number, department name, and
average salary of each department of more than n employees.
create function dept_avg_salary (n int)
returns d_a_s TABLE (
dname varchar (15),
dnumber int,
avg_sal decimal (10,2)
)
begin
insert into d_a_s
select dname, dnumber, AVG(salary)
from employee join department on dno = dnumber
where dept_count(dname) > n
group by dnumber, dname;
return;
end

select *
from dept_avg_salary (2);
114
 Stored Functions

115
Source: a demonstration on MS SQL Server
 Stored Procedures
 Given a department name, return the number of employees
who work for the corresponding department.

116
Source: a demonstration on MS SQL Server
 Stored Procedures
 Return the department number, department name, and
average salary of each department of more than n employees.

117
Source: a demonstration on MS SQL Server
 Procedural language constructs
 SQL supports constructs that gives it almost all the
power of a general-purpose programming language.
 Warning: most database management systems implement
their own variant of the standard syntax.
 Compound statement: begin … end,
 Multiple SQL statements are given between begin and end.
 Local variables can be declared within a compound
statements.
 for, while and repeat statements
 if, if … else … statements
 case statements, …
Source: A. Silberschatz, H. F. Korth, S. Sudarshan, Database System Concepts –
118
6th Edition, McGraw-Hill, 2006.
 Procedural language constructs

119
Source: a demonstration on MySQL
 Procedural language constructs

120
Source: a demonstration on MySQL
 Stored Procedures
 Insert data of a new employee using 20000 if salary is <=0.

121
Source: a demonstration on MySQL
 Stored Procedures
 Insert data of a new employee using the average salary of
the department if no salary is specified (<=0).

122
Source: a demonstration on MS SQL Server
 (DBMS-specific implementation)

Triggers
 A trigger is a named database object that is
associated with a table, and that automatically
activates when a particular event occurs for the table.
 Activation time
 BEFORE or AFTER an event
 INSTEAD OF an event
 Event: INSERT, DELETE, UPDATE
 Refer to columns in the table associated with the trigger by
using the aliases OLD (deleted) and NEW (inserted).
 OLD.col_name: a column of an existing row before it is updated
or deleted
 NEW.col_name: a column of a new row to be inserted or an
existing row after it is updated
123
 Triggers
 When to use triggers
 Enforce application-based semantic constraints
 Maintain summary data
 Replicate databases by recording changes to other
special tables
 When not to use triggers Cascading execution
 Encapsulation facilities available for update (insert,
delete, update) methods with constraints
 Materialized views available for maintaining
summary data
 Built-in DBMS support ready to replicate databases
Source: A. Silberschatz, H. F. Korth, S. Sudarshan, Database System Concepts –
124
6th Edition, McGraw-Hill, 2006.
 Triggers

Create a trigger to ensure each

employee works on at least one project.

DELIMITER $$

CREATE TRIGGER trgTotalEmployee_after_delete_works_on AFTER DELETE ON works_on

FOR EACH ROW
BEGIN

declare total_participation condition for SQLSTATE '45000';

IF NOT EXISTS (select * from works_on where essn = old.essn) THEN

SIGNAL total_participation
SET MESSAGE_TEXT = 'Every employee must work on at least one project!';
END IF;

END $$
125
Source: a demonstration on MySQL
 Triggers
 Update the number of employees of the
department from which an employee is removed.

126
Source: a demonstration on MS SQL Server
 Triggers
 Check if a new employee is with the same
department as his/her supervisor before inserted.

127
Source: a demonstration on MS SQL Server
NOTES on Functions, Procedures,
and Triggers

 More details of stored functions, stored

procedures, and triggers

 Check the specification of a particular DBMS

 See more examples on MS SQL Server with a

demonstration uploaded in BKEL

128
 Summary
 SQL = Structured Query Language ANSI/ISO
Standard:
 Data Definition Language SQL-86,
SQL-89,
 Data Manipulation Language SQL-92,
SQL-1999,
 Data Control Language SQL-2003,
SQL-2006,
 Stored Functions SQL-2008,
 Stored Procedures SQL-2011,
SQL-2016,
 Triggers SQL-2019

 A declarative non-procedural language, based on tuple

relational calculus, supported by existing RDBMSs
 Associated with relational algebra for internal
representation, processing, and optimization 129
Summary
 Further readings
 DDL statements for views, indexes

 Full syntax of each SQL statement on a specific

relational DBMS
 Oracle

 MS SQL Server

 MySQL

 PostgreSQL

 SQLite

 … 130
Chapter 4: The SQL Language

131
 Review
 1. Given the Company database, write a
SELECT statement for each requirement:
 1.1. Retrieve Ssn, name, and address of the employees who
work in department 5.
 1.2. Retrieve Ssn, name, and address of the employees who
work in ‗Research‘ department.
 1.3. Retrieve Ssn, name, and address of the employees who
work in departments in Houston.
 1.4. Return the average salary of the employees who work in
department 1.
 1.5. Return the maximum salary of the employees in each
department.
 1.6. Return the number of the employees who have salaries
greater than the average salary of the employees who work in
department 1. 132
Review
 1. Given the Company database, write a
SELECT statement for each requirement:
 1.7. For every project located in ‗Stafford‘, list the project
number, the controlling department number, and the
department manager‘s last name, address, and birth date.
 1.8. Find the names of employees who work on all the
projects controlled by department number 5.
 1.9. Make a list of project numbers for projects that involve
an employee whose last name is ‗Smith‘, either as a worker
or as a manager of the department that controls the project.
 1.10. List the names of all employees with two or more
dependents in department 5.
 1.11. Retrieve the names of employees who have no
dependents.
133
Review
 1. Given the Company database, write a
SELECT statement for each requirement:
 1.12. List the names of managers who have at least one
dependent.
 1.13. Retrieve the names of all employees in department
5 who work more than 10 hours per week on the ProductX
project.
 1.14. List the names of all employees who have a
dependent with the same first name as themselves.
 1.15. Find the names of all employees who are directly
supervised by ‗Franklin Wong‘.
 1.16. For each project, list the project name and the total
hours per week (by all employees) spent on that project.
 1.17. Retrieve the names of all employees who work on
every project. 134
Review
 1. Given the Company database, write a
SELECT statement for each requirement:
 1.18. Retrieve the names of all employees who do not work
on any project.
 1.19. For each department, retrieve the department name
and the average salary of all employees working in that
department.
 1.20. Retrieve the average salary of all female employees.
 1.21. Find the names and addresses of all employees who
work on at least one project located in Houston but whose
department has no location in Houston.
 1.22. List the last names of all department managers who
have no dependents.
 1.23. List the department name, the number of projects,
and the number of employees of each department that
controls those projects in the same location. 135
 Review
 2. For the Company database, write a DML
statement for each requirement. Is any constraint
violated?
 2.1. Insert <‗Robert‘, ‗F‘, ‗Scott‘, ‗943775543‘, ‗1972-06-21‘,
‗2365 Newcastle Rd, Bellaire, TX‘, M, 58000, ‗888665555‘,
1> into EMPLOYEE.
 2.2. Insert <‗ProductA‘, 4, ‗Bellaire‘, 2> into PROJECT.
 2.3. Insert <‗Production‘, 4, ‗943775543‘, ‗2007-10-01‘> into
DEPARTMENT.
 2.4. Insert <‗677678989‘, NULL, ‗40.0‘> into WORKS_ON.
 2.5. Insert <‗453453453‘, ‗John‘, ‗M‘, ‗1990-12-12‘,
‗spouse‘> into DEPENDENT.
 2.6. Delete the WORKS_ON tuples with Essn = ‗333445555‘.
 2.7. Delete the EMPLOYEE tuple with Ssn = ‗987654321‘.
 2.8. Delete the PROJECT tuple with Pname = ‗ProductX‘.
 2.9. Delete the DEPARTMENT tuple with Dname =
‗Administration‘. 136
 Review
 2. For the Company database, write a DML
statement for each requirement. Is any constraint
violated?
 2.10. Modify the Mgr_ssn and Mgr_start_date of the
DEPARTMENT tuple with Dnumber = 5 to ‗123456789‘ and
‗2007-10-01‘, respectively.
 2.11. Modify the Super_ssn attribute of the EMPLOYEE tuple
with Ssn = ‗999887777‘ to ‗943775543‘.
 2.12. Modify the Hours attribute of the WORKS_ON tuple
with Essn = ‗999887777‘ and Pno = 10 to ‗5.0‘.
 2.13. Modify the Dname attribute of the DEPARTMENT tuple
with Dname = ‗Administration‘ to ‗Headquarters‘.
 2.14. Modify the Salary attribute of the EMPLOYEE tuples
with the average salary of the department ‗Research‘ where
the salary of its manager is 10% higher.
 2.15. Modify the Dno attribute of the EMPLOYEE tuples with
Dno = 1 to Dno = 2. 137
 Review
 3. Given the
relational schema of
the LIBRARY
database, write DDL
statements to
implement this
schema with any
assumption needed
for data types and
constraints. 138
 Review
 4. Write SQL statements on the LIBRARY database in question 3.
 4.1. Add an ISBN attribute of varchar(13) into BOOK relation schema where
ISBN is a secondary key.
 4.2. How many copies of the book titled The Lost Tribe are owned by the
library branch whose name is ‗Sharpstown‘?
 4.3. How many copies of the book titled The Lost Tribe are owned by each
library branch?
 4.4. Retrieve the names of all borrowers who do not have any books checked
out.
 4.5. For each book that is loaned out from the Sharpstown branch and whose
Due_date is today, retrieve the book title, the borrower‘s name, and the
borrower‘s address.
 4.6. For each library branch, retrieve the branch name and the total number
of books loaned out from that branch.
 4.7. Retrieve the names, addresses, and number of books checked out for all
borrowers who have more than five books checked out.
 4.8. For each book authored (or coauthored) by Stephen King, retrieve the
title and the number of copies owned by the library branch whose name is
Central. 139
 Review
 5. Write stored functions to process the data in
the LIBRARY database as follows:
 5.1. Return ISBN and book title of each book
available in every library branch.

 5.2. Given a publisher name and a date, return the

number of books published by this publisher
borrowed on the date.

 5.3. Given an author name, return the details of the

borrowers who borrowed his/her books the most.
140
 Review
 6. Write stored procedures to process the
data in the LIBRARY database as follows:
 6.1. Given a date, update a temporary table with
the details of all the books that are not available
on that date because all of them were borrowed.

 6.2. Given an author name, return the most

recent date when his/her book was borrowed.

 6.3. Return the library branches where the books

have been borrowed the most.
141
Review
 7. Write triggers to process the data in the
LIBRARY database as follows:
 7.1. Enforce the constraint which is: the number
of copies of each book in any library branch must
be at least 2.
 7.2. Enforce the constraint which is: a borrower
cannot borrow more than 3 books at the same
time.
 7.3. Update a temporary table of the books
whose copies are not available in any library
branch because all of them were borrowed.
142
Next
Chapter 5: Relational Database Design

 5.1. Design Guidelines for Relation

Schemas
 5.2. Functional Dependencies
 5.3. Normal Forms Based on Primary Keys
 5.4. Boyce-Codd Normal Form
 5.5. Properties of Relational Decompositions
 5.6. Algorithms for Relational Database
Schema Design

143