Duration: 40 days

Online  Fee: 15,000

Classroom Fee: 2000

 

Database Introduction

Oracle Corporation Products

What is sever Software

What is Application Software?

Package Vs. Language

What is Database?

Objective of Database

 

Database System

What is DBMS

Oracle Database Architecture

Oracle Database

Oracle Instance/Engine

Importance of RAC(Real Application Cluster)

Usage of Listener Service

What is Database Application?

Frontend Vs. backend

What is ODBC?

Role of ODBC Drivers in Database System

Client/Server Technology

 

Database Servers

OLTP DB Server

OLAP DB Server

OLTP Vs OLAP

 

Different types of Database Management System

FMS

Drawbacks of FMS

DBMS

Drawbacks of DBMS

RDBMS

E.F. CoddRules(12)

What is Entity and Attribute?

Drawbacks of RDBMS

ORDBMS

Difference between DBMS, RDBMS and ORDBMS

 

Introduction to SQL

What is SQL?

Procedural  vs. Non-procedural Languages

Role of  SQL in RDBMS

Features of SQL

What is SQL*PLUS

SQL Vs.  SQL*PLUS

PLUS Commands

Different Types of SQl Languages 

DDL Commands :  Create, Alter, Drop

Truncate, Rename, Purge, Flashback

DML Commands  :  Insert, Insert all, Upadate, Delete, Merge

DRL Commands   :  Select

TCL Commands    :  Commit, Rollback, Savepoint

DCL Commands   :   Grant , Revoke

System Control Commands  : Alter System

Session Control Commands: Alter session

 

 

Oracle Data Types

Creation of Table(Create Command)

Naming Rules

Creating a table

Creating a table based on another table

Creating table with Default Values

Viewing Table structure

What is Data Dictionary

Metadata

Schema

User

What is SQL Buffer

 

DML Command – Insert

Insertion of data

Insertion of data at runtime [address method]

Insertion of nulls [implicit &Explicit method]

What is null

Overriding nulls with user defined values

Multiple inserts

Insert all Commands

 

DRL/DQL- Select Command

Selection of data

Projection

Creating column alias name

Usage of column alias names

 

Copying Table Data

Copying Rows fromone table to another table

Creating table based on another table with /without data

Distinct Clause

Eliminating Duplicate Values

 

Order By Clause (Sorting Data)

Sorting data based on single column

Sorting data based on multiple columns

Sorting columns with Column posting and alias names

Sorting null values

 

Where Clause

Filtering Data

 

SQL Operators

Arithmetic Operators

Relational Operators

Relational Negation Operators

Logical Operators

Set Operators( Vertical Joins)

 

SQL FUNCTIONS  

Single Row Functions

Numeric Functions

Character Functions

Date Functions

Data Conversion Functions

Miscellaneous/General Functions

OLAP Functions

Rank(),dense_rank(), row_number(),rollup(),cube()

New Functions

Nvl2(),null if(),coalesce(),extract(),case,………

Group/Aggregate Functions

Max(),min(),count(),sum(),avg()

 

Integrity Constraints

Data Integrity

Data Redundancy

Data Inconsistency

Normalization

Function Vs Transitive Dependency

Types of constraints

Domain Integrity Constraints

Not Null,Check

Entity Integrity Constarints

Unique, Primary Key

Referential Integrity Constraints

 References

Levels of Constraints

Column Level Constraint

Table level Constraint

Composite primary Key Constraint

Unique vs. primary Key

Primary Key vs. Foreign Key

Using Alter Command (DDl)

Enabling/Disabling Constraints

Adding/Dropping Constraints

Viewing Constraints

 

DDL COMMANDS    

Alter, Drop, truncate, Rename, purge, Flashback

     Flash Back Queries

Flashback Tables

Recyclebin Command

Purge Command

Read only Tables

Read/write tables

 

Joins

What is Join

Join Vs. Reference Constraint

Types of joins

Cross Join (Cartesian Join)

Inner Join

Inner Equi Join

Inner Non- Equi Join

          Self-Join

Self Equi Join

Self Non-Equi Join

Outer Joins

LeftOuter Join

Right Outer Join

Full Outer Join

Natural Join

            Join…..Using

            Join….on

 

Sub Queries

What is Sub Query?

Types of Sub Queries

Single Row Sub Query

Multi Row Sub Query

Multi Colum Sub Query

In Line Query

Correlated Sub Query

Sub Query With Multiple tables

Usage of <all, >all, <any, >any operators in sub query

Sub Query Vs. Self join

Sub Query Vs. Inner join                                                                                                                     Sub queries with Updates                                                                                                               Sub queries with Delete

Pseudo Columns

User, uid,rownum, rowed,sysdate,currents_date,nextval,cuur_val,new,old,….

 

Grouping the result of a query

Usage of group by clause

 

Having Clause Order of execution of all Clauses

 

