You are previewing Oracle® SQL by Example, Third Edition.
O'Reilly logo
Oracle® SQL by Example, Third Edition

Book Description

The book ... has enough depth for even a seasoned professional to pick up enough tips to pay back the price of the book many times over.—Dr. Paul Dorsey, President Dulcian, Inc., and President, New York Oracle Users Group.

Students find Oracle SQL by Example to be extremely helpful not only to their coursework but also in preparing them for future careers as Oracle DBAs and developers. The layout of the text is conducive to the learning process. It introduces a concept and then reinforces that concept with an exercise.—Betsy Jenaway, Coordinator, Malcomb Operations, and Senior Lecturer College of Management, Lawrence Technological University, Southfield, MI.

The world's #1 hands-on Oracle SQL workbook ... fully updated for Oracle 10g

Crafted for hands-on learning and tested in classrooms worldwide this book illuminates in-depth every Oracle SQL technique you'll need. From the simplest query fundamentals through the newest regular expression database enhancements, you will focus on the tasks that matter most. Hundreds of guided lab exercises will systematically strengthen your expertise in writing effective, high-performance SQL. Along the way, you will acquire an arsenal of useful Oracle SQL knowledge and techniques: an extraordinary library of solutions for your real-world challenges with Oracle SQL.

  • Creating and using tables, views, indexes, and sequences

  • Working in SQL*Plus and iSQL*Plus environments

  • Oracle functions in depth—especially number, character, date, timestamp, interval, time zone, aggregate, and analytical functions

  • All types of joins, including equijoins, outer joins, self joins and ANSI join syntax options

  • Every type of subquery, including correlated and scalar subqueries, set operators, hierarchical queries, flashback queries, data dictionary, object-relational features, regular expressions, and more

  • Common pitfalls to avoid, and solutions for common mistakes

  • Practical performance, security, and architectural solutions

  • Insights, tips, and tricks-of-the-trade from an experienced Oracle professional

  • For every database developer, administrator, designer or architect, regardless of experience!

