Oracle Database 10g SQL

Table of contents

1. Cover Page
2. Oracle Database 10g SQL
3. Copyright Page
4. Dedication
5. About the Authors
6. Contents at a Glance
7. Contents
8. Acknowledgments
9. Introduction
10. 1 Introduction
    1. What Is a Relational Database?
    2. Introducing the Structured Query Language (SQL)
    3. Using SQL*Plus
       1. Starting the Windows Version of SQL*Plus
       2. Starting the Command-Line Version of SQL*Plus
    4. Performing a SELECT Statement Using SQL*Plus
    5. The SQL*Plus Worksheet
    6. Creating the Store Schema
       1. Running the SQL*Plus Script to Create the Store Schema
       2. Data Definition Language (DDL) Statements Used to Create the Store Schema
    7. Adding, Modifying, and Removing Rows
       1. Adding a Row to a Table
    8. Modifying an Existing Row in a Table
    9. Removing a Row from a Table
    10. The New Oracle 10g BINARY_FLOAT and BINARY_DOUBLE Types
        1. Benefits of BINARY_FLOAT and BINARY_DOUBLE
        2. Using BINARY_FLOAT and BINARY_DOUBLE in a Table
        3. Special Values
    11. Quitting SQL*Plus
    12. Introducing Oracle PL/SQL
    13. Summary
11. 2 Retrieving Information from Database Tables
    1. Performing Single Table SELECT Statements
    2. Selecting All Columns from a Table
    3. Understanding Row Identifiers
    4. Performing Arithmetic
       1. Using Date Arithmetic
       2. Using Columns in Arithmetic
    5. Using Column Aliases
    6. Merging Column Output Using Concatenation
    7. Understanding Null Values
    8. Displaying Distinct Rows
    9. Filtering Rows Using the WHERE Clause
       1. Using Comparison Operators
       2. Using the SQL Operators
       3. Using the Logical Operators
       4. Understanding Operator Precedence
    10. Sorting Rows Using the ORDER BY Clause
    11. Performing SELECT Statements that Use Two Tables
    12. Supplying Table Aliases
    13. Cartesian Products
    14. Performing SELECT Statements that Use More than Two Tables
    15. Understanding Join Conditions and Join Types
        1. Understanding Non-equijoins
        2. Understanding Outer Joins
        3. Understanding Self Joins
    16. Performing Joins Using the SQL/92 Syntax
        1. Performing Inner Joins on Two Tables Using SQL/92
        2. Simplifying Joins with the USING Keyword
        3. Performing Inner Joins on More than Two Tables Using SQL/92
        4. Performing Inner Joins on Multiple Columns Using SQL/92
        5. Performing Outer Joins Using SQL/92
        6. Performing Self Joins Using SQL/92
        7. Performing Cross Joins Using SQL/92
    17. Summary
12. 3 Using Simple Functions
    1. Using Single Row Functions
       1. Character Functions
       2. Numeric Functions
       3. Conversion Functions
       4. Regular Expression Functions
    2. Using Aggregate Functions
       1. AVG()
       2. COUNT()
       3. MAX() and MIN()
       4. STDDEV()
       5. SUM()
       6. VARIANCE()
    3. Grouping Rows
       1. Using the GROUP BY Clause to Group Rows
       2. Incorrect Usage of Aggregate Function Calls
       3. Using the HAVING Clause to Filter Groups of Rows
       4. Using the WHERE and GROUP BY Clauses Together
       5. Using the WHERE, GROUP BY, and HAVING Clauses Together
    4. Summary
