Chapter1: Introduction to DBMS
Approach to Data Management
Introduction to prerequisites
File and Filesystem
Disadvantages of file
Review of Database Management Terminology
Database Models
o Hierarchal Model
o Network Model
o Relational Model
How to download and install oracle step by step
Chapter2: Introduction to RDBMS
Feature of RDBMS
Advantages of RDBMS over FMS and 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
Chapter3: Oracle Database Architecture
Introduction to Oracle Database Architecture
Physical structures Logical structures
DB Memory Structures Background Process
2 Tire, 3 Tire, N-Tier Architecture
Chapter4: Sub Language Commands
Data Definition Language (DDL)
Data Retrieval Language (DRL)
Data Manipulation Language (DML)
Transaction Control Language (TCL)
Database Security and Privileges (DCL)
Chapter5: Introduction to SQL Database Object
Oracle Pre Defined Datatypes
DDL Commands
Create, Alter (add, modify, rename, drop) Columns, Rename, truncate, drop
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)
Working with DML, DRL Commands
Operators Support
Chapter6: Operators
Arithmetic operators
Assignment operator
Relational Operators
Logical Operators
Special Operators (IN, NOT IN, BETWEEN, NOT BETWEEN, LIKE, NOT LIKE, IS NULL, IS NOT
NULL)
Set operators (UNION, UNION ALL, INTERSECT, MINUS)
Chapter7: Built-in Functions
Arithmetic Functions, Character Functions, Date Functions, Conversion Functions
Aggregate Functions, OLAP Functions & General Functions
Column Level Functions: Number Functions, Character Functions, Date Functions
Chapter8: Grouping the Result of a Query
Using Group by and Having Clause of DRL Statement
Using Order by clause
Chapter9: Working with Integrity Constraints
Importance of Data Integrity
Support of Integrity Constraints for Relating Table in RDBMS
o NOT NULL constraint
o UNIQUE constraint
o PRIMARY KEY constraint
o FOREIGN KEY constraint
o CHECK constraint
Working with different types of Integrity Constraints
Chapter10: REF constraint
Understanding ON DELETE clause in referential integrity constraint
Working with a 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
Chapter11: Querying Multiple Tables (Joins)
Equi Join/Inner Join/Simple Join
Cartesian Join
Non-Equi Join
Outer Joins
Self-Join
Chapter12: 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
PAIRWISE and NON-PAIRWISE Comparison in Sub Queries
Be … Aware of NULL’s
Correlated Sub Queries
Handling Data Retrieval with EXISTS and NOT EXISTS Operators
Chapter13: Working with DCL, TCL Commands
Grant, Revoke
Commit, Rollback, Savepoint
SQL Editor Commands
SQL Environment settings
Chapter14: 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
Chapter15: Working with Sequences and Synonyms
Chapter16: Indexes in Oracle
Chapter17: Pseudo Columns in Oracle
Understanding Pseudo Columns in Oracle
Types of Pseudo Columns in Oracle
CURRVAL and NEXTVAL
LEVEL
ROWID
ROWNUM
Chapter18: Data Partitions and Parallel Process
Types of Partitions
Range Partitions
Hash Partitions
List Partition
Composite Partition
Parallel Query Process
Chapter19: Locks
Row level Locks
Table Level Locks
Shared Lock
Exclusive Lock
Dead Lock
Chapter20: 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
Chapter21: PL-SQL (Procedure Language – SQL)
Introduction to Programming Languages
Introduction to PL/SQL
The Advantages of PL/SQL
PL/SQL Architecture
PL/SQL Datatypes
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
o Simple LOOP, WHILE LOOP, FOR LOOP and NESTED LOOPS
o SQL within PL/SQL
o Composite Data types (Complete)
o Cursor Management in PL/SQL
o Implicit Cursors
o Explicit Cursors
o Cursor Attributes
o Cursor with Parameters
o Cursors with LOOPs Nested Cursors
o Cursors with Sub Queries
o Ref. Cursors
Record and PL/SQL Table Types
Chapter22: PL/SQL Blocks
Anonymous Blocks
Named Blocks
Using Built-in Functions
Condition and Unconditional Statements
o Simple if. If..else, Nested if, if…else ladder
o Selection Case, Simple Case
o GOTO Label and EXIT
o Iteration Control Statements
Simple LOOP
While LOOP
For LOOP
Nested LOOPS
SQL within PL/SQL
Composite Data types
Chapter23: 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(Dynamic Static Cursor)
Chapter24: PL/SQL Records and PL/SQL Table Types
Chapter25: 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
Chapter26: Functions in PL/SQL
o Difference between Procedures and Functions
o User Defined Functions
o Nested Functions
o Using stored function in SQL statements
Chapter27: Packages in PL/SQL
Creating PACKAGE Specification and PACKAGE Body
Private and Public Objects in PACKAGE
Chapter28: EXCEPTIONS in PL/SQL
Types of exceptions
User-Defined Exceptions
Pre-Defined Exceptions
RAISE_APPLICATION_ERROR
PRAGMA_AUTONOMOUS_TRANSACTION
SQL Error Code Values
Chapter29: Database Triggers in PL/SQL
Types of Triggers
Row Level Triggers
Statement Level Triggers
DDL Triggers
Trigger Auditing
Dropping Triggers
Chapter30: File Input/Output
PL/SQL file I/O (Input/Output)
Using UTL_FILE Package
Chapter31: 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
Chapter32: 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
Chapter33: 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
Chapter34: 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
