Oracle Training Oracle Support Development Oracle Apps

 E-mail Us
 Oracle Articles
New Oracle Articles

 Oracle Training
 Oracle Tips

 Oracle Forum
 Class Catalog

 Remote DBA
 Oracle Tuning
 Emergency 911
 RAC Support
 Apps Support
 Oracle Support

 SQL Tuning

 Oracle UNIX
 Oracle Linux
 Remote s
 Remote plans
 Application Server

 Oracle Forms
 Oracle Portal
 App Upgrades
 SQL Server
 Oracle Concepts
 Software Support

 Remote S


 Consulting Staff
 Consulting Prices
 Help Wanted!


 Oracle Posters
 Oracle Books

 Oracle Scripts

Don Burleson Blog 







Oracle SQL Programming and Tuning

Also see our

Oracle SQL Tuning Training Class and Advanced SQL training

An intensive Oracle SQL training course
? 2007-2016 by Burleson Corporation


This course is taught at your Company site with up to 20 students. 

Click here for on-site course prices

Optional supplemental mentoring


Key Features

* Understand Oracle.

* Explore Oracle data access.

* Learn DDL and DML.

* Use PL/SQL.

* Understand Oracle Report writing.




Course Description

Introduction to SQL and PL/SQL is an intensive course that is designed to give the student maximum exposure to Oracle SQL.  The student learns by doing, and this class has dozens of in-class exercises and the student will be guided from very simple SQL commands to increasingly complex PL/SQL coding techniques.

The topics start with the basics of SQL and progress into increasingly complex queries, including table joins, subqueries and creating Oracle views.  The PL/SQL section begins with simple concepts and the student gradually masters PL/SQL through increasingly challenging classroom exercises.

Book Required

  Easy Oracle SQL

John Garmany

ISBN 0-9727513-7-8



This course is designed for practicing Oracle professionals who have basic experience with SQL and the use of a relational database. Prior experience with Oracle is not required, but experience using SQL with a relational database is highly desirable.

Curriculum Design

This course was designed by Donald K. Burleson, an acknowledged leader in Oracle SQL tuning.  Burleson was chosen by Oracle Press to write the authorized edition of Oracle High-Performance SQL tuning.  Burleson Corporation instructors offer decades of real world DBA experience in Oracle features, and they will share their Oracle secrets in this intense Oracle SQL tuning training

Learning Objectives

By the end of this course the student will be able to formulate advanced SQL queries including correlated subqueries and outer joins. The student will also become familiar with the internals of Oracle SQL and will be able to use the EXPLAIN PLAN utility to tune SQL statements.

Oracle SQL Programming & Tuning

(c)  2007-2016 by Donald K. Burleson

Day 1 – Introduction and overview of Oracle

This is an introduction to Oracle relational database and the Oracle architecture. We discuss Oracle data storage architecture, Oracle table and indexes, and database access methods.

1-1: The Oracle Relational Database

• The relational model
• Oracle overview
• One-to-many relationships
• Many-to-many relationships
• Recursive many-to-many relationships

1-2: Inside the Oracle Architecture

• SGA regions
• Background processes
• Disk architectures

1-3: Data storage

• Disk storage
• Solid state disks
• RAM caches
• Flat-file data storage (BFILE, external tables)
• Spreadsheet file access
1-4: Introduction to Oracle Tables

• Standard tables
• Index organized tables
• External tables

1-5: Introduction to Oracle Indexes

• B-tree indexes
• Bitmap indexes
• Bitmap join indexes
• Star indexes

1-6: Database access methods

• SQL*Plus
• SQL Worksheet
• Procedural programs

Day 2 – Introduction to Oracle Data Access

This is an introduction to Oracle SQL and SQL*Plus.

2-1: SQL Processing

• Syntax checking
• Semantic processing
• SQL parsing
• Creating the executable
• Inside the library cache

2-2: Oracle data dictionary

