Oracle 11g/12c Training
By
Durga software Solutions

Introduction to DBMS :

• DBMS Features
• Database Models
• Hierarchal Model
• Network Model
• Relational Model
• Introduction to RDBMS :

Feature of RDBMS

• Advantages of RDBMS over FMS ad DBMS
• The 12 rules (E.F Codd’s Rules –RDBMS)
• Need for Database Design
• Support of Normalization Process for Data Management
• Client server Technology
• Oracle Corporation Products
• Oracle Versions
• About SQL&SQL*PLUS
• Constraints
• Normalization and De-Normalization
• ER Diagrams

Sub Language Commands :

• Data Definition Language  (DDL)
• Data Retrieval Language (DRL)
• Data Manipulation Language (DML)
• Transaction Control Language (TCL)
• Database Control Language (DCL)

Introduction to SQL Database Object :

• Oracle Pre Defined Data types
• DDL Commands
• Create, Alter (add, modify, rename, drop) Columns, Rename, truncate, drop
• Working with DML, DRL Commands
• Operators Support
• DML-Insert, update, delete
• DQL-SELECT Statements using WHERE clause
• Comparison and Conditional Operators
• Arithmetic and Logical Operators
• Set Operators (UNION, UNION ALL, INTERSECT, MINUS)
• Special Operators – IN (NOT IN), BETWEEN (NOT BETWEEN), LIKE (NOT LIKE), IS NULL (IS NOT NULL)

Grouping the result of a query :

• Using Group by and Having Clause of DRL Statement
• Using Order by clause

Working with Integrity Constraints :

• Importance of Data Integrity
• Support of Integrity Constraints for Relating Table in RDBMS
• Working with different types of integrity Constraints
• NOT NULL constraint
• UNIQUE constraint
• PRIMARY KEY constraint
• FOREIGN KEY constraint
• CHECK constraint

Reference Constraint:

• Understanding ON DELETE clause in referential integrity constraint
• Working with composite constraint
• Applying DEFAULT option to columns
• Working with multiple constraints upon a column
• Adding constraints to a table
• Dropping of constraints
• Enabling / Disable constraints
• Querying for constraints information

Querying Multiple Tables (Joins) :

• Equi Join/Inner Join/Simple Join
• Cartesian Join, Non-Equi Join
• Outer Joins, Self Join

 Sub Queries, Nested Sub Queries :

• Understanding the practical approach to Sub Queries/Nested Select/Sub Select/Inner Select/Outer Select
• What is the purpose of a Sub Query?
• Sub Query Principle and Usage
• Type of Sub Queries
• Single Row, Multiple Row, Multiple Column
• Applying Group Functions in Sub Queries
• The impact of Having Clause in Sub Queries
• IN, ANY/SOME, ALL Operators in Sub Queries
• PAIR WISE and NON PAIR WISE Comparison in Sub Queries
• Be … Aware of NULL’s
• Correlated Sub Queries
• Handling Data Retrieval with EXISTS and NOT EXISTS Operators

Working with DCL, TCL Commands :

• Grant, Revoke
• Commit, Rollback, Savepoint
• SQL Editor Commands
• SQL Environment settings

Maintaining Database Objects :

 VIEWS in Oracle :

• Understanding the Standards of VIEWS in Oracle
• Types of VIEWS
  • Relational Views, Object Views
• Prerequisites to work with views
• Practical approach of SIMPLE VIEWS and COMPLEX VIEWS
• Column definitions in VIEWS
• Using VIEWS for DML Operations
• In-Line View
• Forced Views
• Putting CHECK Constraint upon VIEWS
• Creation of READ ONLY VIEWS
• Understanding the IN LINE VIEWS
• About Materialized Views
• View Triggers
• Working with Sequences
• Working with Synonyms
• Working with Index and Clusters
• Creating Cluster Tables, Implementing Locks, working with roles

Pseudo Columns in Oracle :

• Understanding Pseudo Columns in Oracle
• Types of Pseudo Columns in Oracle
  • CURRVAL and NEXTVAL
  • LEVEL, ROWID, ROWNUM

Data Partitions & Parallel Process :

Types of Partitions:

• Range Partitions, Hash Partitions, List Partition
• Composite Partition
• Parallel Query Process

Locks:

• Row level Locks
• Table Level Locks
• Shared Lock, Exclusive Lock, Dead Lock

Built in Functions :

• Arithmetic Functions, Character Functions, Date Functions, Conversion Functions
• Aggregate Functions, OLAP Functions & General Functions

