SQL/PL SQL

Using SQL*Plus

Creating basic reports

Altering SQL execution plans

Joining Oracle tables

Sort-merge joins

Nested Loop joins

Hash joins

STAR joins

Bitmap joins

Exercise – Change table join techniques & evaluate performance

Equi-join
Outer join

Advanced SQL operators

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

SQL Tuning

DML and SQL Tuning

Writing and optimizing INSERT statements

Writing and optimizing DELETE statements

Writing and optimizing UPDATE statements

Aggregation IN sql

Aggregation in SQL

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

PL/SQL Section

Basics of PL/SQL

• PL/SQL architecture
• PL/SQL and SQL*Plus
• PL/SQL Basics
• Introduction to PL/SQL
• PL/SQL as a 4^th 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

Error checking – exception handling

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

Cursors in PL/SQL

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

Subqueries

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

• Correlated subquery
• Non-correlated subqueries

SQL access methods

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

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

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

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

Iteration in PL/SQL

• For loop
• While loop

PL/SQL tables

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

Nested blocks in PL/SQL

• Creating nested blocks
• Understanding scope in nested blocks

Triggers in PL/SQL

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