Mastering Oracle SQL, 2nd Edition

Mastering Oracle SQL, 2nd Edition

by Sanjay Mishra, Alan Beaulieu
Released June 2004
Publisher(s): O'Reilly Media, Inc.
ISBN: 9780596006327

Read it now on the O’Reilly learning platform with a 10-day free trial.

O’Reilly members get unlimited access to books, live events, courses curated by job role, and more from O’Reilly and nearly 200 top publishers.

Buy on Amazon Buy on ebooks.com
Start your free trial

Book description

The vast majority of Oracle SQL books discuss some syntax, provide the barest rudiments of using Oracle SQL, and perhaps include a few simple examples. It might be enough to pass a survey course, or give you some buzz words to drop in conversation with real Oracle DBAs. But if you use Oracle SQL on a regular basis, you want much more. You want to access the full power of SQL to write queries in an Oracle environment. You want a solid understanding of what's possible with Oracle SQL, creative techniques for writing effective and accurate queries, and the practical, hands-on information that leads to true mastery of the language. Simply put, you want useful, expert best practices that can be put to work immediately, not just non-vendor specific overview or theory.Updated to cover the latest version of Oracle, Oracle 10g, this edition of the highly regarded Mastering Oracle SQL has a stronger focus on technique and on Oracle's implementation of SQL than any other book on the market. It covers Oracle s vast library of built-in functions, the full range of Oracle SQL query-writing features, regular expression support, new aggregate and analytic functions, subqueries in the SELECT and WITH clauses, multiset union operators, enhanced support for hierarchical queries: leaf and loop detection, and the CONNECT_BY_ROOT operator, new partitioning methods (some introduced in Oracle9i Release 2), and the native XML datatype, XMLType.Mastering Oracle SQL, 2nd Edition fills the gap between the sometimes spotty vendor documentation, and other books on SQL that just don't explore the full depth of what is possible with Oracle-specific SQL. For those who want to harness the untapped (and often overlooked) power of Oracle SQL, this essential guide for putting Oracle SQL to work will prove invaluable.

