Oracle PL SQL Training

Course Overview

Oracle PL/SQL is one of the most popular database query languages. PL/SQL means instructing the compiler “what to do” through SQL and “How to do” in a procedural way. QTSInfo offers live instructor-led online classes by real-time industry professionals. Those designed to give the best learning environment with comfortable timings and classes being more interactive, and engaging.

You will learn PL/SQL key concepts like multiple forms, reports, and data management applications. Our Oracle PL/SQL tutorial covers all the aspects from beginners to experienced level. By the end of this course, you will become a pro-PL/SQL developer.

Oracle PL SQL Certifications Key Features

Learn from basics, what is pl/SQL, architecture, etc.,
Installation and Configuration of Oracle PL/SQL
Core concepts of PL/SQL blocks, stored procedures, and functions
Provide you with important Oracle PL/SQL interview questions
Real-Time base scenario projects for practice
Guidance in building Oracle PL/SQL resume
Get Oracle PL SQL certification
One to One sessions

Who should learn PL SQL Online?

This PL/SQL course primarily benefits application designers, database administrators, software developers, and IT professionals would like to move to PL/SQL domain. Fresher’s who want to pursue a career in PL/SQL domain. Professionals who want certification to advance their career.

Top Hiring Company

Industry Trends

Course curriculum / Syllabus

Introduction to DBMS

Approach to Data Management
Introduction to prerequisites
File and File system
Disadvantages of file
Review of Database Management Terminology
Database Models
Hierarchal Model
Network Model
Relational Model

Introduction to RDBMS

Feature of RDBMS
Advantages of RDBMS over FMS ad DBMS
The 12 rules (E.F Codd’s Rules –RDBMS)
Need for Database Design
Support of Normalization Process for Data Management
Client server Technology
Oracle Corporation Products
Oracle Versions
About SQL&SQL*PLUS

Sub Language Commands:

Data Definition Language (DDL)
Data Retrieval Language (DRL)
Data Manipulation Language (DML)
Transaction Control Language (TCL)
Database Security and Privileges (DCL)

Introduction to SQL Database Object

Oracle Pre Defined Data types
DDL Commands
Create, Alter (add, modify, rename, drop)Columns, Rename, truncate, drop
DML-Insert, update, delete
DQL-SELECT Statements using WHEREclause
Comparison and Conditional Operators
Arithmetic and Logical Operators
Set Operators (UNION, UNION ALL, INTERSECT, MINUS)
Special Operators – IN (NOT IN), BETWEEN (NOT BETWEEN), LIKE (NOTLIKE), IS NULL (IS NOT NULL)
Working with DML, DRL Commands
Operators Support

Built in Functions

Arithmetic Functions, Character Functions, Date Functions, Conversion Functions
Aggregate Functions, OLAP Functions & General Functions

Grouping the Result of a Query

Using Group by and Having Clause of DRL Statement
Using Order by clause

Working with Integrity Constraints

Importance of Data Integrity
Support of Integrity Constraints for Relating Table in RDBMS
NOT NULL constraint
UNIQUE constraint
PRIMARY KEY constraint
FOREIGN KEY constraint
CHECK constraint
Working with different types of integrity Constraints

REF constraint

Understanding ON DELETE clause in referential integrity constraint
Working with composite constraint
Applying DEFAULT option to columns
Working with multiple constraints upon a column
Adding constraints to a table
Dropping of constraints
Enabling / Disable constraints
Querying for constraints information

Querying Multiple Tables (Joins)

Equi Join/Inner Join/Simple Join
Cartesian Join
Non-Equi Join
Outer Joins
Self Join

Working with Sub Queries

Understanding the practical approach to Sub Queries/Nested Select/Sub Select/Inner Select/Outer Select
What is the purpose of a Sub Query?
Sub Query Principle and Usage
Type of Sub Queries
Single Row
Multiple Row
Multiple Column
Applying Group Functions in Sub Queries
The impact of Having Clause in Sub Queries
IN, ANY/SOME, ALL Operators in Sub Queries
PAIR WISE and NON PAIR WISE Comparison in Sub Queries
Be … Aware of NULL’s
Correlated Sub Queries
Handling Data Retrieval with EXISTS and NOT EXISTS Operators

Working with DCL, TCL Commands

Grant, Revoke
Commit, Rollback, Savepoint
SQL Editor Commands
SQL Environment settings

Maintaining Database Objects VIEWS in Oracle

Understanding the Standards of VIEWS in Oracle
Types of VIEWS
Relational Views
Object Views
Prerequisites to work with views
Practical approach of SIMPLE VIEWS and COMPLEX VIEWS
Column definitions in VIEWS
Using VIEWS for DML Operations
In-Line View
Forced Views
Putting CHECK Constraint upon VIEWS
Creation of READ ONLY VIEWS
Understanding the IN LINE VIEWS
About Materialized Views
View Triggers

Working with Sequences Working with Synonyms Working with Index and Clusters Creating Cluster Tables, Implementing Locks, working with roles Pseudo Columns in Oracle

