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 Data types

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 (NOTLIKE), 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

NOT NULL constraint

UNIQUE constraint

PRIMARY KEY constraint

FOREIGN KEY constraint

CHECK constraint

Working with different types of integrity Constraints

Understanding ON DELETE clause in referential integrity constraint

Working with 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

PAIR WISE and NON PAIR WISE 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

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 Data types

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

Simple LOOP,WHILE LOOP,FOR LOOP and NESTED LOOPS

SQL within PL/SQL

Composite Data types (Complete)

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

Record and PL/SQL Table Types

Procedures in PL/SQL

STORED PROCEDURES

PROCEDURE with Parameters (IN,OUT and IN OUT)

POSITIONAL Notation and NAMED Notation

Procedure with Cursors

Dropping a Procedure

Functions in PL/SQL

Difference between Procedures and Functions

User Defined Functions

Nested Functions

Using stored function in SQL statements

Packages in PL/SQL

Creating PACKAGE Specification and PACKAGE Body

Private and Public Objects in PACKAGE

Types of exceptions

User Defined Exceptions

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