2. Table of Contents

Broad Table of Contents

(A) RDBMS Overview
(B) Relational Fundas
(C) Oracle SQL Basics
(D) Select Queries
(E) Oracle Functions-I
(F) Oracle Functions-II : Aggregation Functions
(G) Oracle Functions-III : Analytic, Statistical, Other
(H) DML : Data Manipulation Language
(I) DDL : Data Definition Language
(J) PL/SQL
(K) Triggers, Packages & Subprograms
(L) SQL Plus
(M) Oracle Supplied Packages
(N) Oracle System & Administration
(O) Oracle Special Features
(P) Performance Enhancement & Management
(Q) Oracle Best Practices
(R) Sample Code & Utilities
(S) Appendix
(T) What they say about Computing
(U) Index

Detailed Table of Contents
(A) RDBMS Overview
A.1 Relational Database
A.2 Brief History of RDBMS
A.3 The Relational Power
A.4 SQL Overview
ISO Standard for SQL
A.5 Oracle Versions

(B) Relational Fundas
B.1 Codd’s Rules
B.2 Relational Database Components
Schema
Entity
Attributes
Functional Dependency
Entity Relationships
Entity Types
Null
Keys
B.3 Normalization
Normalization : Overview
First Normal Form (1NF)
Second Normal Form (2NF)
Third Normal Form (3NF)
BCNF : Boyce-Codd Normal Form
Fourth Normal Form (4NF)
Fifth Normal Form (5NF)
DKNF : Domain Key Normal Form
Normal Forms Summarized
Denormalization
B.4 ERD : Entity Relationship Diagrams

(C) Oracle SQL Basics
C.1 Syntax Convention Used
C.2 Oracle Database Object Names
C.3 Comments
C.4 Oracle SQL Datatypes
Character Datatypes
Numeric Datatypes
Binary Datatypes
Date & Timestamp Datatypes
C.5 PseudoColumns
ROWID
ROWNUM
Other PseudoColumns
C.6 Operators
C.7 SQL Expressions & Case Expressions
C.8 Implicit Data Conversion Rules

(D) Select Queries
D.1 Select Components
Syntax
DUAL Table
SAMPLE  Clause
Conditions : Where Clause
Order By Clause
For Update Clause
D.2 Regular Expressions
Metacharacters
Examples of Regular Expressions
REGEXP_LIKE
REGEXP_INSTR
REGEXP_COUNT
REGEXP_SUBSTR
REGEXP_REPLACE
D.3 Union, Intersect, Minus
UNION
INTERSECT
MINUS
Examples : UNION, INTERSECT, MINUS
D.4 Inline View,  Nested & Correlated Query
D.5 Hierarchical Query

(E) Oracle Functions-I
E.1 Date Arithmetic & Date Functions
E.2 String & Char Functions
E.3 Numeric Functions
E.4 Bits-Related Functions
E.5 NULL-related Functions
E.6 Other SQL Functions

(F) Oracle Functions-II : Aggregation Functions
F.1 Dimension & Fact
F.2 Group By
GROUP BY, HAVING
ROLLUP, CUBE
GROUPING SETS
GROUPING(expr)
GROUPING_ID(expr1, expr2, …)
GROUP_ID()
Examples of Group by, Rollup, Cube, Grouping, etc.
F.3 PIVOT & UNPIVOT
PIVOT
UNPIVOT
F.4 Analytic & Aggregate Functions
Analytic Function
Aggregate Functions
AVG, MAX, MIN
COUNT
LAG / LEAD
LISTAGG
MEDIAN
RATIO_TO_REPORT
ROW_NUMBER

(G) Oracle Functions-III : Analytic, Statistical, Other
G.1 Analytic & Statistical Functions
COVAR_POP
COVAR_SAMP
COVAR Examples
Variance and Standard Deviation
VAR_POP
VAR_SAMP
VARIANCE
STDDEV_POP, STDDEV_SAMP, STDDEV
CORR, CORR_K, CORR_S
CORR_K, CORR_S
CUME_DIST
FIRST_VALUE, LAST_VALUE, NTH_VALUE
DENSE_RANK
PERCENTILE_CONT / _DISC
PERCENT_RANK
RANK
Linear Regression Functions
STATS_CROSSTAB
STATS_MODE
Other STATS_ Functions
G.2 MODEL with SQL-Select

