Oracle8I DBA : Sql and Pl/Sql Certification Bible

Oracle8i™ DBA: SQL and PL/SQL Certification Bible brings you comprehensive coverage of the exam objectives, presented according to the Oracle certification guide. Because Oracle's exam scoring provides section scores according to the exact objectives, each exam objective is covered in its own chapter complete with self-assessment opportunities. Four appendices include a listing and description of the Oracle initialization parameters and other elements that will be tested on the exam. These appendices will provide information useful post-exam in real-work implementation of the Oracle systems.

Covers: Exam 1Z0-001

Contents

Part I: The Oracle SQL Language

Chapter 1: The Oracle Database
Overview of Database Concepts
Relational Database Management System (RDBMS)
Object Relational Database Management System (ORDBMS)
Database Objects
Tables
Columns and datatypes
Constraints
Sequences
Views
Indexes
Synonyms
User-defined datatypes
Program Units
The Oracle Data Dictionary
Assessment Questions
Scenario
Lab Exercise
Answers to Chapter Questions
Chapter Pre-Test
Assessment Questions
Scenario

Chapter 2: Retrieving Data Using Basic SQL Statements
A Quick SQL Overview
Data Definition Language statements
Data Control Language statements
Data Manipulation Language statements
The Basic SELECT Statement
Arithmetic operations
Ordering data in the SELECT statement
Limiting Rows Using the WHERE Clause
Using logical operators in WHERE clauses
Additional comparison operators in the WHERE clause
Including the ROWNUM pseudo-column in the WHERE clause
Assessment Questions
Scenarios
Lab Exercises
Answers to Chapter Questions
Chapter Pre-Test
Assessment Questions
Scenarios
Lab Exercises

Chapter 3: Using Single- and Multi-Row Functions
Single-Row Functions
Conversion functions
The TO_DATE function
Character functions
Number functions
Date functions.
Additional functions
Group/Aggregate Functions
Using the GROUP BY Clause
Using the HAVING Clause
Assessment Questions
Scenarios
Lab Exercises
Answers to Chapter Questions
Chapter Pre-Test
Assessment Questions
Scenarios
Lab Exercises

Chapter 4: Advanced SELECT Statements
Working with Joins
Working with equijoins
Cross-joins
Nonequijoins
Outer joins
Self-joins
Working with Subqueries
Working with basic subqueries
Working with inline views
Working with correlated subqueries
Working with SET Operators
Using the UNION operator
Using the UNION ALL operator
Using the INTERSECT operator
Using the MINUS operator
Using Hierarchical Queries
Using Substitution Variables
The ROWID pseudo-column
Assessment Questions
Scenarios
Lab Exercises
Answers to Chapter Questions
Chapter Pre-Test
Assessment Questions
Scenarios
Lab Exercises

Chapter 5: Adding, Updating, and Deleting Data
DML Statements: Inserting Data into Tables
Using the column list
Inserting values using additional language elements
DML Statements: Modifying Existing Data
DML Statements: Removing Data from Tables
Using subqueries in DML statements
How Oracle Processes DML Statements
Controlling Transactions
The ACID test
Transaction control statements
Controlling concurrent operations with locking
Assessment Questions
Scenarios
Lab Exercises
Answers to Chapter Questions
Chapter Pre-Test
Assessment Questions
Scenarios
Lab Exercises

Part II: Managing Database Objects

Chapter 6: The SQL*Plus Environment
The SQL Buffer
Defining Variables
Substitution variables
DEFINE
ACCEPT
Bind variables
SQL*Plus Commands
DESCRIBE
SAVE
EDIT
GET
START
@
SPOOL
EXIT
Customizing SQL*Plus with SET Commands
ARRAYSIZE
COLSEP
FEEDBACK
HEADING
LINESIZE
LONG
PAGESIZE
PAUSE
TERMOUT
Formatting Output with SQL*Plus
Headers and footers
COLUMN
BREAK
COMPUTE
Saving Environment Settings
Scripts
PRODUCT_USER_PROFILE
Assessment Questions
Scenarios
Lab Exercises
Answers to Chapter Questions
Chapter Pre-Test
Assessment Questions
Scenarios
Lab Exercises

