SQL QUERIES

Retrieve the name of all employees who earn more than

the Average Salary

SELECT FNAME, LNAME

FROM EMPLOYEE
WHERE Salary > AVG(SALARY)
NESTED QUERIES
A complete SELECT query, called a nested query , can be
specified within the WHERE-clause of another query, called the
outer query

Retrieve the name of all employees who earn more than

the Average Salary
SELECT FNAME, LNAME
FROM EMPLOYEE
WHERE Salary > (SELECT AVG(SALARY)
FROM EMPLOYEE)
 NESTING OF QUERIES
A complete SELECT query, called a nested query , can be specified
within the WHERE-clause of another query
Retrieve the name of all employees who work for the
'Research' department.
SELECT FNAME, LNAME
FROM EMPLOYEE
WHERE DNO = (SELECT DNUMBER
FROM DEPARTMENT
WHERE DNAME='Research' )

If `=` is used the inner query must return one value

If more than one value is returned then an error msg is generated
 NESTING OF QUERIES
Retrieve the name of all employees who work for the 'Research’ or
Administration ‘department .
SELECT FNAME, LNAME
FROM EMPLOYEE
WHERE DNO IN (SELECT DNUMBER
FROM DEPARTMENT
WHERE DNAME='Research’ OR
DNAME=‘Administration’ )

If `=` is used the inner query must return one value.

If inner query returns more than one value then use IN
 NESTING OF QUERIES
Retrieve the name of all employees who do not work
for the 'Research' department.
SELECT FNAME, LNAME
FROM EMPLOYEE
WHERE DNO NOT IN (SELECT DNUMBER
FROM DEPARTMENT
WHERE DNAME='Research' )
NESTED QUERIES
You can also use: s > ALL R (means greater than every value)
s > ANY R (means greater than any value )
= ANY is same as IN , <>ALL is same as NOT IN

Find name of employees whose salary is greater than the

salary of all employees in department 5

SELECT Fname
FROM Employee
WHERE Salary > ALL (SELECT Salary
FROM Employee
where Dno=5)
NESTED QUERIES & TSQL
A subquery can be nested inside the WHERE or HAVING
clause of an outer SELECT, INSERT, UPDATE, or DELETE
statement.

Statements with subquery usually take one of these formats:

•WHERE expression [NOT] IN (subquery)
•WHERE expression comparison_operator [ANY | ALL] (subquery)
•WHERE [NOT] EXISTS (subquery)

comparison_operator { = | <> | != | > | >= | !> | < | <= | !< }

• Up to 32 levels of nesting is possible,

• This limit depends on available memory and the Slide
8-74
complexity of other expressions in the query.
Why NESTED QUERIES ?
 Many Transact-SQL statements that include subqueries can
be alternatively formulated as joins.

 Other questions can be posed only with subqueries.

 An aggregate may not appear in the WHERE clause

 unless it is in a subquery contained in a HAVING clause or
a select list, and the column being aggregated is an outer
reference

Slide
8-75
 CORRELATED NESTED QUERIES
 If a condition in the nested query references an attribute of a
relation declared in the outer query =>
 Then two queries are said to be correlated
Retrieve the name of each employee who has a dependent with
the same first name as the employee.

Slide
8-76
Nested Correlated query is evaluated once for each tuple in outer query
CORRELATED NESTED QUERIES
A correlated subquery (also called repeating subquery) depends on
the outer query for its values.
• This means that the nested subquery is executed repeatedly, once
for each row that might be selected by the outer query.
Retrieve the name of each employee who has a dependent with
the same first name as the employee.
SELECT E.FNAME, E.LNAME
FROM EMPLOYEE AS E
WHERE E.SSN IN (SELECT ESSN FROM DEPENDENT AS D
WHERE E.SSN = D.ESSN AND FNAME=DEPENDENT_NAME)

Slide
8-77
 NESTED QUERIES
 A query written with nested SELECT... FROM... WHERE... blocks
and using the = or IN comparison operators can always be
expressed as a single block query.

Retrieve the name of each employee who has a dependent

with the same first name as the employee.

SELECT E.FNAME, E.LNAME

FROM EMPLOYEE E, DEPENDENT D
WHERE E.SSN=D.ESSN AND
E.FNAME=D.DEPENDENT_NAME
Slide
8-78
 EXISTS FUNCTION
