Oracle SQL and PL/SQL Fast-Track

Oracle SQL and PL/SQL Fast Track

Audience

This course is perfect for those who wish to gain a comprehensive understanding of how the Oracle Structured Query Language (SQL) is used for data retrieval and reporting. It goes on to provide an in-depth look at PL/SQL in order to write Stored Procedures, Stored Functions and Stored Packages.

This course is suitable for all recent versions of Oracle including 12c, 18c, and 19c.

Prerequisites

Delegates should be computer literate with a good knowledge of the standard editor available on their target hardware platform.

Duration

5 days. Hands on.

Course Objectives

This course introduces delegates to the concepts of relational databases and provides an in-depth look at SQL in order to retrieve and manipulate data from an Oracle database.

This course provides the perfect prerequisite knowledge to embark on training for developer components such as Application Express or administrative roles such as Oracle Database Administration.

On completion of the course, delegates will be able to:

  • Utilise the SQL language to retrieve data from an Oracle database
  • Use the SQL Developer tool
  • Edit, store and recall queries, format results, and create standard queries and reports
  • Write PL/SQL Control Structures
  • Edit, store & recall PL/SQL blocks
  • Create Procedures, Functions and Packages

Course Content

Introduction to RDBMS and Oracle
Relational database theory
Overview of oracle
ISO (incorporating ANSI and BSI) standards
Oracle architecture
Oracle background processes
SQL Developer

Basic SQL
Filtering by rows and columns
Filtering for distinct or unique values
Use of the Between, Like & In operators
Handling Null values
Filtering with a compound Where clause
Sorting results
Perform a calculation
Use of column alias
Deriving values
Data types
Substitution variables

Joins
Inner joins (Pre and Post SQL99)
Outer joins (Pre and Post SQL99)
Combining inner and outer joins

Functions
Conversion functions
Date functions
Character functions
Numeric functions
Aggregate Summary functions
Group By clause
Having.clause

Sub-Queries
Nested sub-queries
Multi-column sub-queries
Correlated sub-queries
Exists clause
Inline views

Case Statements
Case statement examples
Decode vs. Case

Set Operators
Union
Intersect
Minus

Introduction to Data Management
Insert records
Insert via a Select clause
Multi-table inserts
Update records
Delete records
Transactions
Locking

Use of Oracle Data Dictionary
Introduction To Analytic Functions
Cube, rollup & grouping
Partitioned outer join
Analytic Summary Functions
Hierarchical queries

Introduction To Basic SQL Tuning
Overview Of Explain Plan Facility
Overview of Indexes

Programming Overview
Local variables
Database derived variables User defined records
User defined variables
Variable assignment
Conditional structures (IF and CASE)
Iteration structures (FOR LOOP and WHILE LOOP)

Exception Handling
User defined
Oracle predefined
Oracle defined
Nested blocks

Cursors
Cursor basics (OPEN FETCH CLOSE)
Cursor FOR LOOP
Cursor data modifications
Implicit cursors
Cursor variables (REF CURSOR)

Stored Procedures & Functions
Advantages
Structure
Input parameters
Output parameters
I/O parameters
Local procedures and functions
Compilation

Packages
Package structure
Package creation, definition and body

Triggers
Trigger roles
Trigger restrictions
Trigger execution
Trigger creation
Single event triggers
Multiple events triggers
View triggers
DDL Triggers
Database level triggers

Built-In Oracle Packages
Review of Oracle package philosophy
Use of an Oracle package within PLSQL
Common application development packages

Native Dynamic SQL (NDS)
DBMS_SQL versus NDS
NDS Commands
EXECUTE IMMEDIATE
OPEN FOR (CURSOR VARIABLES)
Using BIND variables with NDS

Virtual Courses

ALL of our courses can be delivered virtually. And our Bath public schedule of courses are now available as live virtual sessions, using the popular Zoom Virtual Classroom and remote labs. Delegates can test their access at: www.zoom.us/test

Public Courses

On-Site Courses

Can't attend one of our public classes? Booking for multiple people?

All our courses are available on your site! Delivered for your staff, at your premises.

Contact us to find out more...