Introduction to Oracle SQL and PL/SQL

A five-day intensive Overview

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

• Restricting row returns

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 

• Creating basic reports

Exercise – format a simple query in the emp 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

DAY 2

Joining Oracle tables

• 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

• 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

• 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.