EXISTS Function checks whether the result of a nested query
is empty or not

 Retrieve the name of each employee who has a dependent with

the same first name as the employee.

SELECT FNAME, LNAME

FROM EMPLOYEE
WHERE EXISTS (SELECT *
FROM DEPENDENT
WHERE SSN=ESSN AND
FNAME=DEPENDENT_NAME)
Slide
8-80
EXISTS FUNCTION
Retrieve the names of employees who have no dependents.
SELECT FNAME, LNAME
FROM EMPLOYEE
WHERE NOT EXISTS (SELECT *
FROM DEPENDENT
WHERE SSN=ESSN)
EXISTS is necessary for the expressive power of SQL

The above correlated nested query retrieves all DEPENDENTSlide

8-81
tuples related to an EMPLOYEE tuple.
If none exist , the EMPLOYEE tuple is selected
EXISTS FUNCTION
Find the names of managers who have at least one dependents.

SELECT FNAME, LNAME

FROM EMPLOYEE
WHERE EXISTS (SELECT *
FROM DEPENDENT
WHERE SSN=ESSN)
AND
EXISTS (SELECT *
FROM DEPARTMENT
WHERE SSN=Mgr_SSN)

Slide
8-82
EXISTS FUNCTION
Retrieve the name of each employee who works on all the projects
controlled by department number 4.

Set theory: S1 contains S2 if (S2 – S1 = 0)

SELECT FNAME, LNAME

FROM EMPLOYEE S1 = set of projects of
WHERE NOT EXISTS ( each employee
(SELECT PNUMBER
FROM PROJECT S2 = set of dept 4
WHERE DNUM=4) projects

EXCEPT

(SELECT PNO
FROM WORKS_ON Slide
WHERE SSN=ESSN) 8-83
)
NESTED CORRELATED QUERIES
You can also use: s > ALL R
s > ANY R
EXISTS R
Find Employee whose salary is greater than the salary
of all employee in department 5

SELECT Fname
FROM Employee
WHERE Salary > ALL (SELECT Salary
FROM Employee
where Dno=5)
 Complex Correlated Query
Find Employees (dno and salary) whose salary is greater than the
salaries of all employees in his department

SELECT Fname, Salary, Dno

FROM Employee as E
WHERE Salary > ALL (SELECT Salary
FROM Employee as S
WHERE E.dno=S.dno and E.ssn !=S.ssn )
Nested queries
 Find the second highest salary
SELECT MAX(Salary)
FROM Employee
WHERE Salary NOT IN (
SELECT MAX(Salary)
FROM Employee )

Slide
8-87
CORRELATED NESTED QUERIES
 Find the third highest salary

SELECT *
FROM Employee E1
WHERE (N-1) = (
SELECT COUNT(DISTINCT(E2.Salary))
FROM Employee E2
WHERE E2.Salary > E1.Salary)

Slide
8-88
 Find names of the
departments such that all their
SELECT Dname employees have salary >30000
FROM Employee, Department
WHERE dno= dnumber Find names of the departments
GROUP BY Dnumber, Dname that have all employees with
HAVING 30000 < min(Salary) salary >30000

SELECT Dname
FROM Department
WHERE 300000 < ALL (SELECT Salary
FROM Employee
Almost equivalent… WHERE dno= dnumber)
SELECT Dname
FROM Department
WHERE Dnumber NOT IN (SELECT Dno
FROM Employee
WHERE Salary<= 300000)
 Group By and Having
 Count the number of employees whose salaries exceed
$30,000 in each department.
 Consider only the departments with more than five
employees.

 SELECT Dname, COUNT (*)

FROM DEPARTMENT, EMPLOYEE
WHERE Dnumber=Dno AND Salary>30000
GROUP BY Dname
HAVING COUNT (*) > 5;
Slide
8-90
 Group By and Having
 Count the total number of employees whose salaries
exceed $30,000 in each department, but only for
departments where more than five employees work

 SELECT Dno, COUNT (*) No_of_Employees

FROM EMPLOYEE
WHERE salary > 30000 and DNO IN
(SELECT Dno
FROM EMPLOYEE
GROUP BY Dno
HAVING COUNT (*) > 5)
Slide
Group by DNO 8-91
 Summary of SQL Queries
 A query in SQL can consist of up to six clauses, but only the
first two, SELECT and FROM, are mandatory. The clauses are
specified in the following order:

SELECT <attribute list>

