Oracle

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

•  Features of RDBMS
•  Advantages of RDBMS over FMS ad DBMS
•  The 12 rules (E.F Codd’s Rules –RDBMS)
•  Support of Normalization Process for Data Management
•  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
•  Applying Group Functions in Sub Queries
•  The impact of Having Clause in Sub Queries
•  IN, ANY/SOME, ALL Operators in Sub Queries
•  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
•  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

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

Locks

•  Row level Locks
•  Table Level Locks
•  Shared Lock
•  Exclusive Lock
•  Dead Lock

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
•  SQL Error Code Values

Database Triggers in PL/SQL

•  Types of Triggers
•  Row Level Triggers
•  Statement Level Triggers
•  DDL Triggers