Oracle SQL,SQL*Plus & PL/SQL Course, Class Outline

 (5 days)
Version 9i

---

Prerequisites   Familiarity with data processing concepts and techniques. Ability to use a graphical user interface (GUI).

Duration    5 day(s)

---

SQL Data Definition Language

• Creating a Table Statement
• Select
• Not Null
• Default
• Alter & Drop
• Describe & Rename

Using the SQL*Plus Tool

• Interactive SQL With SQL*Plus
• Graphical SQL*Plus
• Exit or Quit
• Host or $
• Spool
• Start or @
• List, Save & Get
• Run or /
• Edit, Append, Change, Del & Input

SQL Data Manipulation Language

• About SQL DML
• Select & Insert
• Insert Using Select
• Delete & Update
• About Transactions
• Rollback, Commit & Savepoint
• Complex Table References

Introduction to SQL Queries

• Select
• Distinct
• Alias Names

Building a Where Clause

• Equality / Inequality
• Boolean
• Null and Between
• Is [Not] Null
• [Not] Between
• [Not] Like
• In

Ordering the Result Table

• About the Order by Clause
• Including Multiple Columns
• Specifying the Sort Sequence
• Null Values and Order By
• Sorting by Un-Selected Columns
• Referring to Column Aliases

Pseudo Columns and Functions

• The Pseudo Columns
• Rownum, Sysdate, User & UID
• The Dual Tables

Joining Tables

• Equi-Join
• Reflexive Join
• Non Key-Join
• Outer Join

Using Set Operators Upon Tables

• Set Operator Examples
• Union, Intersect & Minus
• Performing a Full Outer Join

About Summary Functions

Using Subqueries

• Standard and Correlated Subqueries
• Exists

Creating & Selecting Groups

• Group by Example
• Selecting Date From the Base Tables
• Having

Creating & Using Views

• Create View Statement
• Alter View
• Drop View

Indexes & Synonyms

• Creating Indexes
• Unique vs. Non-Unique Indexes
• About Synonyms

Understanding the Data Dictionary

• About the Data Dictionary
• Query DD: User_Tables, User_Tab_Columns, User_Objects

Build Simple SQL*Plus Reports

• Session Control With Set
• Format Column Output with Column
• Wrap, Trunc, Heading & Null
• Report Breaks & Subtotals with Compute

Language Features

• Why Use PL/SQL?
• Where PL/SQL Program Units May Be Deployed
• The Java Alternative to PL/SQL
• Structure of a PL/SQL Program Block
• PL/SQL From Interactive Tools
• Generating Output

Declare Clause

• Datatypes
• Declare Example
• Special Datatypes
• %Type Variable
• %Rowtype Variable
• Type Table

Begin Clause

• Data Manipulation
• Char and Varchar
• Numeric
• Date
• Boolean
• Comparison Operators
• Functions
• Date Calculations
• Selectinto
• Logic Control & Branching
• Goto
• Loop
• If-Then-Else
• Case

Exception

• Handling Exception
• Special Features
• System Defined Exceptions
• Using SQLCODE & SQLERRM
• Implicit Cursors
• User-Defined Ex_Ined
• Pragma Exception_Init

Explicit Cursors

• Understanding Cursors
• Explaining the Cursor Example
• Declare Cursor E
• Open Cursor Example
• Fetch Cursor Example
• Close Cursor Example
• Including Cursor Parameters
• Performing Updates From Cursors
• Using Forloop Cursors

Advanced

• Using Nested Blocks
• Using Declared Subprograms
• Display_Message() Procedure Example
• Get_Spouse_Name() Function Example

View outline in Word

OSQI9I

About this Location

This class will be held in our Lincoln, Rhode Island (RI) location. Our Lincoln site is only a few miles from Providence Rhode Island (RI) as well as other major cities in Rhode Island (Warwick, Johnston, Newport, RI), etc. If you need a overnight hotel accommodations, the Lincoln, Rhode Island (RI) Sleep Inn is just 2 miles away on George Washington Highway. In addition, the Lincoln Mall has many convenient places to eat. Many places deliver lunch to our Lincoln, Rhode Island (RI) facility.

---

Request a copy via mail

Student Comments:

I was really impressed with Online Consulting. The professionalism and quality of the couse was exceptional.
R.F.

Extremely knowledgeable instructor! Suggested follow-on courses, suggested types of reading material and books.
G.B.

This is my first time taking classes here. I feel that the training was excellent. I would recommend to my employer that we attend more classes here.
P.L.

Great class, great instructor!
A.A.