Oracle PL SQL Classroom Training

This impeccable PL SQL Training Course is exclusively designed for starters as well as for experienced professionals addressing Oracle Installation, Database Design, Queries, Joins, Stored Procedures, Triggers, Remote Queries, Transactions and Tuning options with BLOB Data and JSON. Practice Material, Certification, Resume & Interview Guidance are included in this Oracle PL SQL Course

 

Oracle PL SQL Training Plans

  PLAN A PLAN B
Course Description SQL Basics,
PL-SQL Queries
PL-SQL
Programming
Total Duration 2.5 Weeks 4.5 Weeks
Database Fundamentals, LINUX Basics Check-Symbol-for-Yes Check-Symbol-for-Yes
SQL Basics with DDL, DML, SELECT Check-Symbol-for-Yes Check-Symbol-for-Yes
SQL Basics with DCL, TCL Commands Check-Symbol-for-Yes Check-Symbol-for-Yes
Queries, Sub Queries and Functions Check-Symbol-for-Yes Check-Symbol-for-Yes
SQL Queries with Constraints, Keys Check-Symbol-for-Yes Check-Symbol-for-Yes
Normal Forms and Relationship Type Check-Symbol-for-Yes Check-Symbol-for-Yes
Queries, Joins and Table Comparisons Check-Symbol-for-Yes Check-Symbol-for-Yes
Writing Sub Queries, Nested Queries Check-Symbol-for-Yes Check-Symbol-for-Yes
Basics of Stored Procedures, Functions Check-Symbol-for-Yes Check-Symbol-for-Yes
Parameterized Queries and Controls Croos-symbol-for-No Check-Symbol-for-Yes
Error Handling and Event Handling Croos-symbol-for-No Check-Symbol-for-Yes
Stored Procedures , Programming Level 2 Croos-symbol-for-No Check-Symbol-for-Yes
Stored Procedures , Programming Level 3 Croos-symbol-for-No Check-Symbol-for-Yes
Control Structures and Loops, Packages Croos-symbol-for-No Check-Symbol-for-Yes
Lock Management and Concurrency Croos-symbol-for-No Check-Symbol-for-Yes
Advanced Control Structures - Level 1 Croos-symbol-for-No Check-Symbol-for-Yes
Advanced Control Structures - Level 2 Croos-symbol-for-No Check-Symbol-for-Yes
Real-time Project [Banking Domain] Croos-symbol-for-No Check-Symbol-for-Yes
Real-time Project with Complete Solution Croos-symbol-for-No Check-Symbol-for-Yes
Resume with Real-time Project FAQs Croos-symbol-for-No Check-Symbol-for-Yes
Total Course Fee INR 3000 INR 4000

PL SQL Training Schedules :

S. No Timings (IST) Start Date  
Schedules for PL-SQL
1 6:45 AM - 7:45 AM Oct 22nd Register
2 8:00 AM - 9:00 AM Nov 5th Register
3 10:15 AM - 11:15 AM Nov 19th Register
4 3:00 PM - 4:00 PM Oct 28th Register
5 6:30 PM - 7:30 PM Nov 11th Register
6 9:00 AM - 11:00 AM (wk) Oct 26th Register

Who can take this Course?

This PL SQL Course is helpful for aspiring Data Scientists, Data Analysts, PL SQL Developers, Report Developers and more..!

If the above schedules does not work for you, opt for PL SQL Video Training (24x7 Access)

PL SQL Training Highlights

✔ Daily Tasks ✔ Weekly Interviews ✔ Real-time Project
✔ Resume Guidance ✔ Certification Guidance ✔ Placement Services

Oracle PL SQL Training Course Contents:

Module 1: Basic SQL

Applicable For Plan A, B

Module 2: SQL & PL SQL

Applicable For Plan A, B

Module 3: Adv PLSQL

Applicable For Plan B

CHAPTER 1 : INTRODUCTION TO DBMS

  • What is Data and Information?
  • Purpose of Data Management
  • File and File System For Data Storage
  • Disadvantages of File Data Management
  • Database : Purpose, Basic Terminology
  • Advantages of Database for Data Storage
  • Possible Operations on the Database
  • Database Models – Hierarchical Model
  • Network Model and Relational Model
  • Introduction to DBMS
  • Introduction to RDBMS
  • Database Design
  • Most Popular RDBMS Products
  • History, Real-time Database Examples (LIVE)

