Oracle PL-SQL Classroom Training at SR Nagar Hyderabad with Realtime Project

[sequelschool]

Oracle PL-SQL Classroom Training at SR Nagar Hyderabad with Realtime Project

 

 

This Oracle Database: Program with PL/SQL training starts with an introduction to PL/SQL and then explores the benefits of this powerful programming language. Through hands-on instruction from expert Oracle instructors, you'll learn to develop stored procedures, functions, packages and more. Practice Material, Certification, Resume & Interview Guidance are included in this PL-SQL.

 

Highlights

• ✔ Completely Practical and Realtime
• ✔ Theory Material provided in Advance
• ✔ Highly Interactive and Interesting
• ✔ Certification Guidance and FAQs
• ✔ 80% Hands on Training, 20% Theory part Explanation

Module: I

• Approach to Data Management, File and
• File System
• Disadvantages of file base data management
• Database Terminology
• Database Models – Hierarchal Model,
• Network Model, Relational Model

Introduction to RDBMS

• Features of RDBMS
• Advantages of RDBMS over FMS & DBMS
• E.F Codd Rules for RDBMS
• Normalization Process
• Client server Technology
• Oracle Corporation Products
• Oracle Versions about SQL & SQL*PLUS
• Difference between OLTP & OLAP

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

• Schema & Schema Objects
• Oracle Data Types
• DDL Commands
• DML Command DRL Commands 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)

Built-in Functions

• Arithmetic Functions
• Character Functions
• Date functions
• Conversion Functions
• Aggregate Functions
• OLAP Functions & General Functions

Grouping the result of a query

• Using Group by and Having Clause of DRL Statement
• Using Order by clause
• Working with, Sequences, Working with Synonyms,
• Working with Index and Clusters, Creating Cluster Tables,
• implementing Locks, Working with roles

Working with Integrity Constraints

• Importance of Data integrity
• Working with different types of integrity constraints
• NOT NULL constraint, UNIQUE constraint,
• PRIMARY KEY constraint, REF constraint
• Understanding ON DELETE clause in referential integrity constraint
• 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, Self Join

Working with Sub Queries

• Understanding the practical approach
• to Sub Queries/Nested Select/Sub Select/Inner Select/Outer Select
• Usage of 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 Date Retrieval with EXISTS and NOT EXISTS Operators

Working with DCL, TCL Commands

• Grant, Revoke,
• Commit, Rollback, Savepoint
• Read Consistency

Maintaining Database Objects

• VIEWS IN ORACLE
• Understanding the Standards of VIEWS
• in Oracle
• Types of VIEWS: Relational Views,
• Practical approach of SIMPLE VIEWS and COMPLEX VIEWS,
• Column definitions in VIEWS, Using VIEWS for DML Operations,
• forced Views, Putting CHECK Constraint upon
• VIEWS, Creation of READ ONLY VIEWS, Understanding the IN LINE VIEWS
• TOP-N Analysis, About Materialized
• Views, View Triggers
• Outer Joins – Types, Advantages and Usage
• Cross Joins – Advantages and Limitations
• Self Joins, Merge Joins and Sub Queries
• Purity Levels, User _Source Dictionary table Packages in PL/SQL:
• Creating PACKAGE Specification

Module: II

• PACKAGE Body, Private and Public Objects in PACKAGE
• XCEPTIONS in PL/SQL: Types of Exceptions: User Defined Exceptions
• SQL Error Code Values

Pseudo Columns in Oracle

• Understanding Pseudo Columns in Oracle
• Type of Pseudo Columns in Oracle
• CURRVAL AND NEXTVAL, LEVEL, ROWID, ROWNUM

Data Partitions & Parallel Process

• Range Partitions, Hash Partition
• List Partition, Composite Partition, Parallel
• Query Process

Locks

• Row level Locks
• Table level Locks
• Shared Lock
• Dead Lock
• Implicit Locking

PL/SQL (Procedure Language – SQL)

• introduction to Programming Languages
• Introduction PL/SQL, The Advantages of
• PL/SQL
• PL/SQL Architecture, PL/SQL Data types
• Variable and Constants, Using Built-in
• Functions
• Conditional and Unconditional Statement
• 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: STORES PROCEDURE,
• PROCEDURE with Parameters (IN, OUT and IN OUT
• POSITIONAL Notation and NAMED Notation,
• Procedure with Cursors, Dropping a Procedure
• Functions in PL/SQL: Differences between
• Procedures and Functions,
• User Defined Functions, Nested Functions,
• using stored function in SQL statements
• Pragma_Autonomous_Transaction(),
• Returning into clause, Bulk Collect,
• For All, Definer/Invoker Rights
• About flash Back Queries, Dynamic SQL,
• Flash back command, Purge Command, Recycle bin, Regular expressions,
• DML Error Logging, Virtual Columns, Read only tables,
• Cross tab Views using, Pivot/Unpivot Operators Follows Clause,
• Compound Triggers, New data types
• Working with LARGE Objects

Database Triggers in PL/SQL

• Types of Triggers, Row Level Triggers,
• Statement Level Triggers, DML Triggers, DDL Triggers, Schema Triggers
• Using OLD and NEW References, Trigger Auditing,
• Enabling / Disabling Triggers, Dropping Triggers

Implementing Object Technology

• What is Object Technology?
• OOPS-Object instances, Creation of objects
• Creating user Defined Date Types
• Creating Object Tables
• Inserting rows in a table using object
• Retrieving date from Object based
• Tables
• Calling a Method, Indexing Abstract
• Data type Attributes

Using Collections

• Advantages of collection
• Ref Cursor (Dynamic Cursor), Weak Ref Cursor,
• Strong Ref Cursor, Nested Tables
• Associative Arrays, VARRAYS or
• VARYING arrays
• Creating tables using nested tables
• Inserting, Updating & Deleting
• Nested, Table Records

Advanced Features

• 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 Transaction

Oracle Built-in Packages

• DBMS_OUTPUT Package
• DBMS_DDL Package
• DBMS_JOB Package
• DBMD_SCHEDULER Package
• UTL_FILE Package
• UTL_MAIL Package

Introduction to Oracle Database

• Introduction to Oracle Database
• Architecture
• Physical Structures Logical structures
• DB Memory Structures

Oracle Utilities

• Oracle Data Pump
• SQL * Loader
• External Tables

Register today for free demo @ http://sqlschool.com/register.html