Lecture 5

More SQL: Complex Queries,

Triggers, Views, and Schema
Modification

Copyright © 2016 Ramez Elmasri and Shamkant B. Navathe Slide 6- 1

Chapter 7 Outline
 More Complex SQL Retrieval Queries
 Specifying Semantic Constraints as Assertions
and Actions as Triggers
 Views (Virtual Tables) in SQL
 Schema Modification in SQL

Copyright © 2016 Ramez Elmasri and Shamkant B. Navathe Slide 7- 3

More Complex SQL Retrieval
Queries
 Additional features allow users to specify more
complex retrievals from database:
 Nested queries, joined tables, and outer joins (in
the FROM clause), aggregate functions, and
grouping

Copyright © 2016 Ramez Elmasri and Shamkant B. Navathe Slide 7- 4

Comparisons Involving NULL
and Three-Valued Logic
 Meanings of NULL
 Unknown value
 Unavailable or withheld value
 Not applicable attribute
 Each individual NULL value considered to be
different from every other NULL value
 SQL uses a three-valued logic:
 TRUE, FALSE, and UNKNOWN (like Maybe)
 NULL = NULL comparison is avoided

Copyright © 2016 Ramez Elmasri and Shamkant B. Navathe Slide 7- 5

Comparisons Involving NULL
and Three-Valued Logic (cont.)
 The SQL NULL value basically means “could be anything”. It is
therefore impossible to tell whether a comparison to NULL is true or
false. That’s where the third logical value, unknown, comes in.
Unknown means “true or false, depending on the NULL values”.

 The result of each of the following comparisons is therefore unknown:

 NULL = 1
 NULL <> 1
 NULL > 1
 NULL = NULL
 Nothing equals null. Not even null equals null because each null
could be different.

Copyright © 2016 Ramez Elmasri and Shamkant B. Navathe Slide 7- 5

Comparisons Involving NULL
and Three-Valued Logic (cont’d.)

Copyright © 2016 Ramez Elmasri and Shamkant B. Navathe Slide 7- 6

Comparisons Involving NULL
and Three-Valued Logic (cont’d.)
 SQL allows queries that check whether an
attribute value is NULL
 IS or IS NOT NULL

Copyright © 2016 Ramez Elmasri and Shamkant B. Navathe Slide 7- 7

Nested Queries, Tuples,
and Set/Multiset Comparisons
 Nested queries
 Complete select-from-where blocks within WHERE
clause of another query
 Outer query and nested subqueries
 Comparison operator IN
 Compares value v with a set (or multiset) of values
V
 Evaluates to TRUE if v is one of the elements in V

Copyright © 2016 Ramez Elmasri and Shamkant B. Navathe Slide 7- 8

Nested Queries (cont’d.)
In Q4A, the first nested query selects the project numbers of projects that have an
employee with last name ‘Smith’ involved as manager,

the second nested query selects the project numbers of projects that have an employee
with last name ‘Smith’ involved as worker.

In the outer query, we use the OR logical connective to retrieve a

PROJECT tuple if the PNUMBER value of that tuple is in the result of either nested query

Copyright © 2016 Ramez Elmasri and Shamkant B. Navathe Slide 7- 9

Nested Queries (cont’d.)
 Use tuples of values in comparisons
 Place them within parentheses

This query will select the Essns of all employees who work the same
(project, hours) combination on some project that employee ‘John Smith’
(whose Ssn = ‘123456789’) works on

Copyright © 2016 Ramez Elmasri and Shamkant B. Navathe Slide 7- 10

Nested Queries (cont’d.)
 Use other comparison operators to compare a
single value v
 = ANY (or = SOME) operator
 Returns TRUE if the value v is equal to some value in
the set V and is hence equivalent to IN
 Other operators that can be combined with ANY (or
SOME): >, >=, <, <=, and <>
 ALL: value must exceed all values from nested
query

Copyright © 2016 Ramez Elmasri and Shamkant B. Navathe Slide 7- 11

Nested Queries (cont’d.)
 Avoid potential errors and ambiguities
 Create tuple variables (aliases) for all tables
referenced in SQL query

Copyright © 2016 Ramez Elmasri and Shamkant B. Navathe Slide 7- 12

