Oracle DBA Training Institute

Introduction to DBMS

• Approach to Data Management
• Introduction to prerequisites, File Settings and File-system
• Review of Database Management Terminology
• Database Models
• Hierarchal Model
• Network Model
• Relational Model

Introduction to RDBMS

• Features & Advantages of RDBMS and DBMS
• The 12 rules (E.F Codd's Rules -RDBMS)
• Need for Database Design
• Support of Normalization Process for Data Management
• Client-Server Technology
• Oracle Versions
• About SQL&SQL*PLUS

Introduction To ORACLE

• Difference between Oracle Old and latest versions
• Installation of Oracle Software
• About SQL*Plus and use of developer tool
• Data types in Oracle
• Operators in Oracle
• Understanding Schema design and objects

ORACLE Sub Language Commands

• Data Definition Language (DDL)
• Data Retrieval Language (DRL)
• Data Manipulation Language (DML)
• Transaction Control Language (TCL)
• Database Security and Privileges (DCL)

Working With DDL Commands in ORACLE

• Table creation using CREATE statement
• Creating table from another table
• Dropping a table using DROP command
• Altering the column of a table
• Renaming an entire table
• Using truncate command
• Difference between Delete and Truncate command

Working With DML Commands in ORACLE

• Table creation using CREATE statement
• Creating table from another table
• Dropping a table using DROP command
• Altering the column of a table
• Renaming an entire table
• Using truncate command
• Difference between Delete and Truncate command

Working with DCL, TCL Commands in ORACLE

• Grant, Revoke
• Page No
• Commit, Rollback, Save point
• SQL Editor Commands
• SQL Environment settings

Managing Users and Securing Database

• Establishing Security Policy on a Database
• Privileges and Roles
• Managing oracle database users
• How to create a new database user
• Auditing Database activities
• Predefined oracle database accounts

Introduction to SQL Database Object in ORACLE

• Oracle Pre Defined Datatypes
• DQL-SELECT Statements using WHERE Clause
• Comparison and Conditional Operators Arithmetic and Logical Operators
• Special Operators - IN (NOT IN), BETWEEN (NOT BETWEEN), LIKE (NOT LIKE), IS NULL (IS NOT NULL)
• Working with DML, DRL Commands.

Working with Integrity Constraints in ORACLE

• Importance of Data Integrity
• How to add constraints to an existing table?
• NOT NULL constraint
• UNIQUE constraint + PRIMARY KEY constraint
• FOREIGN KEY constraint
• CHECK constraint
• Applying DEFAULT option to columns
• How to enable and disable constraints?
• How to get information about constraints?

Data Retrieval Techniques in ORACLE

• How to use select statement in different ways to retrieve records?
• Working with Column and table alias
• Data filtering and sorting with in single table
• Clauses and its types in oracle
• Where clause & Having clause & From clause
• Group by clause, Order by clause
• Constraint clause

Built-in Functions in ORACLE

• How to use single row functions using dummy table?
• Character Functions and String functions
• Date functions
• Mathematical functions
• Conversion functions
• Aggregate Functions
• Special functions
• Analytical functions

Set Operators in ORACLE

• Working with set operator types
• UNION and UNION ALL
• INTERSECT
• MINUS

Pseudo Columns in Oracle

• Understanding Pseudo Columns in Oracle
• Types of Pseudo Columns in Oracle
• CURRVAL and NEXTVAL
• LEVEL
• ROWID
• ROWNUM

Functions in PL/SQL in ORACLE

• Difference between Procedures and Functions
• How to create functions?
• How to use inline functions?
• Nested Functions
• Using stored function in SQL statements

Querying Multiple Tables (Joins) in ORACLE

• Understanding joins and its uses
• Types of joins
• Simple Join / Equi-join / Inner Join / Outer join
• Left & Right outer join and Full outer join
• Cross join

Sub Queries in ORACLE

• Importance of sub queries
• Using different types of sub queries
• Single row sub queries and Multi row sub queries
• Nested queries
• Multi column sub queries
• IN, ANY/SOME, ALL Operators in Sub Queries
• Handling Data Retrieval with EXISTS and NOT EXISTS Operators

VIEWS in Oracle

• Types of VIEWS
• Relational Views and Object Views
• Practical approach of SIMPLE VIEWS and COMPLEX VIEWS
• Column definitions in VIEWS
• Using VIEWS for DML Operations
• Putting CHECK Constraint upon VIEWS
• Creation of READ ONLY VIEWS
• Understanding the IN LINE VIEWS
• About Materialized Views
• View Triggers

Triggers in PL/SQL in ORACLE

• How to create triggers?
• Benefits of trigger
• Using DML trigger & DDL trigger
• How to audit database using triggers?
• What are database level trigger?

Creation Of Stored Procedures in ORACLE

• Creating Procedures in PL/SQL
• IN parameter / OUT parameter / INOUT parameter
• Procedure with Cursors
• Dropping a Procedure

Creating and Using Cursors in ORACLE

• How to create cursors in PL/SQL
• How to create explicit cursor?
• What are cursor parameters?
• How to use for update clause?
• How to use implicit cursors?

Introduction to PL/SQL (Procedural Language Extension to SQL)

• Introduction & advantages of PL/SQL
• Datatypes, Variable and Constants in PL/SQL
• Conditional and Unconditional Statements
• Simple if, if... else, nested if..else, if..else Ladder
• Selection Case, Simple Case, GOTO Label and EXIT
• Simple LOOP, WHILE LOOP, FOR LOOP and NESTED LOOPS
• SQL within PL/SQL

Implementing OOPS Technology in ORACLE

• What is Object Technology?
• Creation of classes and objects
• Inserting rows in a table using Objects
• Retrieving data from Object-based Tables
• Calling a Method

Design Of Schema Objects in ORACLE

• Creating and working with Views
• Working with Synonyms
• Creating Index and clusters
• Working with in materialized view
• Understanding sequences and its types

File Input/Output in ORACLE

• PL/SQL file I/O (Input/Output)
• SQL* Loader Architecture
• .txt to base table

Procedure in Oracle

• Create procedure
• Declare Values & Variables in Procedure.
• Call & Invoke procedure

Creating & Using Packages in ORACLE

• What is a Package?
• Reasons to use packages
• What is package specification?
• What is package body?
• How to initialize instantiated package?
• Creating PACKAGE Specification and PACKAGE Body

Collections In PL/SQL in ORACLE

• What is collection?
• How to use arrays?
• Using nested tables
• How to use index by value?
• Listing types of collection methods.

Exception Handling in ORACLE

• What is an Exception and Types of exceptions?
• Handling system defined exceptions
• Handling user defined exceptions?
• RAISE_APPLICATION_ERROR
• SQL Error Code Values

Advanced Features in ORACLE LATEST VERSIONS

• Introduction to Oracle Database Architecture & its Type
• Merge statement
• Temporary Tables/Global Tables
• New Function EXTRACT()
• About Flash Back Queries
• New data types, Flash back Command
• Regular expressions, DML Error Logging
• Types of Table spaces

Security and Locks in ORACLE LATESET VERSIONS.

• Row level Locks
• Table Level Locks
• Use of commit and rollback
• Use of save-point and set transaction
• How to give system privileges to an user?
• How to invoke and revoke object privileges?
• How to create users and roles?