13. 4 Storing and Processing Dates and Times
    1. Simple Examples of Storing and Retrieving Dates
    2. Converting Datetimes Using TO_CHAR() and TO DATE()
       1. Using TO_CHAR() to Convert a Datetime to a String
       2. Using TO_DATE() to Convert a String to a Datetime
    3. Setting the Default Date Format
    4. How Oracle Interprets Two-Digit Years
       1. Using the YY Format
       2. Using the RR Format
    5. Using Datetime Functions
       1. ADD_MONTHS()
       2. LAST_DAY()
       3. MONTHS_BETWEEN()
       4. NEXT_DAY()
       5. ROUND()
       6. SYSDATE()
       7. TRUNC()
    6. Understanding Time Zones
       1. Time Zone-Related Functions
       2. The Database Time Zone and Session Time Zone
       3. Obtaining Time Zone Offsets
       4. Obtaining Time Zone Names
       5. Converting a Datetime from One Time Zone to Another
    7. Using Timestamps
       1. Using the Timestamp Types
       2. Timestamp-Related Functions
    8. Using Time Intervals
       1. Using the INTERVAL YEAR TO MONTH Type
       2. Using the INTERVAL DAY TO SECOND Type
       3. Time Interval-Related Functions
    9. Summary
14. 5 Using SQL*Plus
    1. Viewing the Structure of a Table
    2. Editing SQL Statements
    3. Saving, Retrieving, and Running Files
    4. Formatting Columns
    5. Setting the Page Size
    6. Setting the Line Size
    7. Clearing Column Formatting
    8. Using Variables
       1. Temporary Variables
       2. Defined Variables
    9. Creating Simple Reports
       1. Using Temporary Variables in a Script
       2. Using Defined Variables in a Script
       3. Passing a Value to a Variable in a Script
       4. Adding a Header and Footer
       5. Computing Subtotals
    10. Automatically Generating SQL Statements
    11. Summary
15. 6 Subqueries
    1. Types of Subqueries
    2. Writing Single Row Subqueries
       1. Subqueries in a WHERE Clause
       2. Subqueries in a HAVING Clause
       3. Subqueries in a FROM Clause (Inline Views)
       4. A Couple of Errors You Might Encounter
    3. Writing Multiple Row Subqueries
       1. Using IN with a Multiple Row Subquery
       2. Using ANY with a Multiple Row Subquery
       3. Using ALL with a Multiple Row Subquery
    4. Writing Multiple Column Subqueries
    5. Writing Correlated Subqueries
       1. A Correlated Subquery Example
       2. Using EXISTS and NOT EXISTS with a Correlated Subquery
    6. Writing Nested Subqueries
    7. Writing UPDATE and DELETE Statements Containing Subqueries
       1. Writing an UPDATE Statement Containing a Subquery
       2. Writing a DELETE Statement Containing a Subquery
    8. Summary
16. 7 Advanced Queries
    1. Using the Set Operators
       1. The Example Tables
       2. Using the UNION ALL Operator
       3. Using the UNION Operator
       4. Using the INTERSECT Operator
       5. Using the MINUS Operator
       6. Combining Set Operators
    2. Using the TRANSLATE() Function
    3. Using the DECODE() Function
    4. Using the CASE Expression
       1. Using Simple CASE Expressions
       2. Using Searched CASE Expressions
    5. Hierarchical Queries
       1. The Example Data
       2. Using the CONNECT BY and START WITH Clauses
       3. Using the LEVEL Pseudo-Column
       4. Formatting the Results from a Hierarchical Query
       5. Starting at a Node Other than the Root
       6. Using a Subquery in a START WITH Clause
       7. Traversing Upward Through the Tree
       8. Eliminating Nodes and Branches from a Hierarchical Query
       9. Including Other Conditions in a Hierarchical Query
    6. Using the Extended GROUP BY Clauses
       1. The Example Tables
    7. Using the ROLLUP Clause
       1. Using the CUBE Clause
       2. Using the GROUPING() Function
       3. Using the GROUPING SETS Clause
       4. Using the GROUPING_ID() Function
       5. Using a Column Multiple Times in a GROUP BY Clause
       6. Using the GROUP_ID() Function
    8. Using the Analytic Functions
       1. The Example Table
       2. Using the Ranking Functions
       3. Using the Inverse Percentile Functions
       4. Using the Window Functions
       5. Using the Reporting Functions
       6. Using the LAG() and LEAD() Functions
       7. Using the FIRST and LAST Functions
       8. Using the Linear Regression Functions
       9. Using the Hypothetical Rank and Distribution Functions
    9. Using the MODEL Clause
       1. An Example of the MODEL Clause
       2. Using Positional and Symbolic Notation to Access Cells
       3. Accessing a Range of Cells Using BETWEEN and AND
       4. Accessing All Cells Using ANY and IS ANY
       5. Getting the Current Value of a Dimension Using CURRENTV()
       6. Accessing Cells Using a FOR Loop
       7. Handling Null and Missing Values
       8. Updating Existing Cells
    10. Summary
