Chapter 9 (Supplemental slides) - Type II nested query demonstration. This chapter presents the following content: More of an interactive demonstration than an animation, demonstrate conceptual execution model of Type II nested queries, type II nested queries are important for solving problems involving difference operator, problems with difference are not common,. | Chapter 9 Supplemental Slides Type II Nested Query Demonstration Welcome to the type II nested query demonstration - More of an interactive demonstration than an animation - Demonstrate conceptual execution model of Type II nested queries - Type II nested queries are important for solving problems involving difference operator - Problems with difference are not common - Important when occurring - Understand SQL statements with Type II nested queries: can be used in other problems such as division problems - See the derivation of rows in the result of a query involving a Type II nested query - Shows an interactive presentation of the static presentation of example of textbook - Complement lecture notes and book - Play it yourself at your own pace Type II Nested Query Animation SELECT FacSSN, FacLastName, FacDept FROM Faculty WHERE NOT EXISTS ( SELECT * FROM Student WHERE = ) Before beginning the animation, let's examine a SELECT statement containing a Type II nested query. Execution model of the Type II Nested query: - More complex execution model: nested loops - Nested query executes one time for each row in the outer query Example query: - Retrieve faculty who are not students - Type II nested query implementation of a difference problem - Set of all faculty MINUS Set of Faculty who are students - Result: set of faculty who are not students EXISTS comparison: - True if nested query is not empty (one or more rows); false otherwise - NOT EXISTS: true if nested query is empty Type II Nested Query Animation Faculty Student Sample Faculty and Student tables - Only the last Faculty row is not in the result - The first two Faculty rows do not match a row in the Student table Query execution: - For each Faculty row, execute nested query on the Student table - Faculty row does not qualify in the result unless it does not match any Student rows. Type II Nested Query Animation FacSSN FacLastName FacRank 098-76-5432 VINCE ASST Faculty . | Chapter 9 Supplemental Slides Type II Nested Query Demonstration Welcome to the type II nested query demonstration - More of an interactive demonstration than an animation - Demonstrate conceptual execution model of Type II nested queries - Type II nested queries are important for solving problems involving difference operator - Problems with difference are not common - Important when occurring - Understand SQL statements with Type II nested queries: can be used in other problems such as division problems - See the derivation of rows in the result of a query involving a Type II nested query - Shows an interactive presentation of the static presentation of example of textbook - Complement lecture notes and book - Play it yourself at your own pace Type II Nested Query Animation SELECT FacSSN, FacLastName, FacDept FROM Faculty WHERE NOT EXISTS ( SELECT * FROM Student WHERE = ) Before beginning the animation, let's examine a SELECT statement containing a
