Package Includes:
  • 8 Sessions featuring live instructor-led classroom sessions with full audio, video and demonstration components with over 14 hours of instruction by Jerry Richard
  • Printable courseware
  • Step-by-step hands-on-labs
  • Exclusive LearningZone Live Mentor (Value at $195) Help whenever you need it! Exclusive LearningZone - Why wait for email support? Chat Live with our Certified Instructors anytime around the clock (24x7)
  • Focused on practical solutions to real-world development problems
  • Free 1 Year Upgrade Policy
OURexpertText
Jerry Richard

Jerry Richard - Mr. Richard has been working with Oracle since 1984. At that time he served as a programmer/analyst for the current version of Oracle, which in 1984 was Version 6. He is experience and has utilized all Oracle Versions and iterations from Version 6 to Oracle 10g. Among, these assets he also has experience with SQL, SQL Writer and SQL Reports.

Mr. Richard is a OCA (Oracle Certified Administrator) DBA (Database Administrator). While at Texas Instruments he also used other types of databases, including but not exclusive to, DB2, SQL Server, and Sybase. Interestingly, he played an integral part in performing conversions between many of the the existing databases. In terms of Operating Systems he has served as an administrator in both a UNIX and a SUN environment, however his OS experience also extends to IBM Mainframe and Microsoft Windows.