(H) DML : Data Manipulation Language
H.1 INSERT
Inserting from a Collection in PL-SQL
Multi-table INSERT
H.2 UPDATE
UPDATE & Collections in PL-SQL
H.3 MERGE
H.4 DELETE
H.5 TRUNCATE TABLE
H.6 SQL Attributes
H.7 Hints
H.8 Transactions
Transactions
Transaction’s ACID Properties
Atomicity & Consistency
Durability
Isolation
Transaction Control
COMMIT
SAVEPOINT
ROLLBACK
SET TRANSACTION
Autonomous Transaction

(I) DDL : Data Definition Language
I.1 Constraints
Overview of Types of Constraints & Related Terms
In-line Constraints
Out-of-line Constraints
type_n_status: Constraint Type & Status
Constraints Related Statements
I.2 Primary Key (PK)
I.3 Foreign Key (FK)
I.4 Unique
I.5 Check Constraint
I.6 Nulls : Various Aspects
I.7 Tables
CREATE TABLE
Physical Attributes
Virtual Columns
Create Table Examples
IOT : Index-Organized Table
Global Temporary Table (GTT)
ALTER TABLE
External Table
I.8 Partitions
I.9 Sequence
I.10 Indexes
Normal Indexes
Function-based Indexes
Optimizer Statistics on Indexes
Partitioned Indexes
I.11 DATABASE LINK
I.12 SCHEMA AUTHORIZATION
I.13 SYNONYM
I.14 RENAME
I.15 VIEW
I.16 MATERIALIZED VIEW (MV)
I.17 CREATE TYPE
I.18 Table Cluster

(J) PL/SQL
J.1 PL/SQL Overview
J.2 PL/SQL Lexical Units
Delimiters
PL/SQL Literals
PL/SQL Datatypes
J.3 PL/SQL Blocks
Block Syntax
PL/SQL: Declaring Variables & Constants
J.4 PL/SQL Control Structures
IF-THEN-ELSIF-ELSE-END IF
CASE-1 Statement
CASE-2 Statement
CASE-3 Expression Example
CASE in SELECT Clause : Examples
Loops
J.5 PL/SQL Collections
PL/SQL Collection Types & Syntax
VARRAY
RECORD
Associative Arrays (Index-By Tables)
Nested Table
PL/SQL: Operation on Collections
PL/SQL: Examples for Various Collections
J.6 BULK COLLECT & FORALL
FETCH crsr BULK COLLECT INTO
SELECT ....... BULK COLLECT INTO
Limiting Rows Fetched
FORALL
J.7 Cursors
What are Cursors?
DECLARE CURSOR
Cursor Commands
Cursor Attributes : Implicit Cursor
Cursor Attributes : Explicit Cursor
Cursors Examples
Cursor Variable
Cursor Expression
J.8 Label, GOTO & NULL Statements
J.9 PL/SQL Errors & Error Handling
What happens when an error occurs?
Exception Handlers
Unhandled Exceptions
Exception Types
Pre-Defined Oracle Exceptions
J.10 PL-SQL Conditional Compilation

(K) Triggers, Packages & Subprograms
K.1 Triggers
Triggers Overview
DML Triggers
DDL & DB Triggers
K.2 Functions & Procedures
K.3 Package
K.4 RESULT_CACHE
K.5 Pipelined Table Functions
K.6 Recursion

(L) SQL Plus
L.1 SQL Plus Basics
L.2 SQL Plus Commands
Commonly Used
File Related
For Reports
Settings

(M) Oracle Supplied Packages
M.1 Select Oracle Packages
M.2 DBMS_ALERT
M.3 DBMS_COMPARISON
M.4 DBMS_DDL
M.5 DBMS_JOB
M.6 DBMS_METADATA
M.7 DBMS_RANDOM
M.8 DBMS_SCHEDULER
M.9 DBMS_SHARED_POOL
M.10 DBMS_SQL
M.11 DBMS_UTILITY
M.12 SYS.STANDARD
M.13 UTL_FILE
M.14 Other Useful Package Subprograms

