Oracle8i SQL, PL_SQL, and SQL Plus Training Class

Lesson 1: Introduction to Oracle 8i: SQL and PL/SQL

Introduction to Structured Query Language
Overview of SQL commands
SQL*PLUS
PL/SQL

Lesson 2: Introduction to SQL*PLUS

Using SQL*PLUS Commands
Calling External Files
Executing SQL Statements
Creating Reports with SQL*PLUS

Lesson 3: Tables and Constraints

Creating and Modifying Tables
Integrity Constraints
Creating and Modifying Constraints

Lesson 4: More Database Objects

Creating Sequences
Creating Synonyms
Creating and managing Views
Accessing the Oracle Data Dictionary Views (either a separate Topic or a Concept within the previous topic)
Creating and Managing Indexes
Creating Roles and Privileges

Lesson 5: SQL Functions

Numeric Functions
Character Functions
Date Functions
Conversion Functions
Error Reporting Functions
Miscellaneous Functions

Lesson 6: Restricting and Sorting Data

Comparison Operators
Sorting Data
Aggregate Functions

Lesson 7: Working with Multiple Tables

Joins
Using a Select Statement without a Direct Match
Writing Simple Subqueries
Writing Multi-Column Subqueries
Performance Comparisons

Lesson 8: Creating a Simple PL/SQL Block

Building a PL/SQL Block
Declaration
Naming Convention
Scope & Visibility
Understanding Identifier
Creating Scalar Data Types
Expressions and Comparisons

Lesson 9: Manipulate Data with PL/SQL

Using Composite Variables
Writing SELECT, INSERT, UPDATE, and DELETE Statements
Using Commit and Rollback
Including Save Points in the Program

Lesson 10: PL/SQL Control Structures

Understanding the Conditional Constructs
Using Loops in PL/SQL
Using the Unconditional Branching Constructs within the PL/SQL

Lesson 11: Collection & Records

Defining and Declaring Collections
Initializing and Referencing Collections
Assigning and Comparing
Manipulating Collections
Collection Methods

Lesson 12: Oracle Cursors

Explicit & Implicit Cursors
Understanding the Cursor Processing
Steps to Build an Explicit Cursor
Cursor Attributes
Using a Cursor in a Loop

Lesson 13: Error Handling

Predefined Exceptions
User Defined Exceptions
Declaring Exceptions
Exception_init
Raising Exceptions
Unhandled Exceptions

Lesson 14: Subprograms

Creating Procedures and Functions
Declaring Subprograms
Parameters
Naming Notation
Overloading
Recursion

Lesson 15: Oracle Packages

Package Creation
Creating Package Body
Creating Package Specifications
Overloading Package Subprograms
Oracle Standard Packages

Lesson 16: Oracle Triggers

Types of Triggers
Building the Trigger Body
Enabling and Disabling Triggers
Understanding the Restrictions on Triggers
How to Fire a Trigger
Using Triggers to Audit