Oracle Training Oracle Support Development Oracle Apps

 
 Home
 E-mail Us
 Oracle Articles
New Oracle Articles


 Oracle Training
 Oracle Tips

 Oracle Forum
 Class Catalog


 Remote DBA
 Oracle Tuning
 Emergency 911
 RAC Support
 Apps Support
 Analysis
 Design
 Implementation
 Oracle Support


 SQL Tuning
 Security

 Oracle UNIX
 Oracle Linux
 Monitoring
 Remote s
upport
 Remote plans
 Remote
services
 Application Server

 Applications
 Oracle Forms
 Oracle Portal
 App Upgrades
 SQL Server
 Oracle Concepts
 Software Support

 Remote S
upport  
 Development  

 Implementation


 Consulting Staff
 Consulting Prices
 Help Wanted!

 


 Oracle Posters
 Oracle Books

 Oracle Scripts
 Ion
 Excel-DB  

Don Burleson Blog 


 

 

 


 

 

 

Introduction to Oracle SQL and PL/SQL

A five-day intensive Overview

© 2001-2016 by Burleson Corporation

 

This course is taught at your Company site with up to 20 students. 

Click here for on-site course prices

Optional supplemental mentoring

 

Key Features

* Learn SQL constructs.

* Use SQL*Plus.

* Understand advanced SQL operators.

* Learn PL/SQL structures.

* Understand PL/SQL tables.

  

Course Description

Introduction to SQL and PL/SQL is an intensive five day 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.

Book Required

  Easy Oracle SQL

John Garmany

ISBN 0-9727513-7-8
  Easy Oracle PL/SQL Programming:

John Garmany

ISBN 0-9759135-7-3

Audience

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 Design

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.



 

Introduction to Oracle SQL and PL/SQL

Syllabus

© 2001-2016 by Donald K. Burleson

Day 1:  Introduction to SQL & SQL*Plus

LESSON 1 – 1: Review of Basic SQL and history of SQL:

 

Intro to the class
History of SQL
Evolution of SQL

Exercise - declarative SQL

Review of pubs database

 

SQL constructs:

·         Select

·         Project

·         Join

Describing Oracle tables

Exercise – investigate the pubs database

Restricting row returns
Where clause

Exercise – write a sample query in the pubs database

Complex Boolean logic in SQL
ROWID restrictions

Exercise – write a sample query in the pubs database

LESSON 1-2: Entity/relation modeling

 

Types of data relationships
Data normalization

One-to-many relationships
One-to-many relationships
Many-to-many relationships
Recursive many-to-many relationships

 

LESSON 1-3: Optimizing Oracle SQL

 

Steps in SQL optimization
Parsing a SQL statement
First_rows vs all_rows optimization
Oracle optimizer overview
Management issues with system-wide optimization
Different modes of SQL optimization
Bi-modal databases
Rule-based optimization
Cost-based optimization
All rows optimization
First_rows optimization

Exercise – display and change optimizer_mode

 

SQL Semantic Analysis
Generating the execution plan
Using optimizer plan stability
Using the v$sql view 
Using the v$sql_plan view

Exercise – Query the library cache

 

LESSON 1 – 4: Using SQL*Plus

 Creating basic reports
Exercise – format a simple query in the pubs database

Using the set commands
Adjusting line output
Setting pagesize and linesize
Echo
Feedback
Verify
termout

Exercise – set the SQL*plus environment

Column wrapping
Exercise – add column statements

Creating breaks and summaries
Exercise – add breaks and summaries

Adding prompts to queries
Exercise – parameterize a SQL*Plus script

 

1-5: Explain plan and reading execution plans

 

•         Explain plan
•        
Oracle autotrace
•        
How to read an execution plan

 

1 – 6: altering SQL execution plans

Using hints 

DAY 2:    Joining Oracle tables

2-1 - Table joining internals

 

Sort-merge joins

Nested Loop joins

Hash joins

STAR joins

Bitmap joins

Exercise – Change table join techniques & evaluate performance

Equi-join
Exercise – write an equi-join

Outer join
Exercise – write an outer join

Hiding joins by creating views
Exercise – create a view of a join

 

LESSON 2 – 5 :Subqueries

  •  
  • Using IN, NOT IN, EXISTS and NOT EXISTS
  • Subqueries

Exercise – write a subquery

  • Correlated subquery
  • Non-correlated subqueries

Advanced SQL operators

  • Between operator
  • IN and NOT In operators
  • Sub-queries
  • EXISTS clause
  • Using wildcards in queries (LIKE operator)

SQL access methods

  • Review of Basic joining methods
  • Merge join
  • Hash Join
  • Nested Loop join
  • Advanced SQL operators
  • Between operator

 

