Oracle PL-SQL Training Course

  Lecture-1 Introduction to Oracle SQL
Live Lecture 
·         What is RDBMS?

·         Oracle versions

·         The architecture of Oracle Database Server

·         Installation of Oracle 12c         

·        Install Oracle 12c

  Lecture-2 Using D.D.L. Statements to Create and Manage Tables
Live Lecture 
·         Categorize the main database objects

·         Review the table structure, List the data types that are

       available for columns

·         Create a simple table

·         Explain how constraints are created at the time of table

       creation

·         Describe how schema objects work

·         Create a database table Person with two column (Name, Age)

       with a constraint on age not greater than 100, Insert records

       using an insert query

  Lecture-3 Retrieving Data Using the SQL SELECT Statement
Live Lecture 
·         List the capabilities of SQL SELECT statements

·         Execute a basic SELECT statement      

·         Use a basic select statement to retrieve all the records in the

       Person table

  Lecture-4 Restricting and Sorting Data
Live Lecture 
·         Limit the rows that are retrieved by a query 

·         Write a select query to retrieve records where age is more

        than  Sort the rows that are retrieved by a query

·         Ampersand substitution to restrict and sort output at runtime

       60 yrs,

·         Write a select query to sort the records by name

·         Write a select query to sort the records by age in descending

       order

·         Use ampersand substitution to restrict and sort output at

       runtime

  Lecture-5 General Functions
Live Lecture 
·         The general functions in SQL

·         Working with any data type and handling Null values, using

       COALESCE() and Null function

·         Constructing and executing SQL query that applies the NUL,

       NUL1, NUL2 and COALESCE()       

·         Use the NULL function to deal with null values in data

  Lecture-6 Using Single-Row Functions to Customize Output
Live Lecture 
•        Describe various types of functions available in SQL

•        Create a table with columns of type char, number, and date

•        Use character, number, and date functions in SELECT statements

  Lecture-7 Large Object Functions
Live Lecture 
·         Describe various types of functions (character, number,

       date, string etc.) available in SQL         

·         Create a table with columns of type char, number, and date

·         Use character, number, and date functions in SELECT

       statements

  Lecture-8 O.L.A.P. Functions
Live Lecture 
·         The various OLAP functions, cube, model clause, roll up

       and grouping functions 

·         Working with OLAP commands

·         Cube, Roll Up, etc.

  Lecture-9 Conversion Functions & Conditional Expressions
Live Lecture 
·         Describe various types of conversion functions that are

       available in SQL

·         Conditional expressions in a SELECT statement        

·         Group data by using the GROUP BY clause, Include or

       exclude grouped rows by using the HAVING clause

  Lecture-10 Displaying Data from Multiple Tables
Live Lecture 
·         Joins, Inner Join, Outer Join, Left Join, Right Join, Equijoins

       and Non-equijoins       

·         Write SELECT statements to access data from more than

       one table using equijoins and non-equijoins,

·         Join a table to itself by using a self-join,

·         View data that generally does not meet a join condition by

       using outer joins,

·        Generate a Cartesian product of all rows from two or more

      tables

  Lecture-11 Using Sub queries to Solve Queries
Live Lecture 
·         Define subqueries

·         Describe the types of problems that the subqueries can solve

·         List the types of subqueries     

·        Write single-row and multiple- row subqueries

  Lecture-12 Using the set of Operators
Live Lecture 
·         Describe set operators, UNION [ALL], INTERSECT,

       MINUS Operators        

·         Use a set operator to combine multiple queries into a single

       query Control the order of rows returned

  Lecture-13 Manipulating Data using SQL
Live Lecture 
·        Describe data manipulation language (DML) statement,

      Insert, Update, Delete Statements, Control transactions       

·        Insert rows into a table, Update rows in a table, Delete rows

      from a table

  Lecture-14 Database Transactions
Live Lecture 
·         What is a database transaction?

·         Properties of a transaction (Atomic, Consistent, Isolated,

       Durable – ACID)

·         Avoiding error/fault in manipulating database records

       using transaction

·        Begin a transaction, Execute queries to update or insert or

      delete records, If no error, commit the transaction Else to

       roll back the transaction and end it

  Lecture-15 Creating Other Schema Objects
Live Lecture 
·         Views – simple & complex, Sequences, Index, Synonym   

·         Create simple and complex views,

·         Retrieve data from views,

·         Create, maintain, and use sequences,

·         Create and maintain indexes, C

·        Create private and public synonyms

  Lecture-16 Writing Cursor and Conditional Statement
Live Lecture 
·         SQL Cursor, SQL Cursor Attributes

·         Controlling PL/SQL flow of executions

·         IF Statement, Simple IF Statement,

·         IF-THAN-ELSE Statement Execution Flow,

·         IF-THAN-ELSE Statement,

·         IF-THAN-ELSIF Statement

·         Logic Tables, Boolean Conditions

·         Iterative controlling loop statement,

·         Nested Loops and Labels         

·         Use Boolean condition on a select query,

·         Use logic table,

·         Create nested loops and labels

  Lecture-17 Introduction to Explicit Cursor
Live Lecture 
·         Writing Explicit cursors,

·         Explicit cursor functions,

·         Controlling explicit cursor,

·         Opening the cursor

·         Fetching Data cursor,

·         Cursor, and records,

·         Cursor for loop using sub queries       

·         Write an explicit cursor,

·         Use cursor function,

·         Fetch data cursor

  Lecture-18 Advance Concept of explicit cursor
Live Lecture 
·         Advance Explicit cursor

·         A cursor with parameters,

