Oracle Online Training

Duration of the Course

• 50 Hrs

Oracle Online Training Course Overview

SQL

• Introduction to Computer Fundamentals

• Introduction to File and advantages & disadvantages

• Introduction to DBMS

• Database Models           

  • Hierarchical Model
  • Network Model
  • Relational Model
  • Introduction to RDBMS
  • Oracle Architecture
  • Client/Server Architecture
  •  Oracle Versions
  • Datatypes
  • Introduction to SQL

  • SQL Sublanguages

    • DDL(Data Definition Language)
      • CREATE, ALTER, RENAME,TRUNCATE,DROP
• DML(Data Manipulation Language)
  • INSERT, UPDATE, DELETE
• DRL(Data Retrieval Language)
  • SELECT
• DCL(Data Control Language)
  • GRANT, REVOKE
• TCL(Transaction Control Language)
  • COMMIT, SAVEPOINT, ROLLBACK

  • Operators

    • Arithmetic operators
    • Assignment operator
    • Relational Operators
    • Logical Operators
    • Special Operators(IN, NOT IN, BETWEEN, NOT BETWEEN, LIKE, NOT LIKE, IS NULL, IS NOT NULL)

• Set operators (UNION, UNION ALL, INTERSECT, MINUS)
• Built-in FunctionsGroup Functions
  • Column Level Functions
    • Number Functions
    • Character Functions
    • Date Functions
• Conversion Functions
• General Functions

• Group by clause

• Having clause

• Order by clause

• Querying Multiple Tables(Joins) Indexes

  • What is Sub-Query & Advantages
  • Equi Join/simple Join/inner Join
  • Non-equi Join
  • Cartesian Product
  • Outer Join
  • Self Join

  • Sub-Queries

    • Simple Sub-Queries/Single Row Sub-Queries
    • Multi-Row Sub-Queries
    • Multi-Column Sub-Queries
    • Co-Related Sub-Queries
    • Scalar Sub-Queries
    • Applying Group Functions in Sub-Queries

    • Constraints in Oracle

      • NOT NULL
      • UNIQUE
      • CHECK
      • DEFAULT
      • PRIMARY KEY
      • FOREIGN KEY(References)
        • ON DELETE CASCADE
        • ON DELETE SET NULL
        • Composite Primary Key
        • Adding Constraints in Column Level and Table Level
        • How to Enable/Disable Constraints
        • How to Rename Constraints
        • Database Objects
          • VIEWS
            • Simple Views
            • Complex Views
            • Read only vies
            • Force Views
            • Granting Views
• Sequences
• Synonyms
• Roles

• Object TechnologyRename column

  • Advantages of objects
  • How to Create Objects  in oracle
  • Creating tables using Objects
  • Objects with Methods
  • Hierarchical Queries
  • Oracle pseudo Columns
    • ROWID
    • ROWNUM
    • NEXTVAL
    • CURRVAL
    • LEVEL
    • Locking Technology in Oracle
      • Row Level Locks
      • Table Level Locks
      • Partitions
        • Range Partition
        • List Partition
        • Hash Partitions
        • Oracle 9i Features
          • 9i Joins
            • Inner Join
            • Left Outer Join
            • Right Outer Join
            • Full Outer Join
• Timestamp Data type
• Insert all Command
• Merge Statement
• NVLS(),NULLIF(),COALESCE()
• CASE Expression of Select Command
• Temporary Tables/Global Tables
• Returning Into Clause
• Bulk Collect
• Dynamic SQL
• Flashback Queries
• Purge Command, Recycle bin
• Read-only Tables
• Data Pump(Import and Export)

PL/SQL

• Introduction Programming Languages

• Introduction to Pl/SQL 

• Advantages of PL/SQL

• PL/SQL Data types

• Variables and Constants

• PL/SQL Blocks
  • Anonymous Blocks
  • Named Blocks
  • Using Built-in Functions
  • Condition and Unconditional Statements
    • Simple if.   If..else,   Nested if,    if…else ladder
    • Selection Case, Simple Case
    • GOTO Label and EXIT
    • Iteration Control Statements
      • Simple LOOP
      • While LOOP
      • For LOOP
      • Nested LOOPS
      • SQL within PL/SQL
      • Composite Data types
      • Cursor Management in PL/SQL
        • Implicit Cursors
        • Explicit Cursors
        • Cursor Attributes
        • Cursor with Parameters
        • Cursors with LOOPS
        • Nested Cursors
        • Cursors with Sub-Queries
        • Ref.Cursors(Dynamic Static Cursor)
        • PL/SQL Records and PL/SQL Table Types

Advanced PL/SQL

• Procedures in PL/SQLDDL Triggers

  • Stored Procedures
  • Procedure with Parameters(IN, OUT and IN OUT)
  • POSITIONAL Notation and NAMED Notation
  • Procedure with Cursors
  • Dropping Procedure
  • Function in PL/SQL
    • Differences between Procedures and Functions
    • User Defined Functions
    • Nested Functions
    • Using Stored Functions in SQL Statements
    • Dropping Function
    • Exception in PL/SQL
      • Types of Exceptions
        • User Defined Exceptions
        • Pre-Defined Exceptions
        • Non-Pre-Defined Exceptions
        • RAISE_APPLICATION_ERROR
        • SQL Error Code Values
        • Packages in PL/SQL
          • Creating PACKAGE Specification and PACKAGE Body
          • Private and Public Variables in PACKAGE
          • Database Triggers in PL/SQL
            • Types of Triggers
              • Row level Triggers
              • Table Level Triggers
• Trigger Auditing
• Dropping Triggers

 

• Discuss Bank Application Program(using triggers, procedures, functions and Packages)  

• SQL * Loader     

• DBA Concepts

  • Creating Table Spaces
  • Creating Databases
  • Import & Export(Data Pumping)
  • Oracle 9i,10g & 11g features.