Course Description:

This course is designed to give users an understanding of Oracle SQL and Oracle PL/SQL languages using Oracle’s SQL*Plus and iSQL*Plus tools. The course covers SQL commands for DML, DDL, Query, and Transaction Control operations. Students are also introduced to procedural programming using PL/SQL. The course topics are applicable to all versions of Oracle through Oracle 10g

Who Should Attend

Application designers and developers, database administrators and operators, and end users should attend this course.

MODULE 1

Database Design

• Database Models
• Beginnings
• WHAT IS DATA BASE MANAGEMENT SYSTEMS (DBMS)
• Benefits of DBMS
• WHAT IS A RELATIONAL DATA BASE MANAGEMENT SYSTEM (RDBMS)
• DBMS VS/ RDBMS
• NORMALIZATION
• First normal Form
• Second Normal Form
• Third Normal form
• Higher Order Normal Form

MODULE 2

Oracle Standard Interfaces

• INTRODUCTION TO STRUCTURED QUERY LANGUAGE (SQL)
• Features of SQL
• Features of SQL *PLUS
• SQL V/s SQL *PLUS
• Rules for SQL
• Components of SQL
• Example of DDL, DML and DCL Commands
• SQL*Plus
• Oracle Architecture
• Viewing a Sample Table
• The SQL Buffer
• The Web-Based Interface

MODULE 3

The Sample Database

• The Entity Relationship Model
• Entity Relationship Diagrams
• The Sample Database
• ER Diagram for Sample Database
• Creating the Sample Data
• Viewing Sample Data
• Data Types of the Sample Data
• TABLE FUNDAMENTALS
• Oracle Data Types
• Basic Data Types

MODULE 4

Data Definition Language

• Categories of SQL Statements
• The CREATE Statement
• Rules for Creating Tables
• Brief Checklist When Creating Tables
• Inserting Data Into Tables
• The DROP Command
• The ALTER Command
• Integrity Constraints
• Entity Integrity Constraints
• Referential Integrity Constraints
• Modifying Table to Use Constraints
• Checking Constraints
• The Data Dictionary

MODULE 5

Data Manipulation Language

• The SELECT Statement - VIEWING DATA IN THE TABLES
• All Rows and All Columns
• Filtering Table Data
• -Selected Columns and All Rows
• Selected Rows and All Column
• Selected Columns And All Selected Rows
• The INSERT Statement
• insertion of a data set into Table from Another Table
• The DELETE Statement
• Removal of all Rows
• Removal Of Specific Row
• Removal of Specific Row Based on the data held the other table
• The UPDATE Statement
• Updating All Rows
• Updating Records Conditionally
• MODIFYING THE STRUCTURE OF TABLES
• Adding New Columns
• Dropping a Column From a table
• Modifying Existing Column
• RENAMING TABLES
• TRUNCATE TABLES
• DESTROYING TABLES

MODULE 6

Transaction Control

• Transactions
• Command Classification
• Savepoints
• The SET TRANSACTION Command

MODULE 7

SQL Operators

• Simple Selects
• COMPUTATIONS DONE ON TABLE DATA
• Arithmetic Operators
• Renaming Column used with Expression lists
• Logical Operators
• Range Searching
• Pattern Matching
• Comparison Operators
• BETWEEN Operator
• The LIKE Operator
• Logical Operators
• IS NULL and IS NOT NULL

MODULE 8

SQL FUNCTIONS

• Introduction
• The DISTINCT Keyword
• Mathematical Functions
• String Functions
• MANIPULATING DATES IN SQL USING THE DATE ()
• TO_SIZE
• TO_DATE
• Conversion Functions

MODULE 9

Joining Tables

• Joins
• Cartesian Product
• Inner Joins
• Equi-Join
• Table Aliases
• Non-Equi Join
• Non-Key Join
• Natural Join
• Outer Joins
• Right Outer Join
• Left Outer Join
• Full Outer Join

MODULE 10

Set Operators

• Introduction
• Selection Criteria
• Union
• Union All
• Intersect
• Minus

MODULE 11

SQL Subqueries

• Introduction
• Using sub-query in the FROM Clause
• Using Correlated Sub-Queries
• Using Multi Column Sub-Queries
• Using Sub-Query in CASE Expressions
• Using Sub-Query in An ORDER BY clause
• Using EXITS / NOT EXISTS Operator

MODULE 12

Groups

• SQL Statements
• GROUP BY Clause And HAVING Clause
• GROUPING DATA FROM TABLES IN SQL
• The Concept of Grouping
• GROUP BY Clause
• HAVING Clause
• Determining Whether Values are Unique
• Group By Using the ROLLUP Operator
• Grouping By Using the CUBE Operator
• Order of a SELECT Statement

MODULE 13

More Database Objects

• VIEWS
• Creating View
• Renaming the Columns of a View
• Updateable Views
• Views Defined From Multiple Tables
• Common Restrictions on Updateable Views
• Destroying a View
• Relational Views
• Updating a View
• Create or Replace
• Indexes
• Address Field in the index
• Duplicate / Unique index
• Creation of an Index
• Creating Simple Index
• Creating Composite Index
• Creation of Unique Index
• Reverse Key Indexes
• Bitmap Indexes
• Function Based Index
• Key-Compressed Index
• Dropping Index
• MULTIPLE INDEXES ON A TABLE
• Instances when the oracle Engine Uses an Index for Data Extraction
• Instances when the oracle Engine Does Uses an Index for Data Extraction
• USING ROWID TO DELETE DUPLICATE ROWS FROM A TABLE
• Inner Select Statement
• Parent SQL Statement