Correlated Nested Queries
 Whenever a condition in the WHERE clause of a nested query
references some attribute of a relation declared in the outer
query, the two queries are said to be correlated.

 We can understand a correlated query better by considering that

the nested query is evaluated once for each tuple (or
combination of tuples) in the outer query.

 For example, we can think of Q16 as follows: For each

EMPLOYEE tuple, evaluate the nested query, which retrieves the
Essn values for all DEPENDENT tuples with the same sex and
name as that EMPLOYEE tuple; if the Ssn value of the
EMPLOYEE tuple is in the result of the nested query, then select
that EMPLOYEE tuple.

Copyright © 2016 Ramez Elmasri and Shamkant B. Navathe Slide 7- 13

Correlated Nested Queries
 Queries that are nested using the = or IN
comparison operator can be collapsed into one
single block: E.g., Q16 can be written as:

 Q16A: SELECTE.Fname, E.Lname

FROM EMPLOYEE AS E, DEPENDENT AS D
WHERE E.Ssn=D.Essn AND E.Sex=D.Sex
AND
E.Fname=D.Dependent_name;

Copyright © 2016 Ramez Elmasri and Shamkant B. Navathe Slide 7- 13

The EXISTS and UNIQUE Functions
in SQL for correlating queries
 EXISTS function
 Check whether the result of a correlated nested
query is empty or not. They are Boolean functions
that return a TRUE or FALSE result.
 EXISTS and NOT EXISTS
 Typically used in conjunction with a correlated
nested query
 SQL function UNIQUE(Q)
 Returns TRUE if there are no duplicate tuples in
the result of query Q

Copyright © 2016 Ramez Elmasri and Shamkant B. Navathe Slide 7- 14

USE of EXISTS
Q7: List the names of managers who have at least one dependent

SELECT Fname, Lname

FROM Employee
WHERE EXISTS (SELECT *
FROM DEPENDENT
WHERE Ssn= Essn)

AND EXISTS (SELECT *

FROM Department
WHERE Ssn= Mgr_Ssn)

One way to write this query is shown in Q7, where we specify two nested correlated
queries; the first selects all DEPENDENT tuples related to an EMPLOYEE, and the
second selects all DEPARTMENT tuples managed by the EMPLOYEE. If at least one of
the first and at least one of the second exists, we select the EMPLOYEE tuple

Copyright © 2016 Ramez Elmasri and Shamkant B. Navathe Slide 7- 15

USE OF NOT EXISTS
 Query- Retrieve the names of employees who have no
dependents

Q6: SELECT Fname, Lname

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

In Q6, the correlated nested query retrieves all DEPENDENT

tuples related to a particular EMPLOYEE tuple. If none exist, the
EMPLOYEE tuple is selected because the WHERE-clause
condition will evaluate to TRUE in this case.

Copyright © 2016 Ramez Elmasri and Shamkant B. Navathe Slide 7- 16

Explicit Sets and Renaming of
Attributes in SQL
 Can use explicit set of values in WHERE clause
Q17: SELECT DISTINCT Essn
FROM WORKS_ON
WHERE Pno IN (1, 2, 3);
 Use qualifier AS followed by desired new name
 Rename any attribute that appears in the result of
a query

Copyright © 2016 Ramez Elmasri and Shamkant B. Navathe Slide 7- 18

Specifying Joined Tables in the
FROM Clause of SQL
 Joined table
 Permits users to specify a table resulting from a
join operation in the FROM clause of a query
 The FROM clause in Q1A
 Contains a single joined table. JOIN may also be
called INNER JOIN

Copyright © 2016 Ramez Elmasri and Shamkant B. Navathe Slide 7- 19

Different Types of JOINed Tables in
SQL
 Specify different types of join
 NATURAL 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

Copyright © 2016 Ramez Elmasri and Shamkant B. Navathe Slide 7- 20

NATURAL JOIN
 Rename attributes of one relation so it can be joined with
another using NATURAL JOIN:

Q1B:    SELECT         Fname, Lname, Address

FROM (EMPLOYEE NATURAL JOIN
(DEPARTMENT AS DEPT (Dname, Dno, Mssn,
Msdate)))
WHERE Dname=‘Research’;

The above works with EMPLOYEE.Dno = DEPT.Dno as an

implicit join condition

