• $499.00 $399.00
  • 30 Days
760 STUDENTS ENROLLED

    Contact Information

    Email : training@learnfromguru.com
    Phone : 408 - 657 – 4159

    LearnFromGuru’s Oracle PL SQL Training, will introduce to the participants to explore the master  programmers who wants to learn how to  develop the software application.

    Participants will be offered guidance and will share lot of demo’s on each topic with real time examples

    Participants will also get to implement one project towards the end of the course.

    After the completion of the Oracle PL SQL Course at LearnFromGuru, you will be able to:

    • Perform effectively as entry level software professionals.
    • Writing different Queries to access the database.
    • Develop and maintain programs in the platform they have studied.
    • Contribute as members of a team.
    • Get a hands-on experience on the concepts learnt during the course.

    Introduction to DBMS & RDBMS

    • Evolution of DBMS
    • Need for DBMS, Features of RDBMS
    • E.F Codd’s Rules
    • OLTP and OLAP Databases
    • Database Environments
    • Overview of Entities and Entity Relationships

    Introduction to ORACLE Database

    • Client-Server Technology
    • Oracle Corporation Products
    • Oracle Versions and Differences
    • Help students with installing Oracle DB/Client software
    • Check if all students can access DB

    Introduction to Client Tools

    • About SQL and SQL*PLUS
    • Creating Users and Granting Appropriate Privileges
    • Overview of Schema and Schema Objects.

    Languages of SQL

    • Data Definition Language (DDL) Statements
    • Data Retrieval Language (DRL) Statements
    • Data Manipulation Language (DML) Statements
    • Transaction Control Language (TCL) Statements
    • Data Control Language (DCL) Statement
    • Oracle Built -in Data types

    Introduction to Tables

    • Overview of Database Tables and Columns
    • Creating Database Tables and Columns
    • Creating Database Table From another table
    • Viewing Definitions of Tables
    • Inserting Data into Database Tables using INSERT Statement,

    Retrieving Data Using SQL SELECT Statement

    • Retrieving Data using SQL SELECT statement
    • Writing Select Statements
    • Selecting All Columns From a Table
    • Selecting Some Columns From a Table
    • Performing Arithmetic Equations
    • Handling NULL Values
    • Renaming Columns with Column Aliases
    • Putting Columns Together With Concatenation Operator
    • Editing SQL Queries Within SQL*PLUS
    • Using Runtime Variables Entering Values at Runtime
    • Introduction to DUAL Table and Pseudocolumns.

    SQL OPERATORS

    • Arithmetic
    • Relational
    • Logical and Special Operators.

    Restricting and Sorting Data

    • Using the WHERE clause to retrieve specific rows
    • Sorting Rows using ORDER BY Clause
    • Sorting Rows by Multiple Columns.

    Altering TABLES

    • Altering Column Definitions
    • Altering Table Definitions
    • Deleting Data From Table Using Truncate Command
    • Dropping Tables.

    Modifying Database Tables Data Using DML Commands

    • Modifying Rows Using UPDATE Statement
    • Updating More than One Column
    • Updating Multiple Rows and Multiple Columns
    • Using Function in Set Clause
    • Update Statement Containing a Sub query
    • Set Value based on a Select Statement
    • Removing Rows Using DELETE Statement
    • Deleting All Rows from a Table
    • Deleting Rows Using Where Clause
    • Delete Statement Containing a Sub query
    • TRUNCATE Command.

    Data Integrity Through Constraints

    • Overview of Integrity Constraints
    • Enforcing Referential Integrity through Constraints
    • NOT NULL Constraint
    • UNIQUE constraint
    • PRIMARY KEY constraint
    • FOREIGN KEY constraint
    • CHECK constraint
    • Adding Constraints to a table
    • Altering Integrity Constraints
    • Dropping Integrity Constraints
    • Enabling/ Disabling Constraints
    • ON DELETE clause in Referential Integrity Constraint

    Aggregating Data using GROUP Functions

    • Introduction to GROUP Functions
    • Using GROUP Functions In Select Statements
    • Using the GROUP BY Clause
    • Dividing Query Output Into Groups
    • Using Multiple Columns In GROUP BY Clause
    • Using GROUP Functions In ORDER BY Clause
    • Excluding GROUP Data using HAVING Clause
    • Counting Duplicate Values In a Column

    Working with DCL, TCL Commands

    • Commit, Rollback
    • Savepoint
    • Grant
    • Revoke
    • SQL *PLUS Editor and Environment commands.

    Displaying Data From Multiple Tables

    • Cartesian Product
    • Equijoins
    • Non-equijoins
    • Inner Joins
    • Outer Joins (Left, Right, Full)
    • Self Joins

    Set Operations

    • When to Use Set Operators
    • UNION ALL
    • UNION
    • INTERSECT
    • MINUS.

    Subqueries

    • Purpose of a Subquery
    • Single Row Subquery
    • Correlated Subquery
    • Multiple Row Subquery
    • Nested Sub Query Multi Column Sub Query
    • Using IN/ANY/SOME
    • ALL Operators in Sub Queries
    • Using EXISTS and NOT exists operators.

    Normalisation

    • Introduction to Normalisation
    • Normal Forms with Examples.

    Introductions to Views

    • Overview of Views
    • Types of Views, Creating a View
    • Creating Simple Views
    • Choosing Specific Columns From Base Table
    • Creating a View with CHECK OPTION Constraint
    • Creating a View with READ ONLY Constraint
    • Creating a View based on User-defined Function
    • Creating a View By Joining Two Tables, IN LINE Views, Modifying a View
    • Dropping Views, Overview of Materialized Views
    • Creating Materialized Views
    • Refreshing Materialized Views
    • Dropping Materialized Views
    • Viewing Definition of Views.

    Introductions to Sequences

    • Overview of Sequence
    • Creating a Simple Sequence
    • Getting Next Value for Sequence
    • Populating Table’s Column with a Sequence
    • Creating a Sequence with other than Default Starting Value
    • Getting Information On Sequences
    • Altering and Dropping Sequences
    • Viewing Definition of Sequences.

    Introductions to Synonyms

    • Overview of Synonyms
    • Creating Private and Public Synonyms
    • Viewing Definition of Synonyms.

    Accessing Remote Data

    • Overview of Database Links
    • Creating Private and Public Database Links
    • Viewing Definition of Database Links

    Introductions to Indexes and Clusters

    • Overview of Table Index
    • Creating a Table Index
    • Index Associated with a Constraint
    • Viewing Table Columns Used In an Index
    • Dropping an Index
    • Listing All Indexes in a Schema.

    Oracle Database Locks

    • Row Level Locks
    • Table Level Locks
    • Shared, Exclusive and Dead Locks

    PL/SQL Concepts:

    Introduction to PL/SQL

    • What is PL/SQL
    • Advantages of PL/SQL.

    PL/SQL Language Fundamentals

    • Comments
    • Block Structure
    • Components of a PL/SQL block,
    • Defining an Anonymous Block.

    Declaring and Using Variables

    • Variable Names
    • Variable Data types
    • Database Types
    • Non-Database Types
    • Collections
    • %TYPE and %ROWTYPE
    • Constant Declaration
    • Variable Value Assignment.

    Operators

    • Arithmetic Operators
    • Comparison Operators
    • Relational Operators
    • Logical Operators
    • String Operators.

    Conditional and Sequential Control

    • Conditional Control Statements
    • Sequential Control Statements.

    Loops

    • Loop Basics
    • Numeric FOR Loop
    • Cursor FOR Loop
    • WHILE Loop.

    Cursors

    • Cursor Basics
    • Implicit and Explicit Cursors
    • Declaring
    • Opening and Fetching from Cursors
    • Column Aliases In Cursors
    • Closing Cursors
    • Cursor Attributes
    • Cursor Parameters,
    • SELECT FOR UPDATE in Cursor
    • Ref Cursors.

    Records in PL/SQL

    • Record Basics
    • Assigning Values to and From Records
    • Nested Records.

    PL/SQL Tables

    • Characteristics of PL/SQL Tables
    • Declaring a PL/SQL Table
    • Referencing and Modifying PL/SQL Table Rows
    • Clearing the PL/SQL Table.

    SQL Operations in PL/SQL

    • SQL Support in PL/SQL
    • Data Manipulation with PL/SQL
    • Transaction Control
    • Querying Data with PL/SQL.

    ADVANCED PL/SQL Concepts

    Exception Handlers

    • Overview of PL/SQL Runtime Error Handling
    • Guidelines for Avoiding and Handling PL/SQL Errors and Exceptions
    • Advantages of Exception Handling
    • Exception Section
    • Types of Exceptions
    • Pre-defined Exceptions
    • User-defined Exceptions
    • EXCEPTION_INIT Pragma
    • Raising an Exception
    • Client-Server Error Communication
    • RAISE_APPLICATION_ERROR
    • Propagation of PL/SQL Exceptions.

    PL/SQL Sub Programs

    • Overview of Subprograms
    • Advantages of Subprograms.

    Stored Procedures in PL/SQL

    • Overview of Stored Procedures
    • Passing Parameters to Stored Procedures
    • Formal and Actual Parameters
    • Positional and Named Parameters
    • Parameter Modes
    • In Out
    • Default Values for Parameters
    • Overloading Stored Procedures
    • Autonomous Transactions
    • Granting Privileges to Subprograms
    • Dropping a Stored Procedure.

    Functions in PL/SQL

    • Differences between Stored Procedure and Function
    • User Defined Function
    • Nested Functions
    • Using Stored Function in SQL statement.

    PL/SQL Packages

    • Introduction to PL/SQL Packages
    • Creating and Using PL/SQL Packages
    • Package Specification
    • Package Body

    Triggers

    • Introduction to Triggers
    • Types of Triggers
    • Row-Level Triggers
    • Statement-Level Triggers
    • BEFORE and AFTER Triggers
    • Insert Triggers
    • Before Insert Trigger
    • After Insert Trigger
    • Update Triggers
    • Before Update Trigger
    • After Update Trigger
    • Delete Triggers
    • Before Delete Trigger
    • After Delete Trigger
    • DML Event Triggers
    • Combing DML Trigger Types
    • Setting Inserted Values
    • Customizing Error Conditions
    • Calling Procedures within Triggers
    • DDL Event Triggers
    • Database Event Triggers
    • INSTEAD OF Triggers
    • MUTATING Triggers
    • Enabling and Disabling Triggers
    • Dropping Triggers

    Important PL/SQL Features (Used in Real Time , Please add if Needed)

    • Using UTL_FILE package
    • Using SQL*Loader to Load Data
    • System Catalog Tables (v$ tables)
    • Debugging PL/SQL programs/issues

    UNIX Concepts

    Introduction to UNIX

    • Basic Unix Commands needed for PL/SQL interaction PERFORMANCE TUNING

    Introduction to Performance Tuning

    • Explain Plan
    • Checking Indexes
    • Gather Stats
    • Basics of Partitioning

    Course Reviews

    N.A

    ratings
    • 1 stars0
    • 2 stars0
    • 3 stars0
    • 4 stars0
    • 5 stars0

    No Reviews found for this course.

    Login

    Register

    Create an Account
    Create an Account Back to login/register
    X