Structured Query Language

Select query, subqueries

Б.Наранчимэг
Мэдээлэл, компьютерийн ухааны тэнхим
ХШУИС, МУИС
naranchimeg@seas.num.edu.mn
Objectives
• Purpose and importance of SQL.
• How to retrieve data from database using SELECT and:
• Use compound WHERE conditions.
• Sort query results using ORDER BY.
• Use aggregate functions.
• Group data using GROUP BY and HAVING.
• Use subqueries.
Grouping results
• Grouped query
• groups the data from the SELECT table(s) and produces a single summary row
for each group.
• Grouping columns
• The columns named in the GROUP BY clause
• SELECT clause may contain only:
• column names;
• aggregate functions
• constants;
• an expression involving combinations of the above.
Example 5.17 Use of GROUP BY
• Find the number of staff working in each branch and the sum of their
salaries.
SELECT branchNo, COUNT(staffNo) AS myCount, SUM(salary) AS mySum
FROM Staff
GROUP BY branchNo
ORDER BY branchNo;
Example 5.17 Use of GROUP BY
Restricted groupings – Having clause
• HAVING clause is designed for the use with GROUP BY to
restrict groups that appear in final result table.
• Similar to WHERE, but WHERE filters individual rows whereas
HAVING filters groups.
• Column names in HAVING clause must also appear in the
GROUP BY list or be contained within aggregate function
Example 5.18 Use of HAVING
• For each branch office with more than one member of staff, find the
number of staff working in each branch and the sum of their salaries.
SELECT branchNo, COUNT(staffNo) AS myCount, SUM(salary) AS
mySum
FROM Staff
GROUP BY branchNo
HAVING COUNT(staffNo) > 1
ORDER BY branchNo;
Example 5.18 Use of HAVING
Subqueries
• Some SQL statements can have a SELECT embedded within
them.
• A subselect used in WHERE and HAVING clauses of an outer
SELECT, where it is called a subquery or nested query.
• Subselects may also appear in INSERT, UPDATE and DELETE
statements.
Example 5.19 Using a subquery with equality
• List the staff who work in the branch at ‘163 Main St’.
SELECT staffNo, fName, lName, position
FROM Staff
WHERE branchNo = (SELECT branchNo
FROM Branch
WHERE street = ‘163 Main St’);
Example 5.19 Using a subquery with equality
• Inner SELECT finds branch number for branch at ‘163 Main St’
(‘B003’).
• Outer SELECT then retrieves details of all staff who work at this
branch.
• Outer SELECT the becomes:
SELECT staffNo, fName, lName, position
FROM Staff
WHERE branchNo = ‘B003’;
Example 5.19 Using a subquery with equality
Example 5.20 Using a subquery with an
aggregate function
• List all staff whose salary is greater than the average salary, and show
by how much their salary is greater than the average.
SELECT staffNo, fName, lName, position,
salary – (SELECT AVG(salary) FROM Staff) AS salDiff
FROM Staff
WHERE salary > (SELECT AVG(salary) FROM Staff);
Example 5.20 Subquery with Aggregate
• Cannot write ‘WHERE salary > AVG(salary)’
• Instead, use subquery to find average salary (17000), and then use
outer SELECT to find those staff with salary greater than this:
SELECT staffNo, fName, lName, position, salary – 17000 AS salDiff
FROM Staff
WHERE salary > 17000;
Example 5.20 Subquery with Aggregate
Subquery rules
• The ORDER BY clause may not be used in a subquery
(although it may be used in the outermost SELECT
statement).
• The subquery SELECT list must consist of a single column
name or expression, except for subqueries that use the
keyword EXISTS.
• By default, column names refer to the table name in the
FROM clause of the subquery. Can refer to a table in a FROM
using an alias.
Subquery rules
• When subquery is an operand in a comparison, subquery
must appear on right-hand side.
• A subquery may not be used as an operand in an expression
Example 5.21 Nested subqueries: use of IN
• List the properties that are handled by staff who work in the branch at ‘163
Main St’.
SELECT propertyNo, street, city, postcode, type, rooms, rent
FROM PropertyForRent
WHERE staffNo IN (SELECT staffNo
FROM Staff
WHERE branchNo = (SELECT branchNo
FROM Branch
WHERE street = ‘163 Main St’));
Example 5.21 Nested subqueries: use of IN
ANY and ALL
• ANY and ALL may be used with subqueries that produce a single
column of numbers.
• With ALL, condition will only be true if it is satisfied by all values
produced by the subquery.
• With ANY, condition will be true if it is satisfied by any (one or more)
values produced by the subquery.
• If the subquery is empty, the ALL condition returns true, the ANY
condition returns false.
• SOME to be used in place of ANY
Example 5.22 Use of ANY/SOME
• Find all staff whose salary is larger than the salary of at least one
member of staff at branch B003.
SELECT staffNo, fName, lName, position, salary
FROM Staff
WHERE salary > SOME (SELECT salary
FROM Staff
WHERE branchNo = ‘B003’);
Example 5.22 Use of ANY/SOME
• The inner query produces the set {12000, 18000, 24000} and
the outer query selects those staff whose salaries are greater
than any of the values in this set.
Example 5.23 Use of ALL
• Find all staff whose salary is larger than the salary of every member of
staff at branch B003.
SELECT staffNo, fName, lName, position, salary
FROM Staff
WHERE salary > ALL (SELECT salary
FROM Staff
WHERE branchNo = ‘B003’);
Example 5.23 Use of ALL
Multi-Table Queries
• Can use subqueries provided result columns come from
same table.
• If result columns come from more than one table must use a
join.
• To perform join, include more than one table in FROM
clause.
• Use comma as separator and typically include where clause
to specify join column(s).
Multi-Table Queries
• Also possible to use an alias for a table named in FROM
clause.
• Alias is separated from table name with a space.
• Alias can be used to qualify column names when there is
ambiguity.
Example 5.24 Simple join
• List the names of all clients who have viewed a property along with
any comment supplied.
SELECT c.clientNo, fName, lName, propertyNo, comment
FROM Client c, Viewing v
WHERE c.clientNo = v.clientNo;
Example 5.24 Simple join
• Only those rows from both tables that have identical values in the
clientNo columns (c.clientNo = v.clientNo) are included in result.
• Equivalent to equi-join in relational algebra.
Alternative JOIN constructs
• SQL provides alternative ways to specify joins:
• FROM Client c JOIN Viewing v ON c.clientNo = v.clientNo
• FROM Client JOIN Viewing USING clientNo
• FROM Client NATURAL JOIN Viewing
• In each case, FROM replaces original FROM and WHERE. However,
first produces table with two identical clientNo columns.
Example 5.25 Sorting a join
• For each branch office, list the numbers and names of staff who
manage properties and the properties that they manage.
SELECT s.branchNo, s.staffNo, fName, lName, propertyNo
FROM Staff s, PropertyForRent p
WHERE s.staffNo = p.staffNo
ORDER BY s.branchNo, s.staffNo, propertyNo;
Example 5.25 Sorting a join
Example 5.26 Three-table join
• For each branch, list the numbers and names of staff who manage
properties, including the city in which the branch is located and the
properties that the staff manage.
SELECT b.branchNo, b.city, s.staffNo, fName, lName, propertyNo
FROM Branch b, Staff s, PropertyForRent p
WHERE b.branchNo = s.branchNo AND s.staffNo = p.staffNo
ORDER BY b.branchNo, s.staffNo, propertyNo;
Example 5.26 Three-table join

• Alternative formulations for the FROM and WHERE clauses