Mastering Oracle SQL

Preface; Why We Wrote This Book; What's New in Oracle SQL?; Objectives of This Book; Audience for This Book; Platform and Version; Structure of This Book; Conventions Used in This Book; Using Code Examples; Comments and Questions; Acknowledgments; Chapter 1: Introduction to SQL; 1.1 What Is SQL?; 1.2 A Brief History of SQL; 1.3 A Simple Database; 1.4 DML Statements; 1.5 So Why Are There 17 More Chapters?; Chapter 2: The WHERE Clause; 2.1 Life Without WHERE; 2.2 WHERE to the Rescue; 2.3 WHERE Clause Evaluation; 2.4 Conditions and Expressions; 2.5 WHERE to Go from Here; Chapter 3: Joins; 3.1 What Is a Join Query?; 3.2 Join Conditions; 3.3 Types of Joins; 3.4 Joins and Subqueries; 3.5 DML Statements on a Join View; Chapter 4: Group Operations; 4.1 Aggregate Functions; 4.2 The GROUP BY Clause; 4.3 The HAVING Clause; 4.4 Nested Group Operations; Chapter 5: Subqueries; 5.1 What Is a Subquery?; 5.2 Noncorrelated Subqueries; 5.3 Correlated Subqueries; 5.4 Inline Views; 5.5 Subquery Case Study: The Top N Performers; Chapter 6: Handling Temporal Data; 6.1 Time Zones; 6.2 Temporal Data Types in Oracle; 6.3 Literals of Temporal Types; 6.4 Getting Temporal Data In and Out of a Database; 6.5 Date and Time Formats; 6.6 Manipulating Temporal Data; Chapter 7: Set Operations; 7.1 Set Operators; 7.2 Precedence of Set Operators; 7.3 Comparing Two Tables; 7.4 Using NULLs in Compound Queries; 7.5 Rules and Restrictions on Set Operations; Chapter 8: Hierarchical Queries; 8.1 Representing Hierarchical Information; 8.2 Simple Hierarchy Operations; 8.3 Oracle SQL Extensions; 8.4 Complex Hierarchy Operations; 8.5 Restrictions on Hierarchical Queries; 8.6 Enhancements in Oracle Database 10g; Chapter 9: DECODE and CASE; 9.1 DECODE, NULLIF, NVL, and NVL2; 9.2 The Case for CASE; 9.3 DECODE and CASE Examples; Chapter 10: Partitioning; 10.1 Partitioning Concepts; 10.2 Partitioning Tables; 10.3 Partitioning Indexes; 10.4 Partitioning Methods; 10.5 Specifying Partitions; 10.6 Partition Pruning; Chapter 11: PL/SQL; 11.1 What Is PL/SQL?; 11.2 Procedures, Functions, and Packages; 11.3 Calling Stored Functions from Queries; 11.4 Restrictions on Calling PL/SQL from SQL; 11.5 Stored Functions in DML Statements; 11.6 The SQL Inside Your PL/SQL; Chapter 12: Objects and Collections; 12.1 Object Types; 12.2 Collection Types; 12.3 Collection Instantiation; 12.4 Querying Collections; 12.5 Collection Unnesting; 12.6 Collection Functions; 12.7 Comparing Collections; 12.8 Manipulating Collections; 12.9 Multilevel Collections; Chapter 13: Advanced Group Operations; 13.1 Multiple Summary Levels; 13.2 Pushing the GROUPING Envelope; 13.3 The GROUPING_ID and GROUP_ID Functions; Chapter 14: Advanced Analytic SQL; 14.1 Analytic SQL Overview; 14.2 Ranking Functions; 14.3 Windowing Functions; 14.4 Reporting Functions; 14.5 Summary; Chapter 15: SQL Best Practices; 15.1 Know When to Use Specific Constructs; 15.2 Avoid Unnecessary Parsing; 15.3 Consider Literal SQL for Decision-Support Systems; Chapter 16: XML; 16.1 What Is XML?; 16.2 Storing XML Data; 16.3 Generating XML Documents; 16.4 Summary; Chapter 17: Regular Expressions; 17.1 Elementary Regular Expression Syntax; 17.2 Advanced Function Options; 17.3 Advanced Regular Expression Syntax; Chapter 18: Model Queries; 18.1 Basic Elements of a Model Query; 18.2 Cell References; 18.3 Rules; 18.4 Iterative Models; 18.5 Reference Models; Appendix A: Oracle's Old Join Syntax; A.1 Old Inner Join Syntax; A.2 Old Outer Join Syntax; A.3 Advantages of the New Join Syntax; Colophon;