Copyright © 2016 Ramez Elmasri and Shamkant B. Navathe Slide 7- 21

INNER and OUTER Joins
 INNER JOIN (versus OUTER JOIN)
 Default type of join in a joined table

 Tuple is included in the result only if a matching tuple exists

in the other relation

 LEFT OUTER JOIN
 Every tuple in left table must appear in result

 If no matching tuple

 Padded with NULL values for attributes of right table

 RIGHT OUTER JOIN

 Every tuple in right table must appear in result

 If no matching tuple

 Padded with NULL values for attributes of left table

Copyright © 2016 Ramez Elmasri and Shamkant B. Navathe Slide 7- 22

Different Types of SQL JOINs

Copyright © 2016 Ramez Elmasri and Shamkant B. Navathe Slide 7- 23

Example: LEFT OUTER JOIN and
RIGHT OUTER JOIN

Copyright © 2016 Ramez Elmasri and Shamkant B. Navathe

Multiway JOIN in the FROM clause
 FULL OUTER JOIN – combines result of LEFT
and RIGHT OUTER JOIN
 Can nest JOIN specifications for a multiway join:

Q2A: SELECT Pnumber, Dnum, Lname, Address, Bdate

FROM ((PROJECT JOIN DEPARTMENT ON
Dnum=Dnumber) JOIN EMPLOYEE ON
Mgr_ssn=Ssn)
WHERE Plocation=‘Stafford’;

Copyright © 2016 Ramez Elmasri and Shamkant B. Navathe Slide 7- 25

Aggregate Functions in SQL
 Used to summarize information from multiple
tuples into a single-tuple summary
 Built-in aggregate functions
 COUNT, SUM, MAX, MIN, and AVG
 Grouping
 Create subgroups of tuples before summarizing
 To select entire groups, HAVING clause is used
 Aggregate functions can be used in the SELECT
clause or in a HAVING clause

Copyright © 2016 Ramez Elmasri and Shamkant B. Navathe Slide 7- 26

Renaming Results of Aggregation
 Following query returns a single row of computed values
from EMPLOYEE table:

Q19: SELECT SUM (Salary), MAX (Salary), MIN (Salary), AVG

(Salary)
FROM EMPLOYEE;
 The result can be presented with new names:

Q19A: SELECT SUM (Salary) AS Total_Sal, MAX (Salary) AS

Highest_Sal, MIN (Salary) AS Lowest_Sal, AVG
(Salary) AS Average_Sal
FROM EMPLOYEE;

Copyright © 2016 Ramez Elmasri and Shamkant B. Navathe Slide 7- 27

Aggregate Functions in SQL (cont’d.)
 NULL values are discarded when aggregate
functions are applied to a particular column

Copyright © 2016 Ramez Elmasri and Shamkant B. Navathe Slide 7- 28

Aggregate Functions on Booleans
 SOME and ALL may be applied as functions on
Boolean Values.
 SOME returns true if at least one element in the
collection is TRUE (similar to OR)
 ALL returns true if all of the elements in the
collection are TRUE (similar to AND)

Copyright © 2016 Ramez Elmasri and Shamkant B. Navathe Slide 7- 29

Grouping: The GROUP BY Clause
 Partition relation into subsets of tuples
 Based on grouping attribute(s)
 Apply function to each such group independently
 GROUP BY clause
 Specifies grouping attributes
 COUNT (*) counts the number of rows in the
group

Copyright © 2016 Ramez Elmasri and Shamkant B. Navathe Slide 7- 30

Examples of GROUP BY
 The grouping attribute must appear in the SELECT clause:
Q24: SELECT Dno, COUNT (*), AVG (Salary)
FROM EMPLOYEE
GROUP BYDno;
 If the grouping attribute has NULL as a possible value, then
a separate group is created for the null value (e.g., null
Dno in the above query)
 GROUP BY may be applied to the result of a JOIN:
Q25: SELECT Pnumber, Pname, COUNT (*)
FROM PROJECT, WORKS_ON
WHERE Pnumber=Pno
GROUP BY Pnumber, Pname;

Copyright © 2016 Ramez Elmasri and Shamkant B. Navathe Slide 7- 31

Grouping: The GROUP BY and
HAVING Clauses (cont’d.)
 HAVING clause
 Provides a condition to select or reject an entire
