Oracle PL/SQL course Beginner level by Three Spear

Course Title :Oracle SQL ,PL/SQL Duration: 45 Hours

SQL
1) Introduction to SQL
DDL commands
DML commands
DCL commands
TCL Commands
Data types in oracle
Detail understanding on Select statement
2) Restricting and Sorting Data
Where clause
Between, In and Like operators
And, OR and Not operators
Order by clause
Defining and Assigning values to variables
3) Single Row functions
Character Manipulation functions
Case conversion functions
Number functions
Date functions
Round and Trunc with date functions
4) Conversion Functions
Data type conversion
To_char, to_date and to_number functions
Replacing null values (NVL2), Decode function
Conditional expressions ( Case)
5) Group functions
Group by clause
Sum, Avg, Min and Max functions
Group by clause on multiple columns
Restricting group results ( Having)
6) Oracle Joins
Type of joins
Join using On and Using clauses
Natural, self and equi joins
Outer joins (left, right and full)
Cross join ( Cartesian product)
7) Sub queries
Types of sub-queries
Single and multiple row sub-queries
Group functions on sub-queries
Using All and Any operators 8) Set operators
Union and union-all operators
Intersect and minus operators
Order by clause in set operators
9) More on DML statements and Data integrity
Inserting data from other table
Conditional insert (Merge)
Select for update statement
Delete vs truncate
Data integrity (Commit & Rollback)
10) More on DDL and Constraints
Creating tables and inserting default values
Primary and foreign key constraints
Unique and check constraints
Adding and dropping columns from a table
11) Creating other Schema objects
Simple and Complex views
B-tree and Bitmap indexes
Working with sequences
Creating synonyms
12) Controlling User Access
Creating users
Granting system & object privileges
Granting and revoking privileges to and from a role
13) Managing other Schema Objects
Setting and Dropping UNUSED columns
Deleting data from Child Tables (on Delete)
Disable & Enable constraints
Drop TablePurge
Flashback Table
Temporary Table
External Table
14) Miscellaneous
Multi Table Insert
Merge statement in detail
Flashback Versions Query
Interval data types
Exists & not exists operators
With Clause
Rollup and Cube operators

PL/SQL

1. Basics of PL/SQL
PL/SQL architecture
PL/SQL and SQL*Plus
PL/SQL Basics, Variables, Constants, data types & error handling
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
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. Triggers in PL/SQL
Triggers and database events
Defining a trigger
Timing a trigger
Enabling and disabling a trigger
9. 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
10. Bulking in PL/SQL
Bulk queries
Bulk DML (for all statement)
Using cursor attributes
Analyzing impact of bulk operations

11. Left Over TOPICS
12. Real Time Assignments