• The X$ fixed tables
• The DBA Views
• The v$ views
• Running dictionary scripts

2-3: SQL*Plus

• Entering SQL*Plus
• Using the desc command
• Using the spool command

2-4: Syntax of SQL

• Select, project and join
• WHERE clause
• ORDER BY clause
• GROUP BY clause
• In-line views
• SQL in SELECT clause (Oracle9i new feature)

2-5: Report generation with SQL*Plus

• Entering SQL*Plus
• Running an SQL command
• Building and formatting an SQL command
• Formatting a SQL report
• Spooling a report

2-6: Views

• Logical encapsulation of complex queries
• Using the DBA_VIEWS view
• Problems combining views

Day 3 – Introduction to DDL and DML

This is an introduction to Oracle schema definition and data updating.

3-1: SQL schema navigation

• Schema entity types
• Relationships between schema entities

3-2: Schema components

• Data files
• Tablespaces
• Oracle objects (tables, indexes, IOT, etc.)

3-3: Referential Integrity

• Implementing Data Integrity
• Types of RI – not null, unique check, foreign key
• Rules for using RI
• System-named constraints (SYSnnn)
• Viewing Constraint Information with dba_constraints

3-4: DDL Introduction

• Create a database
• Create a table
• Create an index
• Altering schema objects

3-5: DML Introduction

• Inserts
• Updates
• Deletes
• RI constraints (restrict, cascade)
• Snapshot too old

3-6: Read consistency, locking & DML

• Why read consistency?
• UNDO segments and read consistency

Day 4 – PL/SQL

4-1: Introduction to the PL/SQL Language

• Interpreted language
• Syntax examples
• Procedures, packages and functions

4-2: PL/SQL Language structures

• Looping within PL/SQL
• If testing (if then else syntax)
• Exception conditions
• Array processing

4-3: SQL with PL/SQL

• Using a cursor
• Fetching from a cursor

4-4: PL/SQL Packages and Procedures

4-5: Extending SQL with a user-defined PL/SQL function

4-6: Oracle supplied DBMS packages

Day 5 – Oracle Report Writing

This is an introduction to Oracle reporting tools and techniques. Your will learn how to use the SQL*Plus formatting commands to create easy reports from Oracle, how to join multiple tables together and how to easily aggregate, summarize and analyze Oracle table data. You also learn how Oracle access the tables and indexes and see how to ensure that your SQL uses the fastest methods for accessing your data.

5-1: Formatting commands in SQL*Plus

5-2: Analytical functions in SQL

5-3: Table joins

• Equi-join
• Outer join
• Hiding joins by creating views
• Subqueries
• Exercise – write a subquery
• Correlated subquery
• Non-correlated subqueries

5-4: Subqueries in SQL

• IN and NOT In operators
• Sub-queries
• EXISTS clause

5-5: Aggregation in SQL

• Between operator
• Using wildcards in queries (LIKE operator)
• Count(*)
• Sum
• Avg
• Min and max
• Using the group by clause

5-6: Materialized Views for reports 


This is a BC Oracle SQL training course (c) 2002-2016

We also provide PLSQL optimization and PL/SQL optimization consulting services.

Burleson is the American Team

Note: This Oracle documentation was created as a support and Oracle training reference for use by our DBA performance tuning consulting professionals.  Feel free to ask questions on our Oracle forum.

Verify experience! Anyone considering using the services of an Oracle support expert should independently investigate their credentials and experience, and not rely on advertisements and self-proclaimed expertise. All legitimate Oracle experts publish their Oracle qualifications.

Errata?  Oracle technology is changing and we strive to update our BC Oracle support information.  If you find an error or have a suggestion for improving our content, we would appreciate your feedback.  Just  e-mail:  and include the URL for the page.


Burleson Consulting

The Oracle of Database Support

Oracle Performance Tuning

Remote DBA Services


Copyright © 1996 -  2016

All rights reserved by Burleson

Oracle ® is the registered trademark of Oracle Corporation.