PL/SQL PROGRAMMING

Oracle Training Contents

Introduction

• Overview of Database
• Concept of DBMS
• Components of a DBMS
• Relationship among data
• Overview of RDBMS
• Overview of ORDBMS
• CODD’S Rule
• Normalization
• Denormalization

Overview Of Oracle Database Architecture

• Introduction Of Oracle
• Oracle Database Memory Structure
• Overview Of System Global Area
• Database buffer Cache
• Shared Pool
• Oracle Process
• Oracle Files
• Storage Architecture
• Segments
• Data Blocks
• Working Of Oracle Architecture Engine

Introduction to SQL

• Sub languages Of SQL
• SCHEMA Concepts
• Rules Of SQL commands
• Working with Alias
• Working with Virtual Column (VC)
• Dual Table

Conditional Selections and Operation

• Clause in Oracle
• Sorting Column through column number
• Sorting table using multiple columns
• Precedence Order of Operators
• Special Operators
• Case Statements

Single –Row function

• What is Sql function
• Types of Sql function
• Types of single-row function
• Character Function
• Analytical(OLAP) function

Multi-Row functon

• Types of group function
• Group By clause
• Restriction of groups using HAVING clause
• OLAP options in multi-row function

Joins

• Introduction
• Advantages Of joins
• Different types of joins
• Set Operators

Sub-queries

• Types of Sub-Queries
• Applying HAVING CLAUSE with sub-query
• Inline View
• Correlated Sub-query
• Exists and not exists operator

Working with substitution Variables and Producing readable output

• Substitution Variables
• Types of substation variables
• DEFINE and UNDEFINE Commands

Data Definition Language

• DDL statements
• Types of tables
• Table Structure
• Create table statements
• Alter table statement
• Drop statements
• Truncate a table
• Types of integrity constraints
• Viewing a constraint
• Removing a constraint
• Status of constraints
• Deferrable

Database Transaction

• INSERT Statement
• Insert with SELECT
• Insert all statement
• Update statement
• Merge statement
• Delete statement
• TCL Mechanism

Working with view and other database objects

• Why Views?
• Creating views
• Types of view
• Materialized view
• Performing DML operation on view
• Index
• Advantages Of Index
• Types of Index
• Using wild cards during searches
• Synonym
• Sequence
• Snapshot

Tablespace and Partitioning

• Tablespace
• Partitioning
• Types of table-partition

Hierarchical Queries

• Introduction
• Connect_by root clause

Controlling User Access

• Locking and Unlocking user
• Dropping User
• Confirming the user
• Privileges

Concept Of Locking

• Introduction
• Types of locking
• Levels of locking

Introduction to PL/SQL

• Introduction
• Characteristics of PL/SQL
• Architecture of PL/SQL
• Components of PL/SQL
• Types of variables

Interaction with Oracle Server