MODULE 14

Introduction

• SQL vs. PL/SQL
• ADVANTAGES OF PL/SQL
• THE GENERIC PL/SQL BLOCK
• The Declare section
• The Begin section
• The Exception section
• The End section
• THE PL/SQL EXECUTION ENVIRONMENT
• PL/SQL
• The Character set
• Literals
• PL/SQL DATA TYPE
• Variables
• Constant
• Logical Comparisons
• comments
• CONTROL STRUCTURE
• Condition control
• Iterative control
• Sequential control

MODULE 15

Declarations and Data Types

• Declarations
• Standard Data Types
• Initialization
• Variable Names
• Specialized Data Types - %TYPE
• Specialized Data Types - %ROWTYPE
• Building Your Own Data Types - Records
• A Quick look at Loops
• Arrays
• Tables
• Nested Blocks

MODULE 15

Language Components

• Introduction
• Assignments
• Decision Making Statements
• Simple Loops
• Loops - for
• Loops - indefinite
• Loops - while
• Simple Loops
• Nested Loops
• Boolean Variables
• PL/SQL Relational Operators
• PL/SQL Logical Operators
• The CASE Construct

MODULE 16

Cursors

• Introduction
• WHAT IS A CURSE?
• Types of cursor
• Implicit cursor
• Explicit cursor
• Cursor Manipulation
• Using the Cursor
• For Loops Cursors
• Cursor Attributes
• Cursor Parameters
• Nested Cursors

MODULE 17

Exceptions

• Errors in Programs
• Run Time Exceptions
• ERROR HANDLING IN PL/SQL
• ORACLE’S NAMED EXCEPTION HANDLERS
• USER-Named Exception Handlers
• User Defined Exception Hand ling (For I/O Validations)
• User Defined Exception Handling (For Business Rule validations)

MODULE 18

PL/SQL SECURITY

• LOCKS

• Oracle’s Default Locking Strategy- Implicit Locking
• Types of Locks
• Levels of Locks
• Explicit Locking
• The SELECT…..FOR UPDATE statement
• Using Lock Table statement
• Releasing – Locks
• Explicit Locking Using SQL and the Behavior of the Oracle Engine
• Explicit Locking Using PL/SQL and the Oracle Engine
• Deadlock

MODULE 19

Functions and Procedures

• What Are Procedures / Function?
• Declarative Part
• Executable Part
• Exception Handling Part
• WHERE DO STORED PROCEDURES AND FUNCTION RESIDE?
• How Does The Oracle Engine Create A Stored Procedure / Function?
• How Does The Oracle Engine Execute Procedure / Function?
• ADVANTAGES OF USING A PROCEDURE OR FUNCTION
• PROCEDURES VERSUS FUNCTIONS
• Creating Stored Procedure
• Keywords and Parameters
• Creating A Functions
• Keywords and Parameters
• Using A Function
• Using A Procedure
• FINALLY A COMMIT IS FIRED TO REGISTER THE CHANGES
• DELETING A STORED PROCEDURE OR FUNCTION
• ORACLE PACKAGES
• Components Of An Oracle Package
• Why Use Packages?
• Packages Specification
• Creating Packages
• Package Objects – Private V/S Public
• Variables, Cursors And Constants
• Package State
• Package Dependency
• DATA BASE TRIGERS
• -Inroduction
• Use Of Data Base Triggers
• Data Base Triggers V/S Procedures
• Data Base Triggers V/S Declarative Integrity Constraints
• How To Apply Database Triggers
• TYPES OF TRIGGERS
• Row Triggers
• Statement Triggers
• Before Triggers
• After triggers
• Combination Triggers
• DELETING A TRIGGER
• Application Using Database Triggers
• GENERATING A PRIMARY KEY USING A DATA BASE TRIGGER
• Introduction
• Automatic Primary Key Generation
• Generating Primary Key Using Sequences
• Generating Primary Key Using The MAX FUNCTION
• Generating Primary Key Using A Lookup Table

Oracle Training center in West Delhi, Uttam nagar. Best certified computer course curriculum for professional learning institute near Dwarka, Uttam Nagar, Jankapuri, Tilak Nagar, Subhash Nagar, Tagore Garden, Rajouri Garden, Ramesh Nagar, Moti Nagar, Kirti Nagar, Shadipur, Patel Nagar, Rajendra Place, Karol Bagh, Jhandewalan, Ramakrishna Ashram, Connaught Place, vikaspuri, najafgarh, Delhi Cantt, Dhaula Kuan, Palam Colony, Palam Villag, Dabri, Hari Nagar, Fateh Nagar, Raja Garden, Punjabi Bagh, Shivaji Park, Paschim Vihar, Peeragarhi, Inderlok, Netaji Subhash Place, Pitampura, Rohini, Chandni Chowk, New Delhi, East Delhi, North Delhi, West Delhi.