DAY 3:  SQL Tuning

 

3-1 – DML and SQL Tuning

 

Writing and optimizing INSERT statements

Writing and optimizing DELETE statements

Writing and optimizing UPDATE statements

 

 

3-2 – Optimizer Statistics

 

Purpose of statistics

Types of statistics (table, column, system)

Histogram statistics

Dynamic sampling

using dbms_stats

Exporting/importing statistics

Statistics management

Exercise – gather system stats

 

 

 

1-6 – SQL Tuning and full-table scans

 

Basics of file I/O

Sequential reads vs. scattered reads

When full scans are best

RAM caching in the SGA

Automating table caching

Solid State Disks

Tracking full-scans over time with AWR

Exercise – Query v$sql

 

1 – 3: Aggregation IN sql

 

Aggregation in SQL

  • Count(*)
  • Sum
  • Avg
  • Min and max
  • Using the group by clause

 

 

DAY 4

PL/SQL Section

1. Basics of PL/SQL

  • PL/SQL architecture
  • PL/SQL and SQL*Plus
  • PL/SQL Basics
  • Introduction to PL/SQL
  • PL/SQL as a 4th generation language
  • Compiling vs. Interpreting
  • Declare statement
  • Exercise: Write “hello” world PL/SQL program

Using PL/SQL Variables
PL/SQL Constants
PL/SQL Datatypes
Error messages – user_errors and show errors
PL/SQL wrapper utility

 

2. PL/SQL structures

  • Simple blocks
  • Control structures
  • PL/SQL records
  • Recognizing the Basic PL/SQL Block and Its Sections
  • Describing the Significance of Variables in PL/SQL
  • Distinguishing Between PL/SQL and Non-PL/SQL Variables
  • Declaring Variables and Constants
  • Executing a PL/SQL Block

3. Error checking – exception handling

  • Defining exceptions
  • Using the when others clause
  • Ensuring complete error checking
  • Passing error messages to calling routine

4. Boolean logic in PL/SQL

  • Identifying the Uses and Types of Control Structures
  • Constructing an IF Statement
  • Constructing and Identifying Different Loop Statements
  • Controlling Block Flow Using Nested Loops and Labels
  • Using Logic Tables
  • If-then-else structure
  • Testing for numbers characters and Booleans

5. Cursors in PL/SQL

  • Cursor basics
  • Using a cursor for a multi-row SQL query

6. Iteration in PL/SQL

  • For loop
  • While loop

 

DAY 5

7. PL/SQL tables

  • Defining PL/SQL tables
  • Reasons to use PL/SQL tables
  • Populating a PL/SQL table
  • Retrieving from a PL/SQL table

8. Dynamic SQL in PL/SQL

  • Introduction to the dbms_sql package
  • Creating a dynamic SQL statement

9. Nested blocks in PL/SQL

  • Creating nested blocks
  • Understanding scope in nested blocks

10. Triggers in PL/SQL

  • Triggers and database events
  • Defining a trigger
  • Timing a trigger
  • Enabling and disabling a trigger

11. Stored procedures, functions and packages

  • Basics of stored procedures
  • Basics of functions
  • Basics of packages
  • Defining stored procedures & functions
  • Function and stored procedures prototypes
  • Passing arguments to functions and stored procedures
  • Recompiling functions and stored procedures
  • Pinning packages in the SGA with dbms_shared_pool.keep
  • Package forward declaration
  • Package dependency
  • Package overloading
  • Listing package information

12. Bulking in PL/SQL

  • Bulk queries
  • Bulk DML (forall statement)
  • Using cursor attributes
Analyzing impact of bilk operations

 

Please note that while the knowledge gained from this Oracle training may be valuable when preparing for Oracle certification exams, the content of this course is not for Oracle Certification, including the Certified Professional (OCP) or Oracle Certified Associate (OCA) programs.

Burleson is the American Team

Note: This Oracle documentation was created as a support and Oracle training reference for use by our DBA performance tuning consulting professionals.  Feel free to ask questions on our Oracle forum.

Verify experience! Anyone considering using the services of an Oracle support expert should independently investigate their credentials and experience, and not rely on advertisements and self-proclaimed expertise. All legitimate Oracle experts publish their Oracle qualifications.

Errata?  Oracle technology is changing and we strive to update our BC Oracle support information.  If you find an error or have a suggestion for improving our content, we would appreciate your feedback.  Just  e-mail:  and include the URL for the page.


                    









Burleson Consulting

The Oracle of Database Support

Oracle Performance Tuning

Remote DBA Services


 

Copyright © 1996 -  2016

All rights reserved by Burleson

Oracle ® is the registered trademark of Oracle Corporation.