ORACLE

Introduction to DBMS: 

  • Approach to Data Management
  • Introduction to prerequisites
  • File and File system
  • Disadvantages of file
  • Review of Database Management Terminology
  • 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

Sub Language Commands: 

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

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, DRL Commands
  • Operators Support 

Built in Functions

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

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
    • NOT NULL constraint
    • UNIQUE constraint
    • PRIMARY KEY constraint
    • FOREIGN KEY constraint
    • CHECK constraint
  • Working with different types of integrity Constraints 

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 

Querying Multiple Tables (Joins)

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

Working with 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