ORACLE TRAINING


oracle sql

FUNDAMENTALS OF DATABASE:
• Introduction to databases
• Modelsa
• RDBMS Concepts
• ORDBMS Concept
• E-R Model
• Introduction to Oracle Database
• Oracle Database History
• Introduction to oracle 11g features/12c features
• Oracle 11g Server and DB Architecture

SQL:
• Introduction to SQL
• Features of SQL
• Introduction to SQL * plus
• Role of SQL in Oracle 11g

Classification of SQL Commands
Data Definition Language (DDL) commands

• Introduction to DDL commands
• Create Table
• Rules for Naming a tab le
• Altering the table structure using ALTER
• Change the table name using RENAME
• Deleting data using TRUNCATE
• Dropping table using DROP

Data Manipulation Language(DML) commands

INSERT:
• Insert of Data (value and address method)
• Insertion of Nulls
• Overriding the Nulls with User defined Values
• Insertion of Date in required formats
• Data Loading methods in Oracle 11g
• Data Loading Performance improvement tips

Data Updation
• Techniques of Updation
• Complex Data Updation
• Correlated Query mechanism in Update

Data Deletion
• Simple data deletion
• Critical Data deletion
• Table delete Vs table truncate

Transaction Control Language Commands
• Introduction of Production Databases
• Introduction to Transaction and ACID properties
• Transaction management in oracle 11g
• What is Session and Types of Terminations
• Commit ,Rollback and Save point

Data Retrieve Language(DRL) command
SELECT

• Select command and it’s clauses
• Data Retrieving methods
• Data sorting in oracle 11g
• Null value sort and user defined null sorts

Operators:
• Type of operators in oracle 11g & Filters

Functions
• Type of functions in oracle 11g
• Pseudo columns of oracle 11g

Table joining in Oracle 11g
• Introduction to table join
• ANSI 1992 and ANSI 1999 Standard
• Types of joins
• Inner and outer joins
• Equi/Non equi join/self join/ Cartesian join
• Oracle specific join and enhancements

Integrity Constraints
• Introduction to Constraints
• Importance of Integrity Constraints
• Levels of constraints
• Type of constraints
• Name of the constraints
Sub queries
• Simple sub query
• Complex sub query on multiple data sources
• Co-related sub query

Oracle database objects:

Index:
• Introduction to index
• Types of indexes in Oracle 11g
• Index for OLTP and OLAP

CLUSTER:

• Introduction to cluster
• Type of clusters and their usage
• Performance evaluation methods in oracle 11g
• Explain plan command usage and oracle scripts

Views:
• Introduction to Views
• Type of Views and usage
• Performance issues with views
• Background process of views
• DML restriction on Views
• Materialized views and usage

SYNONYM:
• Introduction to Synonym and usage
• Private synonyms
• Public synonyms
• How to see list of synonyms
• Dropping the synonyms

SEQUENCE:
• Introduction to Sequence and Usage
• Pseudo Columns usage in sequence(Nextval and Currval)
• Sequence with positive values Ascending and Descending
• Sequence with negative values Ascending and Descending
• Sequence with Cycle option
• Dropping the sequences

Concurrency Control Using Locks
• Types of Locks
• Levels of Locks
• Row level
• Page level
• Table level
• Modes of Locks
• Share mode
• Share update mode
• Exclusive mode
• Wait and no wait options

Data Control Language Commands:

  • What is privilege
  • What is role
  • Granting Privileges
  • Removing Privileges
  • Cascading Privileges

Plus commands

  • Environment Setting Commands
  • Screen Formatting Commands

ORDBMS:

  • Drawbacks of RDBMS
  • Introduction to ORDBMS
  • Implementation of Object Technology in Oracle 11g

Abstract Datatypes

  • Persistent object Vs transient object
  • Column object vs Row Object
  • Introduction to Object table and usage

New Datatypes

  • LOBS, BLOBs, CLOB, NCLOB, BFILE, REG data type
    • REF, DEREF ,VALUE,DANGLING operations
  • DBMS_LOB package and its routines
  • Using collection(composite types)
  • Nested Tables Vs Varray
  • Advantages of Collections
  • Transaction Consistency

Data partitions and Parallel Process:

  • Types of partitions
  • Range partitions
  • List partitions
  • Hash partitions
  • Composite partitions
  • Index partition
  • Global partition
  • Local partition
  • Parallel query process.