group:
 Query 26. For each project on which more than two employees work,
retrieve the project number, the project name, and the number of
employees who work on the project.

Q26: SELECT Pnumber, Pname, COUNT (*)

FROM PROJECT, WORKS_ON
WHERE Pnumber=Pno
GROUP BYPnumber, Pname
HAVING COUNT (*) > 2;

Copyright © 2016 Ramez Elmasri and Shamkant B. Navathe Slide 7- 31

Results of GROUP BY and HAVING.
(Query 26)

Copyright © 2016 Ramez Elmasri and Shamkant B. Navathe

Combining the WHERE and the
HAVING Clause
 Consider the query: we want to count the total number of
employees whose salaries exceed $40,000 in each
department, but only for departments where more than
five employees work.

 INCORRECT QUERY:
SELECT Dno, COUNT (*)
FROM EMPLOYEE
WHERE Salary>40000
GROUP BYDno
HAVING COUNT (*) > 5;

Copyright © 2016 Ramez Elmasri and Shamkant B. Navathe Slide 7- 34

Combining the WHERE and the
HAVING Clause
 The rule is that the WHERE clause is
executed first, to select individual tuples or joined
tuples; the HAVING clause is applied later, to
select individual groups of tuples.

 In the incorrect query, the tuples are already

restricted to employees who earn more than
$40,000 before the function in the HAVING
clause is applied

Copyright © 2016 Ramez Elmasri and Shamkant B. Navathe Slide 7- 35

Combining the WHERE and the
HAVING Clause (continued)
Correct Specification of the Query:
 Note: the WHERE clause applies tuple by tuple whereas

HAVING applies to entire group of tuples

 Query 28. For each department that has more than five employees, retrieve the
department number and the number of its employees who are making more than
$40,000.
SELECT Dno, COUNT (*)
FROM EMPLOYEE
WHERE Salary>40000 AND Dno IN
(SELECT Dno
FROM EMPLOYEE
GROUP BY Dno
HAVING COUNT (*) > 5)
GROUP BY Dno;

Copyright © 2016 Ramez Elmasri and Shamkant B. Navathe Slide 7- 36

EXPANDED Block Structure of SQL
Queries

Copyright © 2016 Ramez Elmasri and Shamkant B. Navathe Slide 7- 40

Schema Change Statements in SQL
 Schema evolution commands
 DBA may want to change the schema while the
database is operational
 Does not require recompilation of the database
schema

Copyright © 2016 Ramez Elmasri and Shamkant B. Navathe Slide 7- 54

The DROP Command
 DROP command
 Used to drop named schema elements, such as
tables, domains, or constraint
 Drop behavior options:
 CASCADE and RESTRICT
 Example:
 DROP SCHEMA COMPANY CASCADE;
 This removes the schema and all its elements
including tables, views, constraints, etc.

Copyright © 2016 Ramez Elmasri and Shamkant B. Navathe Slide 7- 55

The ALTER table command
 Alter table actions include:
 Adding or dropping a column (attribute)
 Changing a column definition
 Adding or dropping table constraints
 Example:
 ALTER TABLE COMPANY.EMPLOYEE ADD
COLUMN Job VARCHAR(12);

Copyright © 2016 Ramez Elmasri and Shamkant B. Navathe Slide 7- 56

Adding and Dropping Constraints
 Change constraints specified on a table
 Add or drop a named constraint

Copyright © 2016 Ramez Elmasri and Shamkant B. Navathe Slide 7- 57

Dropping Columns, Default Values
 To drop a column
 Choose either CASCADE or RESTRICT
 CASCADE would drop the column from views etc.
RESTRICT is possible if no views refer to it.
ALTER TABLE COMPANY.EMPLOYEE DROP COLUMN Address
CASCADE;
 Default values can be dropped and altered :
ALTER TABLE COMPANY.DEPARTMENT ALTER COLUMN Mgr_ssn
DROP DEFAULT;
ALTER TABLE COMPANY.DEPARTMENT ALTER COLUMN Mgr_ssn SET
DEFAULT ‘333445555’;

Copyright © 2016 Ramez Elmasri and Shamkant B. Navathe Slide 7- 42

