tailieunhanh - Lecture Database design, application development and administration - Chapter 9: Advanced query formulation with SQL

This chapter extends your knowledge of query formulation to advanced matching problems. To solve these advanced matching problems, additional parts of the SELECT statement are introduced. This chapter continues with the learning approaches of Chapter 4: provide many examples to imitate and problem-solving guidelines to help you reason through difficult problems. | Chapter 9 Advanced Query Formulation with SQL Welcome to Chapter 9 on advanced query formulation Chapter 9 covers advanced matching problems: - These problems are not common but important when they occur. - Solving these problems requires more specialized knowledge: higher class grade and job expertise Objectives: - Analyze problem statement - Identify the essential problem element - Apply template SQL solutions - SELECT statement extensions - Outer join operator - Nested queries - Recognize - Understand conceptual evaluation process - Interpret SQL statements that use the extensions - Use in advanced matching problems Outline Outer join problems Type I nested queries Type II nested queries and difference problems Nested queries in the FROM clause Division problems Null value effects Outer join problems: - One-sided and full outer join - Outer join animation supplements the lecture notes - SQL:2003 notation for outer joins: Access 2002 and Oracle 9i Type I nested queries: - Understand evaluation - Use in SELECT and DELETE statements Difference problems: - Understand evaluation of Type II nested queries - Solve difference problems using Type I and II nested queries - Outer join animation supplements the lecture notes Nested queries in the FROM clause: - Motivation: nested aggregates and multiple aggregate computations - Example problems Division problems - Division operator: chapter 3 material - Animation supplement to reinforce the meaning of division - Use the Count method to solve division problems Null value effects: - Simple conditions - Compound conditions - Grouping and aggregate functions Outer Join Overview Join excludes non matching rows Preserving non matching rows is important in some business situations Outer join variations Full outer join One-sided outer join Review of Chapter 3 material: cover for review if desired Can skip this material in initial coverage of chapter 2 and cover now Importance of preserving non matching rows: - Offerings without . | Chapter 9 Advanced Query Formulation with SQL Welcome to Chapter 9 on advanced query formulation Chapter 9 covers advanced matching problems: - These problems are not common but important when they occur. - Solving these problems requires more specialized knowledge: higher class grade and job expertise Objectives: - Analyze problem statement - Identify the essential problem element - Apply template SQL solutions - SELECT statement extensions - Outer join operator - Nested queries - Recognize - Understand conceptual evaluation process - Interpret SQL statements that use the extensions - Use in advanced matching problems Outline Outer join problems Type I nested queries Type II nested queries and difference problems Nested queries in the FROM clause Division problems Null value effects Outer join problems: - One-sided and full outer join - Outer join animation supplements the lecture notes - SQL:2003 notation for outer joins: Access 2002 and Oracle 9i Type I nested queries: - Understand