Mastering Oracle SQL. 2nd Edition

Spis treści książki

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

pokaż cały spis treści

ukryj recenzje