Course Features:
Course Outline TXT
Course Introduction
Course Introduction
Introduction to Oracle & SQL*PLUS
Introduction to Oracle & SQL*PLUS
Section Objectives
Basic Components of the SGA
Logging Into SQL*PLUS
A Simple Select
Demo - Correcting an Incorrect SQL
Oracle File Extensions
Saving A SQL Statement To A File
Demo - Running Scripts
CRT Files and Spool Files
Oracle SQL*PLUS Editing Commands
Demo - Using the Oracle Editor
SQL Scripts
Dictionary Tables For Users
Demo - Oracle Data Dictionary Views
Future Directions
Programmer Responsibilities
Benefits of Database Development
Introduction to Lab 1
Lab - Running Oracle & Building Tables
SQL*PLUS Commands
Programming Commands
Demo - Creating Variables in SQL*PLUS
Programming Commands Cont.
Reporting Commands
Demo - Report Commands in SQL*PLUS
More Programming Commands
Demo - Compute Statements with Break
Other Commands
SQL*Plus Enhancements in Oracle 10g
Append, Create and Replace Extensions
Show Recyclebin
Introduction to Lab 2
Lab - Commands Using SQL*Plus
SQL*Plus Commands
Chapter 1 Review
Structured Query Language
Structured Query Language
SQL Components
SQL Data Manipulation Language (DML)
SQL Basic Retrieval
Demo - Basic SQL Commands
Retrieval of Computed Values
Demo - SQL Retrieval
Order By Clause
Ordering By Descending
The Distinct Operand
Selecting On Conditions
Multiple Conditions
The Between Clause
The Like Clause
Demo - Showing Like Clause
Negative Conditions
Built In Functions
Group By Clause
The Having Clause
Demo - Review of Having Clause
The Analytical Commands
Cube
Retrieval Involving Null
Treatment of Nulls
Null Values Are Considered In:
Case Expression
Introduction to Lab 3
Lab - More About Selects
Demo - Review of Exercises
Chapter 2 Review
Advanced Structured Query Language
Advanced Structured Query Language
Advanced DML Functions
Qualified Names
Correlation Variables
Advanced DML Functions Cont.
Demo - Oracle's Natural and Outer Joins
ANSI/ISO Join Statements
Oracle Join Statements
Multiple Table Join
More about Aliases
Demo - Joins
Subqueries
Demo - Single & Multi-Value Subqueries
Multivalued Subqueries
The Any Parameter
In-Line Views
The Exists Parameter
Demo - How Exists Help Performance
Set Operators (Conjunctions)
Union
Demo - Unions
Set Operator Intersect
The Minus Conjunction
The Model Clause
Modify Functions
Update a Single Row
Complex Update
Merging Data In Oracle Tables
SQL Delete
Introduction to Lab 4
Lab - Select Rows from Multiple Tables
Chapter 3 Review
Non ANSI SQL Statements
Non ANSI SQL Statements
Oracle SQL Plus Commands
Number Formats
Numeric Functions
Character
Date Formats
Demo - Date Formats and Usage
Date Formats
The Date Functions
Demo - Date Functions
Syntax Operators & Parameter Subs.
Demo - Identify How Users Input Data
The Conversion Functions
The Sample Clause
Introduction to Lab 5
Lab - SQL*Plus Commands for Reporting
Chapter 4 Review
Data Definition Language Commands
Data Definition Language Commands
Base Tables
DDL Commands
Logical Definition - Tables
Create Table Mystaff
Demo - Create a Table
Logical Definition – System Datatypes
Column Data Types
Creating Table and
Creating Check Constraints
Demo - Creating Constraints
Creating Unique Constraints
Creating Referential Integrity Constraints
Demo - Create Primary and Foreign Keys
Non-Unique Indexes
Composite Indexes
Rebuilding Indexes
Bitmap Indexes
Creating Reverse Key Indexes
Demo - Creating Reverse Key Indexes
Index-Organized Tables
IOT Row-Overflow Strategy
Index Before or After Loading Data
Demo - Altering Tables after Creation
Synonyms
Deleting Catalog Items
Views
Column Names
Join Views
Views of Views
View Retrieval Queries
Introduction to Lab 6
Lab - Creating Tables and Indexes
Chapter 5 Review
PL/SQL Programming
PL/SQL Programming
Overview of PL/SQL
The PL/SQL Block Structure
PL/SQL In The Oracle RDBMS
PL/SQL Variables
Referencing Variables
Using Composite Variables
Demo - Creating Composite Variables
Using Composite Variables Cont.
PL/SQL Records Processing
Assigning Values To Variables
Demo - Local and Global Variables
Assigning Values To Variables Cont.
Defining Composite Variables - Tables
Datatype Conversions
Input and Output Values For Variables
Demo - Creating Global Variables
Using The Select Clause
PL/SQL Insert Statements
PL/SQL Update Processing
PL/SQL Delete Processing
Decision Statements!
Use CASE Statements in PL/SQL
CASE Statement and Expression
CASE_NOT_FOUND Exception
PL/SQL Iterative Processing
Developing A Continue Statement
PL/SQL Iterative Processing Cont.
PL/SQL Exceptions
Error Processing
PL/SQL Exceptions Cont.
Demo - Exception Handling
PL/SQL Error Processing
Demo - Exception Block Processing
Raising User-Defined Exceptions
Demo - User-Defined Exceptions
PL/SQL Error Processing Cont.
Propagating Exceptions
PL/SQL Cursor Processing
Declaring The Cursor
Cursor Attributes With Explicit Cursors
Demo - How CURSORS are Handled
Cursor Attributes With Explicit Cursors
Demo - Cursor Looping
Update The Current Row
Demo - Update Processing via Cursors
Storing Rows In a PL/SQL Table
Passing in the Declare Cursor Command
PL/SQL Subprograms
PL/SQL Subprograms Viewing Code
Demo - Creating Procedures
PL/SQL Subprograms Rebuilding
Procedures
PL/SQL Stored Procedures
Returning Values
Demo - Parameters
Returning Values
The In/Out Argument in a Procedure
Demo - OUTPUT Parameters
Displaying Parameters
Executing a Stored Procedure in SQL
Creating Functions
Create Functions in a Declarative Section
Database Packages
A Sample Package
Reading/Writing Operating System Files
Reading Operating System
Introduction to Lab 7
Lab - Creating Procedures & Functions
Introduction to Lab 8
Lab - Creating Packages
Chapter 6 Review
Course Closure
Course Closure