Mastering Oracle SQL /

Mostrar otras versiones (1)

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, th...

Descripción completa

Detalles Bibliográficos
Clasificación:Libro Electrónico
Autor principal: Mishra, Sanjay
Otros Autores: Beaulieu, Alan
Formato: Electrónico eBook
Idioma:Inglés
Publicado: Sebastopol, CA : O'Reilly, ©2002.
Edición:1st ed.
Temas:
Oracle (Computer file)
SQL (Computer program language)
SQL (Langage de programmation)
Acceso en línea:Texto completo (Requiere registro previo con correo institucional)
Tabla de Contenidos:
  • 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.

Ejemplares similares