FROM <table list>
[WHERE <condition>]
[GROUP BY <grouping attribute(s)>]
[HAVING <group condition>]
[ORDER BY <attribute list>]

 A query is evaluated by first applying the WHERE-clause,

then GROUP BY and HAVING, and finally the SELECT-clause
Slide
8-92

https://docs.microsoft.com/en-us/sql/relational-databases/performance/subqueries?view=sql-server-2017
 Performance of NESTED QUERIES in
TSQL

In T-SQL, there is usually no performance difference between a

statement that includes a subquery and a semantically equivalent
version that does not.

In some cases where existence must be checked, a join

yields better performance.

• Otherwise, the nested query must be processed for each

result of the outer query to ensure elimination of duplicates.
In such cases, a join approach would yield better results.
Slide
https://docs.microsoft.com/en-us/sql/relational- 8-93
databases/performance/subqueries?view=sql-server-2017
SQL Queries
 There are various ways to specify the same query in SQL
 This is to give flexibility to user to specify queries

 For query optimization, it is preferable to write a query

with as little nesting and implied ordering as possible.

 Ideally, DBMS should process the same query in the

same way regardless of how the query is specified.
 But this is quite difficult in practice, (chapter 19,20)

Slide
8-95
Specifying Updates in SQL
There are three SQL commands to modify the database;
• INSERT,
• DELETE, and
• UPDATE

Example:

INSERT INTO EMPLOYEE

VALUES ('Richard','K','Marini', '653298653', '30-DEC-52',
'98 Oak Forest,Katy,TX', 'M', 37000,'987654321', 4 )
Slide
8-96
INSERT WITH QUERY

 Suppose we want to create a temporary table that has the

name, number of employees, and total salaries for each
department.
 A table DEPTS_INFO is created by Q1, and is loaded with the
information retrieved from the database by the query Q2.

 Q1: CREATE TABLE DEPTS_INFO

(D_NAME VARCHAR(10),
NO_OF_EMPS INTEGER,
TOTAL_SAL INTEGER);

Q2: INSERT INTO DEPTS_INFO (D_NAME, NO_OF_EMPS, TOTAL_SAL)
SELECT DNAME, COUNT (*), SUM (SALARY)
FROM DEPARTMENT, EMPLOYEE
WHERE DNUMBER=DNO
GROUP BY DNAME ; Slide
8-99
 DELETE
 Removes tuples from a relation
 Tuples are deleted from only one table at a time (unless
CASCADE is specified on a referential integrity constraint)
 Examples:
DELETE FROM EMPLOYEE
WHERE LNAME='Brown’

DELETE FROM EMPLOYEE

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

DELETE FROM EMPLOYEE Slide

8-100
 UPDATE
 Used to modify attribute values of selected tuples
 Example: 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

Slide
8-101
UPDATE (cont.)
 Example: Give all employees in the 'Research' department a
10% raise in salary.
UPDATE EMPLOYEE
SET SALARY = SALARY *1.1
WHERE DNO IN (SELECT DNUMBER
FROM DEPARTMENT
WHERE DNAME='Research')

Slide
8-102
 Views in SQL
A view is a “virtual” table that is derived from other
tables
 Allows for limited update operations (since the
table may not physically be stored)
 Allows full query operations

 A convenience for expressing certain operations

 simplify complex queries, and
• define distinct conceptual interfaces for different users.

Chapter 9-109
SQL Views: An Example

CREATE VIEW WORKS_ON1 AS

SELECT FNAME, LNAME, PNAME, HOURS
FROM EMPLOYEE, PROJECT, WORKS_ON
WHERE SSN=ESSN AND PNO=PNUMBER

Chapter 9-110
SQL Views: An Example2

Slide
8-111
Query using a Virtual Table

 We can specify SQL queries on a newly created view:

SELECT FNAME, LNAME
FROM WORKS_ON1
WHERE PNAME=‘ProductX’;

 DBMS is responsible to keep view always up-to-date

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

DROP WORKS_ON1;

Chapter 9-112
 Efficient View Implementation
Query modification: present the view query in terms of a query
on the underlying base tables

SELECT FNAME, LNAME

FROM WORKS_ON1
WHERE PNAME=‘ProductX’
SELECT FNAME, LNAME
FROM (EMPLOYEE JOIN PROJECT on SSN=ESSN ) JOIN
WORKS_ON on PNO=PNUMBER
WHERE PNAME=‘PRODUCTX’