17. 8 Changing Table Contents
    1. Adding Rows Using the INSERT Statement
       1. Omitting the Column List
       2. Specifying a Null Value for a Column
       3. Including Single and Double Quotes in a Column Value
       4. Copying Rows from One Table to Another
    2. Modifying Rows Using the UPDATE Statement
       1. The RETURNING Clause
    3. Removing Rows Using the DELETE Statement
    4. Database Integrity
       1. Enforcement of Primary Key Constraints
       2. Enforcement of Foreign Key Constraints
    5. Using Default Values
    6. Merging Rows Using MERGE
    7. Database Transactions
       1. Committing and Rolling Back a Transaction
       2. Starting and Ending a Transaction
       3. Savepoints
       4. ACID Transaction Properties
       5. Concurrent Transactions
       6. Transaction Locking
       7. Transaction Isolation Levels
       8. A SERIALIZABLE Transaction Example
    8. Query Flashbacks
       1. Granting the Privilege for Using Flashbacks
       2. Time Query Flashbacks
       3. System Change Number Query Flashbacks
    9. Summary
18. 9 Database Security
    1. Users
       1. Creating a User
       2. Changing a User’s Password
       3. Deleting a User
    2. System Privileges
       1. Granting System Privileges to a User
       2. Checking System Privileges Granted to a User
       3. Making Use of System Privileges
       4. Revoking System Privileges from a User
    3. Object Privileges
       1. Granting Object Privileges to a User
       2. Checking Object Privileges Made
       3. Checking Object Privileges Received
       4. Making Use of Object Privileges
       5. Synonyms
       6. Public Synonyms
       7. Revoking Object Privileges
    4. Roles
       1. Creating Roles
       2. Granting Privileges to Roles
       3. Granting Roles to a User
       4. Checking Roles Granted to a User
       5. Checking System Privileges Granted to a Role
       6. Checking Object Privileges Granted to a Role
       7. Making Use of Privileges Granted to a Role
       8. Default Roles
       9. Revoking a Role
       10. Revoking Privileges from a Role
       11. Dropping a Role
    5. Summary
19. 10 Creating Tables, Sequences, Indexes, and Views
    1. Tables
       1. Creating a Table
       2. Getting Information on Tables
       3. Getting Information on Columns in Tables
       4. Altering a Table
       5. Renaming a Table
       6. Adding a Comment to a Table
       7. Truncating a Table
       8. Dropping a Table
    2. Sequences
       1. Creating a Sequence
       2. Getting Information on Sequences
       3. Using a Sequence
       4. Populating a Primary Key Using a Sequence
       5. Modifying a Sequence
       6. Dropping a Sequence
    3. Indexes
       1. Creating an Index
       2. Creating a Function-Based Index
       3. Getting Information on Indexes
       4. Getting Information on the Indexes on a Column
       5. Modifying an Index
       6. Dropping an Index
    4. Views
       1. Creating and Using a View
       2. Modifying a View
       3. Dropping a View
    5. Summary
