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
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.