Oracle SQL

Oracle SQL

by Gavin JT Powell, Carol McCullough-Dieter
Released September 2004
Publisher(s): Digital Press
ISBN: 9780080479835

Read it now on the O’Reilly learning platform with a 10-day free trial.

O’Reilly members get unlimited access to books, live events, courses curated by job role, and more from O’Reilly and nearly 200 top publishers.

Start your free trial

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. Cover Page
  2. Title Page
  3. Copyright Page
  4. Contents at a Glance
  5. Foreword
  6. Preface
  7. Acknowledgements
  8. Chapter 1: Introduction to Oracle SQL
    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
  9. Chapter 2: New Features of Oracle SQL
    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
  10. Chapter 3: Oracle Database Architecture
    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
  11. Chapter 4: The SELECT Statement
    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
  12. Chapter 5: Filtering Rows
    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
  13. Chapter 6: Sorting Rows
    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
  14. Chapter 7: Operators, Conditions, and Pseudocolumns
    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
  15. Chapter 8: Using SQL*Plus
    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
  16. Chapter 9: Single-Row Functions
    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
  17. Chapter 10: Joining Tables
    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
  18. Chapter 11: Grouping and Summarizing Data
    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
  19. Chapter 12: Subqueries
    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
  20. Chapter 13: Unusual Query Types
    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
  21. Chapter 14: Expressions
    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
  22. Chapter 15: Data Manipulation Language (DML)
    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
  23. Chapter 16: Datatypes and Collections
    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
  24. Chapter 17: XML in Oracle
    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
  25. Chapter 18: Tables
    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
  26. Chapter 19: Views
    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
  27. Chapter 20: Constraints
    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
  28. Chapter 21: Indexes and Clusters
    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
  29. Chapter 22: Sequences and Synonyms
    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
  30. Chapter 23: Security
    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
  31. Chapter 24: Basic PL/SQL
    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
  32. Appendix A: Schema Scripting
    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
  33. Appendix B: Utility Scripts
    1. B.1 Tables
    2. B.2 Constraints
    3. B.3 Indexes
  34. Appendix C: Sources of Information
    1. C.1 Other titles by the authors
  35. Index

Product information

  • Title: Oracle SQL
  • Author(s): Gavin JT Powell, Carol McCullough-Dieter
  • Release date: September 2004
  • Publisher(s): Digital Press
  • ISBN: 9780080479835