EVEN MORE SQL!
Chapter 4 & 5
1
More Complex SQL Retrieval Queries
⚫
Additional features allow users to specify more complex retrievals from database:
◦ Nested queries
◦ Joined tables
◦ Outer joins
◦ Aggregate functions
◦ Grouping
Nested Queries
⚫
Nested queries
◦ Complete select-from-where blocks within WHERE clause of another query
⚫
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
3
Nested Queries
⚫
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 Using “IN”
⚫ List the name of employees in departments 25, 47, or 53
SELECT Lname, Fname FROM Employee
WHERE Dno IN (25, 47, 53);
5
Nested Queries
⚫ Find all the project names for projects being managed by employee last name ‘Wong’:
SELECT Pname FROM Project
WHERE Pnumber IN (SELECT Pnumber
FROM Project, Department, Employee WHERE Dnum=Dnumber AND
Mgr_ssn=Ssn AND Lname=‘Wong’);
Nested Queries
⚫
Can also use tuples instead of a single value in the where condition:
SELECT Essn FROM Works_On
WHERE (Pno, Hours) IN (SELECT Pno, Hours FROM Works_On
WHERE Essn=‘123456789’);
7
Find all employee SSN who work on the same project and same hours as employee
‘123456789’
Nested Queries (cont’d)
⚫ When an inner query references some
attributes of a relation declared in an outer query, the queries are correlated:
SELECT E.Fname, E.Lname FROM Employee AS E
WHERE E.Ssn IN (SELECT Essn
FROM Dependent AS D
WHEREE.Fname = D.Dependent_name AND E.Sex = D.Sex);
Nested Queries (cont’d)
⚫ Correlated nested queries can always be rewritten as a single-block query with a bit of thought:
SELECT E.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;
◦ Think of the nested query as being run once for each tuple in the outer query
● This is equivalent to the block query above
9
WHERE Condition – Test For Existence
⚫
EXISTS and NOT EXISTS
◦ Typically used in conjunction with a correlated nested query
◦ Check whether the result of a correlated nested query is empty or not
WHERE Condition – EXISTS
⚫ Use the EXISTS function to check whether a nested query is not empty:
SELECT E.Fname, E.Lname FROM Employee AS E
WHERE EXISTS (SELECT *
FROM Dependent AS D
WHEREE.Fname = D.Dependent_name AND E.Ssn = D.Essn
AND E.Sex = D.Sex);
11
Find all employees who have a dependent with the
same first name and same sex as the employee
WHERE Condition – NOT EXISTS
⚫
Use NOT EXISTS function to check whether a nested query is empty:
SELECT E.Fname, E.Lname FROM Employee AS E
WHERE NOT EXISTS (SELECT *
FROM Dependent AS D WHEREE.Ssn = D.Essn);
Find all employees who do not have a dependent
WHERE Condition – Existence examples
⚫ List the names of employees who work in departments in Columbus:
SELECT Lname, Fname FROM Employee EMP
WHERE EXISTS
(SELECT *
FROM Department DEPT WHERE EMP.Dno = DEPT.Dnumber AND LOC = ‘Columbus’ );
⚫ List the names of employees who do not work in departments in Columbus:
SELECT Lname, Fname FROM Employee EMP WHERE NOT EXISTS
(SELECT *
FROM Department DEPT
WHERE EMP.Dno = DEPT.Dnumber AND LOC = ‘Columbus’ );
13
Comparisons involving NULL
⚫ NULL has three possible meanings:
◦ Unknown value
◦ Withheld value
◦ Not Applicable value
⚫ Often not possible to tell which meaning is intended
◦ SQL itself does not distinguish between different meanings for NULL (need to deal with that in applications)
◦ When making comparisons, SQL considers NULL to mean
“Unknown”
⚫ Three-valued logic:
◦ TRUE
◦ FALSE
◦ UNKNOWN
Three-Valued Logic
15
Comparisons involving NULL
⚫ Note that because NULL means “Unknown”, we can’t test for equality to NULL
SELECT *
FROM Employee
WHERE Super_ssn = NULL;
⚫ Instead we use “IS” or “IS NOT” when looking for NULL values
SELECT *
FROM Employee
WHERE Super_ssn IS NULL;
Joins – Inner Joins with JOIN
⚫ Previously…
SELECT Fname, Lname, Address FROM EMPLOYEE, DEPARTMENT
WHERE Dno=Dnumber AND Dname=‘Research’;
⚫ Use JOIN to dynamically create a table “on the fly”
SELECT Fname, Lname, Address
FROM (EMPLOYEE JOIN DEPARTMENT ON Dno=Dnumber) WHERE Dname=‘Research’;
17
Joins – Inner Joins with JOIN
SELECT Fname, Lname, Address
FROM (EMPLOYEE JOIN DEPARTMENT ON Dno=Dnumber) WHERE Dname=‘Research’;
⚫ The FROM clause here is a single joined table
◦ Our join condition is explicit in the FROM clause after the ON keyword
◦ Attributes are all the attributes in the EMPLOYEE table together with the attributes in the DEPARTMENT table
● ON condition shows where things match up
● In this case, on the “Department Number” attribute
Joins – Outer Joins with OUTER JOIN
⚫ Sometimes we want to include results even when an attribute is unknown (i.e. NULL)
⚫ We use an Outer Join (as in relational algebra – can use the qualifiers LEFT, RIGHT and FULL)
19
E S
E S
E S
SELECT E.Lname, S.Lname AS Super
FROM (Employee AS E LEFT OUTER JOIN Employee AS S ON E.Super_ssn=S.ssn);
SELECT E.Lname, S.Lname AS Super
FROM (Employee AS E RIGHT OUTER JOIN Employee AS S ON E.Super_ssn=S.ssn);
SELECT E.Lname, S.Lname AS Super
FROM (Employee AS E FULL OUTER JOIN Employee AS S ON E.Super_ssn=S.ssn);
SQL Retrieval
⚫
SQL Query Syntax:
SELECT <attribute(s)>
FROM <table(s)>
[ WHERE <condition> ]
[ GROUP BY <attribute(s)> ] [ ORDER BY <attribute list> ]
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
● If NULLs exist in grouping attribute, separate NULL group is created
⚫ Grouping
◦ Create subgroups of tuples before summarizing using the GROUP BY clause
⚫ Functions can be used in the SELECT clause
21
Aggregate Functions in SQL
⚫ Calculate the average salary of clerks
SELECT avg(Salary) FROM Employee
WHERE Job = ‘Clerk’;
⚫ How many different jobs are held by employees in department 50?
SELECT count(distinct Job) FROM Employee
WHERE Dno = 50;
Grouping Aggregate Functions
⚫
GROUP BY clause
◦ Specifies grouping attributes
◦ Aggregate functions are applied to each such group independently
23
Grouping Aggregate Functions – GROUP BY
⚫ List department names and the average salary of each department
SELECT Dname, avg(Salary)
FROM Employee E, Department D WHERE E.Dno = D.Dnumber GROUP BY Dname;
⚫ Like functions in relational algebra:
◦ Dname avg(Salary) (Employee ⨝Dno=Dnumber Department)
Grouping Aggregate Functions – GROUP BY
⚫ GROUP BY clauses can contain more than one grouping:
SELECT Dno, COUNT(*), AVG(Salary) FROM Employee
GROUP BY Dno, Super_ssn;
This would group together everyone who works in the same department and have the same manager
25
SQL Retrieval
⚫
SQL Query Syntax:
SELECT <attribute(s)>
FROM <table(s)>
[ WHERE <condition> ]
[ GROUP BY <attribute(s)> ] [ HAVING <group condition> ] [ ORDER BY <attribute list> ]
Grouping Aggregate Functions
⚫
GROUP BY clause
◦ Specifies grouping attributes
◦ Aggregate functions are applied to each such group independently
⚫
HAVING clause
◦ Provides a condition on the summary information
◦ Functions can be used in the HAVING clause to select certain GROUPs
27
Grouping Aggregate Functions – HAVING
⚫ Selects certain results based on the summary information
⚫ Ex., List departments (DNO, DNAME) in which the average employee salary < $25,000
SELECT Dno, Dname
FROM Employee E, Department D
WHERE E.Dno = D.Dnumber
GROUP BY Dno, Dname
HAVING avg(Salary) < 25000;
Grouping Aggregate Functions – HAVING
⚫ List departments (numbers) that employ more than 10 clerks:
SELECT Dno
FROM Employee
WHERE Job = ‘Clerk’
GROUP BY Dno
HAVING count(*) > 10;
29
Grouping Aggregate Functions – HAVING
⚫ We can mix nested selects into our HAVING clauses
SELECT Dno, AVG(Salary) FROM Employee
GROUP BY Dno
HAVING AVG(Salary) >
(SELECT MIN(average)
FROM (SELECT AVG(Salary) AS average FROM Employee GROUP BY Dno));
Find all departments and their average salaries which aren’t the lowest average in the
company
Grouping Aggregate Functions – HAVING
⚫ Another way of performing the previous SELECT
SELECT Dno, AVG(Salary) FROM Employee,
(SELECT MIN(average) AS min_avg FROM (SELECT AVG(Salary) AS average
FROM Employee GROUP BY DNO) ) AS MINA
GROUP BY Dno
HAVING AVG(Salary) > MINA.min_avg;
Find all departments and their average salaries which aren’t the lowest average in the
company
Altogether Now…
⚫ For all departments in Columbus with average salary >
$25,000, list the department number, name, and
average salary ordered by average salary in descending order:
SELECT Dno, Dname, avg(Salary)
FROM Employee EMP, Department DEPT, Dept_Locations DL
WHERE EMP.Dno = DEPT.Dnumber
AND DEPT.Dnumber = DL.Dnumber AND DL.Dlocation = ‘Columbus’
GROUP BY Dno, Dname
HAVING avg(Salary) > 25000 ORDER BY 3 DESC;
SQL Retrieval
⚫
SQL Query Syntax:
SELECT <attribute(s)>
FROM <table(s)>
[ WHERE <condition> ]
[ GROUP BY <attribute(s)> ] [ HAVING <group condition> ] [ ORDER BY <attribute list> ]
33
More interesting SQL
⚫ Show the result of giving everyone in
departments in Columbus a 10% pay raise. List the employee number and name
SELECT Ssn, Lname,
1.1 * Salary AS NEWSAL
FROM Employee EMP, Department DEPT, Dept_Locations DL
WHERE EMP.Dno = DEPT.Dnumber
AND DEPT.Dnumber = DL.Dnumber AND Dlocation = ‘Columbus’;
More interesting SQL
⚫ Give a 10% raise to all employees in Employee, whose employee number appears in the Candidates table.
UPDATEEmployee
SETSalary = Salary * 1.1 WHERE EMPNO IN
(SELECT EMPNO
FROM Candidates);
35
More interesting SQL
⚫ Insert employee named ‘Jones’ with employee number 535 in department 51. Other attributes are null.
INSERT INTO Employee (Empno, Lname, Dno) VALUES (535, ‘Jones’, 51);
More interesting SQL
⚫ Add all employees whose commission is greater
than half their salary to the CANDIDATES relation
◦ Given: CANDIDATES(EMPNO, NAME, DNO, SAL)
INSERT INTO Candidates
(SELECT EMPNO, Lname, Dno, Salary FROM Employee
WHERE COMM > 0.5 * SAL);
37
More interesting SQL
⚫ Delete from Employee the employee with employee number 561.
DELETE FROM Employee WHERE EMPNO = 561;
⚫ Delete from the Department table the departments having no employees.
DELETE FROM Department DEPT WHERE (SELECT COUNT(*)
FROM Employee
WHERE Dno = DEPT.Dnumber) = 0;