·         For update Clause

·         Where current of Clause,

·         Cursor with sub queries           

·         Use cursor with a subquery,

·         Use Where Current clause to retrieve data

·         Exception Handling

·         Handling Exception,

·         Handling Exception with PL/SQL

·         Predefined Exceptions

·         User Defined Exceptions,

·         Non-Predefined Error,

·         Function for trapping Exception

·         Trapping user-defined Exception

·         Raise Application Error Procedure     

·         Use Predefined exception,

·         Write user-defined exception,

·         Generate and handle the exception,

·         Use a function for trapping an exception

  Lecture-19 Subprogram, Procedure & passing parameters
Live Lecture 
·         Overview of subprograms,

·         PL/SQL Subprograms

·         What is Procedure?

·         The syntax for creating Procedure

·         Creating Procedure with a parameter,

·         Example of Passing parameters

·         Referencing a public variable from a standalone procedure

·         Declaring Subprogram 

·         Create a parameterized procedure,

·         Pass parameters in a procedure call

·        Access a public variable from a standalone procedure

  Lecture-20 Creating PL/SQL Package
Live Lecture 
·         PL/SQL records,

·         Using Pl/SQL Table method and example

·         Creating PL/SQL Table

·         Packages – Objective,

·         Overview,

·         Component,

·         Developing,

·         Removing,

·         Advantages

·         Creating the package specification/example

·         Declaring Public construct,

·         Public and private construct,

·         Invoking package construct

·         Guide lines for deploying packages     

·         Create a package,

·        Deploy the created package

  Lecture-21 Advance Package Concepts and functions
Live Lecture 
•        User define package function,

•        Persistent state of package function,

•        Persistent   state of a package variable·         Overloading,

·         Using forward declaration,

·         One time only procedure,

·         Package functions

·         User define package function,

·         Persistent state of package function,

·         Persistent state of a package

·         variable

·         Controlling the persistent state of package cursor

·         Purity end,

·         Using supplied package

·         Using native dynamic SQL,

·          Execution flow

·         Using DBMS-SQL package,

·         Using DBMS-DDL package,

·         Submitting jobs

·         Interacting with operating system links         

·         Use supplied package,

·         Use native dynamic SQL,

·         Use DBMS- SQL package

·         Use DBMS-DDL package,

•        Controlling the persistent state of package cursor

•        Purity end, Using supplied package

•        Using native dynamic SQL, Execution flow

•        Using DBMS-SQL package,

•        Using DBMS-DDL package, Submitting jobs

•        Interacting with operating system links       

•        Use supplied package,

•        Use native dynamic SQL,

•        Use DBMS- SQL package

•        Use DBMS-DDL package

•        Submit a job

  Lecture-22 Introduction and writing Triggers
Live Lecture 
·         Triggers – Definition,

·         objective and its event type,

·         Application & database triggers

·         Business application scenarios for implementing triggers

·         Define DML triggers,

·         Define Non – DML triggers,

·         Triggers event type & body

·         Creating DML triggers using the create triggers statement

·         Define statement level triggers v/s low-level triggers

·         Triggers firing sequence: single row manipulation

·         Creating a DML statement triggers

·         Using old and new qualifiers

·         Old and new qualifiers,

·          Instead of triggers

·         Managing triggers using the alter & drop SQL

·         statement,

·         Testing triggers 

·         Create a DML statement trigger,

·         Use old and new qualifiers,

·         Manage a trigger using the alter & drop SQL statement,

·         Test the created triggers

  Lecture-23 Compound Triggers
Live Lecture 
·         Viewing trigger information

·         Describe user triggers

·         What is a compound trigger & working with it?

·         Compound trigger restrictions,

·         Using a compound trigger to resolve the mutating table

       error

·         Creating triggers on system events,

·         LOGON and LOGOFF triggers example

·         Call statements in triggers

·         Benefits of database-event triggers

·         System privileges required to manage triggers         

·         View a trigger’s information, Use a compound trigger

       structure for views

·         Use a compound trigger to

·         resolve the mutating table error

·         Create triggers on system events, 

·         Use LOGON and LOGOFF triggers,

·         Call statements in triggers

  Lecture-24 Working with Dynamic SQL
Live Lecture 
·         Dynamic SQL-objectives

·         Describe execution flow of SQL statements

·         Dynamic SQL with a DDL statement-example

·         Working with dynamic SQL

·         Native Dynamic SQL(NDS),

·         Using the executive immediate statement-example,

·         Using native dynamic

·         SQL to compile PL/SQL code,

·         Using DBMS-SQL with a DML statement,

·         Using the DBMS-SQL package subprograms,

·         Parameterized DML statement

·         Use the executive immediate statement

·         Use native dynamic SQL to

·         compile PL/SQL code

·         Create DBMS-SQL with a DML statement,

·         Create a DBMS-SQL package subprograms

  Lecture-25 Advance Level Scripting
Live Lecture 
·         Managing Dependencies,

·         Objectives,

·         Overview of schema object dependencies,

·         Direct local dependencies

·         Querying direct object dependencies,

·         Displaying direct and indirect dependencies

·         Fine-Grained dependency management,

·         Changes to synonym dependencies

·         Maintaining valid PL/SQL program units and views,

·         Object re-validation

·         Concepts of remote dependencies      

·         Query direct object dependencies

·         Display direct and indirect

·         dependencies, Set the remote dependencies mode

       parameter

·         Recompile PL/SQL program unit,

·         Edit a procedure and recompile it

·         Setting the remote dependencies mode parameter

·        Recompiling PL/SQL program unit