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 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.
This
is a BC Oracle training course (c) 2002