tailieunhanh - Lecture Database Systems - Lecture 13

After completing this chapter, students will be able to: Grouping through GROUP BY clause, restricted groupings, subqueries, multi-table queries, multi-table queries, outer join, exists and not exists, database updates. | CSC271 Database Systems Lecture # 13 Summary: Previous Lecture Grouping through GROUP BY clause Restricted groupings Subqueries Multi-Table queries 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 Example For each branch, list staff who manage properties, including city in which branch is located and properties they manage SELECT , , , fName, lName, propertyNo FROM Branch b, Staff s, PropertyForRent p WHERE = AND = ORDER BY , , propertyNo; Result: Example Example (a) Find number of properties handled by each staff member SELECT , , COUNT(*) AS myCount FROM Staff s, PropertyForRent p WHERE = GROUP BY , ORDER BY , ; Result: Example (a) Cartesian Product and Join A join is a subset of a more general combination of two tables known as the Cartesian product Cardinality of Cartesian product Degree of Cartesian product ISO standard provides a special form of the SELECT statement for the Cartesian product: SELECT [DISTINCT | ALL] {* | columnList} FROM Table1 CROSS JOIN Table2 Computing a Join Form the Cartesian product of the tables named in the FROM clause If there is a WHERE clause, apply the search condition to each row of the product table, retaining those rows that satisfy the condition For each remaining row, determine the value of each item in the SELECT list to produce a single row in the result table Computing a Join If SELECT DISTINCT has been . | CSC271 Database Systems Lecture # 13 Summary: Previous Lecture Grouping through GROUP BY clause Restricted groupings Subqueries Multi-Table queries 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 Example For each branch, list staff who manage properties, including city in which branch is located and properties they manage SELECT , , , fName, lName, propertyNo FROM Branch b, Staff s, PropertyForRent p WHERE = AND = ORDER BY , , propertyNo; Result: Example .