ORACLE 11g/12c (FastTrack) on 3rd Nov (Monday) 5:30PM-7:00PM at Maitrvanam (Hyd) by Mr. Vijay Kumar

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