- Browse
- » Mastering Oracle SQL
Mastering Oracle SQL
Author
Publisher
O'Reilly
Publication Date
Varies, see individual formats and editions
Language
English
Description
Loading Description...
Table of Contents
From the eBook - 2nd ed.
1. Introduction to SQL --
2. WHERE clause --
3. Joins --
4. Group operations --
5. Subqueries --
6. Handling temporal data --
7. Set operations --
8. Hierarchical queries --
9. DECODE and CASE --
10. Partitioning --
11. PL/SQL --
12. Objects and collections --
13. Advanced group operations --
14. Advanced analytic SQL --
15. SQL best practices --
16. XML --
17. Regular expressions --
18. Model queries.
From the eBook - 1st ed.
Intro
Table of Contents
Preface
Why We Wrote This Book
What's 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
Introduction to SQL
What Is SQL?
A Brief History of SQL
Oracle's SQL Implementation
Theoretical Versus Practical Terminology
A Simple Database
DML Statements
The SELECT Statement
SELECT clause elements
Ordering your results
Removing duplicates
The INSERT Statement
Single-table inserts
Multitable inserts
The DELETE Statement
The UPDATE Statement
The MERGE Statement
So Why Are There 17 More Chapters?
The WHERE Clause
Life Without WHERE
WHERE to the Rescue
WHERE Clause Evaluation
Conditions and Expressions
Equality/Inequality Conditions
Membership Conditions
Range Conditions
Matching Conditions
Regular Expressions
Handling NULL
Placement of Join Conditions
WHERE to Go from Here
Joins
What Is a Join Query?
Join Conditions
The USING Clause
Conditions Involving Multiple Columns
The Natural Join Clause
Types of Joins
Cross Joins/Cartesian Products
Inner Joins
Outer Joins
Left outer joins
Right outer joins
Full outer joins
Equi-Joins Versus Non-Equi-Joins
Self Joins
Self outer joins
Self non-equi-joins
Partition Outer Joins
Joins and Subqueries
DML Statements on a Join View
Key-Preserved Tables
INSERT Statements on a Join View
DELETE Statements on a Join View
UPDATE Statements on a Join View
Data Dictionary Views to Find Updatable Columns
Impact of WITH CHECK OPTION
Group Operations
Aggregate Functions
NULLs and Aggregate Functions
Use of DISTINCT and ALL.
The GROUP BY Clause
Correspondence Between SELECT and GROUP BY
Aggregate expressions generally require a GROUP BY clause
GROUP BY clause must include all nonaggregate expressions
Aggregate functions not allowed in GROUP BY clause
Constants can be omitted from the GROUP BY clause
Scalar functions may be grouped by their underlying column
Concatenated columns may be grouped in either of two ways
You can sometimes exclude a nonaggregate expression from the GROUP BY clause
You are not required to show your GROUP BY columns
GROUP BY Clause and NULL Values
GROUP BY Clause with WHERE Clause
The HAVING Clause
Nested Group Operations
Subqueries
What Is a Subquery?
Noncorrelated Subqueries
Single-Row, Single-Column Subqueries
Multiple-Row, Single-Column Subqueries
Multiple-Column Subqueries
The WITH Clause
Correlated Subqueries
Inline Views
Inline View Basics
Query Execution
Data Set Fabrication
Overcoming SQL Restrictions
Hierarchical queries
Aggregate queries
Inline Views in DML Statements
Restricting Access Using WITH CHECK OPTION
Global Inline Views
Subquery Case Study: The Top N Performers
A Look at the Data
Your Assignment
Second Attempt
Final Answer
Handling Temporal Data
Time Zones
Database Time Zone
Session Time Zone
Temporal Data Types in Oracle
The DATE Data Type
The TIMESTAMP Data Types
TIMESTAMP
TIMESTAMP WITH TIME ZONE
TIMESTAMP WITH LOCAL TIME ZONE
The INTERVAL Data Types
INTERVAL YEAR TO MONTH
INTERVAL DAY TO SECOND
Literals of Temporal Types
DATE Literals
TIMESTAMP Literals
INTERVAL Literals
YEAR TO MONTH interval literals
DAY TO SECOND interval literals
Getting Temporal Data In and Out of a Database
TO_DATE, TO_TIMESTAMP, and TO_TIMESTAMP_TZ
Using the default formats.
Specifying a format
Converting to TIMESTAMP WITH LOCAL TIME ZONE
TO_YMINTERVAL and TO_DSINTERVAL
NUMTOYMINTERVAL and NUMTODSINTERVAL
TO_CHAR
Date and Time Formats
AD/BC Indicators
AM/PM Indicators
Case-Sensitivity of Formats
Two-Digit Years
ISO Standard Issues
ISO standard weeks
ISO standard year
Database Parameters
Manipulating Temporal Data
Using the Built-in Temporal Functions
Addition
Adding numbers to a DATE
Adding months to a DATE
Adding true INTERVAL values rather than numbers
Subtraction
Subtracting one DATE from another
Subtracting one TIMESTAMP from another
Subtracting a number from a DATE
Subtracting months from a DATE
Number of months between two DATEs
Time interval between two DATEs
Subtracting an INTERVAL from a DATE or TIMESTAMP
Determining the First Day of the Month
Rounding and Truncating Dates
SELECTing Data Based on Date Ranges
Creating a Date Pivot Table
Summarizing by a Date/Time Element
Set Operations
Set Operators
UNION ALL
UNION
INTERSECT
MINUS
Precedence of Set Operators
Comparing Two Tables
Using NULLs in Compound Queries
Rules and Restrictions on Set Operations
Hierarchical Queries
Representing Hierarchical Information
Simple Hierarchy Operations
Finding Root Nodes
Finding a Node's Immediate Parent
Finding Leaf Nodes
Oracle SQL Extensions
Tree Traversal Using ANSI SQL
START WITH . . . CONNECT BY and PRIOR
The LEVEL Pseudocolumn
Complex Hierarchy Operations
Finding the Number of Levels
Listing Records in Hierarchical Order
Checking for Ascendancy
Deleting a Subtree
Listing Multiple Root Nodes
Listing the Top Few Levels of a Hierarchy
Aggregating a Hierarchy
Ordering Hierarchical Data
Finding the Path to a Node.
Restrictions on Hierarchical Queries
Enhancements in Oracle Database 10g
Getting Data from the Root Row
Ignoring Cycles
Identifying Cycles
Identifying Leaf Nodes
DECODE and CASE
DECODE, NULLIF, NVL, and NVL2
DECODE
NULLIF
NVL and NVL2
The Case for CASE
Searched CASE Expressions
Simple CASE Expressions
DECODE and CASE Examples
Result Set Transformations
Selective Function Execution
Conditional Update
Optional Update
Selective Aggregation
Checking for Existence
Division by Zero Errors
State Transitions
Partitioning
Partitioning Concepts
Partitioning Tables
Partitioning Indexes
Partitioning Methods
Range Partitioning
Hash Partitioning
Composite Range-Hash Partitioning
List Partitioning
Composite Range-List Partitioning
Specifying Partitions
Partition Pruning
PL/SQL
What Is PL/SQL?
Procedures, Functions, and Packages
Calling Stored Functions from Queries
Stored Functions and Views
Avoiding Table Joins
Deterministic Functions
Restrictions on Calling PL/SQL from SQL
Purity Level
Trust Me...
Other Restrictions
Stored Functions in DML Statements
The SQL Inside Your PL/SQL
Objects and Collections
Object Types
Subtypes
Object Attributes
Object Tables
Object Parameters
Collection Types
Variable Arrays
Nested Tables
Collection Instantiation
Querying Collections
Collection Unnesting
Collection Functions
Comparing Collections
Manipulating Collections
Multilevel Collections
Querying Multilevel Collections
DML Operations on Multilevel Collections
Advanced Group Operations
Multiple Summary Levels
UNION
ROLLUP
Partial ROLLUPs
CUBE
Partial CUBE
The GROUPING Function
GROUPING SETS
Pushing the GROUPING Envelope.
Repeated Column Names in the GROUP BY Clause
Grouping on Composite Columns
Concatenated Groupings
Concatenated groupings with GROUPING SETS
ROLLUP and CUBE as arguments to GROUPING SETS
The GROUPING_ID and GROUP_ID Functions
GROUPING_ID
GROUPING and GROUPING_ID in ORDER BY
GROUP_ID
Advanced Analytic SQL
Analytic SQL Overview
Ranking Functions
RANK, DENSE_RANK, and ROW_NUMBER
Handling NULLs
Top/bottom N queries
FIRST/LAST
NTILE
WIDTH_BUCKET
CUME_DIST and PERCENT_RANK
Hypothetical Functions
Windowing Functions
Working with Ranges
FIRST_VALUE and LAST_VALUE
LAG/LEAD Functions
Reporting Functions
Report Partitions
RATIO_TO_REPORT
Summary
SQL Best Practices
Know When to Use Specific Constructs
EXISTS Is Preferable to DISTINCT
WHERE Versus HAVING
UNION Versus UNION ALL
LEFT Versus RIGHT OUTER JOIN
Avoid Unnecessary Parsing
Using Bind Variables
Using Table Aliases
Consider Literal SQL for Decision-Support Systems
XML
What Is XML?
XML Resources
Oracle and XML
Storing XML Data
Storing XML as a CLOB
Inspecting the XML Document
XPath
The extract( ) member function
The extractValue( ) member function
The existsNode( ) member function
Moving data to relational tables
Storing XML as a Set of Objects
Registering your schema
Assigning the schema to a column
Inserting data
XMLType validity functions
Updating document content
Generating XML Documents
XMLElement( )
XMLAgg( )
XMLForest( )
Putting It All Together
Summary
Regular Expressions
Elementary Regular Expression Syntax
Matching a Single Character
Matching Any of a Set of Characters
Matching Repeating Sequences
Defining Alternate Possibilities
Subexpressions
Anchoring an Expression.
Excerpt
Loading Excerpt...
Author Notes
Loading Author Notes...
More Details
Contributors
ISBN
9780596517472
9780596001292
9780596552473
9780596001292
9780596552473
Reviews from GoodReads
Loading GoodReads Reviews.
Staff View
Loading Staff View.