After completing this chapter, students will be able to: Row selection using WHERE clause, WHERE clause and search conditions, sorting results using ORDER BY clause, SQL aggregate functions. | CSC271 Database Systems Lecture # 12 Summary: Previous Lecture Row selection using WHERE clause WHERE clause and search conditions Sorting results using ORDER BY clause SQL aggregate functions DreamHome Case Study Consist of following tables: Branch (branchNo, street, city, postcode) Staff (staffNo, fName, lName, position, sex, DOB, salary, branchNo) PropertyForRent (propertyNo, street, city, postcode, type, rooms, rent, ownerNo, staffNo, branchNo) Client (clientNo, fName, lName, telNo, prefType, maxRent) PrivateOwner (ownerNo, fName, lName, address, telNo) Viewing (clientNo, propertyNo, viewDate, comment) Instance of DreamHome Instance of DreamHome Instance of DreamHome Grouping Results Aggregate functions provide the totals at the bottom of a report However to obtain subtotals in the reports, we can use GROUP BY clause A query that includes the GROUP BY clause is called a grouped query It groups the data from the SELECT table(s) and produces a single summary row for each group The columns named in the GROUP BY clause are called the grouping columns Grouping Results 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 single-valued per group Further, the SELECT clause may contain only: column names aggregate functions constants expression involving combinations of the above Grouping Results All column names in the SELECT list must appear in the GROUP BY clause unless the name is used only in an aggregate function The contrary is not true: there may be column names in the GROUP BY clause that do not appear in the SELECT list When the WHERE clause is used with GROUP BY, the WHERE clause is applied first, then groups are formed from the remaining rows that satisfy the search condition The ISO standard considers two nulls to be equal for purposes of the GROUP BY clause Example Find number of staff in each branch and their total salaries SELECT branchNo, . | CSC271 Database Systems Lecture # 12 Summary: Previous Lecture Row selection using WHERE clause WHERE clause and search conditions Sorting results using ORDER BY clause SQL aggregate functions DreamHome Case Study Consist of following tables: Branch (branchNo, street, city, postcode) Staff (staffNo, fName, lName, position, sex, DOB, salary, branchNo) PropertyForRent (propertyNo, street, city, postcode, type, rooms, rent, ownerNo, staffNo, branchNo) Client (clientNo, fName, lName, telNo, prefType, maxRent) PrivateOwner (ownerNo, fName, lName, address, telNo) Viewing (clientNo, propertyNo, viewDate, comment) Instance of DreamHome Instance of DreamHome Instance of DreamHome Grouping Results Aggregate functions provide the totals at the bottom of a report However to obtain subtotals in the reports, we can use GROUP BY clause A query that includes the GROUP BY clause is called a grouped query It groups the data from the SELECT table(s) and produces a single summary row for each group The .