20. 11 Introducing PL/SQL Programming
    1. Block Structure
    2. Variables and Types
    3. Conditional Logic
    4. Loops
       1. Simple Loops
       2. WHILE Loops
       3. FOR Loops
    5. Cursors
       1. Step 1: Declare the Variables to Store the Column Values
       2. Step 2: Declare the Cursor
       3. Step 3: Open the Cursor
       4. Step 4: Fetch the Rows from the Cursor
       5. Step 5: Close the Cursor
       6. Complete Example: product_cursor.sql
       7. Cursors and FOR Loops
    6. Exceptions
       1. ZERO_DIVIDE Exception
       2. DUP_VAL_ON_INDEX Exception
       3. INVALID_NUMBER Exception
       4. OTHERS Exception
    7. Procedures
       1. Creating a Procedure
       2. Calling a Procedure
       3. Getting Information on Procedures
       4. Dropping a Procedure
       5. Viewing Errors in a Procedure
    8. Functions
       1. Creating a Function
       2. Calling a Function
       3. Getting Information on Functions
       4. Dropping a Function
    9. Packages
       1. Creating a Package Specification
       2. Creating a Package Body
       3. Calling Functions and Procedures in a Package
       4. Getting Information on Functions and Procedures in a Package
       5. Dropping a Package
    10. Triggers
        1. When a Trigger Runs
        2. Set Up for the Example Trigger
        3. Creating a Trigger
        4. Firing a Trigger
        5. Getting Information on Triggers
        6. Disabling and Enabling Trigger
        7. Dropping a Trigger
    11. Summary
21. 12 Database Objects
    1. Introducing Objects
    2. Creating Object Types
    3. Using DESCRIBE to Get Information on Object Types
    4. Using Object Types to Define Column Objects and Object Tables
       1. Object References and Object Identifiers
    5. Performing DML on the products Table
       1. Inserting Rows into the products Table
       2. Selecting Rows from the products Table
       3. Updating a Row in the products Table
       4. Deleting a Row from the products Table
    6. Performing DML on the object_products Table
       1. Inserting Rows into the object_products Table
       2. Selecting Rows from the object_products Table
       3. Updating a Row in the object_products Table
       4. Deleting a Row from the object_products Table
    7. Performing DML on the object_customers Table
       1. Inserting Rows into the object_customers Table
       2. Selecting Rows from the object_customers Table
    8. Performing DML on the purchases Table
       1. Inserting a Row into the purchases Table
       2. Selecting a Row from the purchases Table
       3. Updating a Row in the purchases Table
    9. Using Objects in PL/SQL
    10. Type Inheritance
    11. NOT INSTANTIABLE Object Types
    12. User-Defined Constructors
    13. Summary
22. 13 Collections
    1. Introducing Collections
    2. Varrays
       1. Creating a Varray Type
       2. Using a Varray Type to Define a Column in a Table
       3. Getting Information on Varrays
       4. Populating a Varray with Elements
       5. Selecting Varray Elements
       6. Modifying Varray Elements
    3. Nested Tables
       1. Creating a Nested Table Type
       2. Using a Nested Table Type to Define a Column in a Table
       3. Getting Information on Nested Tables
       4. Populating a Nested Table with Elements
       5. Selecting Nested Table Elements
       6. Modifying Nested Table Elements
    4. Multilevel Collection Types
    5. Using Collections in PL/SQL
       1. Manipulating Varrays
       2. Manipulating Nested Tables
       3. Collection Methods
    6. Oracle10g Enhancements to Collections
       1. Associative Arrays
       2. Changing the Size or Precision of an Element Type
       3. Increasing the Number of Elements in a Varray
       4. Using Varrays in Temporary Tables
       5. Using a Different Tablespace for a Nested Table’s Storage Table
       6. ANSI Support for Nested Tables
    7. Summary
