SQL/PL SQL


SQL -Structured Query Language is a domain-specific language used in programming and designed for managing data held in a relational database management system(RDBMS), or for stream processing in a relational data stream management system (RDSMS)

PL/SQLProcedural Training/Structured Source Language that includes procedural language components like loops and conditions that permit declaration of constants and variables, procedures and functions and might handle runtime errors. One will produce PL/SQL units like procedures, functions, packages, sorts and triggers that are keep within the information for the use by applications that are utilized by any of the Oracle Database informatic interfaces

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