Practical relational language:
Advanced SQL
Outline
• Handling NULL values
• Nested Queries
• JOIN Operation
• Aggregate Functions
• Grouping
Handling Null Value
• When a NULL value is involved in comparison, the result is UNKNOWN
• Three-valued logic
Handling Null Value
• Generally, in SQL and relational algebra
– ONLY the tuples evaluate to TRUE in the where
clause are selected.
• Exception: outer joins
– Each individual NULL considered to be different
from every other NULL
• Exception: select distinct, group by, sort by, union, intersection,
set difference.
Handling Null Value
R S
A B C D E C
a 1 NULL b NULL NULL
b NULL NULL c 1 NULL
c 1 2
Q1: SELECT * Q3: R*S
FROM R
WHERE B<2; Q4: R∪S
Q2: SELECT C
Q5: R∩S
FROM R;
Handling Null Value
• Check whether an attribute value is NULL in SQL?
IS and IS NOT
• Q7: Select the names of employees who do not have supervisors
SELECT Fname, Lname
FROM EMPLOYEE
WHERE Super_ssn IS NULL;
• What if we use WHERE Super_ssn = NULL?
Nested Queries
(another query within the WHERE clause)
IN: compare a value v with a set (multiset) V
– TRUE if v in set V
• Q8: Select employees who works for the ‘Research’
department
Inner query
SELECT Ssn, Fname, Lname
FROM EMPLOYEE
WHERE Dno IN ( SELECT Dnumber
Outer query FROM DEPARTMENT
WHERE Dname = ‘Research’);
Dnumber
5
Nested Queries
• IN
- IN can be used in the explicit way
SELECT Ssn, Fname, Lname
FROM EMPLOYEE
WHERE Dno IN (1, 4 );
Nested Queries
• OR
– Q9: Select all projects that involve an employee with last name‘Smith’
either as a worker or as a manager of the controlling department
SELECT DISTINCT Pnumber
FROM PROJECT
WHERE
Pnumber IN ( SELECT Pnumber
FROM PROJECT, DEPARTMENT, EMPLOYEE
WHERE Dnum = Dnumber AND Mgr_ssn = Ssn
AND Lname = ‘Smith’)
OR
Pnumber IN ( SELECT Pno
FROM WORKS_ON, EMPLOYEE
WHERE Essn = Ssn AND Lname = ‘Smith’);
Nested Queries
• ALL,SOME(ANY)
– =SOME(or =ANY) is equivalent to the keyword IN
- Q10: Return the names of 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);
Nested Queries
• Correlated Nested Queries
– WHERE clause of a nested query references some attribute(s)
of a relation in the outer query
- Q11: Return the name of each employee who has a dependent with
the same first name and sex as the employee.
SELECT [Link], [Link]
FROM EMPLOYEE AS E
WHERE [Link] IN ( SELECT Essn
FROM DEPENDENT AS D
WHERE [Link] = D.Dependent_name
AND [Link] = [Link]);
Nested Queries
• A query written with nested select-from-where blocks
and using the = or IN comparison can be expressed as a
single block query.
– The previous example can be written as
SELECT [Link], [Link]
FROM EMPLOYEE AS E, DEPENDENT AS D
WHERE [Link] = [Link]
AND [Link] = D.Dependent_name
AND [Link] = [Link];
EXISTS and NOT EXISTS
• EXISTS
– Check whether the result is empty or not
– Opposite: NOT EXISTS
– Typically used in conjunction with a correlated nested query
• Q12: Retrieve the names of employees who have no
dependents.
SELECT Lname, Fname
FROM EMPLOYEE
WHERE NOT EXISTS (SELECT *
FROM DEPENDENT
WHERE Ssn = Essn);
EXISTS and NOT EXISTS
• Q13: Retrieve the names of managers who have at least
one dependent
SELECT Lname, Fname
FROM EMPLOYEE
WHERE EXISTS ( SELECT *
FROM DEPENDENT
WHERE Ssn = Essn)
AND
EXISTS ( SELECT *
FROM DEPARTMENT
WHERE Ssn = Mgr_ssn);
EXISTS and NOT EXISTS
• EXCEPT
- Q14: Retrieve the name of each employee who works
on all the projects controlled by department 5
SELECT Lname, Fname
FROM EMPLOYEE
WHERE NOT EXISTS ( ( SELECT Distinct Pnumber
FROM PROJECT
WHERE Dnum = 5)
EXCEPT( SELECT Distinct Pno
FROM WORKS_ON
WHERE Ssn = Essn) );
JOIN operations in SQL
• Joined table
– Permits to specify a table resulting from a join operation in the FROM clause
– Q15: Retrieve the name and address of every employee who works for the
‘Research’ department.
SELECT Lname, Fname, Address
FROM (EMPLOYEE JOIN DEPARTMENT ON Dno=Dnumber)
WHERE Dname = ‘Research’;
SELECT Lname, Fname, Address
FROM (EMPLOYEE NATURAL JOIN
(DEPARTMENT AS DEPT(Dname, Dno, Mssn, Msdate) ))
WHERE Dname = ‘Research’;
JOIN operations in SQL
• Multi-way join
- Q16: For every project located in ‘Stafford’, list the project number,
the controlling dept number, and the dept manager’s last name, and
birthday.
SELECT Pnumber, Dnum, Lname, Bdate
FROM (
(PROJECT JOIN DEPARTMENT ON Dnum=Dnumber)
JOIN EMPLOYEE ON Mgr_ssn=Ssn
)
WHERE Plocation = ‘Stafford’;
Regard the result of JOIN as a temporary table
JOIN operations in SQL
OUTER JOIN
EMPLOYEE
OFFICE
Name ID
Office_
Jim 1001 ID room
John 1002 1001 T2/3A
Jack 1003 1002 T2/5A
SELECT [Link], O.Office_room
FROM (
EMPLOYEE AS E LEFT OUTER JOIN
OFFICE AS O ON [Link]=[Link]
);
JOIN operations in SQL
Q17: For every employee, retrieve the last name and his or her
supervisor’s last name
SELECT [Link] AS Employee_name, [Link] AS
Supervisor_name
FROM (
(EMPLOYEE AS E LEFT OUTER JOIN EMPLOYEE
AS S ON E.Super_ssn=[Link]
);
Aggregate Functions in SQL
• Used to summarize information from multiple tuples
• Built-in aggregate functions
– COUNT, SUM, MAX, MIN, and AVG
– NULL values are discarded when aggregate functions
are applied to a particular column
SELECT SUM (Salary), MAX (Salary), MIN (Salary), AVG (Salary)
FROM EMPLOYEE;
– SUM/MAX/MIN/AVG(empty)=NULL,
COUNT(empty)=0
Aggregate Functions in SQL
Q18: Retrieve the total number of employees in the
company.
SELECT COUNT (*)
FROM EMPLOYEE; Q19: Retrieve the total number of
employees in the ‘Research’ department.
SELECT COUNT (*)
FROM EMPLOYEE, DEPARTMENT
WHERE Dno=Dnumber AND Dname= ‘Research’;
Q20:
SELECT COUNT (DISTINCT Dlocation)
FROM DEPT_LOCATIONS;
Aggregate Functions in SQL
• Q21: Retrieve the names of employees who
have two or more dependents.
SELECT [Link], [Link]
FROM EMPLOYEE AS E
WHERE ( SELECT COUNT (*)
FROM DEPENDENT
WHERE [Link] = Essn ) >= 2;
Is this correlated nested query?
Grouping in SQL
• Partition relation into subsets of tuples
– Based on grouping attribute(s)
– Apply function to each such group independently
• GROUP BY clause
– Specifies grouping attributes
• HAVING clause
– Conjunction with GROUP BY
– Provides a condition
– Only the groups satisfy the having conditions will be selected.
• If NULLs exist in grouping attribute
– Separate group created for all tuples with a NULL value in grouping
attribute.
- Q22: For each department, retrieve the department number, the
number of employees, and the average salary
SELECT Dno, COUNT (*), AVG (Salary)
FROM EMPLOYEE
GROUP BY Dno;
Q23: For each project on which more than two employees
work, retrieve the project number, project name, and the
number of people working on it.
SELECT Pnumber, Pname, COUNT (*)
FROM PROJECT, WORKS_ON
WHERE Pnumber=Pno
GROUP BY Pnumber, Pname
HAVING COUNT (*) > 2;
Summary of SQL Queries
SELECT <attribute and function list>
FROM <table list>
[ WHERE <condition> ]
[ GROUP BY <grouping attribute(s)> ]
[ HAVING <group condition> ]
[ ORDER BY <attribute list> ] ;
Schema Change Statements in SQL
• Delete schema, table, domain or constraint
– DROP
• Options: CASCADE or RESTRICT(default)
DROP SCHEMA COMPANY CASCADE;
DROP SCHEMA COMPANY RESTRICT;
RESTRICT: the schema is dropped only if it has no elements in it.
Schema Change Statements in SQL
• Delete table
DROP TABLE DEPENDENT CASCADE;
DROP TABLE DEPENDENT RESTRICT;
RESTRICT: a table is dropped only if it is not referenced in any constraints or
by any other elements.
Schema Change Statements in SQL
• Alter table actions include:
– ALTER
– Adding or dropping a column (attribute)
ALTER TABLE EMPLOYEE ADD COLUMN Job VARCHAR(12);
ALTER TABLE EMPLOYEE
ADD COLUMN Type VARCHAR(12) NOT NULL
DEFAULT ‘Temporary’
CHECK (Type IN (‘Temporary’, ‘Permanent’));
ALTER TABLE EMPLOYEE DROP COLUMN Address CASCADE;
Schema Change Statements in SQL
• Alter table actions include:
– Changing a column definition
ALTER TABLE EMPLOYEE ALTER COLUMN Mgr_ssn
DROP DEFAULT;
ALTER TABLE EMPLOYEE ALTER COLUMN Mgr_ssn
SET DEFAULT ‘333445555’;
Schema Change Statements in SQL
• Alter table actions include:
– Adding/dropping table constraints
ALTER TABLE EMPLOYEE ADD CONSTRAINT FKDN
FOREIGN KEY (Dno) REFERENCE
DEPARTMENT(Dnumber);
ALTER TABLE EMPLOYEE DROP CONSTRAINT
FKDN;
Reading Assignment
• Chapter 5.1