Courses Offered: SCJP SCWCD Design patterns EJB CORE JAVA AJAX Adv. Java XML STRUTS Web services SPRING HIBERNATE  
     

ORACLE 11g With Certification (OCA)

Syllabus

Highlights of the Course:

Indepth syllabus coverage(ORACLE 11g,SQL, PL/SQL)

OCA(Oracle Certified Associate) oriented classes.

Special classes on Real Time Database Tools--TOAD,SQL * Loader

Interview FAQs discussion.

Work shops on Real Time scenorios.

Syllabus:

ORACLE 11G

Introduction:

Definition of Database
Definition of DBMS
FMS
Advantage of DBMS over FMS
DB Life Cycle
DB Design:

  • ER MODEL

  • NORMALIZATION
Data Models:
  • Hierarchical

  • Network

  • Relational

  • Object Relational

  • Object Oriented
Features of RDBMS
CODD rules
Client / server Architecture
Oracle versions
Oracle Installation
Oracle architecture

SQL

Introduction to SQL
SQL standards
SQL sublanguages
About SQL*PLUS
Difference between SQL and SQL*PLUS
Datatypes in Oracle
Operators in Oracle
Schemas and Schema Objects
About Table Creation
Inserting Data
Data Retrieval:

  • SELECT statement

  • Column alias, Table alias

  • Clauses in SQL

  • Data filtering using WHERE clause

  • Data Sorting using ORDER BY clause
DML Commands:
  • Copying Data from one table to another

  • INSERT command

  • UPDATE command

  • DELETE command

  • INSERT ALL command

  • MERGE command
DDL commands:
  • Creating Table from another Table

  • ALTER command

  • DROP command

  • TRUNCATE command

  • RENAME command

  • Difference between DELETE and TRUNCATE
Integrity Constraints :
  • Types of Integrity Constraints

  • Declaring Constraints
    • Column level

    • Table level
  • Adding Constraints to an Existing table
  • Enabling & Disabling Constraints
  • Getting information about Constraints

Built-in Functions :

  • Single Row functions
    • String functions
    • Date functions
    • Mathematical functions
    • Conversion functions
    • Special functions
    • Analytical functions
  • Multi Row functions
Data Aggregation :
  • Group by clause

  • Having clause

  • Cube & Rollup
Difference between WHERE and HAVING clause Joins :
  • Importance of Join Operation

  • Types of Joins
    • Equi Join
    • Non-Equi Join
    • Self Join
    • Outer Join
      • Left Outer
      • Right Outer
      • Full Outer
  • Cross join
  • ANSI joins
SET operators :
  • UNION , UNION ALL

  • INTERSECT

  • MINUS
Psuedo columns :
  • ROWID , ROWNUM
Subqueries :
  • Importance of Subqueries
  • Types of Subqueries
    • Single row subqueries
    • Multi row subqueries
    • Nested queries
    • Multi column subqueries
    • Co-related subqueries
  • In-line views
  • Scalar queries
Database Transactions :
  • commit command
  • rollback command
  • savepoing command
Database Security :
  • System privileges and Object privileges
  • Granting and revoking privileges
  • Creating users and roles
Schema objects :
  • Views
  • Synonyms
  • Indexes
  • Clusters
  • Materialized views
  • Sequences
  • Types

About Locks & Isolations Levels
About Table partitioning
Hierarchical queries


PL/SQL

Introduction to PL/SQL
Advantages of PL/SQL
PL/SQL architecture
Datatypes in PL/SQL
PL/SQL program structure
Simple PL/SQL programs
Embedding SQL statements in PL/SQL
Conditional statements
Loops
Cursors :

  • About cursors in PL/SQL
  • Explicit cursors
  • For loop cursor
  • Cursor paramenters
  • For update clause
  • Where current of clause
  • Ref cursors
  • Implicit cursors
Exception Handling :
  • System defined exceptions
  • User defined exceptions
  • Sqlcode & sqlerrm
  • Pragma exception_init
Subprograms :
  • Introduction to subprograms
  • Difference between anonymous & named blocks
Stored procedures :-
  • Creatng procedures
  • Working with IN , OUT ,INOUT parameters
  • Procedures with cursors
  • Procedures returning records
  • Pragma autotnomous transaction
Functions :
  • Difference b/w procedures and functions
  • Inline functions

Packages :

  • Introduction to packages
  • Package specification and package body
  • Overloading package members
Triggers :
  • Introduction to triggers
  • DML triggers
  • DDL triggers
  • Db auditing using triggers
  • DB level triggers

Collections in PL/SQL
Built-in packages in PL/SQL
Oracle 10g features
Oracle 11g features
DBA concepts