Oracle Fundamentals

5 Day Course
Hands On

Book Now - 1 Delivery Method Available:

Classroom Virtual Classroom Private Group - Virtual Self-Paced Online


This course is designed to give delegates practical experience in writing SQL statements and scripts using Oracle SQL. The basic SELECT statement, the use of SQL functions, SQL*Plus parameters and the basic table and view handling statements are introduced.

The course also provides practical experience in using Oracle's PL/SQL programming language to implement conditional execution, loop control, cursor handling and exception handling.

The delegate will practise:

  • Using SQL*Plus and SQL Developer
  • Creating SQL statements to query database tables
  • Using standard aggregate functions and related SELECT statement clauses
  • Implementing extended SQL functions
  • Using SQL*Plus run time parameters
  • Creating and altering tables and views
  • Inserting, updating and deleting rows in database tables
  • Retrieving information from the data dictionary
  • Writing PL/SQL programs
  • Declaring and using variables and constants
  • Using the IF... THEN ... ELSE statement
  • Implementing CASE expressions
  • Implementing basic, WHILE and FOR loop constructs
  • Handling implicit and explicit cursors
  • Implementing the cursor FOR loop
  • Handling system raised exceptions
  • Defining and raising user defined exceptions
  • Declaring and using record types and PL/SQL tables

Training Partners

We work with the following best of breed training partners using our bulk buying power to bring you a wider range of dates, locations and prices.


Hide all

Relational Database Concepts (6 topics)

  • What is an Oracle Database
  • Relational Database Structures
  • Tables, Rows and Columns
  • Indexes, Primary Keys and Foreign Keys
  • Supported Datatypes
  • The Data Dictionary

Using SQL*Plus (5 topics)

  • What is SQL*Plus
  • Getting Started
  • Entering and Executing SQL Statements
  • Editing SQL Statements
  • Creating, Editing and Executing SQL Files

Using SQL Developer (5 topics)

  • What is Oracle SQL Developer
  • Starting SQL Developer
  • Configure a Connection
  • Navigation Tabs
  • SQL Worksheet

Retrieving Data With The Select Statement (9 topics)

  • The SELECT Statement
  • The SELECT and FROM Clauses
  • Conditions and the WHERE Clause
  • Other Conditional Operators
  • Logical Operators
  • The ORDER BY Clause
  • Column Aliases
  • Arithmetic Expressions
  • Precedence of Operators

Aggregate Functions (3 topics)

  • Overview of Built In Aggregate Functions
  • The GROUP BY Clause
  • The HAVING Clause

Joining Tables (7 topics)

  • Overview of Table Joins
  • Inner Joins
  • Table Aliases
  • Outer Joins
  • Self Joins
  • ANSI Standard Joins
  • Set Operators

Numeric, Character and Date Functions (6 topics)

  • Function Types
  • Using the Table dual to try out Functions
  • Numeric Functions
  • Character Functions
  • String Concatenation
  • Date Arithmetic and Date Functions

Conversion and Miscellaneous Functions (5 topics)

  • Conversion Functions
  • The NVL and NVL2 Functions
  • The DECODE Function
  • CASE Expressions
  • The COALESCE and NULLIF Functions

SQL*Plus Parameters (3 topics)

  • Command Line Substitution Parameters
  • The Accept Command
  • The Define and Undefine Commands

Using Subqueries (13 topics)

  • Overview of Subqueries
  • Use a Subquery as an Alternative to Join
  • Handle Multiple Records in Subqueries
  • Subquery in a Having Clause
  • Anti-Join
  • In-Line Views
  • Top-N Queries
  • Complex Subqueries
  • Multi Column Subqueries
  • Correlated Subqueries
  • Subquery Rules
  • Combining Unrelated Aggregates
  • Using the ANY, ALL and SOME Operators

Managing Data (9 topics)

  • Inserting Rows
  • Updating Rows
  • Deleting Rows
  • Verifying Updates
  • Transaction Control
  • Commit and Rollback
  • Savepoints
  • Commits and Constraints
  • Amending Data in SQL Developer

Managing Tables (5 topics)

  • Creating Tables
  • Specifying Constraints
  • Altering Tables, Columns and Constraints
  • Dropping Tables, Columns and Constraints
  • Copying Tables

Managing Indexes and Views (6 topics)

  • Creating Indexes
  • Dropping Indexes
  • Listing Indexes
  • Creating and Using Views
  • Dropping Views
  • Listing Views

Managing Sequences and Synonyms (4 topics)

  • Create a Sequence
  • View Sequence Details
  • Create a Synonym
  • List Synonyms

PL/SQL Fundamentals (7 topics)

  • What is PL/SQL?
  • Basic Elements
  • Variables and Constants
  • Data Types
  • Initialising Variables and Assigning Values
  • Using SQL Statements in Code
  • Generating Output to SQL or SQL Developer

Program Logic (7 topics)

  • IF THEN ELSIF ELSE Statements
  • CASE Statements
  • The Basic Loop Construct
  • WHILE and FOR Loops
  • Nested and Labelled Loops
  • The GOTO Statement
  • The CONTINUE Statement

Using Cursors (9 topics)

  • What is a Cursor?
  • Implicit and Explicit Cursors
  • Cursor Operations
  • Declaring, Opening and Closing Cursors
  • Fetching Rows
  • Status Checking
  • Using Cursors FOR UPDATE
  • The Cursor FOR Loop
  • Parameterised Cursors

Exceptions and Nested Blocks (10 topics)

  • The EXCEPTION Section
  • Types of Exception
  • Handling Named System-Raised Exceptions
  • Handling Un-named System-Raised Exceptions
  • User-Declared Exceptions and Application Errors
  • Nested and Labelled Blocks
  • Propagation of Exceptions
  • Scope of Variables and Cursors
  • Scope of Goto Statements

PL/SQL Records and Index-by Tables (6 topics)

  • Declaring Record Types
  • Handling PL/SQL Records
  • Nested Records
  • Declaring PL/SQL Index-By Tables or Associative Arrays
  • PL/SQL Table Built-in Functions
  • Manipulating PL/SQL Tables or Associative Arrays


An understanding of databases and exposure to information technology in general would be useful

Course PDF


Share this Course


Recommend this Course