TAILIEUCHUNG - 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
đang nạp các trang xem trước
