Oracle Database 11g SQL

Oracle Database 11g SQL

by Jason Price
Released November 2007
Publisher(s): McGraw Hill Computing
ISBN: 9780071596138

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

Write powerful SQL statements and PL/SQL programs

Learn to access Oracle databases through SQL statements and construct PL/SQL programs with guidance from Oracle expert, Jason Price. Published by Oracle Press, Oracle Database 11g SQL explains how to retrieve and modify database information, use SQL Plus and SQL Developer, work with database objects, write PL/SQL programs, and much more. Inside, you'll find in-depth coverage of the very latest SQL features and tools, performance optimization techniques, advanced queries, Java support, and XML. This book contains everything you need to master SQL.

  • Explore SQL Plus and SQL Developer
  • Use SQL SELECT, INSERT, UPDATE, and DELETE statements
  • Write PL/SQL programs
  • Create tables, sequences, indexes, views, and triggers
  • Write advanced queries containing complex analytical functions
  • Create database objects and collections to handle abstract data
  • Use large objects to handle multimedia files containing music and movies
  • Write Java programs to access an Oracle Database using JDBC
  • Tune your SQL statements to make them execute faster
  • Explore the XML capabilities of the Oracle Database
  • Master the very latest Oracle Database 11g features, such as PIVOT and UNPIVOT, flashback archives, and much more