Table of contents

  1. A Note Regarding Supplemental Files
  2. Preface
    1. Why We Wrote This Book
    2. What’s New in Oracle SQL?
    3. Objectives of This Book
    4. Audience for This Book
    5. Platform and Version
    6. Structure of This Book
    7. Conventions Used in This Book
    8. Using Code Examples
    9. Comments and Questions
    10. Acknowledgments
      1. From Sanjay
      2. From Alan
  3. 1. Introduction to SQL
    1. 1.1. What Is SQL?
    2. 1.2. A Brief History of SQL
      1. 1.2.1. Oracle’s SQL Implementation
      2. 1.2.2. Theoretical Versus Practical Terminology
    3. 1.3. A Simple Database
    4. 1.4. DML Statements
      1. 1.4.1. The SELECT Statement
        1. 1.4.1.1. SELECT clause elements
        2. 1.4.1.2. Ordering your results
        3. 1.4.1.3. Removing duplicates
      2. 1.4.2. The INSERT Statement
        1. 1.4.2.1. Single-table inserts
        2. 1.4.2.2. Multitable inserts
      3. 1.4.3. The DELETE Statement
      4. 1.4.4. The UPDATE Statement
      5. 1.4.5. The MERGE Statement
    5. 1.5. So Why Are There 17 More Chapters?
  4. 2. The WHERE Clause
    1. 2.1. Life Without WHERE
    2. 2.2. WHERE to the Rescue
    3. 2.3. WHERE Clause Evaluation
    4. 2.4. Conditions and Expressions
      1. 2.4.1. Equality/Inequality Conditions
      2. 2.4.2. Membership Conditions
      3. 2.4.3. Range Conditions
      4. 2.4.4. Matching Conditions
      5. 2.4.5. Regular Expressions
      6. 2.4.6. Handling NULL
      7. 2.4.7. Placement of Join Conditions
    5. 2.5. WHERE to Go from Here
  5. 3. Joins
    1. 3.1. What Is a Join Query?
    2. 3.2. Join Conditions
      1. 3.2.1. The USING Clause
      2. 3.2.2. Conditions Involving Multiple Columns
      3. 3.2.3. The Natural Join Clause
    3. 3.3. Types of Joins
      1. 3.3.1. Cross Joins/Cartesian Products
      2. 3.3.2. Inner Joins
      3. 3.3.3. Outer Joins
        1. 3.3.3.1. Left outer joins
        2. 3.3.3.2. Right outer joins
        3. 3.3.3.3. Full outer joins
      4. 3.3.4. Equi-Joins Versus Non-Equi-Joins
      5. 3.3.5. Self Joins
        1. 3.3.5.1. Self outer joins
        2. 3.3.5.2. Self non-equi-joins
      6. 3.3.6. Partition Outer Joins
    4. 3.4. Joins and Subqueries
    5. 3.5. DML Statements on a Join View
      1. 3.5.1. Key-Preserved Tables
      2. 3.5.2. INSERT Statements on a Join View
      3. 3.5.3. DELETE Statements on a Join View
      4. 3.5.4. UPDATE Statements on a Join View
      5. 3.5.5. Data Dictionary Views to Find Updatable Columns
      6. 3.5.6. Impact of WITH CHECK OPTION
  6. 4. Group Operations
    1. 4.1. Aggregate Functions
      1. 4.1.1. NULLs and Aggregate Functions
      2. 4.1.2. Use of DISTINCT and ALL
    2. 4.2. The GROUP BY Clause
      1. 4.2.1. Correspondence Between SELECT and GROUP BY
        1. 4.2.1.1. Aggregate expressions generally require a GROUP BY clause
        2. 4.2.1.2. GROUP BY clause must include all nonaggregate expressions
        3. 4.2.1.3. Aggregate functions not allowed in GROUP BY clause
        4. 4.2.1.4. Constants can be omitted from the GROUP BY clause
        5. 4.2.1.5. Scalar functions may be grouped by their underlying column
        6. 4.2.1.6. Concatenated columns may be grouped in either of two ways
        7. 4.2.1.7. You can sometimes exclude a nonaggregate expression from the GROUP BY clause
        8. 4.2.1.8. You are not required to show your GROUP BY columns
      2. 4.2.2. GROUP BY Clause and NULL Values
      3. 4.2.3. GROUP BY Clause with WHERE Clause
    3. 4.3. The HAVING Clause
    4. 4.4. Nested Group Operations
  7. 5. Subqueries
    1. 5.1. What Is a Subquery?
    2. 5.2. Noncorrelated Subqueries
      1. 5.2.1. Single-Row, Single-Column Subqueries
      2. 5.2.2. Multiple-Row, Single-Column Subqueries
      3. 5.2.3. Multiple-Column Subqueries
      4. 5.2.4. The WITH Clause
    3. 5.3. Correlated Subqueries
    4. 5.4. Inline Views
      1. 5.4.1. Inline View Basics
      2. 5.4.2. Query Execution
      3. 5.4.3. Data Set Fabrication
      4. 5.4.4. Overcoming SQL Restrictions
        1. 5.4.4.1. Hierarchical queries
        2. 5.4.4.2. Aggregate queries
      5. 5.4.5. Inline Views in DML Statements
      6. 5.4.6. Restricting Access Using WITH CHECK OPTION
      7. 5.4.7. Global Inline Views
    5. 5.5. Subquery Case Study: The Top N Performers
      1. 5.5.1. A Look at the Data
      2. 5.5.2. Your Assignment
      3. 5.5.3. Second Attempt
      4. 5.5.4. Final Answer
  8. 6. Handling Temporal Data
    1. 6.1. Time Zones
      1. 6.1.1. Database Time Zone
      2. 6.1.2. Session Time Zone
    2. 6.2. Temporal Data Types in Oracle
      1. 6.2.1. The DATE Data Type
      2. 6.2.2. The TIMESTAMP Data Types
        1. 6.2.2.1. TIMESTAMP
        2. 6.2.2.2. TIMESTAMP WITH TIME ZONE
        3. 6.2.2.3. TIMESTAMP WITH LOCAL TIME ZONE
      3. 6.2.3. The INTERVAL Data Types
        1. 6.2.3.1. INTERVAL YEAR TO MONTH
        2. 6.2.3.2. INTERVAL DAY TO SECOND
    3. 6.3. Literals of Temporal Types
      1. 6.3.1. DATE Literals
      2. 6.3.2. TIMESTAMP Literals
      3. 6.3.3. INTERVAL Literals
        1. 6.3.3.1. YEAR TO MONTH interval literals
        2. 6.3.3.2. DAY TO SECOND interval literals
    4. 6.4. Getting Temporal Data In and Out of a Database
      1. 6.4.1. TO_DATE, TO_TIMESTAMP, and TO_TIMESTAMP_TZ
        1. 6.4.1.1. Using the default formats
        2. 6.4.1.2. Specifying a format
        3. 6.4.1.3. Converting to TIMESTAMP WITH LOCAL TIME ZONE
      2. 6.4.2. TO_YMINTERVAL and TO_DSINTERVAL
      3. 6.4.3. NUMTOYMINTERVAL and NUMTODSINTERVAL
      4. 6.4.4. TO_CHAR
    5. 6.5. Date and Time Formats
      1. 6.5.1. AD/BC Indicators
      2. 6.5.2. AM/PM Indicators
      3. 6.5.3. Case-Sensitivity of Formats
      4. 6.5.4. Two-Digit Years
      5. 6.5.5. ISO Standard Issues
        1. 6.5.5.1. ISO standard weeks
        2. 6.5.5.2. ISO standard year
      6. 6.5.6. Database Parameters
    6. 6.6. Manipulating Temporal Data
      1. 6.6.1. Using the Built-in Temporal Functions
      2. 6.6.2. Addition
        1. 6.6.2.1. Adding numbers to a DATE
        2. 6.6.2.2. Adding months to a DATE
        3. 6.6.2.3. Adding true INTERVAL values rather than numbers
      3. 6.6.3. Subtraction
        1. 6.6.3.1. Subtracting one DATE from another
        2. 6.6.3.2. Subtracting one TIMESTAMP from another
        3. 6.6.3.3. Subtracting a number from a DATE
        4. 6.6.3.4. Subtracting months from a DATE
        5. 6.6.3.5. Number of months between two DATEs
        6. 6.6.3.6. Time interval between two DATEs
        7. 6.6.3.7. Subtracting an INTERVAL from a DATE or TIMESTAMP
      4. 6.6.4. Determining the First Day of the Month
      5. 6.6.5. Rounding and Truncating Dates
      6. 6.6.6. SELECTing Data Based on Date Ranges
      7. 6.6.7. Creating a Date Pivot Table
      8. 6.6.8. Summarizing by a Date/Time Element
  9. 7. Set Operations
    1. 7.1. Set Operators
      1. 7.1.1. UNION ALL
      2. 7.1.2. UNION
      3. 7.1.3. INTERSECT
      4. 7.1.4. MINUS
    2. 7.2. Precedence of Set Operators
    3. 7.3. Comparing Two Tables
    4. 7.4. Using NULLs in Compound Queries
    5. 7.5. Rules and Restrictions on Set Operations
  10. 8. Hierarchical Queries
    1. 8.1. Representing Hierarchical Information
    2. 8.2. Simple Hierarchy Operations
      1. 8.2.1. Finding Root Nodes
      2. 8.2.2. Finding a Node’s Immediate Parent
      3. 8.2.3. Finding Leaf Nodes
    3. 8.3. Oracle SQL Extensions
      1. 8.3.1. Tree Traversal Using ANSI SQL
      2. 8.3.2. START WITH . . . CONNECT BY and PRIOR
      3. 8.3.3. The LEVEL Pseudocolumn
    4. 8.4. Complex Hierarchy Operations
      1. 8.4.1. Finding the Number of Levels
      2. 8.4.2. Listing Records in Hierarchical Order
      3. 8.4.3. Checking for Ascendancy
      4. 8.4.4. Deleting a Subtree
      5. 8.4.5. Listing Multiple Root Nodes
      6. 8.4.6. Listing the Top Few Levels of a Hierarchy
      7. 8.4.7. Aggregating a Hierarchy
      8. 8.4.8. Ordering Hierarchical Data
      9. 8.4.9. Finding the Path to a Node
    5. 8.5. Restrictions on Hierarchical Queries
    6. 8.6. Enhancements in Oracle Database 10g
      1. 8.6.1. Getting Data from the Root Row
      2. 8.6.2. Ignoring Cycles
      3. 8.6.3. Identifying Cycles
      4. 8.6.4. Identifying Leaf Nodes
  11. 9. DECODE and CASE
    1. 9.1. DECODE, NULLIF, NVL, and NVL2
      1. 9.1.1. DECODE
      2. 9.1.2. NULLIF
      3. 9.1.3. NVL and NVL2
    2. 9.2. The Case for CASE
      1. 9.2.1. Searched CASE Expressions
      2. 9.2.2. Simple CASE Expressions
    3. 9.3. DECODE and CASE Examples
      1. 9.3.1. Result Set Transformations
      2. 9.3.2. Selective Function Execution
      3. 9.3.3. Conditional Update
      4. 9.3.4. Optional Update
      5. 9.3.5. Selective Aggregation
      6. 9.3.6. Checking for Existence
      7. 9.3.7. Division by Zero Errors
      8. 9.3.8. State Transitions
  12. 10. Partitioning
    1. 10.1. Partitioning Concepts
    2. 10.2. Partitioning Tables
    3. 10.3. Partitioning Indexes
    4. 10.4. Partitioning Methods
      1. 10.4.1. Range Partitioning
      2. 10.4.2. Hash Partitioning
      3. 10.4.3. Composite Range-Hash Partitioning
      4. 10.4.4. List Partitioning
      5. 10.4.5. Composite Range-List Partitioning
    5. 10.5. Specifying Partitions
    6. 10.6. Partition Pruning
  13. 11. PL/SQL
    1. 11.1. What Is PL/SQL?
    2. 11.2. Procedures, Functions, and Packages
    3. 11.3. Calling Stored Functions from Queries
      1. 11.3.1. Stored Functions and Views
      2. 11.3.2. Avoiding Table Joins
      3. 11.3.3. Deterministic Functions
    4. 11.4. Restrictions on Calling PL/SQL from SQL
      1. 11.4.1. Purity Level
      2. 11.4.2. Trust Me...
      3. 11.4.3. Other Restrictions
    5. 11.5. Stored Functions in DML Statements
    6. 11.6. The SQL Inside Your PL/SQL
  14. 12. Objects and Collections
    1. 12.1. Object Types
      1. 12.1.1. Subtyp es
      2. 12.1.2. Object Attributes
      3. 12.1.3. Object Tables
      4. 12.1.4. Object Parameters
    2. 12.2. Collection Types
      1. 12.2.1. Variable Arrays
      2. 12.2.2. Nested Tables
    3. 12.3. Collection Instantiation
    4. 12.4. Querying Collections
    5. 12.5. Collection Unnesting
    6. 12.6. Collection Functions
    7. 12.7. Comparing Collections
    8. 12.8. Manipulating Collections
    9. 12.9. Multilevel Collections
      1. 12.9.1. Querying Multilevel Collections
      2. 12.9.2. DML Operations on Multilevel Collections
  15. 13. Advanced Group Operations
    1. 13.1. Multiple Summary Levels
      1. 13.1.1. UNION
      2. 13.1.2. ROLLUP
      3. 13.1.3. Partial ROLLUPs
      4. 13.1.4. CUBE
      5. 13.1.5. Partial CUBE
      6. 13.1.6. The GROUPING Function
      7. 13.1.7. GROUPING SETS
    2. 13.2. Pushing the GROUPING Envelope
      1. 13.2.1. Repeated Column Names in the GROUP BY Clause
      2. 13.2.2. Grouping on Composite Columns
      3. 13.2.3. Concatenated Groupings
        1. 13.2.3.1. Concatenated groupings with GROUPING SETS
        2. 13.2.3.2. ROLLUP and CUBE as arguments to GROUPING SETS
    3. 13.3. The GROUPING_ID and GROUP_ID Functions
      1. 13.3.1. GROUPING_ID
      2. 13.3.2. GROUPING and GROUPING_ID in ORDER BY
      3. 13.3.3. GROUP_ID
  16. 14. Advanced Analytic SQL
    1. 14.1. Analytic SQL Overview
    2. 14.2. Ranking Functions
      1. 14.2.1. RANK, DENSE_RANK, and ROW_NUMBER
        1. 14.2.1.1. Handling NULLs
        2. 14.2.1.2. Top/bottom N queries
        3. 14.2.1.3. FIRST/LAST
      2. 14.2.2. NTILE
      3. 14.2.3. WIDTH_BUCKET
      4. 14.2.4. CUME_DIST and PERCENT_RANK
      5. 14.2.5. Hypothetical Functions
    3. 14.3. Windowing Functions
      1. 14.3.1. Working with Ranges
      2. 14.3.2. FIRST_VALUE and LAST_VALUE
      3. 14.3.3. LAG/LEAD Functions
    4. 14.4. Reporting Functions
      1. 14.4.1. Report Partitions
      2. 14.4.2. RATIO_TO_REPORT
    5. 14.5. Summary
  17. 15. SQL Best Practices
    1. 15.1. Know When to Use Specific Constructs
      1. 15.1.1. EXISTS Is Preferable to DISTINCT
      2. 15.1.2. WHERE Versus HAVING
      3. 15.1.3. UNION Versus UNION ALL
      4. 15.1.4. LEFT Versus RIGHT OUTER JOIN
    2. 15.2. Avoid Unnecessary Parsing
      1. 15.2.1. Using Bind Variables
      2. 15.2.2. Using Table Aliases
    3. 15.3. Consider Literal SQL for Decision-Support Systems
  18. 16. XML
    1. 16.1. What Is XML?
      1. 16.1.1. XML Resources
      2. 16.1.2. Oracle and XML
    2. 16.2. Storing XML Data
      1. 16.2.1. Storing XML as a CLOB
      2. 16.2.2. Inspecting the XML Document
        1. 16.2.2.1. XPath
        2. 16.2.2.2. The extract( ) member function
        3. 16.2.2.3. The extractValue( ) member function
        4. 16.2.2.4. The existsNode( ) member function
        5. 16.2.2.5. Moving data to relational tables
      3. 16.2.3. Storing XML as a Set of Objects
        1. 16.2.3.1. Registering your schema
        2. 16.2.3.2. Assigning the schema to a column
        3. 16.2.3.3. Inserting data
        4. 16.2.3.4. XMLType validity functions
        5. 16.2.3.5. Updating document content
    3. 16.3. Generating XML Documents
      1. 16.3.1. XMLElement( )
      2. 16.3.2. XMLAgg( )
      3. 16.3.3. XMLForest( )
      4. 16.3.4. Putting It All Together
    4. 16.4. Summary
  19. 17. Regular Expressions
    1. 17.1. Elementary Regular Expression Syntax
      1. 17.1.1. Matching a Single Character
      2. 17.1.2. Matching Any of a Set of Characters
      3. 17.1.3. Matching Repeating Sequences
      4. 17.1.4. Defining Alternate Possibilities
      5. 17.1.5. Subexpressions
      6. 17.1.6. Anchoring an Expression
      7. 17.1.7. Understanding Greediness
    2. 17.2. Advanced Function Options
    3. 17.3. Advanced Regular Expression Syntax
      1. 17.3.1. Using Backreferences
      2. 17.3.2. Using Named Character Classes
      3. 17.3.3. Specifying Collation Elements
      4. 17.3.4. Defining Equivalence Classes
  20. 18. Model Queries
    1. 18.1. Basic Elements of a Model Query
    2. 18.2. Cell References
      1. 18.2.1. Symbolic Cell References
      2. 18.2.2. Positional Cell References
      3. 18.2.3. Combined Positional and Symbolic References
      4. 18.2.4. NULL Measures and Missing Cells
      5. 18.2.5. UNIQUE DIMENSION/UNIQUE SINGLE REFERENCE
      6. 18.2.6. Returning Rows
    3. 18.3. Rules
      1. 18.3.1. Constructing a Rule
        1. 18.3.1.1. CV( )
        2. 18.3.1.2. ANY
        3. 18.3.1.3. FOR loops
        4. 18.3.1.4. IS ANY
        5. 18.3.1.5. IS PRESENT
        6. 18.3.1.6. PRESENTV
        7. 18.3.1.7. PRESENTNNV
      2. 18.3.2. Range References on the Righthand Side
      3. 18.3.3. Order of Evaluation of Rules
        1. 18.3.3.1. SEQUENTIAL ORDER
        2. 18.3.3.2. AUTOMATIC ORDER
      4. 18.3.4. Creating and Updating Cells
    4. 18.4. Iterative Models
      1. 18.4.1. Knowing how many iterations have occurred
        1. 18.4.1.1. Referencing values from the previous iteration
    5. 18.5. Reference Models
  21. A. Oracle’s Old Join Syntax
    1. A.1. Old Inner Join Syntax
    2. A.2. Old Outer Join Syntax
      1. A.2.1. Restrictions on Old Outer Join Syntax
      2. A.2.2. Full Outer Join Using the Old Syntax
    3. A.3. Advantages of the New Join Syntax
  22. About the Authors
  23. Colophon
  24. Copyright

Product information

  • Title: Mastering Oracle SQL, 2nd Edition
  • Author(s): Sanjay Mishra, Alan Beaulieu
  • Release date: June 2004
  • Publisher(s): O'Reilly Media, Inc.
  • ISBN: 9780596006327