Oracle PL SQL Training

Topics: 

• • Introduction to RDBMS 
  • Oracle Database server architecture 
  • Different Oracle versions 
  • Oracle 12c installation

Learning Outcome: You will gain a basic overview of oracle SQL and its architecture.

Topics: 

• • Objectives of the main database 
  • Column data types 
  • Table structure analysis 
  • Simple table creation 
  • Overview of constrains origin process 
  • Schema object working process

Learning Outcome: You will gain hands-on experience in creating various tables and handling the tasks associated with it.

Topics:

• • Basics of SQL Select statement
  • Need for data retrieve 
  • The process to retrieve data using Select statement

Learning Outcome: You will gain hands-on experience in retrieving data from a person table using SQL select statements.

Topics: 

• • General functions in SQL
  • Using Null and COALESCE() function, 
  • Working with various data types and handling Null values 
  • Creating and executing a SQL query that applied to NUL, NUL1, NUL2 and COALESCE()

Learning Outcome: You will learn to use a NULL function to deal with Null values in data.

Topics:

• • Setting limits for retrieving a query
  • Sort rows 
  • Sort output at runtime
  • Ampersand substitution to restrict

Learning Outcome: You will understand the procedure to write different select queries and also gain hands-on exposure in sorting output at runtime and using ampersand substitution to restrict.

Topics:

• • Introduction to Functions in SQL 
  • Various functions available in SQL 
  • Understanding each function

Learning Outcome: You will gain hands-on experience by working with a table which includes all types of functions such as character, number, date, string etc.

Topics:

• • What are Large Object Functions
  • BFILENAME, EMPTY_CLOB, EMPTY_BLOB
  • Introduction to Aggregate or Group functions
  • COUNT(*),  COUNT, SUM, AVG, ,MIN, MAX,
  • HAVING Clause and Group BY Clause

Learning Outcome: You will gain hands-on experience by implementing each large object function in real-time.

Topics:

• • Introduction to OLAP Functions 
  • Model clause 
  • Grouping functions 
  • Roll up

Learning Outcome: You will gain a complete understanding of OLAP Functions and learn to use them.

Topics:

• • Overview of conversion functions 
  • Types of conversion functions available in SQL 
  • Introduction to conditional expressions 
  • Conditional statement in a select statement

Learning Outcome: You will gain hands-on experience in using the GROUP BY clause and HAVING clause.

Topics:

• • Introduction to subqueries
  • Problems that a subquery can solve 
  • Various types of Subqueries available

Learning outcome: You will gain hands-on experience in writing single-row and multiple-row subqueries.

Topics:

• • Joins, Inner Join, Outer Join
  • Equijoins and Non-equijoins
  • Left Join, Right Join

Learning outcome: You will understand the procedure to join different tables in SQL. Moreover, you will also gain hands-on expertise in writing SELECT statements to access data from more than one table, use self-join, outer joins, and Generate a Cartesian.

Topics:

• • Describe Data Manipulation Language (DML) statement,
  • Delete, Insert, Update statements
  • Control transactions

Learning outcome: You will understand the procedure to Insert rows into a table, Delete rows from a table, Update rows in a table.

Topics:

• • Introduction to set operators
  • MINUS Operators
  • UNION [ALL], INTERSECT

Learning outcome: You will learn to use set operators to join various queries into a single query.  

Topics:

• • Introduction to Database Transaction 
  • Various properties of transactions 
  • Learn to execute error-free modifications to database records with the help of Transactions

Learning outcome: You will gain hands-on experience in using Database transactions to execute error-free modification to database records.

Topics:

• • Create simple and complex views,
  • Build and use sequences 
  • Retrieve data from views, 
  •  Create private and public synonyms
  • Create and maintain indexes

Learning outcome: You will understand the procedure to work with multiple schema objects.

Topics:

• • Introduction to conditional statements
  • SQL Cursor, SQL Cursor Attributes.
  • IF Statement, Simple IF Statement,
  • PL/SQL flow of executions, and controlling methods
  • IF-THEN-ELSE Statement, 
  • IF-THEN-ELSEIF Statement
  • Nested Loops and Labels
  • Iterative controlling loop statement, 
  • Boolean Conditions, Logic Tables

Learning outcome: You will gain hands-on experience in using the above mentioned conditional statements in different situations.

Topics:

• • Introduction to Explicit Cursors 
  • Controlling explicit cursor, 
  • Functions of Explicit Cursors 
  • Fetching Data cursor
  • Opening the cursor
  • The cursor for loop using subqueries
  • Cursor and records

Learning outcome: You will gain hands-on experience in handling various functions associated with explicit cursors.

Topics:

• • Advance Explicit cursor
  • Where current of Clause 
  • For update Clause 
  • Cursor with parameters
  • Cursor with subqueries

Learning Outcome: You will gain hands-on experience in using a cursor with a subquery.

Topics:

• • Introduction to Handling Exception 
  • Using PL/SQL predefined Exceptions,
  • Function for trapping Exception,
  • Non-Predefined Error  
  • User-Defined Exceptions
  • Procedure to Raise Application Error
  • Trapping user-defined Exception

Learning Outcome: This module will give you real-time experience in using Predefined exception, Generate and handle an exception, Write user-defined exception, trapping an exception using a function.

Topics:

• •  Introduction to Sub-program 
  • Sub-programmes in PL/SQL 
  • Introdcuton to Procedure
  • Syntax for creating Procedure
  • Passing parameters 
  • Sub programming declaration

Learning Outcome: By the completion of this module you will gain practical knowledge to access a public variable from a stand-alone, PasS Parameter, and process to create parameterized procedures.

Topics:

• • Overview of PL/SQL records 
  • PL/SQL table creation 
  • Creating PL/SQL Table,
  • Packages – Objective, component, developing
  • Public and private construct,
  • Standards for deploying packages

Learning Outcome: Upon the completion of this module you will get practical experience in the creation and deployment of a package.