Syllabus- Important Facts On Oracle Database

Oracle is a powerful and widely used relational database management system (RDBMS) developed by Oracle Corporation. It is designed for enterprise-level applications and is known for its scalability, reliability, and security features. Oracle provides a comprehensive set of tools and technologies for managing large volumes of data, supporting both traditional relational databases and newer cloud-based solutions.

Session 1 – Introduction to Database

  • Approach to Data Management
  • File and File system
    • Database Models
    • Hierarchal Model
    • Network Model
    • Relational Model
    • ObjectRelationalModel
    • Semistructure model

Session 2 – 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,i-SQL

Session 3 – Oracle Database Architecture

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

Session 4 –Sub Language Commands

  • Data Definition Language (DDL)
  • Data Retrieval Language (DQL)
  • Data Manipulation Language (DML)
  • Transaction Control Language (TCL)
  • Database Security and Privileges (DCL)

Session 5 – Introduction to SQL Database Object

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

Session 6 – Built in Functions

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

Session 7 – Grouping the Result of a Query

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

Session 8 – Working with Integrity Constraints

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

Session 9 – REF 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

Session 10 – Querying Multiple Tables (Joins)

  • Equi Join/Inner Join/Simple Join
  • Cartesian Join
  • Non-Equi Join
  • Outer Joins(left,right,full)
  • Outer Joins(left,right,full)
  • Natural join

Session 11 – Working with Sub Queries

  • Understanding the practical approach to Sub Queries/Nested Select/Sub Select/Inner Select/ Outer Select
  • 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
  • Correlated Sub Queries
  • Handling Data Retrieval with EXISTS and NOT EXISTS Operators

Session 12 – Working with DCL, TCL Commands

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

Session 13 – Maintaining Database Objects VIEWS in Oracle

  • Understanding the Standards of VIEWS in Oracle
  • Types of VIEWS
  • Relational Views
  • Object 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(complex view)

Session 14 – 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

Session 15 – Data Partitions & Parallel Process

  • Types of Partitions
  • Range Partitions
  • Hash Partitions
  • List Partition
  • Composite Partition
  • Parallel Query Process

Session 16 – Locks

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

Session 17 – 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

Session 18 – Native SQL/dynamic SQL XMLDB(transaction with XML) Jsql(transaction with java) 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)
    • 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
  • Record and PL/SQL Table Types

Session 19 – Advanced PL/SQL

  • 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
    • Functions in PL/SQL
    • Difference between Procedures and Functions
    • User Defined Functions
    • Nested Functions
    • Using stored function in SQL statements
  • Packages in PL/SQL
  • Creating PACKAGE Specification and PACKAGE Body
  • Private and Public Objects in PACKAGE

Session 20 – EXCEPTIONS in PL/SQL

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

Session 21 – Database Triggers in PL/SQL

  • Types of Triggers
  • Row Level Triggers
  • Statement Level Triggers
  • DDL Triggers
  • SYSTEM LEVEL
  • Trigger Auditing

Session 22 – File Input/Output

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

Session 22 – 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 Abstract Data type Attributes

Session 23 – 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

Session 24 – ORDBMS

  • Object Relational Concepts
  • Abstract Types
  • VARRAYs
  • Nested Tables
  • Object Views
  • Ref, Deref, Makeref, Object ID

Session 25 – Using Collections

  • Advantages of Collections
  • Ref Cursors (Dynamic Cursors)
  • Weak Ref Cursors
  • Strong Ref Cursors
  • Nested Tables, VARRAYs, and VARYING Arrays
  • Creating Tables using Nested Tables
  • Inserting, Updating & Deleting Nested Table Records
  • Nested Tables in PL/SQL

Session 26 – Advanced Features

  • Oracle 9i Joins
  • New Date Functions
  • Renaming Columns
  • 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 in SELECT Command
  • Temporary Tables/Global Tables
  • New Function EXTRACT()
  • Autonomous Transactions
  • PRAGMA _ Autonomous_Transaction()
  • Bulk Collect
  • Flashback Queries
  • Dynamic SQL
  • New Data Types, Flashback Command
  • Purge Command, Recycle Bin
  • 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

Session 27 – 12c Features

  • Online Table Partition or Sub-Partition Migration
  • Invisible Columns
  • Multiple Indexes on a Single Column
  • DDL Logging

Session 28 – DBA Concepts

  • Database
  • Tablespace
  • Types of Tablespaces
  • Data Files / Control File, Log File
  • Segment, Data Extent, Data Block