Oracle Training 

offered by Naresh i Technologies

Approach to Data Management

Introduction to prerequisites

File and Filesystem

Disadvantages of file

Review of Database Management Terminology

Database Models

Feature of RDBMS

Advantages of RDBMS over FMS ad 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 Corporation Products

Oracle Versions

About SQL&SQL*PLUS

Data Definition Language (DDL)

Data Retrieval Language (DRL)

Data Manipulation Language (DML)

Transaction Control Language (TCL)

Database Security and Privileges (DCL)

Oracle Pre Defined Datatypes

DDL Commands

Create, Alter (add, modify, rename, drop)Columns, Rename, truncate, drop

DML-Insert, update, delete

DQL-SELECT Statements using WHEREclause

Comparison and Conditional Operators

Arithmetic and Logical Operators

Set Operators (UNION, UNION ALL, INTERSECT, MINUS)

Special Operators – IN (NOT IN), BETWEEN (NOT BETWEEN), LIKE (NOT LIKE), IS NULL (IS NOT NULL)

Working with DML, DRL Commands

Operators Support

Arithmetic Functions, Character Functions, Date Functions, Conversion Functions

Aggregate Functions, OLAP Functions & General Functions

Using Group by and Having Clause of DRL Statement

Using Order by clause

Importance of Data Integrity

Support of Integrity Constraints for Relating Table in RDBMS

Working with different types of Integrity Constraints

Understanding ON DELETE clause in referential integrity constraint

Working with a composite constraint

Applying DEFAULT option to columns

Working with multiple constraints upon a column

Adding constraints to a table

Dropping of constraints

Enabling / Disable constraints

Querying for constraints information

Equi Join/Inner Join/Simple Join

Cartesian Join

Non-Equi Join

Outer Joins

Self Join

Understanding the practical approach to Sub Queries/Nested Select/Sub Select/Inner Select/Outer Select

What is the purpose of a Sub Query?

Sub Query Principle and Usage

Type of Sub Queries

Single Row

Multiple Row

Multiple Column

Applying Group Functions in Sub Queries

The impact of Having Clause in Sub Queries

IN, ANY/SOME, ALL Operators in Sub Queries

PAIRWISE and NON PAIRWISE Comparison in Sub Queries

Be … Aware of NULL’s

Correlated Sub Queries

Handling Data Retrieval with EXISTS and NOT EXISTS Operators

Grant, Revoke

Commit, Rollback, Savepoint

SQL Editor Commands

SQL Environment settings

Understanding the Standards of VIEWS in Oracle

Types of VIEWS

Relational Views

Object Views

Prerequisites to work with views

Practical approach of SIMPLE VIEWS and COMPLEX VIEWS

Column definitions in VIEWS

Using VIEWS for DML Operations

In-Line View

Forced Views

Putting CHECK Constraint upon VIEWS

Creation of READ ONLY VIEWS

Understanding the IN LINE VIEWS

About Materialized Views

View Triggers

Understanding Pseudo Columns in Oracle

Types of Pseudo Columns in Oracle

CURRVAL and NEXTVAL

LEVEL

ROWID

ROWNUM

Types of Partitions

Range Partitions

Hash Partitions

List Partition

Composite Partition

Parallel Query Process

Row level Locks

Table Level Locks

Shared Lock

Exclusive Lock

Dead Lock

SQL * Loader Architecture

Data file (Input Datafiles)

Control file

Bad file

Discard file

Log file

.txt to base table

.csv to base table

From more than one file to single table

Introduction to Programming Languages

Introduction to PL/SQL

The Advantages of PL/SQL

PL/SQL Architecture

PL/SQL Datatypes

Variable and Constants

Using Built_in Functions

Conditional and Unconditional Statements

Simple if, if… else, nested if..else, if..else Ladder

Selection Case, Simple Case, GOTO Label and EXIT

Iterations in PL/SQL

Record and PL/SQL Table Types

Procedures in PL/SQL

Functions in PL/SQL

Packages in PL/SQL

Pre Defined Exceptions

RAISE_APPLICATION_ERROR

PRAGMA_AUTONOMOUS_TRANSACTION

SQL Error Code Values

Types of Triggers

Row Level Triggers

Statement Level Triggers

DDL Triggers

Trigger Auditing

PL/SQL file I/O (Input/Output)

Using UTL_FILE Package

What is Object Technology ?

OOPS-Object Instances

Creation of objects

Creating User-Defined Data Types

Creating Object Tables

Inserting rows in a table using Objects

Retrieving data from Object-based Tables

Calling a Method

Indexing Abstract Data type Attributes

Large Objects (LOBS)

Creating Tables-LOB

Working with LOB values

Inserting, Updating & Deleting Values in LOBs

Populating Lobis DBMS_LOB Routines

Using B-FILE

Advantages of collection

Ref cursor (Dynamic Cursor)

Weak ref cursor

Strong ref cursor

Nested Tables VARRAYS or VARYING arrays

Creating tables using nested tables

Inserting, updating & deleting Nested

Table records

Nested table in PL/SQL

Introduction to Oracle Database Architecture

Physical structures Logical structures

DB Memory Structures Background Process

2 Tire, 3 Tire, N-Tier Architecture

9i Joines

New Date function

Rename column

Inner Join/Natural Join

Left Outer Join/Right Outer Join

Full Outer Join

Multiple Inserts

Insert All Command

Merge statement

NVL2(), NULLIF(), COALESCE()

CASE expression of Select Command

Temporary Tables/Global Tables

New Function EXTRACT()

Autonomous Traction

Pragma _ Autonomous_ Transaction()

Bulk Collect

About Flash Back Queries

Dynamic SQL

New data types, Flash back Command

Purge Command, Recyclebin

Regular expressions, DML Error Logging

Data Pump, Virtual Columns

Read only tables, Cross tab Views using

Pivot/Unpivot operators, Follows Clause

Compound triggers, New data types

Database

Tablespace

Types of Tablespaces

Data files /Se