Advanced SQL

Nested queries

• Some queries require that values be fetched from relation(s), and then used in comparisons

• This can be done by placing entire select-from-where block within the where clause of another (outer) query.

• Example: Make a list of all 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

Example nested query

• select distinct pnumber from project
where pnumber IN
(select pnumber
from project, department, employee
where dnum=dnumber and mgrssn = ssn
and lname=‘Smith’)
OR pnuber IN
( select pno
from works_on, employee
where essn=ssn and lname=‘Smith’);

• The comparison operator IN compares a value v with a set of values V and returns true if the value is in the set

Query: Retrieve the SSN of all employees who work the same project and hours on some project that employee whose ssn=‘123456789’ works on.

• select distinct essn from works_on
where (pno, hours) IN
( select pno, hours
from works_on
where essn =‘123456789’);

• The IN operator can also compare multiple values v with a set V of union compatible values

Other comparison operators

• In addition to in other operators can compare a single value to a set or multiset of values

• The = ANY operator returns true if the value v is equal to any of the values in the set V specified by the nested query

– Other relational operators can be used instead of =

>, < <=, >=, <>

• ALL can also be combined with the relational operators

– For example (v >V) returns true if the value v is greater than all of the values in the set V

Query: Retrieve the names of all employees whose salary is greater than the salary of all the employees in department 5

• select lname, fname from employee
where salary > ALL
( select salary
from employee
where dno=5);

Retrieve the name of each employee who has a dependent with the same first name and same sex as the employee

• select E.fname, E.lname from employee as E
where E.ssn IN
( select essn from dependent
where E.fname = dep_name
and E.sex = sex);

• Notice that a reference to an ambiguous attribute referrers to the relation in the inner query

Correlated nested queries

• If the condition in the where-clause of a nested query references an attribute of a relation declared in the outer query, the two queries are correlated.

• In general, a query written with a nested select-from-where blocks and using the = or in comparison operators can always be express as a single block query if they are correlated.

• Use whatever seems the most logical to you. The DBMS almost always has a query optimizer so efficiency is not a factor.

The EXISTS function

• The exists function is used to check whether the result of a correlated nested query is empty

• Retrieve the name of each employee who has a dependent with the same first name and same sex as the employee.

• select E.fname, E.lname from employee as E
where EXISTS
( select * from dependent
where E.ssn=essn
E.fname = dep_name
and E.sex = sex);

• For each employee tuple evaluate the nested query; if at least one tuple exists in the result, then select it (them)

Retrieve the names of employees who have no dependents

• select fname, lname from employee
where NOT EXISTS
( select * from dependent
where ssn= essn);

– The nested query will retrieve all dependent tuples related to an employee.

– Each employee tuple is compared to the set retrieved by the nested query.

– If nothing in the nested query returns true to that employee tuple, that employee tuple is selected.

Explicit sets

• It is possible to use an explicit set of values in the where-clause.

• The explicit set is enclosed in parentheses

• Retrieve the social security numbers of all employees who work on project number 1,2, or 3

• Select distinct essn from works_on
where pno IN (1,2,3);

Checking for nulls

• Use IS or IS NOT to check for nulls, not = or ≠

• SQL considers each null value as being distinct from every other null value, so they are never equal

• Retrieve the names of all employees who do not have supervisors

select fname, lname from employees
where superssn IS null;

Modifying the database

• Three commands

– Insert

• adds a single tuple to a relation

– Delete

• Removes tuples from a relation

– Update

• Used to modify attribute values in one or more tuples

Delete

• It includes a where-clause to select the tuples to be deleted.

– If the where-clause is omitted, all the tuples are deleted

• Tuples are deleted from only one table at a time, but the deletion may propagate to tuples in other relations

– This happens if a referential triggered action clause has been set to any foreign key constraint by the DDL.

• The options are set null, cascade, and set default

– These options must be qualified by either on delete or on update

Examples of delete

• delete from employee
where lname=‘Brown’

• Delete everyone in the Research department

• delete from employee
where dno IN
( select dnumber from department
where dname=‘Research’);

Update

• It is used with a where-clause that selects the tuples to be modified

• Updating a PK value may propagate to the foreign key values of tuples in other relations

– This happen if a referential triggered action is specified when the table was created.

Update examples

• update project
set plocation = ‘Bellaire’, dnum = 5
where pnumber =10;

• Give everyone in the research department a 10% raise.

• update employee
set salary = salary * 1.1
where dno IN
( select dnumber
from department
where dname=‘Research’);