Course Description

In this 3-day Oracle SQL Programming Advanced training course, students will develop a deeper insight into relational database design and RDBMS operation, learn concepts and specific SQL syntax for extended Oracle® datatypes, learn analysis and tuning techniques to increase SQL performance, and master advanced features of Oracle SQL for large data sets and data warehouses.

While this course utilises the Oracle 11g release for practical purposes, it is highly suitable for users of earlier or more recent releases of the Oracle RDBMS.


Attendance at our Oracle SQL Programming Introduction training course, or equivalent experience.

Who is this course for?

For Application Developers, Database Administrators, System Administrators and users who write applications and procedures that access an Oracle database.

Further Training

After some further on-the-job programming experience using Oracle SQL at an advanced level, we recommend students consider attending our Oracle PL/SQL Programming Introduction training course to gain skills in the usage of the Oracle PL/SQL procedural programming language.

Course content

Database Design Concepts
Relational Databases
The Relational Model
Relational Operations
The Database Design Process
Second and Third Normal Forms
Other Normal Forms
Applications for Relational Databases
SQL Subqueries
Overview of Subqueries
Inline Views
Correlated Subqueries
EXISTS Clause vs. IN Clause
Quantified Comparisons: ANY and ALL
Scalar Subquery Expression
Subqueries and DML Statements
Inline Views
Top-N and Bottom-N analysis
CREATE TABLE and Subqueries
Hierarchical Queries
Hierarchical Data
Self Joins
Hierarchical Query
Hierarchical Pseudocolumns
Recursive Query Factoring
Object Types
Object-Oriented Programming
Oracle’s Object Relational Model
Creating Object Types
Querying Object Types
DML with Object Types
Object Methods
Object Views
Nested Tables
Times, Dates, and Strings
Datetime Fields
Dates and Timestamps
Date and Interval Literals
Date Arithmetic
Date Functions
Character Types
Session and Database Parameters
REGEXP Functions
Regular Expressions Supported by REGEXP
Applying REGEXP Functions
Temporary Tables
Undo and Redo
Temporary Tables Defined
Data Lifetime — Transaction vs. Session
Creating Temporary Tables
Managing Temporary Tables
Storage of Temporary Tables
Effects of DML and TRUNCATE
SQL Tuning Tools
Automated Statistics Gathering
The DBMS_STATS Package
SQL Tuning Advisor
SQL Tuning Sets
SQL Access Advisor
Retrieving Execution Plans
Interpreting Explain Plan Results
SQL Trace
SQL Tuning
Tuning Goals
The Optimizer
Optimizer Statistics
Identifying SQL to Tune
Optimizer Hints
Optimizer Goal Hints
Access Path Hints
Join Hints
Additional Hints
SQL Plan Management
B-tree and Composite Indexes
Reverse Key and Unique Indexes
Function-Based Indexes
Bitmap Indexes
Index-Organized Tables
Managing Indexes
Oracle Analytic Functions
Grouping Sets
Analytic Functions
Model Clauses
Data Warehouse Features
Partitioned Tables
Partitioning Methods
Partition Pruning and Partition-wise Joins
Bitmap Indexes
Materialized Views
Creating Materialized Views
Refreshing Materialized Views
The MERGE Statement
Multi-table INSERT Statements
Parallel Statements
Formatting Reports with SQL*Plus
Page Formatting
SQL*Plus Options for Formatting
Saving the Output
Data Extraction with SQL*Plus