Understanding Pseudo Columns in Oracle
Types of Pseudo Columns in Oracle
CURRVAL and NEXTVAL
LEVEL
ROWID
ROWNUM

Data Partitions & Parallel Process

Types of Partitions
Range Partitions
Hash Partitions
List Partition
Composite Partition
Parallel Query Process

Locks

Row level Locks
Table Level Locks
Shared Lock
Exclusive Lock
Dead Lock

SQL * Loader

SQL * Loader Architecture
Data file (Input Datafiles)
Control file
Bad file
Discard file
Log file
.txt to base table
.csv to base table
From more than one file to single table

PL-SQL (Procedure Language – SQL)

Introduction to Programming Languages
Introduction to PL/SQL
The Advantages of PL/SQL
PL/SQL Architecture
PL/SQL Data types
Variable and Constants
Using Built_in Functions
Conditional and Unconditional Statements
Simple if, if… else, nested if..else, if..else Ladder
Selection Case, Simple Case, GOTO Label and EXIT
Iterations in PL/SQL
Simple LOOP,WHILE, LOOP, FOR LOOP and NESTED LOOPS
SQL within PL/SQL
Composite Data types (Complete)
Cursor Management in PL/SQL
Implicit Cursors
Explicit Cursors
Cursor Attributes
Cursor with Parameters
Cursors with LOOPs Nested Cursors
Cursors with Sub Queries
Ref. Cursors
Record and PL/SQL Table Types

Advanced PL/SQL

Advanced PL/SQL

Procedures in PL/SQL

STORED PROCEDURES
PROCEDURE with Parameters (IN,OUT and IN OUT)
POSITIONAL Notation and NAMED Notation
Procedure with Cursors
Dropping a Procedure

Functions in PL/SQL

Difference between Procedures and Functions
User Defined Functions
Nested Functions
Using stored function in SQL statements

Packages in PL/SQL

Creating PACKAGE Specification and PACKAGE Body
Private and Public Objects in PACKAGE

EXCEPTIONS in PL/SQL

Types of exceptions
User Defined Exceptions
Pre Defined Exceptions
RAISE_APPLICATION_ERROR
PRAGMA_AUTONOMOUS_TRANSACTION
SQL Error Code Values

Database Triggers in PL/SQL

Types of Triggers
Row Level Triggers
Statement Level Triggers
DDL Triggers
Trigger Auditing

Types of Triggers
Row Level Triggers
Statement Level Triggers
DDL Triggers
Trigger Auditing

File Input/Output

PL/SQL file I/O (Input/Output)
Using UTL_FILE Package

Implementing Object Technology

What is Object Technology ?
OOPS-Object Instances
Creation of objects
Creating User Defined Data Types
Creating Object Tables
Inserting rows in a table using Objects
Retrieving data from Object based Tables
Calling a Method
Indexing Abstract Data type Attributes

Using LOBS

Large Objects (LOBS)
Creating Tables-LOB
Working with LOB values
Inserting, Updating & Deleting Values in LOBs
Populating Lobis DBMS_LOB Routines
Using B-FILE

Using Collections

Advantages of collection
Ref cursor (Dynamic Cursor)
Weak ref cursor
Strong ref cursor
Nested Tables VARRAYS or VARYING arrays
Creating tables using nested tables
Inserting, updating & deleting Nested
Table records
Nested table in PL/SQL

Oracle Database Architecture

Introduction to Oracle Database Architecture
Physical structures Logical structures
DB Memory Structures Background Process
2 Tire, 3 Tire, N-Tier Architecture

Advanced Features

9i Joines
New Date function
Rename column
Inner Join/Natural Join
Left Outer Join/Right Outer Join
Full Outer Join
Multiple Inserts
Insert All Command
Merge statement
NVL2(), NULLIF(), COALESCE()
CASE expression of Select Command
Temporary Tables/Global Tables
New Function EXTRACT()
Autonomous Traction
Pragma _ Autonomous_ Transaction()
Bulk Collect
About Flash Back Queries
Dynamic SQL
New data types, Flash back Command
Purge Command, Recycle bin
Regular expressions, DML Error Logging
Data Pump, Virtual Columns
Read only tables, Cross tab Views using
Pivot/Unpivot operators, Follows Clause
Compound triggers, New data types

Oracle PL SQL Training FAQ’s:

1.What is PL/SQL?

PL/SQL is an extension of the SQL language. It is a procedural language designed specifically to embrace SQL statements within its syntax.

2.Do you assist in Oracle PL/SQL developer certification?

Yes, we assist once you decide on the type of certification. This is a proctored exam and is conducted in Prometric centers.

3.What are the fees for Oracle PL/SQL certification?

The certification of fees for Oracle PL/SQL is $245.

4.Do you provide Oracle PL SQL certification?

Yes, we provide you with certification upon completion of the course successfully. Our certificate is accredited by many organizations. It will add weightage to your resume.

5.What if I miss the SQL online class?

In such situations, we provide oracle PL/SQL online training videos with tutorial videos.

6.Do you provide job assistance?

Yes, we do provide you with job assistance and help you prepare for the interview. Our course is job-oriented.