Database Systems

Advanced SQL

Lecture 07

visit [Link] for more.

1
 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.

visit [Link] for more.
2
 Select Statement - Aggregates
• Each operates on a single column of a table and return 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.
• 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 (it eliminates duplicates itself), but may have
with SUM/AVG, e.g. – select avg(sal) from emp ≠ select avg(distinct sal) from emp;

visit [Link] for more.

3
 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 outwith an aggregate function. For
example, following is illegal:

SELECT staffNo, COUNT(salary) FROM Staff;

visit [Link] for more.

4
 Select Statement - Aggregates
• Use of COUNT(*)

• How many properties cost more than 350 per month to rent?

SELECT COUNT(*) AS count FROM PropertyForRent

WHERE rent > 350;

visit [Link] for more.

5
 Select Statement - Aggregates
• Use of COUNT(DISTINCT)

• How many different properties viewed in May ‘01?

SELECT COUNT(DISTINCT propertyNo) AS count FROM Viewing

WHERE date BETWEEN ‘1-May-01’ AND '31-May-01';

visit [Link] for more.

6
 Select Statement - Aggregates
• 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';

visit [Link] for more.

7
 Select Statement - Aggregates
• Use of MIN, MAX, AVG

• Find minimum, maximum, and average staff salary.

SELECT MIN(salary) AS min,

MAX(salary) AS max,
AVG(salary) AS avg
FROM Staff;

visit [Link] for more.

8
 Select Statement – Column Grouping
• Count the number of customers with addresses in each state to which we ship.
SELECT CustomerState, COUNT (CustomerState)
FROM Customer_T
GROUP BY CustomerState;

SELECT CustomerState, COUNT (CustomerState)

FROM Customer_T
GROUP BY CustomerState
HAVING COUNT (CustomerState) > 1;

• Use GROUP BY clause to get sub-totals.

• Useful when paired with aggregate functions, SUM or COUNT
visit [Link] for more.
9
 Example – Column Grouping
• Find number of staff in each branch and their total salaries.

SELECT branchNo, COUNT(staffNo) AS count, SUM(salary) AS sum

FROM Staff
GROUP BY branchNo
ORDER BY branchNo;

visit [Link] for more.

10
 Restricted Grouping – Having Clause
• HAVING clause is designed for use with GROUP BY to restrict groups that appear in final
result table.

• For each branch with more than 1 member of staff, find number of staff in each branch
and sum of their salaries.

SELECT branchNo, COUNT(staffNo) AS count, SUM(salary) AS sum

FROM Staff
GROUP BY branchNo
HAVING COUNT(staffNo) > 1
ORDER BY branchNo;

visit [Link] for more.

11
 Processing Multiple Tables
• Relational Table
• Primary key from one table and a foreign key that references the table with
the primary key will share a common domain and are frequently used to
establish a join.

• Join
• A relational table that causes two tables (recommended upto 10 tables) with
a common domain to be combined into a single table or view.

• Joins without Relationships

• Occasionally, joins will be established using columns that share a common
visit [Link]
domain but not the primary / foreign key relationship, and that also [Link] more.
12
 Equi-Join
• The joining condition is based on equality between values in the common columns.
Number of rows is equal from each table.

• Query: What are the customer IDs and names of all customers, along with the order IDs
for all the orders they have placed?

SELECT Customer_T.CustomerID, Order_T.CustomerID, CustomerName, OrderID

FROM Customer_T, Order_T
WHERE Customer_T.CustomerID = Order_T. CustomerID
ORDER BY OrderID;

visit [Link] for more.

13
 Equi-Join
• Query: What are the customer IDs and names of all customers, along with the order IDs
for all the orders they have placed?

SELECT Customer_T.CustomerID, Order_T.CustomerID, CustomerName, OrderID

FROM Customer_T INNER JOIN Order_T ON
Customer_T.CustomerID = Order_T.CustomerID
ORDER BY OrderID;

SELECT Customer_T.CustomerID, Order_T.CustomerID, CustomerName, OrderID

FROM Customer_T INNER JOIN Order_T USING CustomerID
ORDER BY OrderID ; visit [Link] for more.
14
 Natural-Join
• Most commonly used form of join operation.
• Same as an equi-join, except that it is performed over matching columns, and one of the
duplicate columns is eliminated in the result table.

• Query: For each customer who has placed an order, what is the customer’s ID, name, and
order number?

SELECT Customer_T.CustomerID, CustomerName, OrderID

FROM Customer_T NATURAL JOIN Order_T ON
Customer_T.CustomerID = Order_T.CustomerID;

visit [Link] for more.

15
 Outer-Join
• Rows that do not have matching values in common columns are also included.
• Null values appear in columns where there is not a match between tables.
• OUTER JOIN syntax does not apply easily to a join condition of more than two tables.

• Left Outer Join

• For all rows in A that have no matching rows in B
• Right Outer Join
• For all rows in B that have no matching rows in A
• Full Outer Join
• Returns all rows from A and B, extended with nulls if they do not satisfy the join
condition
visit [Link] for more.
16
 Outer-Join
• Left Outer Join
• Query: List customer name, identification number, and order number for all
customers listed in the Customer table. Include the customer identification
number and name even if there is no order available for that customer.

SELECT Customer_T.CustomerID, CustomerName, OrderID

