In the lab your will learn 'Sorting Data', 'Grouping Data' and 'Aggregate Operations Basics'
The ORDER BY clause have a list of columns which sort the result in respective order. A column may be either a column name or a column number.
Example: Produce a list of salaries for all staff, arranged in descending order of salary.
SELECT staffNo, fName, lName, salary FROM Staff ORDER BY salary DESC;
It is possible to include more than one element in the ORDER BY clause. The major sort key determines the overall order of the result table.
Example: Produce an abbreviated list of properties arranged in order of property type.
SELECT propertyNo, type, rooms, rent
FROM PropertyForRent
ORDER BY type;
SELECT propertyNo, type, rooms, rent
FROM PropertyForRent
ORDER BY type, rent DESC;
To perform some form of summation or aggregation of data, similar to the totals at the bottom of a report. The ISO standard defines five aggregate functions:
COUNT(*) is a special use of COUNT, which counts all the rows of a table, regardless of whether nulls or duplicate values occur.
It is important to note that an aggregate function can be used only in the SELECT list and in the HAVING clause. The following query is illegal:
SELECT staffNo, COUNT(salary)
FROM Staff;
Example: How many properties cost more than £350 per month to rent?
SELECT COUNT(*) AS myCount
FROM PropertyForRent
WHERE rent <=350;
Example: How many different properties were viewed in May 2004?
SELECT COUNT(DISTINCT propertyNo) AS myCount
FROM Viewing
WHERE viewDate BETWEEN ’1-May-04’ AND ’31-May-04’;
Example: Find the total number of Managers and the sum of their salaries.
SELECT COUNT(staffNo) AS myCount, SUM(salary) AS mySum
FROM Staff
WHERE position = ’Manager’;
Example: Find the minimum, maximum, and average staff salary.
SELECT MIN(salary) AS myMin, MAX(salary) AS myMax, AVG(salary) AS myAvg
FROM Staff;
Aggregate function can be used only in the SELECT list and in the HAVING clause. If the SELECT list includes an aggregate function and no GROUP BY clause is being used to group data together, then no item in the SELECT list can include any reference to a column unless that column is the argument to an aggregate function For example, the following query is illegal:
SELECT staffNo, COUNT(salary)
FROM Staff;
The ISO standard requires the SELECT clause and the GROUP BY clause to be closely integrated. When GROUP BY is used, each item in the SELECT list must be singlevalued per group. Further, the SELECT clause may contain only:
TheWHERE clause filters individual rows going into the final result table, whereas HAVING filters groups going into the final result table. The ISO standard requires that column names used in the HAVING clause must also appear in the GROUP BY list or be contained within an aggregate function.
The following rules apply to subqueries:
SELECT staffNo, fName, lName, position, salary
FROM Staff
WHERE (SELECT AVG(salary) FROM Staff) < salary;
The keywords ANY and ALL may be used with subqueries that produce a single column of numbers. For ALL the condition will only be true if it is satisfied by all values produced by the subquery For ANY the condition will be true if it is satisfied by any (one or more) values produced by the subquery
Example: 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: 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’);
To earn PASS status, system is about to evaluate you using multiple choice questions (MCQs) and True/False type questions.
Topics |
|
