CS3402: Chapter 6
SQL: Structured Query Language II
CS3402 1
–
Structure of Basic SQL Queries
◼ SELECT statement
◆The basic statement for retrieving information from a database
The SELECT
FROM
–
WHERE
Specified attributes
Title
Year
Length
Type
Star War
1977
124
Color
Mighty Duck
1991
104
Color
Wayne’s World
1992
95
Color
Satisfy the conditions
CS3402
2
One possible database state for the COMPANY relational database schema
CS3402 3
One possible database state for the COMPANY relational database schema
CS3402 Slide 6- 13 4
Nested Queries
◼ Nested queries
◆Some queries require that existing values in the database be
fetched and then used in a comparison condition.
◆ Such queries can be conveniently formulated by using nested queries, which are complete select-from-where blocks within another SQL query.
◆The inner one is called nested query and the outer one is called the outer query.
◆These nested queries can also appear in the WHERE clause or the FROM clause or the SELECT clause or other SQL clauses as needed.
CS3402
5
Nested Queries
◼ Comparison operator IN
◆Compares a single value v with a set (or multiset) of values V ◆Evaluates “v IN V” to TRUE if v is one of the elements in V
e.g. 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.
The project no. of projects that have an manager with last name “Smith”
The project no. of projects that have an employee with last name “Smith”
with
IN
CS3402 6
Nested Queries
◼ Comparison operator IN
◆ If a nested query returns a single value, it is permissible to
use = instead of IN for the comparison operator.
◆In general, the nested query will return a table (relation), which is a set or multiset of tuples. So to be safe IN is recommended.
with
IN
CS3402
7
Nested Queries, IN
◼ Comparison operator IN
◆IN can be used to compare tuples of values by placing them
within parentheses.
◆v IN V will return true if the tuple v exists in V
e.g. Select the Essn of all employees who work the same (project, hours) as the employee Essn =“123456789”
The (Pno, Hours) of the employee with Essn =“123456789”
CS3402 8
Nested Queries, SOME/ANY, ALL
◼ In addition to the IN operator, a number of other comparison operators can be used to compare a single value v to a set or multiset V by combining the keywords ANY/SOME, ALL.
◼ ANY/SOME
◆ = ANY (or = SOME) 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 <>.
◼ ALL
◆ALL can also be combined with each of these operators:=, >, >=, <, <=, and <>.
◆E.g. v > ALL V returns TRUE if the value v is greater than all the values in the set V.
CS3402
9
Nested Queries, SOME/ANY, ALL
◼ ANY/SOME，ALL
e.g. Select the names of employees whose salary is greater than the
salary of all the employees in department 5
The salary of Employee tuples with Dno = 5
◼ What will be returned if we change the ALL to ANY?
CS3402 10
Correlated Nested
◼ Correlated nested query
◆Whenever a condition in the WHERE clause of a nested query references some attributes of a relation declared in the outer query.
◆We can understand a correlated query better by considering that the nested query is evaluated once for each tuple in the outer query.
e.g. Retrieve the name of each employee who has a dependent with the same first name and the same sex as the employee.
Queries
CS3402
11
Correlated Nested Queries
◼ EXISTS/ NO EXISTS function
◆ Boolean functions that return TRUE or FALSE, used in a WHERE
clause condition.
◆Check whether the result of a nested query is empty or not
◆EXISTS returns TRUE if not empty; NO EXISTS returns TRUE if empty.
◆Typically used in conjunction with a correlated nested query e.g. Retrieve the name of each employee who has a dependent
with the same first name and the same sex as the employee.
, EXISTS
CS3402 12
Correlated Nested Queries
◼ EXISTS/ NO EXISTS function
e.g. retrieve the names of employees who have no dependents:
For each EMPLOYEE tuple, the correlated nested query selects all DEPENDENT tuples whose Essn value matches the EMPLOYEE Ssn; if the result is empty, no dependents are related to the employee, so we select that EMPLOYEE tuple and retrieve its Fname and Lname.
, EXISTS
CS3402 13
Correlated Nested Queries
◼ EXISTS/ NO EXISTS function
e.g. List the names of managers who have at least one dependent :
The first nested query selects all DEPENDENT tuples related to an EMPLOYEE.
The second selects all DEPARTMENT tuples managed by the EMPLOYEE. If at least one of the first and at least one of the second exists, we select the EMPLOYEE tuple.
, EXISTS
CS3402
14
Correlated Nested Queries
◼ EXISTS/ NO EXISTS function
e.g. Retrieve the name of each employee who works on all the
projects controlled by department number 5:
The first subquery selects all projects controlled by department 5.
The second subquery selects all projects that the particular employee being considered works on.
If the set difference of the first subquery result MINUS (EXCEPT) the second subquery result is empty, it means that the employee works on all the
projects and is therefore selected.
, EXISTS
CS3402 15
Aggregate Functions
◼ Used to summarize information from multiple tuples into a single- tuple
◼ Built-in aggregate functions
◆COUNT:returns the number of values / tuples in the set or
multiset .
◆ SUM,MAX,MIN, and AVG: applied to a set or multiset of numeric values and return, respectively, the sum, maximum value, minimum value, and average (mean) of those values.
◼ Functions can be used in the SELECT clause or in a HAVING clause
CS3402 16
Aggregate Functions
◼ Used to summarize information from multiple tuples into a single- tuple
e.g. Find the sum of the salaries of all employees, the maximum salary, the minimum salary, and the average salary
This query returns a single-row summary of all the rows in the EMPLOYEE table.
SUM(Salary)
MAX(Salary)
MIN(Salary)
AVG(Salary)
281000
55000
25000
35125
CS3402 17
Aggregate Functions
◼ Aggregate Functions combining with where clause ◆Only the tuples satisfying condition will be aggregated.
e.g. Find the sum of the salaries of all employees of the ‘Research’ department, as well as the maximum salary, the minimum salary, and the average salary in this department
e.g. Retrieve the number of employees in the ‘Research’
department .
Here the asterisk (*) refers to the rows (tuples), so COUNT (*) returns the number of rows in the result of the query.
CS3402
18
Aggregate Functions
◼ COUNT:
● will not eliminate duplicated values/tuples
● add keyword “DISTINCT” if we do not want to count duplicated tuples
e.g. count the number of distinct salary values in the database
● can be used to count tuples with COUNT(*);
CS3402 19
Aggregate Functions
◼ NULL in aggregate functions:
● In general, NULL values are discarded when aggregate functions are applied to a particular column (attribute); the only exception is for COUNT(*) because tuples instead of values are counted.
● When an aggregate function is applied to a collection of values, NULLs are removed from the collection before the calculation; if the collection becomes empty because all values are NULL, the aggregate function will return NULL (except in the case of COUNT, where it will return 0 for an empty collection of values).
CS3402 20
Aggregate Functions
◼ Aggregate functions in nested query
e.g. retrieve the names of all employees who have two or more
dependents:
The correlated nested query counts the number of dependents that each employee has; if this is greater than or equal to two, the employee tuple is selected.
CS3402 21
GROUP BY Clauses
◼ GROUP BY
◆Followed by attribute list called the grouping attribute(s).
◆partition the relation into nonoverlapping subsets (or groups) of tuples.
◆Each group (partition) will consist of the tuples that have the same value on the grouping attribute(s).
◆We can then apply aggregate functions to each such group independently to produce summary information about each group.
CS3402
22
GROUP BY Clauses
◼ GROUP BY
◆The grouping attributes should also appear in the SELECT clause, so that the value resulting from applying each aggregate function to a group of tuples appears along with the value of the grouping attribute(s).
e.g. for each department, retrieve the department number, the number of employees in the department, and their average salary:
Each group having the same value for the GROUP BY attribute CS3402 Dno. 23
GROUP BY Clauses
◼ GROUP BY
CS3402 24
GROUP BY Clauses
◼ GROUP BY
◆If NULLs exist in the grouping attribute, then a separate group is
created for all tuples with a NULL value in the grouping attribute.
◆GROUP BY can be applied after joining two or more relations.
◆WHERE clause will be performed before GROUP BY, that means only tuples satisfying condition will be grouped.
e.g. for each project, retrieve the project number, the project name, and the number of employees who work on that project:
CS3402 25
Clauses
HAVING
◼ HAVING clause
◆Provides a condition to select or reject an entire group. Only the groups that satisfy the condition are retrieved in the result of the query.
◆appear in conjunction with a GROUP BY clause.
e.g. 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.
CS3402 26
HAVING
◼ HAVING clause
Clauses
CS3402
27
Clauses
HAVING
◼ HAVING clause
◆the WHERE clause is executed first, to select individual tuples or joined tuples; the HAVING clause is applied later, to select individual groups of tuples.
e.g. count the total number of employees whose salaries exceed $40,000 in each department, but only for departments where more than five employees work.
Incorrect!
It will select only departments that have more than five employees who each earn more than $40,000.
CS3402
28
Clauses
HAVING
◼ HAVING clause
◆the WHERE clause is executed first, to select individual tuples or joined tuples; the HAVING clause is applied later, to select individual groups of tuples.
e.g. count the total number of employees whose salaries exceed $40,000 in each department, but only for departments where more than five employees work.
Correct!
CS3402
29
EXPANDED Block Structure of SQL Queries
CS3402 30
Summary of SQL Syntax
CS3402 31
Summary of SQL Syntax
CS3402 32
References
◼ 6e
● Ch. 4. p. 83 – 107 ● Ch. 5, p. 111 – 126
CS3402
33