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