You are previewing Oracle SQL.
O'Reilly logo
Oracle SQL

Book Description

Developers and DBAs use Oracle SQL coding on a daily basis, whether for application development, finding problems, fine-tuning solutions to those problems, or other critical DBA tasks. Oracle SQL: Jumpstart with Examples is the fastest way to get started and to quickly locate answers to common (and uncommon) questions. It includes all the basic queries: filtering, sorting, operators, conditionals, pseudocolumns, single row functions, joins, grouping and summarizing, grouping functions, subqueries, composite queries, hierarchies, flashback queries, parallel queries, expressions and regular expressions, DML, datatypes (including collections), XML in Oracle, DDL for basic database objects such as tales, views and indexes, Oracle Partitioning, security, and finally PL/SQL.

* Each of the hundreds of SQL code examples was tested on a working Oracle 10g database
* Invaluable everyday tool that provides an absolute plethora of properly tested examples of Oracle SQL code
* Authors have four decades of commercial experience between them as developers and database administrators

Table of Contents

    1. 1.1 A Little History
      1. 1.1.1 The Evolution of Database Modeling
      2. 1.1.2 The History of Relational Databases
      3. 1.1.3 The Evolution of Oracle Database
    2. 1.2 The Basics of Relational Data Modeling
      1. 1.2.1 Normalization
        1. 1.2.1.1 Referential Integrity
      2. 1.2.2 Denormalization
      3. 1.2.3 Different Forms of the Relational Data Model
    3. 1.3 Structured Query Language (SQL)
      1. 1.3.1 The Humble Origins of SQL
      2. 1.3.2 What Is Oracle SQL?
        1. 1.3.2.1 ANSI Standards and Oracle
    4. 1.4 Software Useful for Reading this Book
    5. 1.5 Syntax Conventions Used in This Book
    6. 1.6 SQL Tools
      1. 1.6.1 SQL*Plus in Command-Line Mode
      2. 1.6.2 SQL*Plus in Windows Mode
      3. 1.6.3 SQL*Plus Worksheet
      4. 1.6.4 iSQL*Plus
    7. 1.7 The MUSIC Schema
      1. 1.7.1 The MUSIC Schema Sales Data Warehouse
    8. 1.8 Endnotes
    1. 2.1 New Features in Oracle Database 10g
      1. 2.1.1 Oracle SQL Improvements in Oracle Database 10g
      2. 2.1.2 PL/SQL Improvements in Oracle Database 10g
        1. 2.1.2.1 Java Improvements in Oracle Database 10g
      3. 2.1.3 XML Improvements in Oracle Database 10g
      4. 2.1.4 Some Utility Improvements in Oracle Database 10g
      5. 2.1.5 Database Object Improvements in Oracle 10g
    2. 2.2 New Features in Oracle Database 9i
      1. 2.2.1 Oracle SQL Improvements in Oracle Database 9i
      2. 2.2.2 New PL/SQL Features in Oracle Database 9i
    1. 3.1 The Basic Concepts
    2. 3.2 The Oracle Instance
    3. 3.3 Oracle Database Physical Architecture
      1. 3.3.1 Datafiles, Tablespaces, and Objects
      2. 3.3.2 Controlfiles, Logging, and Archiving
      3. 3.3.3 Rollback and Undo
      4. 3.3.4 Temporary Sort Space
    4. 3.4 Database Startup and Shutdown
    5. 3.5 Enhancing the Physical Architecture
      1. 3.5.1 Oracle Managed Files
      2. 3.5.2 Partitioning
      3. 3.5.3 Replication
      4. 3.5.4 Standby Databases
      5. 3.5.5 Clustering and Oracle RAC
    1. 4.1 The Basic SELECT Statement
      1. 4.1.1 Uses of the SELECT Statement
      2. 4.1.2 Syntax Conventions
      3. 4.1.3 Some Simple Example SELECT Statements
    2. 4.2 Types of SELECT Queries
      1. 4.2.1 Simple Query
      2. 4.2.2 Filtered Query
      3. 4.2.3 Sorted Query
      4. 4.2.4 Grouping or Aggregated Query
      5. 4.2.5 Join Query
      6. 4.2.6 Subquery
      7. 4.2.7 Table or View Creation Query
      8. 4.2.8 Hierarchical Query
      9. 4.2.9 Composite Queries
    3. 4.3 Other Aspects of the SELECT Statement
      1. 4.3.1 The DUAL Table
      2. 4.3.2 Using Functions
      3. 4.3.3 Arithmetic Operations
      4. 4.3.4 Using DISTINCT
      5. 4.3.5 Null Values
      6. 4.3.6 Using Pseudocolumns
    1. 5.1 WHERE Clause Syntax
      1. 5.1.1 Some Simple WHERE Clause Examples
    2. 5.2 WHERE Clause Expression Conditions
    3. 5.3 Logical Operators in the WHERE Clause
    4. 5.4 Top-N Queries
    5. 5.5 Endnotes
    1. 6.1 ORDER BY Clause Syntax
      1. 6.1.1 Some Simple ORDER BY Clause Examples
    2. 6.2 Sorting and Null Values
    3. 6.3 Sorting Methods
      1. 6.3.1 Sorting by Position
      2. 6.3.2 Sorting by Expression
    4. 6.4 Endnotes
    1. 7.1 Precedence
    2. 7.2 Operators
      1. 7.2.1 Arithmetic Operators
      2. 7.2.2 Logical Operators
      3. 7.2.3 The Concatenation Operator
      4. 7.2.4 Hierarchical Query Operators
      5. 7.2.5 Set Operators
      6. 7.2.6 Multiset Operators
      7. 7.2.7 User-Defined Operators
    3. 7.3 Conditions
    4. 7.4 Pseudocolumns
    1. 8.1 Environmental Settings
    2. 8.2 Using Scripts and Variables
    3. 8.3 Formatting Query Output in SQL*Plus
      1. 8.3.1 Column Formatting and Headings
        1. 8.3.1.1 Formatting Dates
      2. 8.3.2 Lines, Pages, and Breaks
    4. 8.4 Using iSQL*Plus
      1. 8.4.1 Embedding Scripts in HTML
      2. 8.4.2 iSQL*Plus versus SQL*Plus
      3. 8.4.3 Troubleshooting iSQL*Plus
      4. 8.4.4 Customizing iSQL*Plus Display
    5. 8.5 Endnotes
    1. 9.1 Types of Functions
    2. 9.2 Single-Row Functions
      1. 9.2.1 String Functions
      2. 9.2.2 Number Functions
        1. 9.2.2.1 Binary Floating-Point Number Functions
      3. 9.2.3 Date Functions
      4. 9.2.4 Datatype Conversion Functions
        1. 9.2.4.1 Number Conversion Function Formats
        2. 9.2.4.2 Date Conversion Function Formats
      5. 9.2.5 Miscellaneous Functions
    3. 9.3 Combining Functions
    4. 9.4 Endnotes
    1. 10.1 Join Formats
      1. 10.1.1 Oracle’s Proprietary Format
      2. 10.1.2 ANSI Format
    2. 10.2 Types of Joins
    3. 10.3 Examining Different Types of Joins
      1. 10.3.1 Cross-Join or Cartesian Product
      2. 10.3.2 Natural or Inner Join
        1. 10.3.2.1 The USING clause
        2. 10.3.2.2 The ON clause
      3. 10.3.3 Outer Join
        1. 10.3.3.1 Left Outer Join
        2. 10.3.3.2 Right Outer Join
        3. 10.3.3.3 Full Outer Join
      4. 10.3.4 Self-Join
        1. 10.3.4.1 Grouping Self-Join
        2. 10.3.4.2 Hierarchical (Fishhook) Self-Join
      5. 10.3.5 Equi-Joins, Anti-Joins, and Range Joins
      6. 10.3.6 Mutable and Complex Joins
    4. 10.4 Endnotes
    1. 11.1 GROUP BY Clause Syntax
    2. 11.2 Types of Group Functions
      1. 11.2.1 Aggregate Functions
        1. 11.2.1.1 Simple Summary Functions
        2. 11.2.1.2 Statistical Function Calculators
        3. 11.2.1.3 Statistical Distribution Functions
        4. 11.2.1.4 Ranking Functions
        5. 11.2.1.5 Grouping Functions
      2. 11.2.2 Enhancing Grouping Functions for Analysis
        1. 11.2.2.1 The OVER Clause
    3. 11.3 Special Grouping Function Behavior
      1. 11.3.1 Group Functions and Null Values
      2. 11.3.2 Selecting DISTINCT or ALL in Group Functions
    4. 11.4 Using the GROUP BY Clause
      1. 11.4.1 Grouping Rows
      2. 11.4.2 Filtering Grouped Results with the Having Clause
      3. 11.4.3 Extending the GROUP BY Clause Further
        1. 11.4.3.1 The ROLLUP Clause
        2. 11.4.3.2 The CUBE Clause
        3. 11.4.3.3 The GROUPING SETS Clause
    5. 11.5 The SPREADSHEET (MODEL) Clause
      1. 11.5.1 SPREADSHEET (MODEL) Clause Syntax
      2. 11.5.2 SPREADSHEET (MODEL) Clause Functions
      3. 11.5.3 Using the SPREADSHEET (MODEL) Clause
    6. 11.6 Endnotes
    1. 12.1 Types of Subqueries
    2. 12.2 Where Can Subqueries Be Used?
    3. 12.3 Comparison Conditions and Subqueries
    4. 12.4 Demonstrating Subqueries
      1. 12.4.1 Single-Row Subqueries
      2. 12.4.2 Multiple-Row Subqueries
      3. 12.4.3 Multiple-Column Subqueries
      4. 12.4.4 Regular versus Correlated Subqueries
      5. 12.4.5 Nested Subqueries
      6. 12.4.6 Inline Views
      7. 12.4.7 Other Uses for Subqueries
    5. 12.5 Endnotes
    1. 13.1 Composite Queries
      1. 13.1.1 Set Operators
      2. 13.1.2 Using Composite Queries
    2. 13.2 Hierarchical Queries
      1. 13.2.1 Hierarchical Query Operators
      2. 13.2.2 Hierarchical Query Pseudocolumns
      3. 13.2.3 Using Hierarchical Queries
    3. 13.3 Flashback and Versions Queries
      1. 13.3.1 Flashback Query Syntax
      2. 13.3.2 Versions Query Pseudocolumns
      3. 13.3.3 Using Flashback Queries
    4. 13.4 Parallel Queries
    5. 13.5 Endnotes
    1. 14.1 Types of Expressions
    2. 14.2 Regular Expressions
      1. 14.2.1 Regular Expression Functions
      2. 14.2.2 Regular Expression Patterns
      3. 14.2.3 Using Regular Expressions
    3. 14.3 Oracle Expression Filter
    4. 14.4 Endnotes
    1. 15.1 What Is DML?
    2. 15.2 Transaction Control
      1. 15.2.1 Locks
      2. 15.2.2 The SET TRANSACTION Command
      3. 15.2.3 The SAVEPOINT Command
    3. 15.3 Adding Data (INSERT)
      1. 15.3.1 Inserting One Row
      2. 15.3.2 Inserting with a Subquery
      3. 15.3.3 The Multiple-Table INSERT Command
    4. 15.4 Changing Data (UPDATE)
      1. 15.4.1 Updating One Row
      2. 15.4.2 Updating Many Rows
    5. 15.5 Deleting Data (DELETE)
      1. 15.5.1 Deleting One Row
      2. 15.5.2 Deleting Many Rows
      3. 15.5.3 Deleting All Rows
    6. 15.6 Merging New and Old Data (MERGE)
      1. 15.6.1 How To Use MERGE
    1. 16.1 Simple Datatypes
    2. 16.2 Complex and Object Datatypes
      1. 16.2.1 Binary Object Datatypes
      2. 16.2.2 Reference Pointer Datatypes
        1. 16.2.2.1 Using the REF Datatype
        2. 16.2.2.2 Using the BFILE Datatype
      3. 16.2.3 User-Defined Datatypes
      4. 16.2.4 Object Collection Datatypes
        1. 16.2.4.1 Using VARRAY Collections
        2. 16.2.4.2 Using Nested Table Collections
      5. 16.2.5 Object Collection Functions
      6. 16.2.6 Metadata Views
    3. 16.3 Special Datatypes
    1. 17.1 What Is XML?
      1. 17.1.1 What Is XSL?
    2. 17.2 Using XML in Oracle
      1. 17.2.1 Creating XML Documents
        1. 17.2.1.1 The XMLType Datatype
        2. 17.2.1.2 Generating XML from Tables
          1. 17.2.1.2.1 The SQL/XML Standard
          2. 17.2.1.2.2 The SYS_XMLGEN Function
      2. 17.2.2 XML and the Database
        1. 17.2.2.1 New XML Documents
        2. 17.2.2.2 Retrieving from XML Documents
        3. 17.2.2.3 Changing and Removing XML Document Content
    3. 17.3 Metadata Views
    4. 17.4 Endnotes
    1. 18.1 What Is a Table?
      1. 18.1.1 Types of Tables
      2. 18.1.2 Methods of Creating Tables
        1. 18.1.2.1 Scripted Method
        2. 18.1.2.2 CREATE TABLE ... AS Subquery
        3. 18.1.2.3 Tools
    2. 18.2 CREATE TABLE Syntax
    3. 18.3 Creating Different Table Types
      1. 18.3.1 Creating Relational Tables
      2. 18.3.2 Creating Object Tables
      3. 18.3.3 Creating Temporary Tables
      4. 18.3.4 Creating Index-Organized Tables (IOTs)
      5. 18.3.5 Creating External Tables
      6. 18.3.6 Creating Partitioned Tables
        1. 18.3.6.1 What Are the Types of Partitions?
          1. 18.3.6.1.1 Partition Indexing
        2. 18.3.6.2 CREATE TABLE Partition Syntax
          1. 18.3.6.2.1 CREATE TABLE Range Partition Syntax
          2. 18.3.6.2.2 CREATE TABLE List Partition Syntax
          3. 18.3.6.2.3 CREATE TABLE Hash Partition Syntax
          4. 18.3.6.2.4 CREATE TABLE Range-Hash Partition Syntax
          5. 18.3.6.2.5 CREATE TABLE Range-List Partition Syntax
    4. 18.4 Changing Table Structure
      1. 18.4.1 Adding, Modifying, and Removing Columns
      2. 18.4.2 Rebuilding a Table
      3. 18.4.3 Renaming a Table
    5. 18.5 Dropping a Table
      1. 18.5.1 Truncating Instead of Dropping Tables
    6. 18.6 Adding Comments to Tables
      1. 18.6.1 Adding Comments to Schema Objects
      2. 18.6.2 Scripting and SQL Code Comments
    7. 18.7 The Recycle Bin
    8. 18.8 Metadata Views
    9. 18.9 Endnotes
    1. 19.1 What Is a View?
    2. 19.2 Types and Uses of Views
    3. 19.3 CREATE VIEW Syntax
      1. 19.3.1 Creating Simple Views
      2. 19.3.2 Creating Constraint Views
      3. 19.3.3 Creating Complex Views
        1. 19.3.3.1 Views with Joins
        2. 19.3.3.2 Inline Subquery Views
    4. 19.4 Changing and Dropping Views
    5. 19.5 Working with Views
      1. 19.5.1 Querying a View
      2. 19.5.2 Views and DML Commands
        1. 19.5.2.1 DML and Views with Joins
    6. 19.6 Metadata Views
    7. 19.7 Data Dictionary Views (Metadata)
    8. 19.8 Endnotes
    1. 20.1 What Are Constraints?
      1. 20.1.1 Types and Uses of Constraints
    2. 20.2 Managing Constraints
      1. 20.2.1 CREATE TABLE Syntax
        1. 20.2.1.1 Primary Key and Unique Constraints
        2. 20.2.1.2 Foreign Key Constraints
          1. 20.2.1.2.1 Out-of-Line Primary and Foreign Keys
        3. 20.2.1.3 Check Constraints
        4. 20.2.1.4 REF Constraints
      2. 20.2.2 CREATE VIEW Syntax
    3. 20.3 Adding, Modifying, and Dropping Constraints
      1. 20.3.1 ALTER TABLE Syntax
      2. 20.3.2 ALTER VIEW Syntax
      3. 20.3.3 Working with Constraints and ALTER TABLE
        1. 20.3.3.1 Adding a Constraint to an Existing Table
        2. 20.3.3.2 Modifying Constraints on Existing Tables
        3. 20.3.3.3 Constraint States
      4. 20.3.4 Renaming a Constraint
      5. 20.3.5 Dropping Constraints
        1. 20.3.5.1 Dropping Constraints with CASCADE
    4. 20.4 Metadata Views
    1. 21.1 Indexes
      1. 21.1.1 What Is an Index?
      2. 21.1.2 Types of Indexes
        1. 21.1.2.1 Index Attributes
      3. 21.1.3 Creating Indexes
      4. 21.1.4 Changing and Dropping Indexes
      5. 21.1.5 More Indexing Refinements
    2. 21.2 Clusters
      1. 21.2.1 What is a Cluster?
      2. 21.2.2 Types of Clusters
      3. 21.2.3 Creating Clusters
    3. 21.3 Metadata Views
    4. 21.4 Endnotes
    1. 22.1 Sequences
      1. 22.1.1 Creating Sequences
      2. 22.1.2 Changing and Dropping Sequences
      3. 22.1.3 Using Sequences
        1. 22.1.3.1 Using the CURRVAL and NEXTVAL Pseudocolumns
        2. 22.1.3.2 Using Sequences in an INSERT Statement
        3. 22.1.3.3 Other Uses of Sequences
    2. 22.2 Synonyms
      1. 22.2.1 Creating Public Synonyms
      2. 22.2.2 Creating Private Synonyms
      3. 22.2.3 Using Synonyms
    3. 22.3 Metadata Views
    4. 22.4 Endnotes
    1. 23.1 Users
      1. 23.1.1 Users Provided by Oracle
      2. 23.1.2 Creating Users
      3. 23.1.3 Modifying User Passwords
      4. 23.1.4 Dropping Users
    2. 23.2 Privileges
      1. 23.2.1 Granting Privileges
      2. 23.2.2 Revoking Privileges
        1. 23.2.2.1 Revoked System Privileges DO NOT Cascade
        2. 23.2.2.2 Revoked Object Privileges DO Cascade
    3. 23.3 Grouping Privileges Using Roles
      1. 23.3.1 Creating and Altering Roles
      2. 23.3.2 Granting and Revoking Privileges on Roles
      3. 23.3.3 Setting User Roles
      4. 23.3.4 Dropping Roles
    4. 23.4 Metadata Views
    1. 24.1 What is PL/SQL?
    2. 24.2 Why Is PL/SQL a Programming Language?
      1. 24.2.1 Blocks and Exception Trapping
      2. 24.2.2 Procedures, Functions, Triggers, and Packages
        1. 24.2.2.1 Using Named Procedures
        2. 24.2.2.2 Using Functions
        3. 24.2.2.3 Using Triggers
        4. 24.2.2.4 Using Packages
    3. 24.3 Variables and Datatypes in PL/SQL
    4. 24.4 Retrieving Data in PL/SQL
      1. 24.4.1 Explicit Cursors
      2. 24.4.2 Implicit Cursors
        1. 24.4.2.1 The Internal SQL Implicit Cursor
        2. 24.4.2.2 Single-Row SELECT Implicit Cursor
        3. 24.4.2.3 Cursor FOR Loop Implicit Cursor
    5. 24.5 Changing Data in PL/SQL
    6. 24.6 Dynamic SQL
      1. 24.6.1 Building Cursors Dynamically
    7. 24.7 Control Structures
      1. 24.7.1 Selection
        1. 24.7.1.1 The IF Statement
        2. 24.7.1.2 The CASE Statement
          1. 24.7.1.2.1 CASE Statement Search Condition
          2. 24.7.1.2.2 CASE Statement Selector and Expression
      2. 24.7.2 Iteration or Repetition
        1. 24.7.2.1 The FOR Loop
        2. 24.7.2.2 The WHILE Loop
        3. 24.7.2.3 The LOOP...END LOOP Construct
        4. 24.7.2.4 The FORALL Command
      3. 24.7.3 Sequence Controls
        1. 24.7.3.1 The GOTO Statement
        2. 24.7.3.2 The NULL Command
    8. 24.8 Objects and Methods
    9. 24.9 Oracle-Provided Packages
    10. 24.10 Metadata Views
    1. A.1 MUSICMASTER.SQL
    2. A.2 CREATEUSER.SQL
    3. A.3 SCHEMAOLTP.SQL
    4. A.4 SEQUENCES.SQL
    5. A.5 INSTRUMENT.SQL
    6. A.6 ARTIST.SQL
    7. A.7 GENRE.SQL
    8. A.8 MUSISCD.SQL
    9. A.9 SONGANDTRACK.SQL
    10. A.10 GUESTARTIST.SQL
    11. A.11 STUDIOTIME.SQL
    12. A.12 UPDATEDATA.SQL
    13. A.13 CHECKDATA.SQL
    14. A.14 SCHEMADW.SQL
    15. A.15 DIMENSIONS.SQL
    16. A.16 FACTS.SQL
    1. B.1 Tables
    2. B.2 Constraints
    3. B.3 Indexes
    1. C.1 Other titles by the authors