1. Chapter 1 Introduction to SQL

   1. What Is SQL?

   2. A Brief History of SQL

   3. A Simple Database

   4. DML Statements

   5. So Why Are There 17 More Chapters?

2. Chapter 2 The WHERE Clause

   1. Life Without WHERE

   2. WHERE to the Rescue

   3. WHERE Clause Evaluation

   4. Conditions and Expressions

   5. WHERE to Go from Here

3. Chapter 3 Joins

   1. What Is a Join Query?

   2. Join Conditions

   3. Types of Joins

   4. Joins and Subqueries

   5. DML Statements on a Join View

4. Chapter 4 Group Operations

   1. Aggregate Functions

   2. The GROUP BY Clause

   3. The HAVING Clause

   4. Nested Group Operations

5. Chapter 5 Subqueries

   1. What Is a Subquery?

   2. Noncorrelated Subqueries

   3. Correlated Subqueries

   4. Inline Views

   5. Subquery Case Study: The Top N Performers

6. Chapter 6 Handling Temporal Data

   1. Time Zones

   2. Temporal Data Types in Oracle

   3. Literals of Temporal Types

   4. Getting Temporal Data In and Out of a Database

   5. Date and Time Formats

   6. Manipulating Temporal Data

7. Chapter 7 Set Operations

   1. Set Operators

   2. Precedence of Set Operators

   3. Comparing Two Tables

   4. Using NULLs in Compound Queries

   5. Rules and Restrictions on Set Operations

8. Chapter 8 Hierarchical Queries

   1. Representing Hierarchical Information

   2. Simple Hierarchy Operations

   3. Oracle SQL Extensions

   4. Complex Hierarchy Operations

   5. Restrictions on Hierarchical Queries

   6. Enhancements in Oracle Database 10g

9. Chapter 9 DECODE and CASE

   1. DECODE, NULLIF, NVL, and NVL2

   2. The Case for CASE

   3. DECODE and CASE Examples

10. Chapter 10 Partitioning

    1. Partitioning Concepts

    2. Partitioning Tables

    3. Partitioning Indexes

    4. Partitioning Methods

    5. Specifying Partitions

    6. Partition Pruning

11. Chapter 11 PL/SQL

    1. What Is PL/SQL?

    2. Procedures, Functions, and Packages

    3. Calling Stored Functions from Queries

    4. Restrictions on Calling PL/SQL from SQL

    5. Stored Functions in DML Statements

    6. The SQL Inside Your PL/SQL

12. Chapter 12 Objects and Collections

    1. Object Types

    2. Collection Types

    3. Collection Instantiation

    4. Querying Collections

    5. Collection Unnesting

    6. Collection Functions

    7. Comparing Collections

    8. Manipulating Collections

    9. Multilevel Collections

13. Chapter 13 Advanced Group Operations

    1. Multiple Summary Levels

    2. Pushing the GROUPING Envelope

    3. The GROUPING_ID and GROUP_ID Functions

14. Chapter 14 Advanced Analytic SQL

    1. Analytic SQL Overview

    2. Ranking Functions

    3. Windowing Functions

    4. Reporting Functions

    5. Summary

15. Chapter 15 SQL Best Practices

    1. Know When to Use Specific Constructs

    2. Avoid Unnecessary Parsing

    3. Consider Literal SQL for Decision-Support Systems

16. Chapter 16 XML

    1. What Is XML?

    2. Storing XML Data

    3. Generating XML Documents

    4. Summary

17. Chapter 17 Regular Expressions

    1. Elementary Regular Expression Syntax

    2. Advanced Function Options

    3. Advanced Regular Expression Syntax

18. Chapter 18 Model Queries

    1. Basic Elements of a Model Query

    2. Cell References

    3. Rules

    4. Iterative Models

    5. Reference Models

1. Appendix A Oracle's Old Join Syntax

   1. Old Inner Join Syntax

   2. Old Outer Join Syntax

   3. Advantages of the New Join Syntax

2. Colophon

Our look is the result of reader comments, our own experimentation, and feedback from distribution channels. Distinctive covers complement our distinctive approach to technical topics, breathing personality and life into potentially dry subjects. The animals on the cover of Mastering Oracle SQL, Second Edition are lantern flies. The lantern fly is mostly tropical, with a wingspan of up to six inches. The lantern fly's elongated head is an evolutionary adaptation called automimicry, in which parts of the body are disguised or artifically shifted to other areas to confuse predators: the lantern fly's head looks like a tail, and its tail looks like a head. On the rear it has artificial eyes and antennae. Matt Hutchinson was the production editor for Mastering Oracle SQL, Second Edition. Octal Publishing, Inc. provided production services. Sarah Sherman, Marlowe Shaeffer, and Colleen Gorman provided quality control.

Ellie Volckhausen and Emma Colby designed the cover of this book, based on a series design by Edie Freedman. The cover image is from Johnson's Natural History. Emma Colby produced the cover layout with QuarkXPress 4.1 using Adobe's ITC Garamond font.

Melanie Wang designed the interior layout, based on a series design by David Futato. This book was converted by Julie Hawks to FrameMaker 5.5.6 with a format conversion tool created by Erik Ray, Jason McIntosh, Neil Walls, and Mike Sierra that uses Perl and XML technologies. The text font is Linotype Birka; the heading font is Adobe Myriad Condensed; and the code font is LucasFont's TheSans Mono Condensed. The illustrations that appear in the book were produced by Robert Romano and Jessamyn Read using Macromedia FreeHand 9 and Adobe Photoshop 6. The tip and warning icons were drawn by Christopher Bing. This colophon was written by Colleen Gorman.