Table of Contents

  1. Copyright
    1. Dedication
  2. The Prentice Hall PTR Oracle Series The Independent Voice on Oracle
  3. About Prentice Hall Professional Technical Reference
  4. Foreword
    1. The Ancient Problem
    2. The Ancient Solution
    3. The Modern-Day Problem
    4. The Modern-Day Solution
  5. Acknowledgments
  6. About the Author
  7. Introduction
    1. Who This Book Is For
    2. What Makes This Book Different
    3. How This Book Is Organized
      1. Layout Of A Chapter
    4. About The Companion Web Site
      1. Installation Files
      2. Test Your Thinking
      3. Additional Self-Review Questions
    5. What You Will Need
      1. Oracle 10g
      2. Oracle SQL*PLUS Software or Web Browser
      3. Access To The Internet
      4. About The Student Schema
    6. Conventions Used In This Book
    7. Errata
  8. 1. SQL and Data
    1. Lab 1.1 Data, Databases, And The Definition Of Sql
    2. Tables
    3. Columns
    4. Rows
    5. Primary Key
    6. Foreign Keys
    7. Sql Language Commands
    8. Lab 1.1 Exercises
      1. 1.1.1. Identify and Group Data
      2. 1.1.2. Define SQL
      3. 1.1.3. Define the Structures of a RDBMS: Tables, Columns, Rows, and Keys
    9. Lab 1.1 Exercise Answers
      1. 1.1.1. Answers
      2. 1.1.2. Answers
      3. 1.1.3. Answers
    10. Lab 1.1 Self-Review Questions
    11. Lab 1.2 Table Relationships
    12. Data Normalization
      1. First Normal Form
      2. Second Normal Form
      3. Third Normal Form
      4. Boyce–Codd Normal Form, Fourth Normal Form, And Fifth Normal Form
    13. Table Relationships
      1. One-To-Many Relationship (1:M)
      2. One-To-One Relationship (1:1)
      3. Many-To-Many Relationship (M:M)
    14. Drawing Relationships
      1. Cardinality And Optionality
      2. Real-World Business Practice
      3. Labeling Relationships
      4. Identifying And Nonidentifying Relationships
    15. Database Development Context
      1. Requirements Analysis
      2. Conceptual Data Model
      3. Logical Data Model
      4. Physical Data Model
      5. Transfer From Logical To Physical Model
      6. Denormalization
    16. Lab 1.2 Exercises
      1. 1.2.1. Read a Schema Diagram
      2. 1.2.2. Identify Data Normalization Rules and Table Relationships
      3. 1.2.3. Understand the Database Development context
    17. Lab 1.2 Exercise Answers
      1. 1.2.1. Answers
        1. Mandatory Relationship On Both Ends
        2. No Duplicates Allowed
      2. 1.2.2. Answers
      3. 1.2.3. Answers
    18. Lab 1.2 Self-Review Questions
    19. Lab 1.3 The Student Schema Diagram
    20. The Student Table
      1. Datatypes
    21. The Course Table
      1. Recursive Relationship
    22. The Section Table
    23. The Instructor Table
    24. The Zipcode Table
    25. What About Delete Operations?
    26. The Enrollment Table
    27. The Grade_Type Table
    28. The Grade Table
    29. The Grade_Type_Weight Table
    30. The Grade_Conversion Table
    31. Lab 1.3 Exercises
      1. 1.3.1. Understand the Schema Diagram and Identify Table Relationships
    32. Lab 1.3 Exercise Answers
      1. 1.3.1. Answers
    33. Lab 1.3 Self-Review Questions
    34. Chapter 1 Test Your Thinking
  9. 2. SQL: The Basics
    1. Lab 2.1 The SQL*PLUS Environment
    2. Stand-Alone Environment
    3. Client–Server
    4. Three-Tier Architecture
    5. Sql And The Oracle Database Server
    6. User Id And Password
    7. Accessing The Oracle Database Server
    8. SQL*PLUS Client For Windows
    9. Connecting With A Web Browser: iSQL*PLUS
      1. Starting The iSQL*PLUS Application Server
    10. Command-Line Interfaces For SQL*PLUS
    11. The Remote Database And Common Log-On Problems
      1. Common Log-On Problems
    12. Exiting From SQL*PLUS or iSQL*PLUS
    13. Creating The Student Schema
    14. Lab 2.1 Exercises
      1. 2.1.1. Identify Oracle's Client/Server Software
      2. 2.1.2. Login and Logout of SQL*Plus
    15. Lab 2.1 Exercise Answers
      1. 2.1.1. Answers
      2. 2.1.2. Answers
    16. Common Datatypes
      1. Date
      2. Number
      3. VARCHAR2 and CHAR
      4. Other
    17. Lab 2.1 Self-Review Questions
    18. Lab 2.2 The Anatomy Of A Select Statement
    19. The Select Statement
      1. How Do You Write A Sql Query?
    20. Executing The Sql Statement
    21. Retrieving Multiple Columns
    22. Selecting All Columns
    23. Eliminating Duplicates With Distinct
    24. Displaying The Number Of Rows Returned
    25. Sql Statement Formatting Conventions
    26. Cancelling A Command And Pausing The Output
    27. Lab 2.2 Exercises
      1. 2.2.1. Write a SQL SELECT Statement
      2. 2.2.2. Use DISTINCT in a SQL Statement
    28. Lab 2.2 Exercise Answers
      1. 2.2.1. Answers
    29. Formatting Your Result: The SQL*PLUS COLUMN and FORMAT Commands
    30. Formatting Numbers
      1. 2.2.2. Answers
    31. Lab 2.2 Self-Review Questions
    32. Lab 2.3 Editing a SQL Statement
    33. The Line Editor
    34. Using An Editor In SQL*PLUS For Windows
      1. Changing The Default Directory Of SQL*PLUS For Windows
      2. Copying And Pasting Statements In SQL*PLUS For Windows
    35. Editing In iSQL*PLUS
      1. Preferences Screen
    36. Differences Between SQL*PLUS and iSQL*PLUS
    37. Lab 2.3 Exercises
      1. 2.3.1. Edit a SQL Statement Using SQL*Plus Commands
      2. 2.3.2. Edit a SQL Statement Using an Editor
      3. 2.3.3. Save, Retrieve, and Run a SQL Statement in iSQL*Plus
    38. Lab 2.3 Exercise Answers
      1. 2.3.1. Answers
    39. Other Useful Line Editor Commands
      1. 2.3.2. Answers
      2. 2.3.3. Answers
    40. Running Multiple Statements In iSQL*PLUS
    41. Comments In Sql Scripts
    42. Lab 2.3 Self-Review Questions
    43. Lab 2.4 The Where Clause: Comparison And Logical Operators
    44. Comparison Operators
      1. Testing For Equality And Inequality
      2. The Greater Than And Less Than Operators
      3. The Between Comparison Operator
        1. The IN Operator
        2. The LIKE Operator
        3. Negating Using Not
        4. Evaluating NULL Values
        5. Overview Of Comparison Operators
        6. Logical Operators
        7. Precedence Of Logical Operators
        8. NULLS and Logical Operators
    45. Lab 2.4 Exercises
      1. 2.4.1. Use Comparison and Logical Operators in a WHERE Clause
      2. 2.4.2. Use NULL in a WHERE Clause
    46. Lab 2.4 Exercise Answers
      1. 2.4.1. Answers
        1. BETWEEN and Text Literals
      2. 2.4.2. Answers
    47. Lab 2.4 Self-Review Questions
    48. Lab 2.5 The ORDER BY Clause
    49. Using The ORDER BY Clause
    50. Column Alias
    51. DISTINCT and ORDER BY
    52. NULL Values and ORDER BY
    53. Understanding Oracle Error Messages
      1. 1. Read The Oracle Error Message Carefully
      2. 2. Resolve One Error At A Time
      3. 3. Double-Check The Syntax Of Your Statement
      4. 4. Look Up The Oracle Error Number
    54. Lab 2.5 Exercises
      1. 2.5.1. Custom Sort Query Results
    55. Lab 2.5 Exercise Answers
      1. 2.5.1. Answers
    56. Lab 2.5 Self-Review Questions
    57. Chapter 2 Test Your Thinking
  10. 3. Character, Number, and Miscellaneous Functions
    1. Datatypes
    2. How To Read A Syntax Diagram
    3. Lab 3.1 Character Functions
    4. The Lower Function
    5. The Upper And Initcap Functions
    6. The LPAD and RPAD Functions
    7. The Dual Table
    8. The LTRIM, RTRIM, and TRIM Functions
    9. The SUBSTR Function
    10. The INSTR Function
    11. The LENGTH Function
    12. Functions In WHERE and ORDER BY Clauses
    13. Nested Functions
    14. Concatenation
    15. The REPLACE Function
    16. The TRANSLATE Function
    17. The SOUNDEX Function
    18. Which Character Function Should You Use?
    19. Searching, Replacing, And Validating Text
    20. Lab 3.1 Exercises
      1. 3.1.1. Use a Character Function in a SQL Statement
      2. 3.1.2. Concatenate Strings
    21. Lab 3.1 Exercise Answers
      1. 3.1.1. Answers
        1. Using TRANSLATE For Pattern Search
        2. Using INSTR For Pattern Search
        3. The Escape Character and the LIKE Operator
      2. 3.1.2. Answers
    22. Lab 3.1 Self-Review Questions
    23. LAB 3.2 Number Functions
    24. The ABS Function
    25. The SIGN Function
    26. ROUND and TRUNC Functions
    27. The FLOOR and CEIL Functions
    28. The MOD Function
    29. The NUMBER Versus The BINARY_FLOAT and BINARY_DOUBLE Datatypes
    30. The ROUND Function and FLOATING-POINT Numbers
    31. The REMAINDER Function
    32. Which Number Function Should You Use?
    33. Arithmetic Operators
    34. Lab 3.2 Exercises
      1. 3.2.1. Use Number Functions and Perform Mathematical Computations
    35. Lab 3.2 Exercise Answers
      1. 3.2.1. Answers
    36. Lab 3.2 Self-Review Questions
    37. Lab 3.3 Miscellaneous Single-Row Functions
    38. The NVL Function
    39. The COALESCE Function
    40. The NVL2 Function
    41. The NULLIF Function
    42. The NANVL Function
    43. The DECODE Function
      1. The DECODE Function and NULLS
      2. The DECODE Function and Comparisons
    44. THE SEARCHED CASE EXPRESSION
      1. NESTING CASE EXPRESSIONS
      2. CASE EXPRESSION IN THE WHERE CLAUSE
        1. DATATYPE INCONSISTENCIES
    45. SIMPLE CASE EXPRESSION
    46. OVERVIEW OF MISCELLANEOUS FUNCTIONS AND CASE EXPRESSIONS
    47. Lab 3.3 Exercises
      1. 3.3.1. Apply Substitution Functions and Other Miscellaneous Functions
      2. 3.3.2. Utilize the Power of the DECODE Function and the CASE Expression
    48. Lab 3.3 Exercise Answers
      1. 3.3.1. Answers
      2. 3.3.2. Answers
      3. Solution Using The Case Expression
        1. Solution Using The DECODE Function
        2. Solution Using CASE Expression
        3. Solution Using DECODE Function
    49. Lab 3.3 Self-Review Questions
    50. Chapter 3 Test Your Thinking
  11. 4. Date and Conversion Functions
    1. Lab 4.1 Applying Oracle's DATE Format Models
    2. Changing The DATE Display Format
      1. Using Special Format Masks
    3. How To Perform A DATE Search
    4. Implicit Conversion and Default DATE Format
    5. The RR DATE Format Mask and The Previous Century
    6. Don't Forget About The Time
      1. Time and The TRUNC Function
    7. The ANSI DATE and ANSI TIMESTAMP Formats
    8. Lab 4.1 Exercises
      1. 4.1.1. Compare a Text Literal to a DATE Column
      2. 4.1.2. Apply Format Models
    9. Lab 4.1 Exercise Answers
      1. 4.1.1. Answers
        1. Error When Entering The Wrong Format
      2. 4.1.2. Answers
    10. The Fill Mode
      1. Solution One
      2. Solution Two
    11. Avoid This Common Error
      1. TO_CHAR Function Versus TO_DATE Function
    12. Lab 4.1 Self-Review Questions
    13. Lab 4.2 Performing Date And Time Math
    14. The SYSDATE Function
    15. The ROUND Function
    16. Performing Arithmetic On Dates
    17. The EXTRACT Function
    18. Lab 4.2 Exercises
      1. 4.2.1. Understand the SYSDATE Function and Perform Date Arithmetic
    19. Lab 4.2 Exercise Answers
      1. 4.2.1. Answers
    20. Lab 4.2 Self-Review Questions
    21. Lab 4.3 Understanding TIMESTAMP and TIME ZONE DATATYPES
    22. The TIMESTAMP Datatype
    23. The TIMESTAMP WITH TIME ZONE Datatype
    24. The TIMESTAMP WITH LOCAL TIME ZONE Datatype
    25. Daylight Savings
    26. Default Format Masks
    27. Datetime Functions
      1. The LOCALTIMESTAMP Function
      2. The SYSTIMESTAMP Function
      3. The CURRENT_TIMESTAMP Function
      4. The CURRENT_DATE Function
      5. The SESSIONTIMEZONE Function
      6. The DBTIMEZONE Function
      7. Changing The Local Time Zone
      8. Overriding The Individual Session Time Zone
    28. Extract Functions
      1. The SYS_EXTRACT_UTC Function
      2. The EXTRACT Function and The TIMESTAMP Datatype
      3. EXTRACT and The TIMESTAMP With Time Zone Datatype
      4. The DATE_EXAMPLE Table
    29. Conversion Functions
    30. DATETIME Expression
    31. Lab 4.3 Exercises
      1. 4.3.1. Use Oracle's Timestamp and Time Zone Datatypes
    32. Lab 4.3 Exercise Answers
      1. 4.3.1. Answers
        1. Conversion Between Oracle Date Datatypes
        2. The TZ_OFFSET Function
        3. Avoid This Common Error
    33. Lab 4.3 Self-Review Questions
    34. Lab 4.4 Performing Calculations With The Interval Datatypes
    35. The INTERVAL Datatypes
      1. Using INTERVALS
      2. EXTRACT and INTERVALS
      3. INTERVAL Expression
    36. Determining OVERLAPS
    37. Lab 4.4 Exercises
      1. 4.4.1. Understand the Functionality of the Interval Datatypes
    38. Lab 4.4 Exercise Answers
      1. 4.4.1. Answers
    39. Lab 4.4 Self-Review Questions
    40. Lab 4.5 Converting From One Datatype To Another
    41. DAtatype Conversion
    42. The CAST Function
      1. CAST Versus Oracle's Conversion Functions
    43. Formatting Data
    44. Lab 4.5 Exercises
      1. 4.5.1. Convert between Different Datatypes
      2. 4.5.2. Format Data
    45. Lab 4.5 Exercise Answers
      1. 4.5.1. Answers
      2. 4.5.2. Answers
    46. Lab 4.5 Self-Review Questions
    47. Chapter 4 Test Your Thinking
  12. 5. Aggregate Functions, GROUP BY, and HAVING
    1. LAB 5.1 AGGREGATE FUNCTIONS
    2. The COUNT Function
      1. COUNT and NULLS
      2. COUNT and DISTINCT
    3. The SUM Function
    4. The AVG Function
    5. The MIN and MAX Functions
      1. MIN and MAX With Other Datatypes
    6. AGGREGATE Functions and NULLS
    7. AGGREGATE Functions and CASE
    8. AGGREGATE Function Syntax
    9. Lab 5.1 Exercises
      1. 5.1.1. Use Aggregate Functions in a SQL Statement
    10. Lab 5.1 Exercise Answers
      1. 5.1.1. Answers
    11. Lab 5.1 Self-Review Questions
    12. Lab 5.2 The GROUP BY and HAVING Clauses
    13. The GROUP BY Clause
      1. Grouping By Multiple Columns
    14. Oracle Error Ora-00979
    15. Sorting Data
    16. The HAVING Clause
    17. The WHERE and HAVING Clauses
      1. Multiple Conditions In The HAVING Clause
      2. Constants and Functions Without Parameters
      3. Order Of The Clauses
    18. Nesting Aggregate Functions
    19. Taking Aggregate Functions And Groups To The Next Level
    20. Lab 5.2 Exercises
      1. 5.2.1. Use the GROUP BY and HAVING Clauses
    21. Lab 5.2 Exercise Answers
      1. 5.2.1. Answers
        1. NULLS and The GROUP BY Clause
        2. A Common Error You Can Avoid
    22. Lab 5.2 Self-Review Questions
    23. Chapter 5 Test Your Thinking
  13. 6. Equijoins
    1. Lab 6.1 The Two-Table Join
    2. Steps To Formulate The SQL Statement
    3. Table Alias
    4. Narrowing Down Your Result Set
    5. NULLS and JOINS
    6. ANSI JOIN Syntax
      1. The INNER JOIN
        1. The USING Condition
        2. The ON Condition
        3. Additional WHERE Clause Conditions
      2. The NATURAL JOIN
    7. Cartesian Product
      1. The ANSI Standard Cross-Join
    8. Lab 6.1 Exercises
      1. 6.1.1. Write Simple Join Constructs
      2. 6.1.2. Narrow Down Your Result Set
      3. 6.1.3. Understand the Cartesian Product
    9. Lab 6.1 Exercise Answers
      1. 6.1.1. Answers
      2. 6.1.2. Answers
      3. 6.1.3. Answers
        1. Joining Along The Primary/Foreign Key Path
    10. Lab 6.1 Self-Review Questions
    11. Lab 6.2 Joining Three Or More Tables
    12. Three Or More Table Joins
    13. ANSI JOIN Syntax For Three And More Table Joins
    14. Multicolumn Joins
      1. Expressing Multicolumn Joins Using The ANSI JOIN Syntax
    15. Joining Across Many Tables
    16. The ANSI JOIN VErsus The Traditional JOIN Syntax
    17. Different Types Of JOINS
    18. Lab 6.2 Exercises
      1. 6.2.1. Join Three or More Tables
      2. 6.2.2. Join with Multicolumn Join Criteria
    19. Lab 6.2 Exercise Answers
      1. 6.2.1. Answers
      2. 6.2.2. Answers
        1. Skipping The Primary/Foreign Key Path
    20. Lab 6.2 Self-Review Questions
    21. Chapter 6 Test Your Thinking
  14. 7. Subqueries
    1. Lab 7.1 Simple Subqueries
    2. Scalar Subqueries
    3. Subqueries Returning Multiple Rows
    4. Nesting Multiple Subqueries
    5. Subqueries and JOINS
    6. Subqueries Returning Multiple Columns
    7. SUBQUERIES and NULLS
    8. ORDER BY Clause In Subqueries
    9. Lab 7.1 Exercises
      1. 7.1.1. Write Subqueries in the WHERE and HAVING Clauses
      2. 7.1.2. Write Subqueries Returning Multiple Rows
      3. 7.1.3. Write Subqueries Returning Multiple Columns
    10. Lab 7.1 Exercise Answers
      1. 7.1.1. Answers
      2. 7.1.2. Answers
      3. 7.1.3. Answers
    11. Lab 7.1 Self-Review Questions
    12. Lab 7.2 Correlated Subqueries
    13. Correlated Subqueries
      1. Steps Performed By The Correlated Subquery
        1. Step 1: Select A Row From The Outer Query
        2. Step 2: Determine The Value Of The Correlated Column(s)
        3. Step 3: Execute The Inner Query
        4. Step 4: Evaluate The Condition
        5. Step 5: Repeat Steps 2 Through 4 For Each Subsequent Row Of The Outer Query
    14. The EXISTS Operator
    15. The NOT EXISTS Operator
    16. NOT EXISTS Versus NOT IN
      1. Using NOT EXISTS
      2. Using NOT IN
    17. Avoiding Incorrect Results Through The Use Of Subqueries
    18. Unnesting Of Queries
    19. Subquery Performance Considerations
    20. Lab 7.2 Exercises
      1. 7.2.1. Write Correlated Subqueries
      2. 7.2.2. Write Correlated Subqueries Using the EXISTS and NOT EXISTS Operators
    21. Lab 7.2 Exercise Answers
      1. 7.2.1. Answers
      2. 7.2.2. Answers
        1. Solution 1: Correlated Subquery
        2. Solution 2: Equijoin
        3. Solution 3: In Subquery
        4. Solution 4: Another Correlated Subquery
    22. Lab 7.2 Self-Review Questions
    23. Lab 7.3 Inline Views And Scalar Subquery Expressions
    24. Inline Views
      1. Top-N Query
      2. Practical Uses Of Inline Views
    25. Scalar Subquery Expressions
      1. Scalar Subquery Expression In The SELECT Clause
      2. Scalar Subquery Expression In The WHERE Clause
      3. Scalar Subquery Expression In The ORDER BY Clause
      4. Scalar Subquery Expression And The Case Expression
      5. Scalar Subquery Expressions And Functions
      6. Errors In Scalar Subquery Expressions
      7. Performance Considerations
    26. Lab 7.3 Exercises
      1. 7.3.1. Write Inline Views and Scalar Subquery Expressions
    27. Lab 7.3 Exercise Answers
      1. 7.3.1. Answers
    28. Lab 7.3 Self-Review Questions
    29. LAB 7.4 ANY, SOME, and ALL Operators In Subqueries
    30. ANY and SOME
    31. ALL
    32. Lab 7.4 Exercises
      1. 7.4.1. Use the ANY, SOME, and ALL Operators in Subqueries
    33. Lab 7.4 Exercise Answers
      1. 7.4.1. Answers
    34. Lab 7.4 Self-Review Questions
    35. Chapter 7 Test Your Thinking
  15. 8. Set Operators
    1. Lab 8.1 The Power Of UNION and UNION ALL
    2. ORDER BY and SET Operations
    3. Lab 8.1 Exercises
      1. 8.1.1. Use the UNION and UNION ALL Set Operators
    4. Lab 8.1 Exercise Answers
      1. 8.1.1. Answers
        1. Controlling The Sort Order
        2. Datatype Conversions And Nulls
    5. Lab 8.1 Self-Review Questions
    6. Lab 8.2 The MINUS And INTERSECT SET Operators
    7. The MINUS Operator
    8. The INTERSECT Operator
      1. INTERSECT Instead Of EQUIJOINS
    9. Execution Order Of Set Operations
    10. Compare Two Tables
    11. Lab 8.2 Exercises
      1. 8.2.1. Use the MINUS Set Operator
      2. 8.2.2. Use the INTERSECT Set Operator
    12. Lab 8.2 Exercise Answers
      1. 8.2.1. Answers
      2. 8.2.2. Answers
    13. Lab 8.2 Self-Review Questions
    14. Chapter 8: Test Your Thinking
  16. 9. Complex Joins
    1. Lab 9.1 Outer JOINS
    2. Missing Rows?
    3. The ANSI OUTER JOIN
    4. The Oracle OUTER JOIN Operator (+)
    5. The OUTER JOIN and The UNION ALL Operator
    6. FULL OUTER JOIN
      1. ANSI FULL OUTER JOIN
      2. FULL OUTER JOIN Using The UNION Operator
    7. Lab 9.1 Exercises
      1. 9.1.1. Write Outer Joins with Two Tables
      2. 9.1.2. Write Outer Joins with Three Tables
    8. Lab 9.1 Exercise Answers
      1. 9.1.1. Answers
    9. Oracle OUTER JOIN Operator Restrictions
      1. WHERE Conditions and The Oracle OUTER JOIN Operator
      2. WHERE Conditions and The ANSI OUTER JOINS
        1. Using Inline Views and Outer Joins
      3. Alternative Solution With A Scalar Subquery
      4. 9.1.2. Answers
    10. Lab 9.1 Self-Review Questions
    11. Lab 9.2 SELF-JOINS
    12. The NON-EQUIJOIN
    13. Lab 9.2 Exercises
      1. 9.2.1. Write Self-Joins and Detect Data Inconsistencies
    14. Lab 9.2 Exercise Answers
      1. 9.2.1. Answers
    15. Lab 9.2 Self-Review Questions
    16. Chapter 9 Test Your Thinking
  17. 10. INSERT, UPDATE, and DELETE
    1. Lab 10.1 Creating Data And Transaction Control
    2. Inserting Data
      1. Inserting An Individual Row
      2. Inserting Dates And Times
      3. Rounding Of Numbers
      4. Inserting A Floating Point Number
      5. INSERTS and SCALAR Subqueries
      6. Inserting Multiple Rows
    3. Inserting Into Multiple Tables
      1. The Unconditional Insert All
      2. The Conditional Insert All
      3. The Conditional INSERT FIRST
      4. The Pivoting INSERT ALL
    4. Transaction Control
      1. COMMIT
        1. What is a session?
      2. ROLLBACK
      3. Example Of A Transaction
      4. SAVEPOINT
        1. Example Of A Savepoint
      5. Controlling Transactions
      6. Statement-Level Rollback
    5. Lab 10.1 Exercises
      1. 10.1.1. Insert Data, Rollback and Commit Transactions
    6. Lab 10.1 Exercise Answers
      1. 10.1.1. Answers
        1. USING SPECIAL CHARACTERS IN SQL STATEMENTS
          1. The Ampersand (&)
          2. The Single Quote (')
        2. LOCKING OF ROWS THROUGH ABNORMAL TERMINATION
    7. Lab 10.1 Self-Review Questions
    8. Lab 10.2 Updating And Deleting Data
    9. Updating Data
    10. Updating Columns To Null Values
    11. Column Default Value
    12. Updates And The Case Expression
    13. Subqueries And The UPDATE Command
      1. Subqueries Returning Null Values
      2. Subqueries Returning Multiple Values
      3. Updates And Correlated Subqueries
      4. Avoid This Common Scenario With Correlated Subqueries
      5. Updates And Subqueries Returning Multiple Columns
    14. MERGE: COMBINING INSERTS, UPDATES, and DELETES
    15. Deleting Data
    16. Referential Integrity and The DELETE Command
      1. Deletes And Referential Integrity In Action
      2. The Schema Diagram
    17. The TRUNCATE Command
    18. LOCKING
      1. The Lost Update Problem
      2. Locking Of Rows bY DDL Operations
    19. Read-Consistency Of Data
      1. What Is A Rollback Segment Or The Undo Tablespace?
      2. The System Change Numbers (SCN) And Multi-Versioning
      3. Flashback Queries
      4. Statement-Level Flashback
        1. Determining the data changes with a minus operation
      5. Retrieving Flashback History With The Versions Parameter
      6. Flashback Transaction Query
      7. Flashback Table And Flashback Database
    20. Performance Considerations When Writing DML Statements
    21. Lab 10.2 Exercises
      1. 10.2.1. Update Data
      2. 10.2.2. Delete Data
    22. Lab 10.2 Exercise Answers
      1. 10.2.1. Answers
        1. Updates To Multiple Tables
        2. Uniquely Identifying Records
      2. 10.2.2. Answers
    23. Lab 10.2 Self-Review Questions
    24. Chapter 10 Test Your Thinking
  18. 11. CREATE, ALTER, and DROP Tables
    1. Lab 11.1 Creating And Dropping Tables
    2. Creating Tables
      1. Table Names
      2. Column Names
    3. Commonly Used Oracle Datatypes
      1. Character Data
      2. Numeric Data
      3. DATE and TIME
      4. Binary Data And Large Object Data Types
    4. Integrity Constraints
    5. The Primary Key Constraint
    6. The Unique Constraint
    7. The Foreign Key Constraint
      1. Deletes And The Foreign Key
      2. Recursive Relationship
    8. The Check Constraint
    9. The Not Null Check Constraint
    10. The Default Column Option
    11. Naming Constraints
    12. Table-Level And Column-Level Constraints
    13. What Are Business Rules?
      1. What Is A Database Trigger?
      2. Where To Enforce Business Rules?
    14. Creating Tables Based On Other Tables
      1. Renaming Tables
      2. Dropping Tables
      3. Flashback A Table
      4. Purging The Recycle Bin
    15. Truncate Table Versus Drop Table
    16. STORAGE Clause
      1. Estimating The Table Size
        1. Storage Clause
      2. Partitioning Of Tables
      3. Data Compression
    17. Oracle's Other Table Types
      1. Temporary Tables
        1. Creating Temporary Tables
      2. Index-Organized Tables
      3. External Tables
    18. Lab 11.1 Exercises
      1. 11.1.1. Create and Drop Tables
      2. 11.1.2. Create Constraints
    19. Lab 11.1 Exercise Answers
      1. 11.1.1. Answers
        1. Documenting The Tables And Columns
      2. 11.1.2. Answers
    20. Lab 11.1 Self-Review Questions
    21. Lab 11.2 Altering Tables And Manipulating Constraints
    22. Adding Columns
    23. Dropping Columns
    24. Renaming Columns
    25. Modifying Columns
    26. ADDING, DROPPING, DISABLING, and ENABLING Constraints
      1. Adding Constraints
        1. Foreign Key
        2. Self-Referencing Foreign Key
        3. Unique Index
        4. Check Constraints
      2. Dropping Constraints
      3. Renaming Constraints
      4. Disabling And Enabling Constraints
        1. NOVALIDATE Option
    27. Determine Which Rows Violate Constraints
      1. Foreign Key Constraint Violations
      2. Primary Key Constraint Violations
    28. Writing Complex Check Constraints
    29. REstrictions On Check Constraints
    30. DML and DDL Operations
    31. Online Table Definition
    32. Oracle Enterprise Manager (OEM)
    33. Lab 11.2 Exercises
      1. 11.2.1. Alter Tables and Manipulate Constraints
    34. Lab 11.2 Exercise Answers
      1. 11.2.1. Answers
        1. Setting Columns To The Default Values
        2. Modifying Or Removing Column Default Values
        3. Defining A Column As NULL Versus NOT NULL
        4. Changing A Column's Datatype
        5. Increasing And Decreasing The Column Width
    35. Lab 11.2 Self-Review Questions
    36. Chapter 11 Test Your Thinking
  19. 12. Views, Indexes, and Sequences
    1. LAB 12.1 CREATING AND MODIFYING VIEWS
    2. Purpose Of Views
    3. Creating A View
      1. Using Column Aliases
    4. Altering A View
    5. Renaming A View
    6. Dropping A View
    7. Lab 12.1 Exercises
      1. 12.1.1. Create, Alter, and Drop Views
      2. 12.1.2. Understand the Data Manipulation Rules for Views
    8. Lab 12.1 Exercise Answers
      1. 12.1.1. Answers
        1. View Constraints
        2. Compile A View
        3. Referencing An Invalid View
        4. Forcing The Creation Of A View
      2. 12.1.2. Answers
        1. Data Manipulation Rules On Views
        2. JOIN VIEWS and Data Manipulation
    9. Lab 12.1 Self-Review Questions
    10. Lab 12.2 Indexes
    11. The B-Tree Index
      1. Steps Performed To Search For Values In A B-Tree Index
      2. The Rowid Pseudocolumn
    12. Create An Index
    13. Composite Indexes
    14. Nulls And Indexes
    15. Functions And Indexes
    16. Indexes And Tablespaces
    17. Unique Index Versus Unique Constraint
      1. Creating An Index Associated With A Constraint
    18. Indexes And Foreign Keys
    19. Drop An Index
    20. Bitmapped Index
    21. Bitmap Join Index
    22. Guidelines When To Index
    23. Altering An Index
    24. Loading Large Amounts Of Data
    25. Lab 12.2 Exercises
      1. 12.2.1. Create B-Tree Indexes
      2. 12.2.2. Understand When Indexes Are Useful
    26. Lab 12.2 Exercise Answers
      1. 12.2.1. Answers
        1. SOLUTION 1:
        2. SOLUTION 2:
        3. Composite Indexes Versus Individual Indexes
      2. For Example
      3. 12.2.2. Answers
    27. Lab 12.2 Self-Review Questions
    28. Lab 12.3 Sequences
    29. Create A Sequence
    30. Using Sequence Numbers
    31. Altering A Sequence
    32. Rename A Sequence
    33. Usage Of Sequence Values
    34. Lab 12.3 Exercises
      1. 12.3.1. Create and Use Sequences
    35. Lab 12.3 Exercise Answers
      1. 12.3.1. Answers
        1. Automating Sequence Numer Creation With Triggers
    36. Lab 12.3 Self-Review Questions
    37. Chapter 12 Test Your Thinking
  20. 13. The Data Dictionary and Advanced SQL*PLUS Commands
    1. Lab 13.1 The Oracle Data Dictionary Views
    2. The Static Data Dictionary Views
    3. The Dynamic Data Dictionary Views
    4. The Dictionary
    5. Retrieving DDL ABout Schema Objects
    6. Lab 13.1 Exercises
      1. 13.1.1. Query the Data Dictionary
    7. Lab 13.1 Exercise Answers
      1. 13.1.1. Answers
        1. Object Dependencies
        2. OTher Constraint Types
        3. Distinguish Not Null Constraints From Check Constraints
    8. Lab 13.1 Self-Review Questions
    9. Lab 13.2 Advanced SQL*PLUS Commands
    10. SQL*PLUS Substitution Variables
      1. Suppressing The Use Of Substitution Variables
      2. Predefined SQL*PLUS Variables
    11. Generate Dynamic SQL
    12. Lab 13.2 Exercises
      1. 13.2.1. Write Interactive SQL Statements
      2. 13.2.2. Use Advanced Scription Capabilities in SQL*Plus
    13. Lab 13.2 Exercise Answers
      1. 13.2.1. Answers
      2. 13.2.2. Answers
      3. Common SQL*PLUS Commands In SQL*PLUS Scripts
      4. Documenting Your Script
        1. Using Quotes In SQL
    14. Lab 13.2 Self-Review Questions
    15. Chapter 13 Test Your Thinking
  21. 14. Security
    1. Lab 14.1 Users, Privileges, Roles, And Synonyms
    2. What Is A Schema?
    3. Special Users: System And Sys
    4. Creating Users
    5. Changing The Password And Altering The User Settings
      1. Changing The Password Within SQL*PLUS or iSQL*PLUS
      2. Operating System Authentication
      3. Locked Accounts
    6. Dropping Users
    7. Login and Logout of SQL*PLUS
      1. What Are Privileges?
      2. System Privileges
      3. Object Privileges
    8. The GRANT Command
    9. Granting Privileges On Columns
    10. Roles
    11. Ability To Extend The Privileges To Others
    12. The REVOKE Command
    13. Referring To Objects In Other Schemas
    14. Private Synonyms
    15. Public Synonyms
    16. Drop and Rename Synonyms
    17. Resolving Schema References
    18. User-Defined Roles
    19. PROFILE
    20. Security Implementation
    21. Connecting With Special Privileges: SYSOPER and SYSDBA
    22. Starting Up And Shutting Down A Database
      1. The Nolog Argument
    23. Lab 14.1 Exercises
      1. 14.1.1. Create Users and Grant and Revoke Privileges
      2. 14.1.2. Create and Use Synonyms
      3. 14.1.3. Create User-Defined Roles
    24. Lab 14.1 Exercise Answers
      1. 14.1.1. Answers
      2. 14.1.2. Answers
      3. 14.1.3. Answers
    25. Lab 14.1 Self-Review Questions
    26. Chapter 14 Test Your Thinking
  22. 15. Regular Expressions and Hierarchical Queries
    1. LAB 15.1 REGULAR EXPRESSIONS
    2. What Is A Regular Expression?
    3. Practical Uses Of Regular Expressions
    4. Regular Expressions And The Oracle Database
    5. The Match Any and Anchoring Metacharacters
    6. Exploring Quantifiers
    7. The POSIX Character Classes
    8. Character Lists
    9. Negation Of Character Lists
    10. Subexpressions And Alternate Matches
    11. The REGEXP_LIKE Operator
    12. The REGEXP_SUBSTR Function
    13. The REGEXP_INSTR Function
    14. The REGEXP_REPLACE Function
    15. Exploring The MATCH PARAMETER Option
      1. Case-Sensitive Matches
      2. Matching A Pattern That Crosses Multiple Lines
      3. Treating A String As A Multiline Source
      4. Combining Match Parameters
    16. Backreferences
    17. Word Boundaries
    18. The Backslash Character
    19. Applying Regular Expressions In Data Validation
    20. Understanding Matching Mechanics
    21. Comparing Regular Expressions To Existing Functionality
    22. Why Should You Use Regular Expressions?
    23. Regular Expression Resources
    24. Lab 15.1 Exercises
      1. 15.1.1. Use Regular Expression Functionality within the Oracle Database
    25. Lab 15.1 Exercise Answers
      1. 15.1.1. Answers
    26. Lab 15.1 Self-Review Questions
    27. Lab 15.2 Hierarchical Queries
    28. The Connect By Clause And The Prior Operator
    29. The Start With Clause
    30. Understanding Level And Lpad
    31. Hierarchy Path
    32. Pruning The Hierarchy Tree
    33. Accessing Root Row Data With The CONNECT_BY_ROOT Operator
    34. The CONNECT_BY_ISLEAF Pseudocolumn
    35. Joining Tables
    36. Sorting
    37. Lab 15.2 Exercises
      1. 15.2.1. Restrict the Result Set in Hierarchical Queries
      2. 15.2.2. .Move Up and Down the Hierarchy Tree
    38. Lab 15.2 Exercise Answers
      1. 15.2.1. Answers
      2. 15.2.2. Answers
    39. Lab 15.2 Self-Review Questions
    40. Chapter 15 Test Your Thinking
  23. 16. Exploring Data Warehousing Features
    1. Lab 16.1 Advanced SQL Concepts, Analytical Functions, and the WITH Clause
    2. Transpose Results
      1. Using The DECODE Function
      2. Using CASE
      3. Using A Scalar Subquery
    3. Analytical Functions
      1. Query Processing With Analytical Functions
      2. Analytical Function Types
      3. Ranking Functions
      4. RANK, DENSE_RANK, and ROW_NUMBER
      5. Partitioning The Result
      6. NTILE
      7. Hypothetical Ranking
      8. FIRST/LAST Functions
      9. MEDIAN
      10. STATS_MODE
      11. Reporting Functionality
      12. RATIO_TO_REPORT
      13. WINDOWING
      14. Logical and Physical Windows
      15. The ORDER BY Clause
      16. Intervals and Logical Window
      17. LAG/LEAD Functions
      18. Advantages Of Analytical Functions
    4. The WITH Clause
    5. Interrow Calculations
    6. Creating Your Own Custom Function
    7. Lab 16.1 Exercises
      1. 16.1.1. Transpose a Result Set
      2. 16.1.2. Utilize Analytical Functions and the WITH Clause
    8. Lab 16.1 Exercise Answers
      1. 16.1.1. Answers
      2. 16.1.2. Answers
        1. Bottom-n Ranking
    9. Lab 16.1 Self-Review Questions
    10. Lab 16.2 ROLLUP and CUBE Operators
    11. The ROLLUP Operator
    12. The CUBE Operator
    13. Determining The ROLLUP and CUBE Combinations
      1. Partial CUBE and ROLLUP Results
    14. Grouping Sets
      1. Combine Grouping Sets
    15. The GROUPING Function
    16. The GROUPING_ID Function
      1. GROUP_ID Function
    17. Lab 16.2 Exercises
      1. 16.2.1. Use the ROLLUP, CUBE, GROUPING, and GROUPING SETS Capabilities
    18. Lab 16.2 Exercise Answers
      1. 16.2.1. Answers
    19. Lab 16.2 Self-Review Questions
    20. Chapter 16 Test Your Thinking
  24. 17. SQL Optimization
    1. Lab 17.1: The Oracle Optimizer and Writing Effective SQL Statements
    2. SQL Statement Processing
    3. The Optimizer
    4. Choosing The Optimizer Mode
    5. Keeping Statistics Up To Date In Oracle
    6. Manual Gathering Of Statistics
      1. The DBMS_STATS Package
      2. Exact Statistics or Sample Size
    7. Managing Statistics
    8. Timing The Execution Of A Statement
    9. The Execution Plan
    10. Retrieving The Execution Plan
      1. DBMS_XPLAN
    11. Understanding Cost, Rows, And Bytes Values
    12. Hints
    13. Incorrectly Specifying Hints
    14. Join Types
      1. Nested Loop Join
      2. Sort-Merge Join
      3. Hash Join
    15. Bind Variables and The Optimizer
    16. Histograms
    17. SQL Performance Improvement Tips
    18. The SQL Tuning Advisor
      1. SQL Profile
    19. The SQL Access Advisor
    20. Lab 17.1 Exercises
      1. 17.11.2. Read the Execution plan
      2. 17.1.2. Understand Join Operations and Alternate SQL Statements
    21. Lab 17.1 Exercise Answers
      1. 17.1.1. Answers
      2. 17.1.2. Answers
    22. Lab 17.1 Self-Review Questions
    23. Chapter 17 Test Your Thinking
  25. A. Answers To Self-Review Questions
    1. Chapter 1
      1. Lab 1.1 Data, Databases, and the Definition of SQL
      2. Lab 1.2 Table Relationships
      3. Lab 1.3 The STUDENT Schema Diagram
    2. Chapter 2
      1. Lab 2.1 The SQL*Plus Environment
      2. Lab 2.2 The Anatomy of a SELECT Statement
      3. Lab 2.3 Editing a SQL Statement
      4. Lab 2.4 The WHERE Clause: Comparison and Logical Operators
      5. Lab 2.5 The ORDER BY Clause
    3. Chapter 3
      1. Lab 3.1 Character Functions
      2. Lab 3.2 Number Functions
      3. Lab 3.3 Miscellaneous Single-Row Functions
    4. Chapter 4
      1. Lab 4.1 Applying Oracle's Date Format Models
      2. Lab 4.2 Performing Date and Time Math
      3. Lab 4.3 Understanding Timestamp and Time Zone Datatypes
      4. Lab 4.4 Performing Calculations with the Interval Datatypes
      5. Lab 4.5 Converting from One Datatype to Another
    5. Chapter 5
      1. Lab 5.1 Aggregate Functions
      2. Lab 5.2 The GROUP BY and HAVING Clauses
    6. Chapter 6
      1. Lab 6.1 The Two-Table Join
      2. Lab 6.2 Joining Three or More Tables
    7. Chapter 7
      1. Lab 7.1 Simple Subqueries
      2. Lab 7.2 Correlated Subqueries
      3. Lab 7.3 Inline Views and Scalar Subquery Expressions
      4. Lab 7.4 ANY, SOME, and ALL Operators in Subqueries
    8. Chapter 8
      1. Lab 8.1 The Power of UNION and UNION ALL
      2. Lab 8.2 The MINUS and INTERSECT Set Operators
    9. Chapter 9
      1. Lab 9.1 Outer Joins
      2. Lab 9.2 Self-Joins
    10. Chapter 10
      1. Lab 10.1 Creating Data and Transaction Course
      2. Lab 10.2 Updating and Deleting Data
    11. Chapter 11
      1. Lab 11.1 Creating and Dropping Tables
      2. Lab 11.2 Altering Tables and Manipulating Constraints
    12. Chapter 12
      1. Lab 12.1 Creating and Modifying Views
      2. Lab 12.2 Indexes
      3. Lab 12.3 Sequences
    13. Chapter 13
      1. Lab 13.1 The Oracle Data Dictionary Views
      2. Lab 13.2 Advanced SQL*Plus Commands
    14. Chapter 14
      1. Lab 14.1 Users, Privileges, Roles, and Synonyms
    15. Chapter 15
      1. Lab 15.1 Regular Expressions
      2. Lab 15.2 Hierarchical Queries
    16. Chapter 16
      1. Lab 16.1 Advanced SQL Concepts, Analytical Functions, and the WITH Clause
      2. Lab 16.2 ROLLUP and CUBE Operators
    17. Chapter 17
      1. Lab 17.1 The Oracle Optimizer and Writing Effective SQL Statements
  26. B. SQL Formatting Guide
    1. CASE
    2. Formatting SQL Code
      1. IN Queries
      2. IN DML Statements
      3. IN DDL Statements
    3. Comments
  27. C. SQL*PLUS Command Reference
    1. Unsupported SQL*PLUS Commands In iSQL*PLUS
    2. Formatting Output In SQL*PLUS
      1. Formatting The Query Result
      2. Changing The Column Heading
    3. SQL*PLUS Line Editor Editing Commands
    4. Using The SQL*PLUS Line Editor To Save And Retrieve Files
    5. Using An Editor To Create A Command Script
    6. Using An Editor That Is External To SQL*PLUS
    7. Changing The Default Editor
    8. SQL*PLUS Commands
  28. D. Student Database Schema
  29. E. Table and Column Descriptions
  30. F. Additional Example Tables
    1. Table And Column Descriptions
      1. Chapter 3: Character, Number, And Miscellaneous Functions
      2. Chapter 4: Date And Conversion Functions
      3. Chapter 9: Complex Joins
      4. Chapter 10: INSERT, UPDATE, and DELETE
      5. Chapter 15: Regular Expressions And Hierarchical Queries
      6. Chapter 16: Exploring Data Warehousing Features
  31. G. Navigating Through The Oracle Documentation
    1. Why Do You Need To Read This Appendix?
    2. Where Do You Find The Oracle Documentation?
    3. Jumpstart Your Search
    4. Oracle Documentation Titles
    5. Error Messages
    6. Reading Oracle Syntax Diagrams
  32. H. Resources
    1. Useful Oracle-Related Web Sites
    2. Oracle Newsgroups
    3. Oracle's Own Web Sites
    4. Alternative SQL*PLUS Software Tools
    5. Database Design Software
    6. User Groups
    7. Oracle-Related Publications
    8. Academic Resources
    9. Books
      1. Bibliography
  33. I. Oracle Datatypes