Topic outline
Oracle Introduction
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
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
Built in Functions
- Arithmetic Functions, Character Functions, Date Functions, Conversion Functions
- Aggregate 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
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
- About Materialized Views
- View Triggers
Managing Database Objects - II
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
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
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
EXCEPTIONS in PL/SQL
- Types of exceptions
- User Defined Exceptions
- Pre Defined Exceptions
- RAISE_APPLICATION_ERROR
- PRAGMA_AUTONOMOUS_TRANSACTION
- SQL Error Code Values
Database Triggers in PL/SQL
- Types of Triggers
- Row Level Triggers
- Statement Level Triggers
- DDL Triggers
- Trigger Auditing
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