CHAPTER 7 : CONSTRAINTS & KEYS

  • Importance of Data Integrity
  • Working with Integrity constraints
  • Types of Integrity Constraints
  • Domain Integrity Constraint and Usage
  • Entity Integrity Constraints and Usage
  • Referential Integrity Constraint and Usage
  • Check Constraints and Usage
  • NOT NULL Constraint, UNIQUE Constraint
  • PRIMARY KEY Constraint and Usage
  • FOREIGN KEY Constraints and Relations
  • Column Level  & Table Level Constraints
  • Adding Constraints to Tables, User_Constraints
  • Enabling - Dropping Constraints, Self Referential Integrity
  • Disabling Constraints on Tables

CHAPTER 13 : PL/SQL – (CONTROL STRUCTURE) - Level I

  • Simple If, If..Else. Nested If..Else Statements
  • Ladder, Selection, Simple Case Statements
  • GOTO Label and EXIT Statements in PL/SQL
  • Iterations in PL/SQL, Simple LOOP, WHILE
  • FOR LOOP and NESTED LOOPS in PLSQL
  • PL/SQL Select statements
  • SQL within PL/SQL, Composite Data Types
  • Cursor Variables and Management in PL/SQL
  • Implicit & Explicit Cursors and Attributes
  • Cursor with Parameters and Nested LOOPs
  • Cursors with Sub Queries, Reference Cursors
  • Implicit Cursors,Explicit Cursor
  • Parameterized Cursors,Ref Cursors
  • REF Cursors

CHAPTER 2: ORACLE INSTALLATION

  • Oracle 2018 Installation Guidance
  • Oracle 2012 Installation
  • Installation Pre-Requisites and Precautions
  • Oracle Sql Developer
  • Oracle Sql Command prompt
  • Oracle Versions and Editions Comparisions
  • Connect Sql Developer Tool
  • Creating a new User
  • Grant permission
  • Lock and Unlock User Account
  • How to Reset User Account Password
  • SQL* Plus
  • How to Establish the Oracle Connection
  • Default System user  in Oracle
  • Default Sysdba user  in Oracle

CHAPTER 8 : QUERIES & JOINS

  • Need for Joins and Table Comparisons
  • SET Operations on Tables and Joins
  • Join Types : Equi Join, Simple Join
  • Inner Join and Query Conditions
  • Cross Join (Cartesian Join), Conditions
  • Join syntax - Sql syntax and Ansi Syntax
  • Non-Equi Join and Self Join
  • Outer Joins – Types, Advantages and Usage
  • Cross Joins – Advantages and Limitations
  • Self Joins, Merge Joins and Sub Queries
  • Using Aliases in Joins and Conditions
  • Using Inner Self Joins with HAVING
  • JOINS and GROUP BY Queries

CHAPTER 14: ADVANCED PL/SQL - 1

  • Procedures in PL/SQL: STORED PROCEDURE
  • PROCEDURES @ Parameters (IN, OUT, IN OUT)
  • POSITIONAL Notation and NAMED Notation
  • Procedure with Cursors and Sub Queries
  • ALTER and DROP of Stored Procedures
  • Functions in PL/SQL: Real-time Usage
  • User Defined Functions, Nested Functions
  • Using Functions in SQL Statements
  • Working with Procedures and Functions
  • Comparing Stored Procedures and Functions
  • Using SPs with Table Value Functions
  • Using SPs for Dynamic SQL Statements
  • Loops and Table Variables in SQL Programs
  • Merge, NVL2(), NULLIF(), COALESCE()
  • CASE & Temporary /Global Tables

CHAPTER 3: INTRODUCTION TO RDBMS

  • Features of RDBMS
  • Advantages of RDBMS
  • E.F Codd Rules for RDBMS
  • Normalization Concepts & Process
  • Client Server Communication
  • Oracle Corporation Products
  • RDBMS from Other Vendors: Microsoft, IBM,etc
  • Oracle Versions about SQL & SQL*PLUS
  • Database Types: OLTP and OLAP Databases
  • Real-world Advantage of OLTP & OLAP
  • Why a database is called as Relational Database Model
  • Database Entites and Attributes
  • Applicative use of RDBMS Databases

CHAPTER 9 : VIEWS, SYNONYMS,SEQUENCES

  • VIEWS IN ORACLE, Understanding Views & Use
  • Relational Views and Standard Views
  • SIMPLE VIEWS and COMPLEX VIEWS in PLSQL
  • Column Definitions in VIEWS
  • Using VIEWS for DML Operations
  • Forced Views, CHECK Constraints in Views
  • Creation of READ ONLY VIEWS - Realtime Use
  • TOP-N Analysis, (Indexed) Materialized Views
  • SequenWorking ces, with Synonyms
  • What is the Use of Synonym in Oracle?
  • Difference between View and Synonym in Oracle
  • When should Table Synonyms be used?
  • Public Synonym and Private Synonyms
  • All_Synonyms and Dba_Synonyms System Tables
  • Creating Index Tables – Purpose

CHAPTER 15 : ADVANCED PL/SQL - 2

  • Cross Tab Views using Pivot/Unpivot Operators
  • Follows Clause and READONLY Tables
  • IN-LINE VIEWS. Manipulations with Triggers
  • Purity Levels in Oracle - with Examples
  • User_Source Dictionary Table Packages
  • Creating PACKAGE Specifications with PLSQL
  • PACKAGE Body - Examples with Big Data Tables
  • Private and Public Objects in PACKAGE
  • Types of Exceptions: User Defined Exceptions
  • EXCEPTIONS in PL/SQL with Real-time
  • Event Handling and Error Handling Techniques
  • Important Error Code Values in PL/SQL
  • RAISE_APPLICATION_ERROR Procedure

CHAPTER 4 : SQL LANGUAGE COMMANDS

  • Structured Query Language (SQL) Basics
  • What is SQL?
  • What Can SQL do?
  • Possible Operations with SQL
  • Data Definition Language (DDL)
  • Data Retrieval Language (DRL)
  • Data Manipulation Language (DML)
  • Transaction Control Language (TCL)
  • Database Security and Privileges (DCL)
  • Rules of SQL Queries and Statements
  • Real-world applicative uses of SQL
  • Creating Users and Tables
  • Table Data Inserts and Validations
  • Oracle Data Types
  • DDL Commands with Examples
  • CREATE, ALTER, TRUNCATE,DROP,RENAME
  • Learn the DESCRIBE command to display the table structure
  • DML Command DRL Commands Operators
  • INSERT, UPDATE, DELETE Statements
  • Difference between Truncate and Delete commands
  • SELECT Statements with Multiplier
  • SQL Comments
  • Single Line Comments & Multi Line Comments
  • WHERE Keyword for Query Conditions

CHAPTER 10 : SUB QUERIES, NESTED QUERIES

  • Sub Queries in Real-world
  • Dynamic Conditions with Sub Queries
  • Sub Queries and Nested Sub Queries
  • How does oracle Execute Nested Sub Queries?
  • Inner Select and Outer Select Queries
  • Usage of Sub Queries with WHERE, HAVING
  • Impact of Having Clause in Sub Queries
  • Select Nth Highest salary
  • Select Duplicate Records
  • Delete Duplicate Records
  • Advantages of Oracle Sub Query
  • Subquery in the Select Clause Examples
  • Subquery in the From Clause Examples
  • Display Odd Rows in a table
  • Display Even Rows in a table
  • Execution of Correlated Sub Queries in SQL
  • IN, ANY SOME, ALL Operators in Sub Queries
  • PAIR WISE and NON PAIR WISE in Sub Queries
  • Single Row Subquery & Multiple Row Subquery
  • Multiple Column Subquery
  • UnCorrelated Subquery
  • Difference between Correlated and Non-Correlated Subquery
  • How does Oracle Execute Correlated Subquery?
  • NULLs and Correlated Sub Queries in SQL

CHAPTER 16 : ADVANCED PL/SQL - 3

  • Pragma_Autonomous_Transaction() with SPs
  • Returning into clause, Bulk Collect
  • For All, Definer/Invoker Rights & Usage
  • About Flash Back Queries, Dynamic SQL
  • Flash Back Command, Purge Command
  • Regular Expressions in PLSQL
  • What is the Recycle Bin?
  • How to Delete RecycleBin Table?
  • How to Delete Recycle Bin?
  • SQL Loader - Flat file into Oracle table
  • Abstract Datatypes
  • DML Error Logging and Virtual Columns
  • Types of Triggers, Row Level Triggers
  • Statement Level Triggers, DML Triggers
  • DDL Triggers and Schema Level Triggers
  • Using OLD & NEW References, Trigger Auditing
  • Enabling / Disabling Triggers, Dropping Triggers
  • Triggers in PL/SQL and Data Manipulations
  • Using Memory Tables in Triggers
  • DML and DDL Events with Triggers
  • Compound Triggers, New data types
  • Working with LARGE Tables in PLSQL
  • New: EXTRACT(), Autonomous Transaction
  • Pragma Exception_init in oracle

CHAPTER 5.SQL SPECIAL OPERATORS

  • SET, AND, OR, NOT, IN
  • Special Operators – IN (NOT IN)
  • BETWEEN (NOT BETWEEN),
  • Arithmetic and Logical Operators
  • Understand Operator Precedence
  • UNION, UNION ALL, INTERSECT, MINUS
  • LIKE (NOT LIKE), IS NULL (IS NOT NULL)
  • Understanding DCL and TCL Commands
  • Transaction Concepts in Databases with SQL
  • Transaction Types and Uses
  • Commit and Rollback Operations
  • Nested Transactions with Savepoints
  • Database Read Consistency with SQL
  • DDL and DML Operations with Transactions

CHAPTER 11: LOCKS in ORACLE TABLES

  • Open Transactions and Lock Concepts
  • Lock Types and Locking Mechanism
  • Row Level Locks for Table Data Access
  • Table Level Locks and Query Blocking
  • Shared Lock and Real-time Use
  • Shared Update Lock and Real-time Use
  • Exclusive Lock and Real-time Use
  • DeadLock : Detection, Prevention, Avoidance
  • Implicit Locking Concepts in Oracle with PLSQL
  • How to check howmany locks are occured?
  • LOCK Hints and Query Blocking
  • What is row exclusive lock in Oracle?
  • What is lock in SQL?
  • What are different types of locks?

CHAPTER 17: OBJECT TECHNOLOGY – Level I

  • Object Technology and Applicative Use
  • OOPS-Object Instances, Creation of Objects
  • Creating User Defined Data Types
  • Creating Object Tables in Oracle
  • Inserting rows in Table using Object
  • Retrieving data from Object Based Tables
  • Calling a Method, Indexing Abstract
  • Data type Attributes in PLSQL
  • Advantages of Collections
  • Ref Cursor (Dynamic Cursor), Weak Ref Cursor
  • Strong Ref Cursors and Nested Tables
  • Associative Arrays, VARRAYS/VARYING arrays
  • Creating tables using Nested Tables
  • Inserting, Updating & Deleting
  • Nested, Table Records

CHAPTER 6: GROUPING QUERY RESULTS

  • Identify Distinct Values in Tables
  • Group Function or Aggregate function
  • Group By Syntax
  • Group By Operations in Queries
  • Having Clause of DRL Statements
  • Aggregate Functions with Group By
  • Order By Clause and Group By
  • SQL Group By Statement
  • Query Execution Order with Group By
  • Arithmetic Functions,Character Function
  • Date & Time Functions,String Function
  • Conversion Functions,Analytical Function
  • Rank(), Dense_Rank(),Row_Number()
  • NVL(),NVL2(), NullIF(),Coalesce()

CHAPTER 12 : PERFORMANCE (QUERY) TUNING

  • Indexes - Definition and Architecture
  • B Tree Concept in Indexes
  • Clustered and Non clustered Indexes
  • Primary Key and Clustered Index
  • Unique Key and Non Clustered Indexes
  • Simple Index, Rebuild Index
  • Materialized Views - Indexed Views
  • Composite Index, Function Based Index
  • User_indexes System Table
  • Range Partitions and Hash Partition
  • Partion “VALUES LESS THAN “ Clause
  • List Partition and Composite Partition
  • Parallel Query Process with Partitions
  • Performance Tuning Advantages

CHAPTER 18: OBJECT TECHNOLOGY – Level II

  • Perform a Basic Search using the REGEXP_LIKE function
  • patterns using the REGEXP_INSTR function
  • Extract Substrings using the REGEXP_SUBSTR function
  • Replace Patterns Using the REGEXP_REPLACE
  • Invisible Columns in Oracle Database 12c
  • What is fetch in SQL?
  • Can we use limit in Oracle?
  • What is offset in Oracle?
  • Is PL SQL object oriented?
  • What is Oracle object type?
  • What is object type in Oracle PL SQL?
  • Is Oracle object oriented database?
  • What is PL SQL in Oracle?
  • What is Oracle constructor?
 
All Classes are Instructor-Led & LIVE. Completely Practical and Real-time with Study Material, Session Notes, Tasks and 24x7 LIVE Server.
 

Oracle SQL & PL SQL Training - Highlights :

  • Completely Practical and Real-time
  • Suitable for Starters + Working Professionals
  • Session wise Handouts and Tasks + Solutions
  • TWO Real-time Case Studies, One Project
  • Weekly Mock Interviews, Certifications
  • Certification & Interview Guidance
 
 
  • Detailed SQL Basics includes DDL, DML, DCL, TCL
  • Table Design, Normal Forms and RAID Levels
  • Queries, Sub Queries and Nested Queries
  • Loops, Variables, Functions and Cursors
  • Query Tuning, Stored Procedures, Triggers
  • PL SQL Programming Concepts and Advanced PLSQL
Register Today  Other Popular Courses: SQL DBA Training, MSBI Training, SSIS Training, SSAS Training, SSRS Training [+] More Courses

Job-Oriented Real-time Training @ SQL School Training Institute - Trainer: Mr. Sai Phanindra T