Disadvantage:
Inefficient for views defined via complex queries
Esp. if additional queries are to be applied within a short time period
Chapter 9-113
Efficient View Implementation

View materialization: involves physically

creating and keeping a temporary table
• assumption: other queries on the view will follow
• concerns: maintaining correspondence between
the base table and the view when the base table
is updated
• strategy: incremental update

Chapter 9-114
 View Update

Single view without aggregate operations:

• update may map to an update on the underlying base
table

Views involving joins:

• an update may map to an update on the underlying
base relations
• not always possible

Chapter 9-115
EXAMPLE – Complex View Update

 Example:
UPDATE WORKS_ON1
SET PNAME=COMPUTERIZATION
WHERE FNAME=‘JOHN AND
LNAME='SMITH' AND
PNAME=‘PRODUCTX’

Chapter 9-116
 EXAMPLE – Complex View Update
UPDATE WORKS_ON1
SET PNAME=COMPUTERIZATION
WHERE FNAME=‘JOHN AND LNAME='SMITH' AND PNAME=‘PRODUCTX’

A) UPDATE PROJECT
SET PNAME=‘COMPUTERIZATION’
WHERE PNAME=‘PRODUCTX’

Chapter 9-117
View Update
UPDATE WORKS_ON1
SET PNAME=COMPUTERIZATION
WHERE FNAME=‘JOHN AND LNAME='SMITH' AND PNAME=‘PRODUCTX’

 B)UPDATE WORKS_ON
SET PNO = (SELECT PNUMBER
FROM PROJECT
WHERE PNAME=‘COMPUTERIZATION’)
WHERE ESSN IN (SELECT SSN
FROM EMPLOYEE
WHERE LNAME=‘SMITH’ AND FNAME=‘JOHN’)
AND
Slide
PNO = (SELECT PNUMBER FROM PROJECT 8-118
WHERE PNAME=‘PRODUCTX’)
Un-updatable Views
 Views defined using groups and aggregate functions are
not updateable

 Views defined on multiple tables using joins are generally not

updateable

Chapter 9-119
 SQL Triggers
Triggers monitors a database and executes when an event
occurs in the database server.
• like insertion,
• deletion or
• updation of data.
It is a database object which is bound to a table and is
executed automatically.

You can’t explicitly invoke triggers.

• The only way to do this is by performing the required action

on the table that they are assigned to.
Chapter 9-120
SQL Triggers
Objective: to monitor a database and take action
when a condition occurs

Triggers include the following:

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

Triggers are classified into two main types:

• After Triggers (For Triggers)
• Instead Of Triggers
Chapter 9-122
 SQL Triggers: An Example
Using a trigger with a reminder message

CREATE TRIGGER reminder1

ON Employee
AFTER INSERT, UPDATE
AS PRINT 'Notify employee added’

https://www.codeproject.com/Articles/25600/Triggers-SQL-Server
Chapter 9-123
 SQL Triggers: An Example
A trigger to compare an employee’s salary to his/her
supervisor after insert or update operations:

CREATE TRIGGER Emp_Salary ON Employee

FOR INSERT, UPDATE
AS
IF EXISTS (SELECT * FROM inserted as i JOIN Employee as e ON
i.super_SSN= e.SSN WHERE i.salary > e.salary)
BEGIN
PRINT ‘Employee salary is greater than the Supervisor Salary'
END

INSERT INTO EMPLOYEE (FNAME, LNAME, SSN, Super_SSN, Salary)

VALUES ('Richard', 'Marini', '653298653','123456789',500000)
Chapter 9-124
 SQL Triggers
 CREATE TRIGGER SampleTrigger ON Employee
 INSTEAD OF INSERT

 AS

 SELECT * FROM Employee

 To fire the trigger we can insert a row in table and it will

show list of all user instead of inserting into the table

INSERT INTO EMPLOYEE (FNAME, LNAME, SSN, Super_SSN, Salary)

VALUES ('Richard', 'Marini', '653298653','123456789',500000)
 SQL Triggers: An Example
Using a trigger with a reminder message

CREATE TRIGGER reminder2

ON employee
AFTER INSERT, UPDATE, DELETE
AS
EXEC msdb.dbo.sp_send_dbmail
@profile_name = ‘The Administrator’,
@recipients = 'danw@Adventure-Works.com’,
@body = 'Don''t forget to print a report’,
@subject = 'Reminder’;

