Oracle SQL and PL/SQL Training Course

Maximize your learning with custom Oracle training, at your site by expert instructors.

Introduction to Oracle SQL and PL/SQL

This course is a 5 day course

$10k for up to 20 students. 

Required Courseware:

Easy Oracle SQL

John Garmany

ISBN= 0-9727513-7-8

Easy Oracle PL/SQL

John Garmany

ISBN= 0-9759135-7-3 

Description:

Introduction to SQL and PL/SQL is an intensive hands-on course that is designed to give the student maximum exposure to Oracle SQL and PL/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.

Prerequisites:

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 development:

This course was designed by John Garmany with a focus of getting started fast.  Garmany is a noted author of six Oracle books and a seasoned PL/SQL instructor.

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 Oracle9i SQL and will be able to use the EXPLAIN PLAN utility to tune SQL statements.  Topics also include SQL constructs, using SQL*Plus, advanced SQL operators and PL/SQL structures and tables.

Syllabus Topics:

1.       Review of Basic SQL syntax

2.       Restricting row returns (WHERE Clause)

3.       Complex Boolean logic in SQL

4.       ROWID restrictions

5.       Using SQL*Plus

6.       Creating basic reports

7.       Joining Oracle tables

8.       Using a cursor for a multi-row SQL query

9.       Iteration (loops) in PL/SQL

10.     Hiding joins by creating views

11.     Using IN, NOT IN, EXISTS and NOT EXISTS

12.     Subqueries

13.     Non-correlated subqueries

14.     Advanced SQL operators

15.     Aggregation in SQL

16.     Introduction to cost-based optimization

17.     Collecting table and index statistics

18.     Using column histograms

19.     SQL reusability within the library cache

20.     Using hints to improve SQL performance

21.     Using parallel query to improve performance

22.     Tuning sub-queries

23.     Basic PL/SQL Block and Sections

24.     Declaring Variables and Constants

25.     Executing a PL/SQL Block

26.     Error checking – exception handling

27.     Defining & populating PL/SQL tables

28.     Bulking in PL/SQL (forall)