Contents Listing

Preface
1. Introduction to SQL
What Is SQL?
A Brief History of SQL
A Simple Database
DML Statements
So Why Are There 17 More Chapters?
2. The WHERE Clause
Life Without WHERE
WHERE to the Rescue
WHERE Clause Evaluation
Conditions and Expressions
WHERE to Go from Here
3. Joins
What Is a Join Query?
Join Conditions
Types of Joins
Joins and Subqueries
DML Statements on a Join View
4. Group Operations
Aggregate Functions
The GROUP BY Clause
The HAVING Clause
Nested Group Operations
5. Subqueries
What Is a Subquery?
Noncorrelated Subqueries
Correlated Subqueries
Inline Views
Subquery Case Study: The Top N Performers
6. Handling Temporal Data
Time Zones
Temporal Data Types in Oracle
Literals of Temporal Types
Getting Temporal Data In and Out of a Database
Date and Time Formats
Manipulating Temporal Data
7. Set Operations
Set Operators
Precedence of Set Operators
Comparing Two Tables
Using NULLs in Compound Queries
Rules and Restrictions on Set Operations
8. Hierarchical Queries
Representing Hierarchical Information
Simple Hierarchy Operations
Oracle SQL Extensions
Complex Hierarchy Operations
Restrictions on Hierarchical Queries
Enhancements in Oracle Database 10g
9. DECODE and CASE
DECODE, NULLIF, NVL, and NVL2
The Case for CASE
DECODE and CASE Examples
10. Partitioning
Partitioning Concepts
Partitioning Tables
Partitioning Indexes
Partitioning Methods
Specifying Partitions
Partition Pruning
11. PL/SQL
What Is PL/SQL?
Procedures, Functions, and Packages
Calling Stored Functions from Queries
Restrictions on Calling PL/SQL from SQL
Stored Functions in DML Statements
The SQL Inside Your PL/SQL
12. Objects and Collections
Object Types
Collection Types
Collection Instantiation
Querying Collections
Collection Unnesting
Collection Functions
Comparing Collections
Manipulating Collections
Multilevel Collections
13. Advanced Group Operations
Multiple Summary Levels
Pushing the GROUPING Envelope
The GROUPING_ID and GROUP_ID Functions
14. Advanced Analytic SQL
Analytic SQL Overview
Ranking Functions
Windowing Functions
Reporting Functions
Summary
15. SQL Best Practices
Know When to Use Specific Constructs
Avoid Unnecessary Parsing
Consider Literal SQL for Decision-Support Systems
16. XML
What Is XML?
Storing XML Data
Generating XML Documents
Summary
17. Regular Expressions
Elementary Regular Expression Syntax
Advanced Function Options
Advanced Regular Expression Syntax
18. Model Queries
Basic Elements of a Model Query
Cell References
Rules
Iterative Models
Reference Models
Appendix: Oracle's Old Join Syntax
Index