(N) Oracle System & Administration
N.1 Database Directories / Files
N.2 Database Initialization Parameters
Database Initialization Parameter Files
Initialization Parameters Views
Select Initialization Parameters
ALTER  SYSTEM
ALTER  SESSION
N.3 Users
Create, Alter, Drop User / Schema
ROLE
Privileges
GRANT
REVOKE
Oracle Predefined Users (=Schemas)
DBA_Users
N.4 Memory
Automatic Memory Management
SGA : System Global Area
PGA : Program Global Area
UGA : User Global Area
N.5 Disk Storage
Database Physical Components
Disk Space Allocation
TableSpaces & Data Files
Redo Logs
Archiving Logs
N.6 Directories
N.7 RAID
N.8 Encryption
N.9 Wrap
N.10 Export / Import Utility
Export Utility
Import Utility
Export / Import Utility Examples
N.11 Database Pump Export / Import
N.12 Recycle Bin
N.13 Oracle Isolation Levels & Locks
Transaction Isolation Levels
Locking
N.14 Oracle Startup
N.15 Oracle Data Dictionary
Oracle Meta Data Views
Useful Data Dictionary Views
Oracle Data Dictionary Query Scripts
List of Oracle System Info Views
Particulars of select Dynamic Performance Views

(O) Oracle Special Features
O.1 Database Audit Features
Overview
AUDIT Statement & Related Aspects
FGA: Fine Grained Audit with DBMS_FGA
DBMS_RLS, VPD & OLS Overview
O.2 Oracle & Java
Java Permissions
Java Stored Procedures
Create Java SQL Statement
Oracle Package for O/S Commands & File Handling
O.3 Oracle & C
O.4 Oracle & XML
O.5 Overview of RAC : Real Application Clusters
O.6 Overview of ASM : Automatic Storage Management
O.7 Overview of Oracle Streams
O.8 Overview of Oracle Data Warehousing Support
O.9 Oracle Database Options
1. Oracle Active Data Guard
2. Oracle Advanced Compression
3. Oracle Advanced Security
4. Oracle Label Security
5. Oracle Data Mining
6. Oracle On-Line Analytical Processing (OLAP)
7. Oracle In-Memory Database Cache
8. Oracle Real Application Testing
O.10 Oracle Management Packs
1. Oracle Data Masking Pack
2. Oracle Change Management Pack
3. Oracle Provisioning and Patch Automation Pack
4. Oracle Configuration Management Pack
5. Oracle Diagnostic Management Pack
6. Oracle Tuning Pack

(P) Performance Enhancement & Management
P.1 SQL Execution Steps
P.2 Optimizer
P.3 EXPLAIN PLAN
P.4 Trace and TKPROF
Trace
TRCSESS
TKPROF
P.5 DBMS_TRACE
P.6 Oracle Profiler : DBMS_Profiler
P.7 Oracle Hierarchical Profiler : DBMS_HPROF
P.8 DBMS_STATS : Database Statistics
P.9 Oracle Performance Diagnostics
P.10 Parallel Operations
Serial vs. Parallel
Parallel Execution
Server Startup Parameters
Session Settings
Table Settings
Indexes, Statistics, etc.
Hints in the Code
Recommended Settings
Relevant Views
DBMS_PARALLEL_EXECUTE

(Q) Oracle Best Practices
Q.1 Naming Conventions
Naming Conventions-I : General Rules
Naming Conventions-II : Tables & Columns
Naming Conventions-III : Other DB Objects
Abbreviated / Standard Object Names
Examples of Good and Bad Object Names
Q.2 Database Design
Database Design & Creation Stages/Steps
Database Design Illustration
Q.3 Database Development & Coding
Follow Agile Testing Methodology
Follow Agile Development Methodology
SQL vs. Row-by-Row / Cursor Access
Q.4 Client_Identifier
Q.5 Performance Enhancement Guidelines
Overview of Oracle Performance Factors
Optimal Environment
Q.6 Oracle Best Practices (BP) - Summarized
Oracle Setup
Naming Conventions
Database Design
Coding
Performance-related Additional BPs
Error-Handling
Documentation

(R) Sample Code & Utilities
R.1 Trigger for Audit-I
R.2 Trigger for Audit-II
R.3 Log  DDLs
R.4 Log  Logon / Logoff
R.5 Estimating Table Size
Method-1
Method-2
Method-3
R.6 Tablespace Sizes
R.7 Customized Timer & Error Handler
R.8 Oracle & Java : File List
R.9 Types Example : timer_type
R.10 PKG_Examples
R.11 PKG_UTILS : Utilities
R.12 Assorted Coding Tips & Techniques
Modified Display, using UNION ALL
Select using ROWID
Update using ROWID
Generating integers from 1 to N

(S) Appendix
S.1 Oracle Reserved Words
S.2 Example Schema
Naming Convention in the Examples
Schema

(T) What they say about Computing
T.1 What should software be like
T.2 Laws of Computing
T.3 Sayings on Computing

(U) Index