Back to Search Results

Training Course Summary:

After an overview of relational principles and concepts, participants will learn how to use both SQL and PL/SQL. SQL is the industry-standard non-procedural language used to store, retrieve, and manipulate data, and to create and maintain database objects. Oracle has extended the SQL for extra functionality. PL/SQL is Oracle’s block-structured procedural extension to SQL. Participants will learn how to create PL/SQL blocks of code, how to use explicit cursors and handle runtime errors.

Pre-Requisites:

Computer literacy

Suggested:

Some 3GL programming experience or awareness of programming constructs and techniques.

Training Course Overview/Content:

Introduction to Relational Principles,
Concepts and the Oracle Server
Writing a Basic SELECT Statement
The basic query block
Using arithmetic expressions
Overview of SQL*Plus and its commands

Restricting and Sorting Data Functions
Character, number, and date functions
Conversion functions
Nesting functions
Group functions

Data from Multiple Tables
Cartesian products
Equi-joins
Nonequi-joins
Self joins
Outer joins

Nested Subqueries
Single-row and multi-row subqueries
Multi-column subqueries
Subquery in a FROM clause

Producing Readable Output with SQL*Plus
Substitution variables
Customise the SQL*Plus environment
Format output
Create and execute script files
Save customisations

Manipulating Data
INSERT, UPDATE and DELETE
Transaction processing and read consistency
Locking

Creating and Managing Tables
Oracle data structures
CREATE command
Oracle7 datatypes and Oracle8 datatypes
ALTER, DROP, RENAME and TRUNCATE tables
Data integrity constraints
Creating Views
CREATE command
Retrieve data via a view
Using views for DML
DROP views
Inline Views
Alter the definition of a view

Other Database Objects
CREATE, maintain and use sequences
CREATE and maintain indexes
CREATE synonyms

Controlling User Access
Users, roles, and privileges
GRANT and REVOKE object privileges
CREATE roles and GRANT privileges to roles

PL/SQL Blocks and Declaring Variables
Basic PL/SQL block
Declare variables, constants and bind variables
Execute a PL/SQL block

Writing Executable Statements
The executable section
Nested blocks

Interacting with Oracle Server
SQL in PL/SQL blocks
Control transactions in PL/SQL
Writing Control Structures
Conditional control
Loops
Nested loops and labels

Working with Composite Datatypes
User-defined PL/SQL records
PL/SQL tables
Writing Explicit Cursors
Implicit and explicit cursors
Cursor FOR loops

Advanced Explicit Cursor Concepts
Cursors with parameters
FOR UPDATE clause in cursors
Use of PL/SQL table variable and PL/SQL table of Records

Handling Exceptions
PL/SQL exceptions
Exception propagation in nested blocks
Customise PL/SQL exception messages
Optional

SQL workshop
Additional practices




Course Objectives

Understand relational principles Perform complex queries and format the output Set up and maintain Oracle database structures Understand the control of user access to the database Design and use PL/SQL Syntax Use PL/SQL records and tables Use explicit cursors Handle runtime errors