23. 14 Large Objects
    1. Introducing Large Objects (LOBs)
    2. The Example Files
    3. Understanding the Large Object Types
    4. Creating Tables Containing Large Objects
    5. Using Large Objects in SQL
       1. Using CLOBs and BLOBs
       2. Using BFILEs
    6. Using Large Objects in PL/SQL
       1. READ()
       2. WRITE()
       3. APPEND()
       4. CLOSE()
       5. COMPARE()
       6. COPY()
       7. CREATETEMPORARY()
       8. ERASE()
       9. FILECLOSE()
       10. FILECLOSEALL()
       11. FILEEXISTS()
       12. FILEGETNAME()
       13. FILEISOPEN()
       14. FILEOPEN()
       15. FREETEMPORARY()
       16. GETCHUNKSIZE()
       17. GET_STORAGE_LIMIT()
       18. GETLENGTH()
       19. INSTR()
       20. ISOPEN()
       21. ISTEMPORARY()
       22. LOADFROMFILE()
       23. LOADBLOBFROMFILE()
       24. LOADCLOBFROMFILE()
       25. OPEN()
       26. SUBSTR()
       27. TRIM()
       28. WRITEAPPEND()
    7. Understanding the LONG and LONG RAW Types
       1. The Example Tables
       2. Using LONG and LONG RAW Columns
    8. Oracle Database 10g Enhancements to Large Objects
       1. Implicit Conversion Between CLOB and NCLOB Objects
       2. Use of the :new Attribute When Using LOBs in a Trigger
    9. Summary
24. 15 Running SQL Using Java
    1. Getting Started
    2. Configuring Your Computer
       1. Setting the ORACLE_HOME Environment Variable
       2. Setting the JAVA_HOME Environment Variable
       3. Setting the PATH Environment Variable
       4. Setting the CLASSPATH Environment Variable
       5. Setting the LD_LIBRARY_PATH Environment Variable
    3. The Oracle JDBC Drivers
       1. The Thin Driver
       2. The OCI Driver
       3. The Server-Side Internal Driver
       4. The Server-Side Thin Driver
    4. Importing the JDBC Packages
    5. Registering the Oracle JDBC Drivers
    6. Opening a Database Connection
       1. Connecting to the Database Using the getConnection() Method of the DriverManager Class
       2. Connecting to the Database Using an Oracle Data Source
    7. Creating a JDBC Statement Object
    8. Retrieving Rows from the Database
       1. Step 1: Create and Populate a ResultSet Object
       2. Step 2: Read the Column Values from the ResultSet Object
       3. Step 3: Close the ResultSet Object
    9. Adding Rows to the Database
    10. Modifying Rows in the Database
    11. Deleting Rows from the Database
    12. Handling Numbers
    13. Handling Database Null Values
    14. Controlling Database Transactions
    15. Performing Data Definition Language Statements
    16. Handling Exceptions
    17. Closing Your JDBC Objects
    18. Example Program: BasicExample1.java
        1. Compile BasicExample1
        2. Run BasicExample1
    19. Prepared SQL Statements
    20. Example Program: BasicExample2.java
    21. The Oracle JDBC Extensions
        1. The oracle.sql Package
        2. The oracle.jdbc Package
        3. Example Program: BasicExample3.java
    22. Summary
25. 16 High Performance SQL Tuning
    1. Introducing SQL Tuning
    2. Use a WHERE Clause to Filter Rows
    3. Use Table Joins Rather than Multiple Queries
    4. Use Fully Qualified Column References When Performing Joins
    5. Use CASE Expressions Rather than Multiple Queries
    6. Add Indexes to Tables
    7. Use WHERE Rather than HAVING
    8. Use UNION ALL Rather than UNION
    9. Use EXISTS Rather than IN
    10. Use EXISTS Rather than DISTINCT
    11. Use Bind Variables
        1. Non-Identical SQL Statements
        2. Defining Identical SQL Statements Using a Bind Variable
        3. Listing and Printing Bind Variables
        4. Using a Bind Variable to Store a Value Returned by a PL SQL Function
        5. Using a Bind Variable to Store Rows from a REFCURSOR
    12. Comparing the Cost of Performing Queries
        1. Examining Execution Plans
        2. Comparing Execution Plans
    13. Passing Hints to the Optimizer
    14. Additional Tuning Tools
        1. Statspack Package
        2. Oracle Enterprise Manager Diagnostics Pack
        3. Automatic Database Diagnostic Monitoring
    15. Summary
26. A Oracle Data Types
    1. Oracle SQL Types
    2. Oracle PL/SQL Types
27. Index