Introduction to Oracle SQL and
PL/SQL
A
five-day intensive Overview
© 2001-2009
by Burleson Consulting
* Learn SQL
constructs.
* Use SQL*Plus.
* Understand advanced SQL
operators.
* Learn PL/SQL
structures.
* Understand PL/SQL
tables.
|
|
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.
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.
|
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-2009 by Donald K.
Burleson
Day
1
Introduction to SQL
constructs
- Review of
Basic SQL statements
Select
Project
Join
Describing Oracle tables
Exercise – investigate the
EMP database
Where clause
Exercise – write a sample
query in the emp database
Complex Boolean logic in SQL
ROWID restrictions
Exercise – write a sample
query in the emp database
Using
SQL*Plus
Exercise – format a simple
query in the emp database
Adjusting line output
Setting pagesize and
linesize
Echo
Feedback
Verify
termout
Exercise – set the SQL*plus
environment
Exercise – add column
statements
- Creating
breaks and summaries
Exercise – add breaks
and summaries
- Adding
prompts to queries
Exercise – parameterize a
SQL*Plus script
DAY
2
Joining
Oracle tables
Exercise – write an
equi-join
Exercise – write an outer
join
- Hiding joins
by creating views
Exercise – create a view of
a join
- 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)
Aggregation in SQL
- Count(*)
- Sum
- Avg
- Min and
max
- Using the
group by clause
SQL access
methods
- Review of
Basic joining methods
- Merge
join
- Hash
Join
- Nested Loop
join
- Advanced SQL
operators
- Between
operator
DAY 3
SQL
Tuning
- Introduction
to rule-based optimization
- Introduction
to cost-based optimization
- Collecting
table and index statistics
- Using column
histograms
- Changing the
default optimizer modes
- Using
TKPROF
- Using
SQL*Trace
- SQL
reusability within the library cache
- Table
high-water mark
- Table
striping and table partitions
- Using EXPLAIN
PLAN
- Interpreting
EXPLAIN PLAN Output
- Using indexes
to improve performance
- Identifying
full-table scans
- Re-writing
SQL queries
- Using hints
to improve SQL performance
- Using
parallel query to improve performance
- Tuning
sub-queries
DAY
4
PL/SQL
Section
1. Basics
of PL/SQL
- PL/SQL
architecture
- PL/SQL and
SQL*Plus
- PL/SQL
Basics
Variables
Constants
Datatypes
Error messages – user_errors and show errors
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
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.
|