DML Commands- Update

Data Updating

Updating data using sub queries

 

TCL Commands

Commit

Rollback

Save Point

 

DCL Commands

What is Privilege?

            What is Role

            Types of Privilege

            System level Privileges

Object level Privileges

Granting Privileges                                                                                                                             Revoking Privileges

Viewing Privileges

 

 

 

PLUS Commands

Environment Setting Commands

Screen Formatting Commands

 

Database Objects

Index

What is Index?

Importance of Index

Btree index

Unique index

Composite index

Function based index

Bitmap index

Viewing indexes

Dropping indexes

Cluster

What is Cluster?

Importance of Cluster

Viewing Cluster

Dropping Cluster

Sequence

What is Sequence?

Importance of Sequence

Usage of nextval and currval

Viewing Sequences

Dropping/altering sequences

Synonym    

What is synonym?

Importance of synonym

Types of Synonyms

Private Synonym

Public Synonym

Viewing/dropping synonyms

 

Views

What is View

Importance of views                                                                                                       

Types of views

Simple views

Complex Views

Forced views

View with CHECK OPTION Clause

View With READ ONLY Clause

DML operations on simple views

Instead of trigger

Compiling Views

Finding invalid views

Viewing/dropping views

 

Materialized View

What is materialized view

Importance of materialized view

Viewing/dropping materialized views

 

Locks

Rows level locks

Table level locks

Shared locks

Exclusive locks

Partitions

Types of partitions

Range Partition

List partition

Hash partition

PL/SQL

Introduction to programming languages                                                                                            Introduction to Oracle PL/SQL                                                                                                                       Procedural Vs Non –procedural Languages                                                                                               Usage of PL/SQL in production Database                                                                                                    Key Features of PL/SQL over SQL                                                                                                                 Architecure of PL/SQL                                                                                                                                       Types of PL/SQL Blocks

Anonymous PL/SQLBlock

Nested PL/SQL Block

Named Pl/SQL Block

Labeled PL/SQL Block

Structure of PL/SQL Block

 

Assignment Operator                                                                                                                                      Concentration Operator                                                                                                                                      PL/SQL Comments                                                                                                                                                Debugging Statements

 

Variables

What is Variable?

Variable declaration

Variable with Default values

Not null Variables

Constant Variables

Data types

Oracle data types

ANSI Data types

PL/SQL Data types

Sub types

User defined data types

Flow Control Statements

Simple if

If..else…end if

Nested if

If…elsif…end if

Exit

Raise

Goto

Looping Control statements

While loop

Simple/infinite loop

For loop

Nested loops

Embedded SQL

What is embedded SQL

Attribute/Anchor data types

%type

%rowtypeUsage of Embedded SQL

Cursor Management

What is cursor

Types of cursors

Implicit Cursor

Explicit Cursor

Cursor Attributes

Cursor using simple loop

Cursor using While loop

Cursor for loop

Cursor parameter

 

Ref Cursor

Dyanamic Ref Cursor

Static Ref Cursor

PL/SQL Records

PL/SQL Tables

Bulk Collect/Bulk Binding

Dynamic SQL

 

Advanced PL/SQL

Subprograms

Anonymous Vs. Named PL/SQL

Procedure Vs.Function

 

Stored procedures

What is stored procedure

How to Execute stored procedure

Procedure without parameters

Procedure with parameters

Procedure with Cursors

Procedure with in a procedure

Procedure with Default Arguments

Passing arguments in positional and Named method

Bind Variables

 

Types of parameters

In, Out, in out parameters

Host/Binding Variables

Viewing procedures

Granting privileges on procedures

Dropping procedure

 

Stored Functions

User Defined Functions

Nested Functions

Viewing Functions

Dropping Functions

 

Packages

What is package

Importance of package

Creating package specification

Creating package body

Private and public objects in packages

Function Overloading mechanism package

 

Exception Handling

What is exception

    Exception types

Oracle predefined Exception

Oracle Non- predefined Exception

User Defined Exceptions

Raise

Raise_application_error

Usage of PRAGMA exception_init ()

SQL Error Code Values

 

Triggers

What is trigger

Usage of trigger

Types of Triggers

Row Level Triggers

Statement Level Triggers

Trigger events

New and old pseudo columns usage

Instead of trigger

DDL Triggers

Trigger auditing

Enabling/Disabling Triggers

Dropping Triggers

Viewing Triggers

 

Object Technology

What is Object Technology

Creating objects

Creating user defined Objects

Creating Object tables

InsertingRows in a table using objects

Retrieving data from object based tables

 

Collections

Ref Cursor

Static Ref Cursor

Dynamic Ref Cursor

Nested tables

VARRAYS or VARYING arrays

Creating tables using nested tables

Inserting, updating& deleting nested tables

Records

Introduction to SQL*Loader

Introduction to Toad