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 12c

  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
  Data types in Oracle
  Operators in Oracle
  Schemas and schema objects
  About Table Creation
  Inserting Data
  Data Retrieval :-

  • SELECT statement
  • Column alias, table alias
  • Clauses in ORACLE
  • Data filtering using WHERE clause
  • Data sorting using ORDER BY clause
  • Eliminating Duplicates Using DISTINCT Clause

  DML Commands:

  • 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
  • FLASHBACK & PURGE commands

  Integrity Constraints:

  • Importance of  Integrity Constraints
  • Types of Integrity Constraints
  • Declaring Constraints
  • Column Level
  • Table level
  • Adding Constraints to an Existing table
  • Enabling & Disabling Constraints
  • DELETE rules
  • Initially Deferred Deferrable Option
  • 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
  • WHERE vs HAVING  clauses
  • Cube & Rollup
  • GROUPINT_ID function

  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 Join
  • Natural Join
  • Queries using  GROUP BY and JOIN

  SET Operators :-

  • UNION, UNION ALL
  • INTERSECT
  • MINUS
  • Difference between UNION & JOIN operation

  Psuedo columns:

  • ROWID,
  • ROWNUM,
  • USER,
  • SYSDATE

  Subqueries:

  • Importance of sub queries
  • Types of sub queries
  • Single row sub queries
  • Multi row sub queries
  • Nested sub queries
  • Multi column sub queries
  • Co-related subqueries
  • In-line views
  • Scalar queries

  Database Transactions:

  • Commit command
  • Rollback command
  • Save point command
  • ACID properties

  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

  Locks  :-

  • Concurrent transactions
  • Types of Locks
  • Row Level Locks
  • Table Level Locks
  • Dead Lock
  • Isolation Levels

  Advanced Concepts :

  • Hierarchical Queries
  • Table partitioning
  • User Defined Datatypes
  • Flashback Queries
  • External Tables
  • Global Temporary Tables
  • Data Pump
  • Displaying data in XML format
  • Oracle 11g features
  • Oracle 12c features
  • DBA concepts
  • Performance Tuning

    PL/SQL :

  Introduction to PL/SQL
  Advantages of PL/SQL
  PL/SQL architecture
  Data types 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 parameters
  • For update clause
  • Where current of clause
  • Ref  cursors
  • Implicit cursors

  Exception handling:

  • System defined exceptions
  • User defined exceptions
  • Sqlcode & sqlerrm
  • Pragma exception_init

  Sub programs:

  • Introduction to subprograms
  • Difference between anonymous & named blocks

  Stored procedures:

  • Creating Stored Procedures
  • Working with IN, OUT, IN OUT parameters
  • Using CURSORS in Stored Procedures
  • Procedures returning records.
  • Pragma autonomous_ transaction

  Functions:

  • Difference between procedures and functions
  • Creating Functions
  • Inline functions

  Packages:

  • Introduction to packages
  • Package specification and package body
  • Creating packages
  • Overloading package members

  Triggers:

  • Introduction to triggers
  • DML triggers
  • DDL triggers
  • auditing using triggers
  • DB level triggers
  • FOLLOWS clause
  • Compound Triggers
  • Difference between Procedures and Triggers
  • Difference between Triggers and Constraints

  Collections  :-

  • VARRAYS & NESTED TABLES
  • BULK COLLECT
  • LIMIT clause
  • FORALL loop

  Built-in Packages :

                          UTL_FILE
                          DBMS_SQL
                          DBMS_SCHEDULE

                          DBMS_LOB