Chapter 7: Creating and Managing Oracle Database Objects
The Ground Rules for Creating Objects
Data Definition Language (DDL)
Oracle naming conventions
Fully qualified object names
Creating and Managing Tables
The CREATE TABLE statement
The ALTER TABLE command
The DROP TABLE command
The TRUNCATE TABLE command
Documenting tables and columns
Data Integrity Using Constraints
Naming constraints
Defining constraints
A little more about constraint types
Managing constraints
Creating Other Database Objects
Views
Indexes
Sequences
Synonyms
Assessment Questions
Scenarios
Lab Exercises
Answers to Chapter Questions
Chapter Pre-Test
Assessment Questions
Scenarios
Lab Exercises

Chapter 8: Configuring Security in Oracle Databases
Users and Schemas
Creating and managing users
Granting and Administering User Privileges
System privileges
Object privileges
Roles
Creating and granting roles
Determining privileges and roles granted
Revoking roles
Assessment Questions
Scenarios
Lab Exercises
Answers to Chapter Questions
Chapter Pre-Test
Assessment Questions
Scenarios
Lab Exercises

Part III: Using PL/SQL

Chapter 9: Introduction to PL/SQL
Uses and Benefits of PL/SQL
Modularity
Variables
Control structures
Superior performance
Error handling
Support for SQL
Portability
The PL/SQL Engine and Statement Processing
Types of PL/SQL blocks
Block structure
Comments
Variables
Scalar variables
Bind variables
Composite datatypes
User-defined types
Reference types
Executing and Testing PL/SQL Blocks
Print command
DBMS_OUTPUT package
Assessment Questions
Scenarios
Lab Exercises
Answers to Chapter Questions
Chapter Pre-Test
Assessment Questions
Scenarios
Lab Exercises

Chapter 10: Controlling Program Execution in PL/SQL
Loops
Basic loop
WHILE loop
FOR loop
Nested loops and labels
Conditional Processing
IF . . . THEN
ELSE
ELSIF
Nested Blocks
Transaction Control
Assessment Questions
Scenarios
Lab Exercise
Answers to Chapter Questions
Chapter Pre-Test
Assessment Questions
Scenarios
Lab Exercise

Chapter 11: Interacting with the Database Using PL/SQL
SQL Statements
SELECT
INSERT
UPDATE
DELETE
Implicit cursors
Explicit Cursors
Declaring explicit cursors
Opening explicit cursors
Closing an explicit cursor
Fetching from explicit cursors
%ROWTYPE
The cursor FOR loop
Cursor variables
Cursors with subqueries
Cursor parameters
FOR UPDATE and WHERE CURRENT OF
Composite and Collection Datatypes
PL/SQL records
Index-by tables
Tables of records
Nested tables
VARRAYs
Collection methods
Assessment Questions
Scenarios
Lab Exercises
Answers to Chapter Questions
Chapter Pre-Test
Assessment Questions
Scenarios
Lab Exercises

Chapter 12: Handling Errors and Exceptions in PL/SQL
Types of Errors
Compile errors
Program logic errors
Runtime errors or exceptions
Exception Handling
Predefined exceptions
Non-predefined exceptions
User-defined exceptions
WHEN OTHERS Clause
Error Propagation
Coding Conventions
Assessment Questions
Scenarios
Lab Exercises
Answers to Chapter Questions
Chapter Pre-Test
Assessment Questions
Scenarios
Lab Exercises

Chapter 13: Introduction to Stored Programs
Subprograms
Procedures
Client-side procedures
Server-side procedures
Parameters
Nesting
Deleting Procedures
User-Defined Functions
Deleting Functions
Packages
Specification
Package body
Accessing programs and variables in packages
Removing Packages
Listing Package contents
Triggers
Triggering events
Statement-level triggers
Row-level triggers
Trigger predicates
Restrictions on triggers
Order of firing
INSTEAD OF triggers
Event triggers
Enabling and disabling triggers
Removing triggers
Data Dictionary Views
USER_SOURCE
USER_TRIGGERS
USER_OBJECTS
Assessment Questions
Scenarios
Lab Exercises
Answers to Chapter Questions
Chapter Pre-Test
Assessment Questions
Scenarios
Lab Exercises

Appendix A: What's on the CD-ROM?

Appendix B: Practice Exam

Appendix C: Objective Mapping

Appendix D: Exam Tips

Appendix E: Database Schema for Labs

Appendix F: Data Dictionary Views

Appendix G: Suggested Readings, Web Sites, and Other Resources