CSC 4710 Lecture Notes - Lecture 13: Tuple, Relational Algebra, Global Variable
26 Oct 2019
School
Department
Course
Professor
Csc 4710 lecture 13
• Duplicates
• Duplicate rows not allowed in a relation
• However, duplicate elimination from query result is costly and not done by default; must be
explicitly requested:
• Use of Expressions
• Set Operators
• SQL provides UNION, EXCEPT (set difference), and INTERSECT for union compatible tables
• Example: Find all professors in the CS Department and all professors that have taught CS
courses
• Nested Queries
• Correlated Nested Queries
• Correlated Nested Queries (con’t)
• Tuple variables T and C are local to subquery
• Tuple variables P and D are global to subquery
• Correlation: subquery uses a global variable, D
• The value of D.DeptId parameterizes an evaluation of the subquery
• Subquery must (at least) be re-evaluated for each distinct value of D.DeptId
• Correlated queries can be expensive to evaluate
• Division in SQL
• Query type: Find the subset of items in one set that are related to all items in another set
• Example: Find professors who taught courses in all departments
• Why does this involve division?
• Division in SQL
• Strategy for implementing division in SQL:
• Find set, A, of all departments in which a particular professor, p, has taught a course
• Find set, B, of all departments
• Output p if A B, or, equivalently, if B–A is empty
• But how to do this exactly in SQL?
• Division Solution Sketch (1)
• Division Solution Sketch (1)
• Division – SQL Solution in details
• Aggregates
• Functions that operate on sets:
• COUNT, SUM, AVG, MAX, MIN
• Produce numbers (not tables)
• Aggregates over multiple rows into one row
• Not part of relational algebra (but not hard to add)
• Aggregates (cont’d)
• Grouping
• But how do we compute the number of courses taught in S2000 per professor?
• Strategy 1: Fire off a separate query for each professor:
