Oracle SQL and Plsql Certification Training

Oracle SQL and Plsql

✔ DBMS Concepts
✔ Role of Oracle 11g in Information management.
✔ Concepts of RDBMS
✔ RDBMS Rules (Codd’s Rules)
✔ What is Object Model?
✔ Features of Oracle 9i/10g/11i/12c
✔ E-R Modeling

✔ What is SQL?
✔ Roles of SQL in RDBMS
✔ What is SQL * Plus
✔ Types of Plus Commands

✔ Data Definition Language (DDL)
✔ Naming Conversion in Oracle
✔ Data Types of Oracle
✔ Implementation of Data Integrity & Integrity Constraints
✔ Normalization
✔ System Tables & Data Dictionary

✔ Insert
✔ Select
✔ Update
✔ Delete

✔ Single Row Functions (Oracle SQL and Plsql)
✔ Group functions
✔ Arithmetic Functions
✔ String Manipulation Functions
✔ Date Manipulation Functions
✔ Data conversion Functions
✔ List of Functions /Misc.Functions

✔ What is joining?
✔ Types of joins
✔ Cartesian joins
✔ Equi join/Non-Equi/Outer/Self join

✔ Simple /complex subqueries
✔ Co-related subquery
✔ Filters/Set operator usage

✔ Arithmetic Operators
✔ Relational Operators
✔ Relational Negation Operators
✔ Logical Operators
✔ Set Operators

✔ Index
✔ Types of Indexes
✔ Unique Index
✔ Non-Unique Index
✔ Function Bases Index

✔ Types of views (Oracle SQL and Plsql)
✔ Forced views
✔ Rules for DML on Views
✔ In-Line Views
✔ Materialized Views

✔ Types of synonyms
✔ Private synonym
✔ Public synonym
✔ Security aspect

✔ Usage mechanism of clusters

✔ Usage of various clauses
✔ Pseudo columns

✔ Simple Deletion
✔ Critical Data Deletion
✔ Using joining concept
✔ Using Sub query concept
✔ Using correlate Sub queries

✔ Table Update
✔ Multi-column updates
✔ Table Updation Using Decode Statement

✔ COMMIT
✔ ROLLBACK
✔ SAVE POINT
✔ Data Consistency

✔ GRANT
✔ REVOKE
✔ Create New User
✔ Assign Privileges For User
✔ What are privileges?
✔ What is a role?
✔ Grant privileges
✔ Cascading privileges
✔ Password Change

✔ Oracle Table Partitions
✔ Range Partitions
✔ List Partitions
✔ Composite Partitions

✔ Introduction to PL/SQL
✔ Key benefits of PL/SQL over SQL
✔ The architecture of PL/SQL
✔ PL/SQL Block Structure
✔ Constructs of PL/SQL
✔ Data type support in PL/SQL
✔ Variables /Constants of PL/SQL
✔ Flow control Statement Types
✔ If /Nested if .. / Exit/Go to/Raise
✔ Instructive statement types
✔ Simple loop (infinite loop)
✔ While loop
✔ For loop
✔ Reverse For loop

✔ What is Embedded SQL
✔ Need of Embedded SQL
✔ Constructs of Embedded SQL
✔ Transactions through Embedded SQL
✔ Valid statements of SQL
✔ Dynamic SQL

✔ What is an Exception?
✔ Type of Exceptions
✔ User Defined Exceptions
✔ Use of raise and its Scope
✔ Raise – Application-Error ( )
✔ PRAGMA EXCEPTION_INIT ( )

✔ Description
✔ Advantages
✔ Types of variables of PL/SQL
✔ Record type of PL/SQL

✔ Instruction to Cursor Management
✔ Pictorial view of cursor Diagram
✔ Implicit curser
✔ Explicit curser
✔ Cursor 1st Model
✔ Cursor 2nd Model
✔ Cursor 3rd Model
✔ Cursor 4th Model
✔ Cursor 5th Model
✔ Cursor Attributes
✔ Cursors using a simple loop
✔ Cursor for loops
✔ Cursor Exceptions
✔ Cursor Parameters

✔ Static Ref cursor
✔ Dynamic Ref cursor
✔ Dynamic behaviors of cursor mnt
✔ User-defined types (RECORDS)
✔ PL/SQL tables (2D ARRAYS)
✔ Dynamic behaviors of PL/SQL tables
✔ PL/SQL table Attributes
✔ Bulk Collect
✔ Bulk Bind

✔ Types of PL/SQL blocks
✔ Anonymous PL/SQL block
✔ Named PL/SQL block
✔ Stored PL/SQL
✔ Procedures
✔ Type of parameters
✔ Procedures Using In Parameter
✔ Procedures Using Out Parameter
✔ Procedures Using InOut Parameters
✔ Functions

✔ Description
✔ Advantages over Standalone Schema objects
✔ Encapsulation
✔ Package overloading
✔ Packages Create Using Global Variables
✔ Packages Create Using Record Types
✔ Packages Create Using Cursors
✔ Packages Create Using Ref Cursors
✔ Packages Create Using plsql Table Types
✔ Packages Create Using Procedures
✔ Packages Create Using Functions

✔ 12 Types of Triggers
✔ Triggering events
✔ Usage of Old & New reference
✔ View Trigger instead of Trigger Cascading /firing
✔ Table mutation error
✔ Bind variable support
✔ Special clauses of Triggers
✔ Statement Level Triggers
✔ Row Level Triggers

✔ Object Types
✔ Create Object Tables

✔ Create Nested Tables
✔ Index By Tables / Associative Array
✔ Varray

✔ New enhancements in oracle 10g,11g, And 12c
✔ New clauses in joining of tables
✔ Inner join/left outer join /right outer join
✔ Full outer join/natural full outer join
✔ Using clause/cross join
✔ Multi-table inserts
✔ Insert all command
✔ Merge command (UpSert)
✔ NVL ( ) enhancements
✔ Case statement of select command
✔ Searched CASE expression
✔ Heap tables
✔ Index Organized Tables (IOT)
✔ Temporary tables/global tables
✔ Multi-column updates
✔ Multiple Row subqueries
✔ New function EXTRACT( )
✔ Usage of subquery in the select list
✔ Autonomous Transaction
✔ Flashback Concepts
✔ Purge Concepts