Table of contents

  1. Cover Page
  2. Oracle Database 11g SQL
  3. Copyright Page
  4. Dedication Page
  5. About the Technical Editor
  6. Contents
  7. Acknowledgments
  8. Introduction
  9. 1 Introduction
    1. What Is a Relational Database?
    2. Introducing the Structured Query Language (SQL)
    3. Using SQL*Plus
      1. Starting SQL*Plus
      2. Starting SQL*Plus from the Command Line
      3. Performing a SELECT Statement Using SQL * Plus
    4. SQL Developer
    5. 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
    6. Adding, Modifying, and Removing Rows
      1. Adding a Row to a Table
      2. Modifying an Existing Row in a Table
      3. Removing a Row from a Table
    7. The 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
    8. Quitting SQL*Plus
    9. Introducing Oracle PL/SQL
    10. Summary
  10. 2 Retrieving Information from Database Tables
    1. Performing Single Table SELECT Statements
    2. Retrieving All Columns from a Table
    3. Specifying Rows to Retrieve Using the WHERE Clause
    4. Row Identifiers
    5. Row Numbers
    6. Performing Arithmetic
      1. Performing Date Arithmetic
      2. Using Columns in Arithmetic
    7. Using Column Aliases
    8. Combining Column Output Using Concatenation
    9. Null Values
    10. Displaying Distinct Rows
    11. Comparing Values
    12. Using the SQL Operators
      1. Using the LIKE Operator
      2. Using the IN Operator
      3. Using the BETWEEN Operator
    13. Using the Logical Operators
    14. Operator Precedence
    15. Sorting Rows Using the ORDER BY Clause
    16. Performing SELECT Statements That Use Two Tables
    17. Using Table Aliases
    18. Cartesian Products
    19. Performing SELECT Statements That Use More than Two Tables
    20. Join Conditions and Join Types
      1. Non-equijoins
      2. Outer Joins
      3. Self Joins
    21. 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
    22. Summary
  11. 3 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. Getting Help from SQL* Plus
    11. Automatically Generating SQL Statements
    12. Disconnecting from the Database and Exiting SQL* Plus
    13. Summary
  12. 4 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. 5 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. Using Time Zones
      1. Time Zone 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 Functions
    8. Using Time Intervals
      1. Using the INTERVAL YEAR TO MONTH Type
      2. Using the INTERVAL DAY TO SECOND Type
      3. Time Interval Functions
    9. Summary
  14. 6 Subqueries
    1. Types of Subqueries
    2. Writing Single-Row Subqueries
      1. Subqueries in a WHERE Clause
      2. Using Other Single-Row Operators
      3. Subqueries in a HAVING Clause
      4. Subqueries in a FROM Clause (Inline Views)
      5. 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
  15. 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. Using the PIVOT and UNPIVOT Clauses
      1. A Simple Example of the PIVOT Clause
      2. Pivoting on Multiple Columns
      3. Using Multiple Aggregate Functions in a Pivot
      4. Using the UNPIVOT Clause
    11. Summary
  16. 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
    3. The RETURNING Clause
    4. Removing Rows Using the DELETE Statement
    5. Database Integrity
      1. Enforcement of Primary Key Constraints
      2. Enforcement of Foreign Key Constraints
    6. Using Default Values
    7. Merging Rows Using MERGE
    8. 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
    9. Query Flashbacks
      1. Granting the Privilege for Using Flashbacks
      2. Time Query Flashbacks
      3. System Change Number Query Flashbacks
    10. Summary
  17. 9 Users, Privileges, and Roles
    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. Auditing
      1. Privileges Required to Perform Auditing
      2. Auditing Examples
      3. Audit Trail Views
    6. Summary
  18. 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. Retrieving 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 a B-tree Index
      2. Creating a Function-Based Index
      3. Retrieving Information on Indexes
      4. Retrieving Information on the Indexes on a Column
      5. Modifying an Index
      6. Dropping an Index
      7. Creating a Bitmap Index
    4. Views
      1. Creating and Using a View
      2. Modifying a View
      3. Dropping a View
    5. Flashback Data Archives
    6. Summary
  19. 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
      8. OPEN-FOR Statement
      9. Unconstrained Cursors
    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 Fires
      2. Set Up for the Example Trigger
      3. Creating a Trigger
      4. Firing a Trigger
      5. Getting Information on Triggers
      6. Disabling and Enabling a Trigger
      7. Dropping a Trigger
    11. New Oracle Database 11g PL/SQL Features
      1. SIMPLE_INTEGER Type
      2. Sequences in PL/SQL
      3. PL/SQL Native Machine Code Generation
    12. Summary
  20. 12 Database Objects
    1. Introducing Objects
    2. Creating Object Types
    3. Using DESCRIBE to Get Information on Object Types
    4. Using Object Types in Database Tables
      1. Column Objects
      2. Object Tables
      3. Object Identifiers and Object References
      4. Comparing Object Values
    5. Using Objects in PL/SQL
      1. The get_products() Function
      2. The display_product() Procedure
      3. The insert_product() Procedure
      4. The update_product_price() Procedure
      5. The get_product() Function
      6. The update_product() Procedure
      7. The get_product_ref() Function
      8. The delete_product() Procedure
      9. The product_lifecycle() Procedure
      10. The product_lifecycle2() Procedure
    6. Type Inheritance
    7. Using a Subtype Object in Place of a Supertype Object
      1. SQL Examples
      2. PL/SQL Examples
      3. NOT SUBSTITUTABLE Objects
    8. Other Useful Object Functions
      1. IS OF()
      2. TREAT()
      3. SYS_TYPEID()
    9. NOT INSTANTIABLE Object Types
    10. User-Defined Constructors
    11. Overriding Methods
    12. Generalized Invocation
    13. Summary
  21. 13 Collections
    1. Introducing Collections
    2. Creating Collection Types
      1. Creating a Varray Type
      2. Creating a Nested Table Type
    3. Using a Collection Type to Define a Column in a Table
      1. Using a Varray Type to Define a Column in a Table
      2. Using a Nested Table Type to Define a Column in a Table
    4. Getting Information on Collections
      1. Getting Information on a Varray
      2. Getting Information on a Nested Table
    5. Populating a Collection with Elements
      1. Populating a Varray with Elements
      2. Populating a Nested Table with Elements
    6. Retrieving Elements from Collections
      1. Retrieving Elements from a Varray
      2. Retrieving Elements from a Nested Table
    7. Using TABLE() to Treat a Collection as a Series of Rows
      1. Using TABLE() with a Varray
      2. Using TABLE() with a Nested Table
    8. Modifying Elements of Collections
      1. Modifying Elements of a Varray
      2. Modifying Elements of a Nested Table
    9. Using a Map Method to Compare the Contents of Nested Tables
    10. Using CAST() to Convert Collections from One Type to Another
      1. Using CAST() to Convert a Varray to a Nested Table
      2. Using CAST() to Convert a Nested Table to a Varray
    11. Using Collections in PL/SQL
      1. Manipulating a Varray
      2. Manipulating a Nested Table
      3. PL/SQL Collection Methods
    12. Multilevel Collections
    13. Oracle Database 10g Enhancements to Collections
      1. Associative Arrays
      2. Changing the Size 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
    14. Summary
  22. 14 Large Objects
    1. Introducing Large Objects (LOBs)
    2. The Example Files
    3. 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. APPEND()
      2. CLOSE()
      3. COMPARE()
      4. COPY()
      5. CREATETEMPORARY()
      6. ERASE()
      7. FILECLOSE()
      8. FILECLOSEALL()
      9. FILEEXISTS()
      10. FILEGETNAME()
      11. FILEISOPEN()
      12. FILEOPEN()
      13. FREETEMPORARY()
      14. GETCHUNKSIZE()
      15. GET_STORAGE_LIMIT()
      16. GETLENGTH()
      17. INSTR()
      18. ISOPEN()
      19. ISTEMPORARY()
      20. LOADFROMFILE()
      21. LOADBLOBFROMFILE()
      22. LOADCLOBFROMFILE()
      23. OPEN()
      24. READ()
      25. SUBSTR()
      26. TRIM()
      27. WRITE()
      28. WRITEAPPEND()
      29. Example PL/SQL Procedures
    7. LONG and LONG RAW Types
      1. The Example Tables
      2. Adding Data to LONG and LONG RAW Columns
      3. Converting LONG and LONG RAW Columns to LOBs
    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. Oracle Database 11g Enhancements to Large Objects
      1. Encrypting LOB Data
      2. Compressing LOB Data
      3. Removing Duplicate LOB Data
    10. Summary
  23. 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 getConnection()
      2. The Database URL
      3. 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
  24. 16 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 GROUPING SETS Rather than CUBE
    12. Use Bind Variables
      1. Non-Identical SQL Statements
      2. Identical SQL Statements That Use Bind Variables
      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
    13. Comparing the Cost of Performing Queries
      1. Examining Execution Plans
      2. Comparing Execution Plans
    14. Passing Hints to the Optimizer
    15. Additional Tuning Tools
      1. Oracle Enterprise Manager Diagnostics Pack
      2. Automatic Database Diagnostic Monitor
    16. Summary
  25. 17 XML and the Oracle Database
    1. Introducing XML
    2. Generating XML from Relational Data
      1. XMLELEMENT()
      2. XMLATTRIBUTES()
      3. XMLFOREST()
      4. XMLAGG()
      5. XMLCOLATTVAL()
      6. XMLCONCAT()
      7. XMLPARSE()
      8. XMLPI()
      9. XMLCOMMENT()
      10. XMLSEQUENCE()
      11. XMLSERIALIZE()
      12. A PL/SQL Example That Writes XML Data to a File
      13. XMLQUERY()
    3. Saving XML in the Database
      1. The Example XML File
      2. Creating the Example XML Schema
      3. Retrieving Information from the Example XML Schema
      4. Updating Information in the Example XML Schema
    4. Summary
  26. A Oracle Data Types
    1. Oracle SQL Types
    2. Oracle PL/SQL Types
  27. Index

Product information

  • Title: Oracle Database 11g SQL
  • Author(s): Jason Price
  • Release date: November 2007
  • Publisher(s): McGraw Hill Computing
  • ISBN: 9780071596138