Oracle Database Architecture

• Introduction to Oracle Database Architecture
• Physical structures Logical structures
• DB Memory Structures Background Process
• 2 Tire, 3 Tire, N-Tier Architecture

Flashback queries

• TIMESTAMP, SCN

PL-SQL (Procedure Language – SQL) :

• Introduction to Programming Languages
• Introduction to PL/SQL
• The Advantages of PL/SQL
• PL/SQL Architecture
• PL/SQL Data types
• Variable and Constants
• Using Built_in Functions
• Conditional and Unconditional Statements
• Simple if, if... else, nested if..else, if..else Ladder
• Selection Case, Simple Case, GOTO Label and EXIT
• Iterations in PL/SQL
• Simple LOOP,WHILE LOOP,FOR LOOP and NESTED LOOPS
• SQL within PL/SQL
• Composite Data types (Complete)
• Record and PL/SQL Table Types

Advanced PL/SQL:

EXCEPTIONS in PL/SQL :

• Types of Exceptions :
• User Defined Exceptions
• Pre Defined Exceptions
• RAISE_APPLICATION_ERROR
• PRAGMA_AUTONOMOUS_TRANSACTION
• SQL Error Code Values

Procedures in PL/SQL :

• STORED PROCEDURES
• PROCEDURE with Parameters (IN,OUT  and IN OUT)
• POSITIONAL Notation and NAMED Notation
• Procedure with Cursors
• Dropping a Procedure

Database Triggers in PL/SQL :

• Types of Triggers:
• Row Level Triggers
• Statement Level Triggers           
• DDL Triggers
• Trigger Auditing

Functions in PL/SQL :

• Difference between Procedures and Functions
• User Defined Functions
• Nested Functions
• Using stored function in SQL statements

Cursor Management in PL/SQL:

• Implicit Cursors
• Explicit Cursors
• Cursor Attributes
• Cursor with Parameters
• Cursors with LOOPs
• Nested Cursors
• Cursors with Sub Queries
• Ref. Cursors

Packages in PL/SQL :

• Creating PACKAGE Specification and PACKAGE Body
• Private and Public Objects in PACKAGE

File Input/Output :

• PL/SQL file I/O (Input/Output)
• Using UTL_FILE Package

Implementing Object Technology:

• What is Object Technology ?
• OOPS-Object Instances
• Creation of objects
• Creating User Defined Data Types
• Creating Object Tables
• Inserting rows in a table using Objects
• Retrieving data from Object based Tables
• Calling a Method
• Indexing Abstact Data type Attributes

Using LOBS:

• Large Objects (LOBS)
• Creating Tables-LOB
• Working with LOB values
• Inserting, Updating & Deleting Values in LOBs
• Populating Lobis DBMS_LOB Routines
• Using B-FILE

SQL * Loader :

• SQL * Loader Architecture
• Data file (Input Datafiles)
• Control file, Bad file, Discard file, Log file
• .txt to base table
• .csv to base table
• From more than one file to single table

Using Collections:

• Advantages of collection
• Ref cursor (Dynamic Cursor)
• Weak ref cursor
• Strong ref cursor
• Nested Tables VARRAYS or VARYING arrays
• Creating tables using nested tables
• Inserting, updating & deleting Nested
• Table records
• Nested table in PL/SQL

Advanced Features:

• 9i Joines
• New Date function
• Rename column
• Inner Join/Natural Join
• Left Outer Join/Right Outer Join
• Full Outer Join
• Multiple Inserts
• Insert All Command
• Merge statement
• NVL2(), NULLIF(), COALESCE()
• CASE expression of Select Command
• Temporary Tables/Global Tables
• New Function EXTRACT()
• Autonomous Traction
• Pragma _ Autonomous_ Transaction()
• Bulk Collect
• About Flash Back Queries
• Dynamic SQL
• New data types, Flash back Command
• Purge Command, Recyclebin
• Regular expressions, DML Error Logging
• Data Pump, Virtual Columns
• Read only tables, Cross tab Views using
• Pivot/Unpivot operators, Follows Clause
• Compound triggers, New data types

DBA CONCEPTS:

• Database, Tablespace
• Types of tablespaces
• Data files /Se

Oracle 12C  Features

1. Invisible columns

2. Identity columns

3. Session specific sequences

4. New top n analysis (fetch first, fetch next clauses)

5. Truncate table cascade

6. With clause used in pl/sql programs

7. Accessible by clause used in procedures.