O'Reilly logo

Stay ahead with the world's most comprehensive technology and business learning platform.

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, tutorials, and more.

Start Free Trial

No credit card required

Mastering Oracle SQL

Mastering Oracle SQL

by Alan Beaulieu, Sanjay Mishra
Publisher: O'Reilly Media, Inc.
Release Date: April 2002
ISBN: 0596001290
View table of contents
Start reading

Book Description

If you write programs to run against an Oracle database, you spend a lot of time and mental energy writing queries to return the data your programs need. Knowledge of SQL, and particularly of Oracle's implementation of SQL, is the key to writing good queries in a timely manner. In this book, the authors share their knowledge of Oracle SQL, and show you many creative techniques that you can use to advantage in your own applications. This book shows you how to:

  • Leverage Oracle's vast library of built-in SQL functions

  • Query time-based data, and write joins involving date and time ranges

  • Use Oracle SQL's hierarchical query features to deal with data best represented in a tree format

  • Use DECODE and CASE to implement conditional logic in your queries

  • Use Oracle's new, analytic SQL features to write ranking queries, lag and lead queries, windowing queries, and more

  • Join data from two or more tables using the newly supported SQL92 join syntax

In addition, you'll see how SQL can best be integrated with PL/SQL. You'll also learn various best practices to help you write SQL queries that perform efficiently. Precious few books on the market today go beyond discussing syntax and the barest rudiments of using Oracle SQL. This book changes that, showing you how to creatively leverage the full power of SQL to write queries in an Oracle environment.