https://docs.microsoft.com/en-us/sql/t-sql/statements/create-trigger-transact-sql?view=sql-server-2017
Chapter 9-126
127

Why Transactions?
 Transaction is a process involving database queries
and/or modification.

 Database systems are normally being accessed by many

users or processes at the same time.

 Example- ATM

 Formed in SQL from single statements or explicit

programmer control
128

ACID TRANSACTIONS

Atomic • Whole transaction or none is done.

Consistent • Database constraints preserved.

• It appears to the user as if only one

Isolated process executes at a time.

Durable • Effects of a process survive a crash.

Optional: weaker forms of transactions are often supported

as well.
129

T-SQL AND Transactions

SQL has following transaction modes.
 Autocommit transactions
 Each individual SQL statement = transaction.

 Explicit transactions
BEGIN TRANSACTION
[SQL statements]
COMMIT or ROLLBACK
130

Transaction Support in TSQL

 BEGIN TRAN
 UPDATE Department
 SET Mgr_ssn = 123456789
 WHERE DNumber = 1
 UPDATE Department
 SET Mgr_start_date = '1981-06-19'
 WHERE Dnumber = 1
 COMMIT TRAN
 Transaction Support in SQL
Potential problem with lower isolation levels:
 Dirty Read
 Reading a value that was written by a failed transaction.

 Nonrepeatable Read
 Allowing another transaction to write a new value between
multiple reads of one transaction.
 A transaction T1 reads a given value from a table.
 If another transaction T2 later updates that value and T1 reads that

value again, T1 will see a different value.

Transaction Support in SQL
 Potential problem with lower isolation levels (contd.):
 Phantoms
 New rows being read using the same read with a condition.
 A transaction T1 may read a set of rows from a table, perhaps
based on some condition specified in the SQL WHERE clause.
 Now suppose that a transaction T2 inserts a new row that also
satisfies the WHERE clause condition of T1, into the table used by
T1.
 If T1 is repeated, then T1 will see a row that previously did not

exist, called a phantom.

TRANSACTION SUPPORT IN TSQL
134

TRANSACTION SUPPORT IN TSQL

1. “Dirty reads”
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
2. “Committed reads”
SET TRANSACTION ISOLATION LEVEL READ COMMITTED
3. “Repeatable reads”
SET TRANSACTION ISOLATION LEVEL REPEATABLE READ
4. Serializable transactions (default):
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
135

ACID TRANSACTIONS

Atomic • Whole transaction or none is done.

Consistent • Database constraints preserved.

• It appears to the user as if only one

Isolated process executes at a time.

Durable • Effects of a process survive a crash.

Optional: weaker forms of transactions are often supported

as well.
EXAMPLE OF FUND TRANSFER

 Transaction to transfer $50 from account A to account B:

1. read(A)
2. A := A – 50
3. write(A)
4. read(B)
5. B := B + 50
6. write(B)

 Atomicity requirement :
 if the transaction fails after step 3 and before step 6,
 the system should ensure that :
 its updates are not reflected in the database,

 else an inconsistency will result.

EXAMPLE OF FUND TRANSFER

 Transaction to transfer $50 from account A to account B:

1. read(A)
2. A := A – 50
3. write(A)
4. read(B)
5. B := B + 50
6. write(B)

 Consistency requirement :
 the sum of A and B is:
 unchanged by the execution of the transaction.
EXAMPLE OF FUND TRANSFER (CONT.)
 Transaction to transfer $50 from account A to account B:
1. read(A)
2. A := A – 50
3. write(A)
4. read(B)
5. B := B + 50
6. write(B)
 Isolation requirement —
 if between steps 3 and 6, another transaction is allowed to access the
partially updated database,
 it will see an inconsistent database (the sum A + B will be less than it should
be).
 Isolation can be ensured trivially by:
 running transactions serially, that is one after the other.
 However, executing multiple transactions concurrently has significant
benefits.
EXAMPLE OF FUND TRANSFER (CONT.)
 Transaction to transfer $50 from account A to account B:
1. read(A)
2. A := A – 50
3. write(A)
4. read(B)
5. B := B + 50
6. write(B)

 Durability requirement :
 once the user has been notified that the transaction has completed :
 (i.e., the transfer of the $50 has taken place),
 the updates to the database by the transaction must persist

 despite failures.