Views (Virtual Tables) in SQL
 Concept of a view in SQL
 Single table derived from other tables called the
defining tables
 Considered to be a virtual table that is not
necessarily populated

Copyright © 2016 Ramez Elmasri and Shamkant B. Navathe Slide 7- 45

Specification of Views in SQL
 CREATE VIEW command
 Give table name, list of attribute names, and a query to
specify the contents of the view
 In V1, attributes retain the names from base tables. In
V2, attributes are assigned names

Copyright © 2016 Ramez Elmasri and Shamkant B. Navathe Slide 7- 46

Specification of Views in SQL
(cont’d.)
 Once a View is defined, SQL queries can use the
View relation in the FROM clause
 View is always up-to-date
 Responsibility of the DBMS and not the user
 DROP VIEW command
 Dispose of a view

Copyright © 2016 Ramez Elmasri and Shamkant B. Navathe Slide 7- 47

View Update
 Update on a view defined on a single table without any
aggregate functions
 Can be mapped to an update on underlying base table-

possible if the primary key is preserved in the view

 Update not permitted on aggregate views. E.g.,
UV2: UPDATE DEPT_INFO
SET Total_sal=100000
WHERE Dname=‘Research’;
cannot be processed because Total_sal is a computed value
in the view definition
 View involving joins

 Often not possible for DBMS to determine which of the

updates is intended
Copyright © 2016 Ramez Elmasri and Shamkant B. Navathe Slide 7- 51
View Update

 In summary, we can make the following

observations:
 A view with a single defining table is updatable

if the view attributes contain the primary key of

the base relation, as well as all attributes with
the NOT NULL constraint that do not have
default values specified.
 Views defined on multiple tables using joins are
generally not updatable.
 Views defined using grouping and aggregate
functions are not updatable.
Copyright © 2016 Ramez Elmasri and Shamkant B. Navathe
Specifying Constraints as Assertions
and Actions as Triggers
 Semantic Constraints: The following are beyond
the scope of the relational model
 CREATE ASSERTION
 Specify additional types of constraints outside
scope of built-in relational model constraints
 CREATE TRIGGER
 Specify automatic actions that database system
will perform when certain events and conditions
occur

Copyright © 2016 Ramez Elmasri and Shamkant B. Navathe Slide 7- 41

Specifying General Constraints as
Assertions in SQL
 CREATE ASSERTION
 Specify a query that selects any tuples that violate
the desired condition
 Use only in cases where it goes beyond a simple
CHECK which applies to individual attributes and
domains

Copyright © 2016 Ramez Elmasri and Shamkant B. Navathe Slide 7- 49

Introduction to Triggers in SQL
 CREATE TRIGGER statement
 Used to monitor the database
 Typical trigger has three components which make
it a rule for an “active database “ (more on active
databases in section 26.1) :
 Event(s)
 Condition
 Action

Copyright © 2016 Ramez Elmasri and Shamkant B. Navathe Slide 7- 43

Triggers Syntax
 create trigger [trigger_name]
[before | after]
{insert | update | delete}
on [table_name]
[for each row]
[trigger_body]

Copyright © 2016 Ramez Elmasri and Shamkant B. Navathe

USE OF TRIGGERS
 AN EXAMPLE with standard Syntax.(Note : other
SQL implementations like PostgreSQL use a
different syntax.)

R5:
CREATE TRIGGER SALARY_VIOLATION
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)

Copyright © 2016 Ramez Elmasri and Shamkant B. Navathe Slide 7- 44

Table 7.2 Summary of SQL
Syntax

continued on next slide

Copyright © 2016 Ramez Elmasri and Shamkant B. Navathe Slide 7- 59

Table 7.2 (continued)
Summary of SQL Syntax

Copyright © 2016 Ramez Elmasri and Shamkant B. Navathe Slide 7- 60

Summary
 Complex SQL:
 Nested queries, joined tables (in the FROM
clause), outer joins, aggregate functions, grouping
 Handling semantic constraints with CREATE
ASSERTION and CREATE TRIGGER
 CREATE VIEW statement
 Schema Modification for the DBAs using ALTER
TABLE , ADD and DROP COLUMN, ALTER
CONSTRAINT etc.

Copyright © 2016 Ramez Elmasri and Shamkant B. Navathe Slide 7- 61