FROM Customer_T LEFT OUTER JOIN Order_T ON
WHERE Customer_T.CustomerID = Order_T. CustomerID;

visit [Link] for more.

17
 Outer-Join
• Right Outer Join
• Query: List customer name, identification number, and order number for all
orders listed in the Order table. Include the order number, even if there is no
customer name and identification number available.

SELECT Customer_T.CustomerID,CustomerName, OrderID

FROM Customer_T RIGHT OUTER JOIN Order_T ON
Customer_T.CustomerID = Order_T.CustomerID;

visit [Link] for more.

18
 Multiple Tables Join
• Query: Assemble all information necessary to create an invoice for order number
1006.

visit [Link] for more.

19
 Multiple Tables Join
SELECT Customer_T.CustomerID, CustomerName,
CustomerAddress, CustomerCity, CustomerState,
CustomerPostalCode, Order_T.OrderID,
OrderDate, OrderedQuantity, ProductDescription,
StandardPrice, (OrderedQuantity * ProductStandardPrice)

FROM Customer_T, Order_T, OrderLine_T, Product_T

WHERE Order_T.CustomerID = Customer_T.CustomerID

AND Order_T.OrderID = OrderLine_T.OrderID
AND OrderLine_T.ProductID = Product_T.ProductID;
visit [Link] for more.
AND Order_T.OrderID = 1006; 20
 Self-Join
• Unary relationship: Joining a table with itself.
• Query: What are the employee ID and name of each employee and the name of
his or her supervisor (label the supervisor’s name Manager)?

SELECT [Link], [Link], [Link] AS Manager

FROM Employee_T E, Employee_T M
WHERE [Link] = [Link];

visit [Link] for more.

21
 Subqueries
• Some SQL statements can have a SELECT embedded within them.

• A subselect can be 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 DELETEs.

visit [Link] for more.

22
 Subquery with Equality
• List staff who work in branch at '163 Main St'.

SELECT staffNo, fName, lName, position

FROM Staff
WHERE branchNo =
(SELECT branchNo
FROM Branch
WHERE street = '163 Main St');

• Inner SELECT finds branch number for branch at '163 Main St' ('B003').
visit [Link] for more.
• Outer SELECT then retrieves details of all staff who work at this branch. 23
 Subquery with Aggregate
• List all staff whose salary is greater than the average salary, and show by how
much.

SELECT staffNo, fName, lName, position,

salary – (SELECT AVG(salary) FROM Staff)
As SalDiff
FROM Staff
WHERE salary >
(SELECT AVG(salary) FROM Staff);

visit [Link] for more.

24
 Nested Subquery – Use of IN
• List properties handled by staff 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
visit [Link] for more.
WHERE street = '163 Main St')); 25
 Nested Subquery – 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
subquery.
• With ANY, condition will be true if it is satisfied by any values produced by
subquery.
• If subquery is empty, ALL returns true, ANY returns false.
• SOME may be used in place of ANY.

visit [Link] for more.

26
 Nested Subquery – Use of ANY/SOME
• Find staff whose salary is larger than 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');

visit [Link] for more.

27
 Nested Subquery – Use of ALL
• Find staff whose salary is larger than 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');

visit [Link] for more.

28
 EXISTS and NOT EXISTS
• EXISTS and NOT EXISTS are for use only with subqueries.
• Produce a simple true/false result.
• True if and only if there exists at least one row in result table returned by
subquery.
• False if subquery returns an empty result table.
• NOT EXISTS is the opposite of EXISTS.
• As (NOT) EXISTS check only for existence or non-existence of rows in subquery
result table, subquery can contain any number of columns.
• Common for subqueries following (NOT) EXISTS to be of form:
(SELECT * ...)
visit [Link] for more.
29
 Query using EXISTS
• Find all staff who work in a London branch.

SELECT staffNo, fName, lName, position

FROM Staff s
WHERE EXISTS
(SELECT *
FROM Branch b
WHERE [Link] = [Link] AND
city = 'London');

visit [Link] for more.

30
 Use of UNION
• List all cities where there is either a branch or property.

(SELECT city
FROM Branch
WHERE city IS NOT NULL) UNION
(SELECT city
FROM PropertyForRent
WHERE city IS NOT NULL);

visit [Link] for more.

31
 Use of INTERSECT
• List all cities where there is both a branch and rental property.

(SELECT city FROM Branch)

INTERSECT
(SELECT city FROM PropertyForRent);

• Could rewrite this query without INTERSECT operator:

SELECT [Link]
FROM Branch b PropertyForRent p
WHERE [Link] = [Link];
visit [Link] for more.
32
 Use of EXCEPT
• List of all cities where there is a branch but no properties.

(SELECT city FROM Branch)

city FROM EXCEPT
(SELECT PropertyForRent);

• Could rewrite this query without EXCEPT:

SELECT DISTINCT city FROM Branch
WHERE city NOT IN
(SELECT city FROM PropertyForRent);
visit [Link] for more.
33
 INSERT … SELECT
• Populate StaffPropCount using Staff and PropertyForRent tables.

INSERT INTO StaffPropCount

(SELECT [Link], fName, lName, COUNT(*)
FROM Staff s, PropertyForRent p
WHERE [Link] = [Link]
GROUP BY [Link], fName, lName);

visit [Link] for more.

34