Date Functions

Conversion Functions

Chapter Summary

Two-Minute Drill

Fill-in-the-Blank Questions

Chapter Questions

Fill-in-the-Blank Answers

Answers to Chapter Questions

2 Advanced Data Selection in Oracle

Displaying Data from Multiple Tables

select Statements That Join Data from More Than One Table

Creating Outer Joins

Joining a Table to Itself

Group Functions and Their Uses

Identifying and Using Group Functions

Using the group by Clause

Excluding group Data with having

Using Subqueries

Understanding and Defining Subqueries

Listing and Writing Different Types of Subqueries

Multiple-Column Subqueries

Writing Multiple-Column Subqueries

NULL Values and Subqueries

Subqueries in a from Clause

Producing Readable Output with SQL*Plus

Entering Variables

Customizing SQL*Plus Environments

Producing Readable Output

Creating and Executing Scripts

Saving Customizations

Chapter Summary

Two-Minute Drill

Fill-in-the-Blank Questions

Chapter Questions

Answers to Chapter Questions

Fill-in-the-Blank Answers

3 Creating Oracle Database Objects

Creating the Tables of an Oracle Database

Describing Tables

Creating Tables

Datatypes and Column Definitions

Altering Table Definitions

Dropping, Renaming, and Truncating Tables

Including Constraints

Describing Constraints

Creating and Maintaining Constraints

The Oracle Data Dictionary

Available Dictionary Views

Querying the Data Dictionary

Manipulating Oracle Data

Adding New Rows to a Table

Making Changes to Existing Row Data

Deleting Data from the Oracle Database

The Importance of Transaction Control

Chapter Summary

Two-Minute Drill

Fill-in-the-Blank Questions

Chapter Questions

Fill-in-the-Blank Answers

Answers to Chapter Questions

4 Creating Other Database Objects in Oracle

Creating Views

Creating Simple Views

Creating Views That Enforce Constraints

Creating Complex Views

Modifying and Removing Views

Other Database Objects

Overview of Other Database Objects

Using Sequences

Using Indexes

Using Public and Private Synonyms

User Access Control

Creating Users

Granting and Revoking Object Privileges

Using Roles to Manage Database Access

Chapter Summary

Two-Minute Drill

Fill-in-the-Blank Questions

Chapter Questions

Answers to Chapter Questions

Fill-in-the-Blank Answers

5 Introducing PL/SQL

Overview of PL/SQL

Benefits of PL/SQL

Parts of a PL/SQL Block

Declaring and Using Variables

Executing PL/SQL Code

Writing Executable Statements

Writing Executable PL/SQL Statements

Nesting PL/SQl Blocks

Executing and Testing PL/SQL

Using Code Conventions

Writing Control Structures

Constructing an if-then Statement

Using Loops

Using Logic Tables

Using Nested Loops and Labels

Two-Minute Drill

Chapter Summary

Fill-in-the-Blanks

Chapter Questions

Fill-in-the-Blank Answers

Answers to Chapter Questions

6 Database Programming with PL/SQL

Interacting with the Oracle Database

Using select in PL/SQL Code

Declaring PL/SQL Variable Types Dynamically

Writing DML Statements in PL/SQL

Transaction Processing in PL/SQL

Determining SQL Statement Outcome

Working with Composite Datatypes

Creating PL/SQL Records

Using %rowtype to Create Records

Creating PL/SQL Tables

Creating PL/SQL Tables of Records

Reviewing PL/SQL Records, Tables, and Tables of Records

Using Explicit Cursors

Explicit Cursor Handling

Using PL/SQL Records to Fetch Data from Cursors

Writing cursor for Loops

Understanding Advanced Explicit Cursor Concepts

Passing Parameters to Explicit Cursors

Using the for update and where current of Clauses

Using Subqueries in Cursors

Error Handling

Defining a PL/SQL Exception

Trapping Unhandled and Unanticipated Errors

Customizing PL/SQL Error Messages

Understanding Exception Propagation in Nested Blocks

Chapter Summary

Two-Minute Drill

Fill-in-the-Blanks

Chapter Questions

Answers to Chapter Questions

Fill-in-the-Blank Answers

PARTⅡOCP Oracle8i DBA Practice Exams

7 OCP Exam I: Introduction to SQL and PL/SQL

Practice Exam 1

Practice Exam 2

Practice Exam 3

Answers to Practice Exam 1

Answers to Practice Exam 2

Answers to Practice Exam 3

Index