SQL/PL SQL

Introduction to SQL & SQL*Plus

Review of Basic SQL and history of SQL:

History of SQL

Evolution of SQL

SQL constructs:

Select

 Project

 Join

Complex Boolean logic in SQL

ROWID restrictions

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

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

Dynamic SQL in PL/SQL

Introduction to the dbms_sql package

Creating a dynamic SQL statement

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

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

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

Package forward declaration

Package dependency

Package overloading

Listing package information