Table of Contents

  1. Mastering Oracle SQL
    1. Preface
      1. Why We Wrote This Book
      2. Objectives of This Book
      3. Audience for This Book
      4. Platform and Version
      5. Structure of This Book
      6. Conventions Used in This Book
      7. Comments and Questions
      8. Acknowledgments
        1. From Sanjay
        2. From Alan
    2. 1. Introduction to SQL
      1. What Is SQL?
      2. A Brief History of SQL
        1. Oracle’s SQL Implementation
        2. Theoretical Versus Practical Terminology
      3. A Simple Database
      4. DML Statements
        1. The SELECT Statement
          1. SELECT clause elements
          2. Ordering your results
          3. Removing duplicates
        2. The INSERT Statement
        3. The DELETE Statement
        4. The UPDATE Statement
        5. So Why Are There 13 More Chapters?
    3. 2. The WHERE Clause
      1. Life Without WHERE
      2. WHERE to the Rescue
      3. WHERE Clause Evaluation
      4. Conditions and Expressions
        1. Equality/Inequality Conditions
        2. Membership Conditions
        3. Range Conditions
        4. Matching Conditions
        5. Handling NULL
      5. WHERE to Go from Here
    4. 3. Joins
      1. Inner Joins
        1. Cartesian Product
        2. Join Condition
        3. Equi-Join Versus Non-Equi-Join
      2. Outer Joins
        1. Restrictions on Outer Joins
        2. Full Outer Joins
      3. Self Joins
        1. Self Outer Joins
        2. Self Non-Equi-Joins
      4. Joins and Subqueries
      5. DML Statements on a Join View
        1. Key-Preserved Tables
        2. INSERT Statements on a Join View
        3. DELETE Statements on a Join View
        4. UPDATE Statements on a Join View
        5. Data Dictionary Views to Find Updateable Columns
      6. ANSI-Standard Join Syntax in Oracle9i
        1. New Join Syntax
        2. ANSI Outer Join Syntax
        3. Advantages of the New Join Syntax
    5. 4. Group Operations
      1. Aggregate Functions
        1. NULLs and Aggregate Functions
        2. Use of DISTINCT and ALL
      2. The GROUP BY Clause
        1. GROUP BY Clause and NULL Values
        2. GROUP BY Clause with WHERE Clause
      3. The HAVING Clause
    6. 5. Subqueries
      1. What Is a Subquery?
      2. Noncorrelated Subqueries
        1. Single-Row, Single-Column Subqueries
        2. Multiple-Row Subqueries
        3. Multiple-Column Subqueries
      3. Correlated Subqueries
      4. Inline Views
        1. Inline View Basics
        2. Query Execution
        3. Data Set Fabrication
        4. Overcoming SQL Restrictions
          1. Hierarchical queries
          2. Aggregate queries
        5. Inline Views in DML Statements
        6. Restricting Access Using WITH CHECK OPTION
      5. Subquery Case Study: The Top N Performers
        1. A Look at the Data
        2. Your Assignment
        3. Second Attempt
        4. Final Answer
    7. 6. Handling Temporal Data
      1. Internal DATE Storage Format
      2. Getting Dates In and Out of a Database
        1. TO_DATE
          1. Using the default date format
          2. Specifying a date format
        2. TO_CHAR
        3. Date Formats
          1. AD/BC indicators
          2. AM/PM indicators
          3. Case-sensitivity of formats
          4. Two-digit years
        4. Date Literals
        5. ISO Standard Issues
          1. ISO standard weeks
          2. ISO standard year
      3. Date Manipulation
        1. Addition
        2. Subtraction
        3. Last Day of the Month
        4. Next Day
        5. Rounding and Truncating Dates
        6. NEW_TIME
        7. SELECTing Data Based on Date Ranges
        8. Creating a Date Pivot Table
        9. Summarizing by a DATE/Time Element
      4. Oracle9i New DATETIME Features
        1. Time Zones
          1. Database time zone
          2. Session time zone
        2. Date and Time Data with Fractional Seconds
          1. TIMESTAMP
          2. TIMESTAMP WITH TIME ZONE
          3. TIMESTAMP WITH LOCAL TIME ZONE
        3. Date and Time Intervals
          1. INTERVAL YEAR TO MONTH
          2. INTERVAL DAY TO SECOND
      5. INTERVAL Literals
        1. YEAR TO MONTH Interval Literals
        2. DAY TO SECOND Interval Literals
        3. Manipulating Timestamps and Intervals
          1. DBTIMEZONE
          2. SESSIONTIMEZONE
          3. SYSTIMESTAMP
          4. CURRENT_DATE
          5. CURRENT_TIMESTAMP
          6. LOCALTIMESTAMP
          7. TO_TIMESTAMP
          8. TO_TIMESTAMP_TZ
          9. FROM_TZ
          10. NUMTOYMINTERVAL
          11. NUMTODSINTERVAL
          12. TO_YMINTERVAL
          13. TO_DSINTERVAL
          14. TZ_OFFSET
    8. 7. Set Operations
      1. Set Operators
        1. UNION ALL
        2. UNION
        3. INTERSECT
        4. MINUS
      2. Using Set Operations to Compare Two Tables
      3. Using NULLs in Compound Queries
      4. Rules and Restrictions on Set Operations
    9. 8. Hierarchical Queries
      1. Representing Hierarchical Information
      2. Simple Hierarchy Operations
        1. Finding the Root Node
        2. Finding a Node’s Immediate Parent
        3. Finding Leaf Nodes
      3. Oracle SQL Extensions
        1. START WITH...CONNECT BY and PRIOR
        2. The LEVEL Pseudocolumn
      4. Complex Hierarchy Operations
        1. Finding the Number of Levels
        2. Listing Records in Hierarchical Order
        3. Checking for Ascendancy
        4. Deleting a Subtree
        5. Listing Multiple Root Nodes
        6. Listing the Top Few Levels of a Hierarchy
        7. Aggregating a Hierarchy
      5. Restrictions on Hierarchical Queries
    10. 9. DECODE and CASE
      1. DECODE, NVL, and NVL2
        1. DECODE
        2. NVL and NVL2
      2. The Case for CASE
        1. Searched CASE Expressions
        2. Simple CASE Expressions
      3. DECODE and CASE Examples
        1. Result Set Transformations
        2. Selective Function Execution
        3. Conditional Update
        4. Optional Update
        5. Selective Aggregation
        6. Division by Zero Errors
        7. State Transitions
    11. 10. Partitions, Objects, and Collections
      1. Table Partitioning
        1. Partitioning Concepts
        2. Partitioning Tables
        3. Partitioning Indexes
        4. Partitioning Methods
          1. Range partitioning
          2. Hash partitioning
          3. Composite partitioning
          4. List partitioning
        5. Specifying Partitions
        6. Partition Pruning
      2. Objects and Collections
        1. Object Types
          1. Object attributes
          2. Object tables
          3. Object parameters
        2. Collection Types
          1. Variable arrays
          2. Nested tables
        3. Collection Creation
        4. Collection Unnesting
        5. Querying Collections
        6. Manipulating Collections
    12. 11. PL/SQL
      1. What Is PL/SQL?
      2. Procedures, Functions, and Packages
      3. Calling Stored Functions from Queries
        1. Stored Functions and Views
        2. Avoiding Table Joins
      4. Restrictions on Calling PL/SQL from SQL
        1. Purity Level
        2. Trust Me...
        3. Other Restrictions
        4. Consistency Issues
      5. Stored Functions in DML Statements
      6. The SQL Inside Your PL/SQL
    13. 12. Advanced Group Operations
      1. ROLLUP
        1. Using UNION (The Old Way)
        2. Using ROLLUP (The New Way)
        3. Generating Partial ROLLUPs
      2. CUBE
      3. The GROUPING Function
      4. GROUPING SETS
      5. Oracle9i Grouping Features
        1. Repeating Column Names in the GROUP BY Clause
        2. Grouping on Composite Columns
        3. Concatenated Groupings
          1. Concatenated groupings with GROUPING SETS
          2. ROLLUP and CUBE as arguments to GROUPING SETS
      6. The GROUPING_ID and GROUP_ID Functions
        1. GROUPING_ID
        2. GROUP_ID
    14. 13. Advanced Analytic SQL
      1. Analytic SQL Overview
      2. Ranking Functions
        1. RANK, DENSE_RANK, and ROW_NUMBER
          1. Handling NULLs
          2. Top/Bottom-N queries
          3. FIRST/LAST
        2. NTILE
        3. WIDTH_BUCKET
        4. CUME_DIST and PERCENT_RANK
        5. Hypothetical Functions
      3. Windowing Functions
        1. FIRST_VALUE and LAST_VALUE
        2. LAG/LEAD Functions
      4. Reporting Functions
        1. Report Partitions
        2. RATIO_TO_REPORT
      5. Summary
    15. 14. SQL Best Practices
      1. Know When to Use Specific Constructs
        1. EXISTS Is Preferable to DISTINCT
        2. EXISTS Versus IN
        3. WHERE Versus HAVING
        4. UNION Versus UNION ALL
      2. Avoid Unnecessary Parsing
        1. Using Bind Variables
        2. Using Table Aliases
      3. Consider Literal SQL for Decision Support Systems
    16. Index
    17. Colophon