ORACLE 11g Training in Patna Bihar

Introduction to DBMS:
Approach to Data Management
Introduction to prerequisites
File and File system
Disadvantages of file
Review of Database Management Terminology
Database Models
Hierarchal Model
Network Model
Relational Model
Introduction to RDBMS:
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
Sub Language Commands:
Data Definition Language (DDL)
Data Retrieval Language (DRL)
Data Manipulation Language (DML)
Transaction Control Language (TCL)
Database Security and Privileges (DCL)
Introduction to SQL Database Object:
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
Built in Functions :
Arithmetic Functions, Character Functions, Date Functions, Conversion Functions
Aggregate Functions, OLAP Functions & General Functions
Grouping the result of a query :
Using Group by and Having Clause of DRL Statement
Using Order by clause
Working with Integrity Constraints :
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
REF constraint
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
Querying Multiple Tables (Joins) :
Equi Join/Inner Join/Simple Join
Cartesian Join
Non-Equi Join
Outer Joins
Self Join
Working with Sub Queries :
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
Working with DCL, TCL Commands :
Grant, Revoke
Commit, Rollback, Savepoint
SQL Editor Commands
SQL Environment settings
Maintaining Database Objects :
VIEWS in Oracle :
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
Working with Sequences
Working with Synonyms
Working with Index and Clusters
Creating Cluster Tables, Implementing Locks, working with roles
Pseudo Columns in Oracle :
Understanding Pseudo Columns in Oracle
Types of Pseudo Columns in Oracle
CURRVAL and NEXTVAL
LEVEL
ROWID
ROWNUM
Data Partitions & Parallel Process :
Types of Partitions
Range Partitions
Hash Partitions
List Partition
Composite Partition
Parallel Query Process
Locks:
Row level Locks
Table Level Locks
Shared Lock
Exclusive Lock
Dead Lock
SQL * Loader :
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
PL-SQL (Procedure Language SQL) :
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
Advanced PL/SQL
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
EXCEPTIONS in PL/SQL :
Types of exceptions :
User Defined Exceptions
Pre Defined Exceptions
RAISE_APPLICATION_ERROR
PRAGMA_AUTONOMOUS_TRANSACTION
SQL Error Code Values
Database Triggers in PL/SQL :
Types of Triggers
Row Level Triggers
Statement Level Triggers
DDL Triggers
Trigger Auditing
File Input/Output :
PL/SQL file I/O (Input/Output)
Using UTL_FILE Package