CHAPTER 7

More SQL: Complex Queries,

Triggers, Views, and Schema
Modification

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

Announcement
■ Quiz 2 will be held on the following date and time
■ November 20th (Thursday)
■ During class time
■ Chapter 6 and 7

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

Chapter 7 Outline
■ More Complex SQL Retrieval Queries
■ Actions as Triggers
■ Views (Virtual Tables) 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 (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 (Sub Queries)

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

 Nested Queries (cont’d.)
■ Find the names of all employees in the Sales department

employees departments

How can we find an employee name for the department

name “Sales”?

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

 Nested Queries (using IN)
■ Find the names of all employees in the Sales department

employees departments

tempField

11
Example of Comparison operator IN
12

7
Copyright © 2016 Ramez Elmasri and Shamkant B. Navathe Slide 7- 9
 Nested Queries (cont’d.)
■ Find the names of all employees in the Sales department

employees departments

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

Nested Queries (using ANY)
■ 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 <>

SELECT * FROM employees

WHERE dept_id = ANY ( SELECT D.dept_id FROM Department D
WHERE dept_name = ‘Finance' OR dept_name = 'Sales’)

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

Nested Queries (using ALL)
■ Find all the name of employees whose salary is
more than the employees in department 5

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

Nested Queries (using ALL)
■ ALL: value must exceed all values from nested
query

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

The EXISTS and UNIQUE Functions
in SQL for correlating queries
■ EXISTS and NOT EXISTS
■ Check whether the result of a correlated nested
query is empty or not. They are Boolean functions
that return a TRUE or FALSE result.

■ 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: Find all employee names who is a department manager

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

USE of EXISTS

Q7: List first and last name of employees who is a department manager

SELECT Fname, Lname

FROM Employee E
WHERE EXISTS (SELECT *
FROM Department D
WHERE E.Ssn = D.Mgr_ssn)

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

USE of NOT EXISTS

Q7: List first and last name of employees who is not a department manager

SELECT Fname, Lname

FROM Employee E
WHERE NOT EXISTS (SELECT *
FROM Department D
WHERE E.Ssn = D.Mgr_ssn)

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

Specifying Joined Tables in the
FROM Clause of SQL
■ Joined table
■ A Join clause is used to combine rows from two or
more tables, based on the related column between
them
■ Permits users to specify a table resulting from a
join operation in the FROM clause of a query

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

 Specifying Joined Tables in the
FROM Clause of SQL

NATURAL JOIN on two relations

R and S
Student
No join condition specified

SELECT *
FROM Student NATURAL JOIN Student_Marks;

Student_Marks Output:

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

 Specifying Joined Tables in the
FROM Clause of SQL
JOIN may also be called INNER JOIN

Student

SELECT *
FROM Student S INNER JOIN Student_Marks M
ON S.Roll_No = M.Roll_No;
Student_Marks
Output:

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

Specifying Joined Tables in the
FROM Clause of SQL
■ Various types of OUTER JOIN (LEFT, RIGHT,
FULL )

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

Aggregate Functions (cont.)

From all the Employees, show the Sum of salaries,

maximum salary, minimum salary and average salary.

Copyright © 2016 Ramez Elmasri and Shamkant B. Navathe

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

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

(Salary)
FROM EMPLOYEE;

Output:

SUM(Salary) MAX(Salary) MIN(Salary) AVG(Salary)

281000 55000 25000 35125

Copyright © 2016 Ramez Elmasri and Shamkant B. Navathe

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

The result can be presented with new names:

SELECT SUM (Salary) AS Total_Sal, MAX (Salary) AS
Highest_Sal, MIN (Salary) AS Lowest_Sal, AVG (Salary) AS
Average_Sal
FROM EMPLOYEE;

Output:
Total_Sal Highest_Sal Lowest_Sal Average_Sal
281000 55000 25000 35125

Copyright © 2016 Ramez Elmasri and Shamkant B. Navathe

EXPANDED Block Structure of SQL
Queries

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

Examples of GROUP BY (single column)
■ Find all the names and their salary grouped by employee
names

Employee
emp_ID name salary age

1 John 25000 25

2 Bob 40000 30

3 Adam 30000 26

4 Sandra 40000 29

5 John 32000 27

Copyright © 2016 Ramez Elmasri and Shamkant B. Navathe

Examples of GROUP BY (single column)
■ Find all the names and their salary grouped by employee
names

Employee SELECT name, SUM(salary)

emp_ID name salary age FROM emp
GROUP BY name;
1 John 25000 25
Output:
2 Bob 40000 30
name SUM (sal)
3 Adam 30000 26 John 57000
4 Sandra 40000 29 Bob 40000

5 John 32000 27 Adam 30000

Sandra 40000

■ MySQL or PostgreSQL do not automatically sort.

Microsoft SQL Server automatically sort the grouped
result.
Copyright © 2016 Ramez Elmasri and Shamkant B. Navathe
Examples of GROUP BY (multiple columns)
■ Find the total number of courses by subject and year

Student
name year subject

Alice 1 Math

Bob 2 English

Charlie 3 Science

David 1 Math

Emily 2 English

Frank 3 Science

Copyright © 2016 Ramez Elmasri and Shamkant B. Navathe

Examples of GROUP BY (multiple columns)
■ Find the total number of courses by subject and year

Student
SELECT subject, year, COUNT(*)
name year subject
FROM Student
Alice 1 Math GROUP BY subject, year;
Bob 2 English
Output:
Charlie 3 Science
subject year COUNT(*)
David 1 Math
English 2 2
Emily 2 English Math 1 2

Frank 3 Science Science 3 2

Copyright © 2016 Ramez Elmasri and Shamkant B. Navathe

Examples of GROUP BY (NULL)
■ Find all departments that the employee belong to.

Employee SELECT dept

emp_ID name salary dept FROM Employee
GROUP BY dept;
1 John 25000 IT

2 Bob 40000 NULL Output:

3 Adam 30000 ADMIN dept

IT
4 Sandra 40000 IT
Second row is empty
5 John 32000 NULL
ADMIN

Copyright © 2016 Ramez Elmasri and Shamkant B. Navathe

Examples of GROUP BY (NULL)
■ Find how many employees are in each department

SELECT dept, COUNT(*)

Employee FROM Employee
emp_ID name salary dept
GROUP BY dept
ORDER BY dept;
1 John 25000 IT

2 Bob 40000 NULL Output:

3 Adam 30000 ADMIN dept COUNT(*)

2 2 NULLs
4 Sandra 40000 IT
ADMIN 1
5 John 32000 NULL
IT 2

Copyright © 2016 Ramez Elmasri and Shamkant B. Navathe

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.

Copyright © 2016 Ramez Elmasri and Shamkant B. Navathe

Grouping: The GROUP BY and
HAVING Clauses (cont’d.)
■ HAVING clause

■ 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

Introduction to Triggers in SQL
■ CREATE TRIGGER statement
■ Used to monitor the database
■ Convenient to specify the type of action to be
taken when certain events occur and when certain
conditions are satisfied
■ Typical trigger has three components
■ Event(s)
■ Condition
■ Action

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

Introduction to Triggers in SQL
■ CREATE TRIGGER statement
■ Used to monitor the database
■ We can set triggers to fire
■ BEFORE or AFTER an
■ INSERT, UPDATE, or DELETE operation

■ Trigger generic format -

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

Example Trigger

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

Creating a Trigger
■ Consider a scenario where you want to log changes
in this employees table.
■ You might create a trigger like this:

This example creates a trigger that logs updates made to

the employees table by inserting the old employee data
into an employees_log table whenever an update occurs.
Copyright © 2016 Ramez Elmasri and Shamkant B. Navathe Slide 7- 36
Modifying or Deleting a Trigger
■ To modify a trigger, you can drop the existing one and
create a new one with the desired changes.
■ Here’s how you can do it:

Delete a Trigger:

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

Tracking Deleted Row using Trigger
Id name position

1 John Project Manager

2 Bob Software Engineer

CREATE TABLE deletion_log (

id INT AUTO_INCREMENT PRIMARY KEY,
employee_id INT,
deleted_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
old_name VARCHAR(100),
old_position VARCHAR(100)
);

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

 Tracking Deleted Row using Trigger
After Delete Trigger:

CREATE TRIGGER after_employee_delete

AFTER DELETE ON employees
FOR EACH ROW
BEGIN
INSERT INTO deletion_log (employee_id, old_name, old_position)
VALUES (OLD.id, OLD.name, OLD.position);
END
id employee_id deleted_at old_name old_position
deletion_log
1 2 2025-03-18 12:30:26 Bob Software Engineer
Copyright © 2016 Ramez Elmasri and Shamkant B. Navathe Slide 7- 39
VIEW in SQL
■ Concept of a view in SQL
■ Single table (virtual) derived from other tables

(Virtual table)

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

 Specification of Views in SQL
■ CREATE VIEW command
■ Give table name, list of V1
attribute names, and a
query to specify the V2
contents of the view

■ In V1, attributes retain the

Generated Views V1 and V2
names from base tables -
■ In V2, attributes are
assigned names

Copyright © 2016 Ramez Elmasri and Shamkant B. Navathe

Specification of Views in SQL
(cont’d.)
■ Once a View is defined, SQL queries can use the
View relation in the FROM clause
Querying the View WORKS_ON1 in QV1
QV1: SELECT Fname, Lname
FROM WORKS_ON1
WHERE Pname = ‘ProductX’

• View using Join:

■ DROP VIEW command

■ DROP VIEW WORKS_ON1;
Copyright © 2016 Ramez Elmasri and Shamkant B. Navathe
Views as authorization mechanism
■ Views can be used to hide certain attributes or
tuples from unauthorized users

■ E.g., For a user who is only allowed to see

employee information who work for department 5,
he may only access the following view DEPT5EMP
CREATE VIEW DEPT5EMP AS
SELECT *
FROM EMPLOYEE
WHERE Dno = 5;

Copyright © 2016 Ramez Elmasri and Shamkant B. Navathe