Structured Query Language
Select query
Б.Наранчимэг
Мэдээлэл, компьютерийн ухааны тэнхим
ХШУИС, МУИС
naranchimeg@seas.num.edu.mn
Instances of the Branch and Staff relations
Instances of the Branch and Staff relations
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.
Example 5.6
Compound Comparison Search Condition
List addresses of all branch offices in London or Glasgow.
SELECT *
FROM Branch
WHERE city = ‘London’ OR city = ‘Glasgow’;
Example 5.7 Range Search Condition
List all staff with a salary between 20,000 and 30,000.
SELECT staffNo, fName, lName, position, salary
FROM Staff
WHERE salary BETWEEN 20000 AND 30000;
• BETWEEN test includes the endpoints of range.
Example 5.7 Range Search Condition
Example 5.7 Range Search Condition
• Also a negated version NOT BETWEEN.
• BETWEEN does not add much to SQL’s expressive power. Could
also write:
SELECT staffNo, fName, lName, position, salary
FROM Staff
WHERE salary>=20000 AND salary <= 30000;
• Useful, though, for a range of values.
Example 5.8 Set Membership
List all managers and supervisors.
SELECT staffNo, fName, lName, position
FROM Staff
WHERE position IN (‘Manager’, ‘Supervisor’);
Example 5.8 Set Membership
• Thereis a negated version (NOT IN).
• IN does not add much to SQL’s expressive power.
• Could have expressed this as:
SELECT staffNo, fName, lName, position
FROM Staff
WHERE position=‘Manager’ OR
position=‘Supervisor’;
• IN is more efficient when set contains many values.
Example 5.9 Pattern Matching
Find all owners with the string ‘Glasgow’ in their address.
SELECT clientNo, fName, lName, address, telNo
FROM PrivateOwner
WHERE address LIKE ‘%Glasgow%’;
Example 5.9 Pattern Matching
• SQL has two special pattern matching symbols:
• %: sequence of zero or more characters;
• _ (underscore): any single character.
• LIKE ‘%Glasgow%’ means a sequence of characters of any
length containing ‘Glasgow’.
Example 5.10 NULL Search Condition
List details of all viewings on property PG4 where a comment has not been
supplied.
• There are 2 viewings for property PG4, one with and one without a comment.
• Have to test for null explicitly using special keyword IS NULL:
SELECT clientNo, viewDate
FROM Viewing
WHERE propertyNo = ‘PG4’ AND
comment IS NULL;
Example 5.10 NULL Search Condition
• Negated version (IS NOT NULL) can test for non-null values.
Example 5.11 Single Column Ordering
List salaries for all staff, arranged in descending order of salary.
SELECT staffNo, fName, lName, salary
FROM Staff
ORDER BY salary DESC;
Example 5.11 Single Column Ordering
Example 5.12 Multiple Column Ordering
SELECT propertyNo, type, rooms, rent
FROM PropertyForRent
ORDER BY type;
SELECT propertyNo, type, rooms, rent
FROM PropertyForRent
ORDER BY type, rent DESC;
Example 5.12 Multiple Column Ordering
SELECT Statement - Aggregates
• ISO standard defines five aggregate functions:
COUNT returns number of values in specified column.
SUM returns sum of values in specified column.
AVG returns average of values in specified column.
MIN returns smallest value in specified column.
MAX returns largest value in specified column.
SELECT Statement - Aggregates
• Each operates on a single column of a table and returns a
single value.
• COUNT, MIN, and MAX apply to numeric and non-numeric
fields, but SUM and AVG may be used on numeric fields
only.
• Apart from COUNT(*), each function eliminates nulls first and
operates only on remaining non-null values.
SELECT Statement - Aggregates
• COUNT(*) counts all rows of a table, regardless of whether
nulls or duplicate values occur.
• Can use DISTINCT before column name to eliminate
duplicates.
• DISTINCT has no effect with MIN/MAX, but may have with
SUM/AVG.
SELECT Statement - Aggregates
• Aggregate functions can be used only in SELECT list and in
HAVING clause.
• If SELECT list includes an aggregate function and there is no
GROUP BY clause, SELECT list cannot reference a column out
with an aggregate function. For example, the following is
illegal:
SELECT staffNo, COUNT(salary)
FROM Staff;
Example 5.13 Use of COUNT(*)
How many properties cost more than £350 per
month to rent?
SELECT COUNT(*) AS count
FROM PropertyForRent
WHERE rent > 350;
Example 5.14 Use of COUNT(DISTINCT)
How many different properties viewed in May ‘01?
SELECT COUNT(DISTINCT propertyNo) AS count
FROM Viewing
WHERE viewDate BETWEEN ‘1-May-01’
AND ‘31-May-01’;
Example 5.15 Use of COUNT and SUM
Find number of Managers and sum of their salaries.
SELECT COUNT(staffNo) AS count,
SUM(salary) AS sum
FROM Staff
WHERE position = ‘Manager’;
Example 5.16 Use of MIN, MAX, AVG
• Find the minimum, maximum, and average staff salary.
• SELECT MIN(salary) AS myMin, MAX(salary) AS myMax, AVG(salary)
AS myAvg FROM Staff;
