Price 15,000.00 GST

Course Features
language
Language: English
access_time
40 hours
spellcheck
Study Level: Intermediate
terrain
Certificate of Completion

SQL Basics & PL-SQL

Complete Practical & Real-time Training Sessions

Training Highlights

Complete Practical and Real-time Scenarios

Session wise Material and Practice Labs

Session wise Notes & Doubts Clarifications

Certification Material & Resume Preparation

Interview Preparation and Guidance

Technical Support and Placements Assistance

One Real-time Project and FAQs with Answers

Mock Interview and Course Completion Certificate

All Training Sessions are Completely Practical & Real-time

Every session includes Study Material and Practice Material.

Course includes total of 21 Chapters.

SQL Basics & Oracle PL/SQL Developer Training

All Our Training Sessions are COMPLETELY PRACTICAL & REALTIME with Hands-On Lab.

Chapter 1 : INTRODUCTION TO DBMS

What is Data and Information?

Purpose of Data Management

File and File System For Data Storage

Disadvantages of File Data Management

Database : Purpose, Basic Terminology

Advantages of Database for Data Storage

Possible Operations on the Database

Database Models Hierarchical Model

Network Model and Relational Model

History, Real-time Database Examples (LIVE)

Chapter 2 : INTRODUCTION TO RDBMS

Features of RDBMS

Advantages of RDBMS

E.F Codd Rules for RDBMS

Normalization Concepts & Process

Client Server Communication

Oracle Corporation Products

RDBMS from Other Vendors: Microsoft, IBM,etc

Oracle Versions about SQL & SQL*PLUS

Database Types: OLTP and OLAP Databases

Real-world Advantage of OLTP & OLAP

Applicative use of RDBMS Databases

Chapter 3 : SQL LANGUAGE COMMANDS

Structured Query Language (SQL) Basics

Possible Operations with SQL

Data Definition Language (DDL)

Data Retrieval Language (DRL)

Data Manipulation Language (DML)

Transaction Control Language (TCL)

Database Security and Privileges (DCL)

Rules of SQL Queries and Statements

Real-world applicative uses of SQL

Chapter 4 : SQL BASICS

Creating Databases and Tables

Chapter 12 : PL/SQL (PROCEDURE LANGUAGE – SQL)

Introduction to Programming Languages

Introduction & Advantage 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 Statements

Ladder, Selection, Simple Case Statements

GOTO Label and EXIT Statements in PL/SQL

Iterations in PL/SQL, Simple LOOP, WHILE

FOR LOOP and NESTED LOOPS in PLSQL

SQL within PL/SQL, Composite Data Types

Cursor Variables and Management in PL/SQL

Implicit & Explicit Cursors and Attributes

Cursor with Parameters and Nested LOOPs

Cursors with Sub Queries, Reference Cursors

Record and PL/SQL Table Types. Real-time Use

Chapter 13 : ADVANCED PL/SQL – 1

Procedures in PL/SQL: STORED PROCEDURE

PROCEDURES @ Parameters (IN, OUT, IN OUT)

POSITIONAL Notation and NAMED Notation

Procedure with Cursors and Sub Queries

ALTER and DROP of Stored Procedures

Functions in PL/SQL: Real-time Usage

User Defined Functions, Nested Functions

Using Functions in SQL Statements

Working with Procedures and Functions

Comparing Stored Procedures and Functions

Using SPs with Table Value Functions

Using SPs for Dynamic SQL Statements

Loops and Table Variables in SQL Programs

Merge, NVL2(), NULLIF(), COALESCE()

CASE & Temporary /Global Tables

Chapter 14 : ADVANCED PL/SQL – 2

Pragma_Autonomous_Transaction() with SPs

Returning into clause, Bulk Collect

For All, Definer/Invoker Rights & Usage

About Flash Back Queries, Dynamic SQL

Flash Back Command, Purge Command

Recycle Bin, Regular Expressions in PLSQL

DML Error Logging and Virtual Columns

Types of Triggers, Row Level Triggers

Table Data Inserts and Validations

Schema & Schema Objects

Oracle Data Types

DDL Commands with Examples

CREATE, ALTER, DELETE Statements

DML Command DRL Commands Operators

INSERT, UPDATE, DELETE Statements

SELECT Statements with Multiplier

WHERE Keyword for Query Conditions

SET, AND, OR, NOT, IN

Special Operators IN (NOT IN)

BETWEEN (NOT BETWEEN),

Arithmetic and Logical Operators

UNION, UNION ALL, INTERSECT, MINUS

LIKE (NOT LIKE), IS NULL (IS NOT NULL)

Chapter 5 : BUILT-IN FUNCTIONS in SQL

Arithmetic Functions

Character Functions

Date & Time Functions

Character, String Functions

Conversion Functions

Aggregate Functions

General Functions

OLAP Functions

Other Functions

Chapter 6 : GROUPING QUERY RESULTS

Identify Distinct Values in Tables

Group By Operations in Queries

Having Clause of DRL Statements

Aggregate Functions with Group By

Order By Clause and Group By

Query Execution Order with Group By

Sequences, Working with Synonyms

Creating Index Tables – Purpose

Creating Cluster Tables

Implementing Locks For Concurrent Access

Working with Roles and Permissions

Chapter 7 : CONSTRAINTS & KEYS

Importance of Data Integrity

Working with Integrity constraints

NOT NULL Constraint, UNIQUE Constraint

PRIMARY KEY Constraint and Usage

FOREIGN KEY Constraints and Relations

ON UPDATE Clause in Foreign Keys

ON DELETE Clause in Foreign Keys

Statement Level Triggers, DML Triggers

DDL Triggers and Schema Level Triggers

Using OLD & NEW References, Trigger Auditing

Enabling / Disabling Triggers, Dropping Triggers

Triggers in PL/SQL and Data Manipulations

Using Memory Tables in Triggers DML and DDL Events with Triggers

Compound Triggers, New data types

Working with LARGE Tables in PLSQL

New: EXTRACT(), Autonomous Transaction

Chapter 15 : ADVANCED PL/SQL – 3

Cross Tab Views using Pivot/Unpivot Operators

Follows Clause and READONLY Tables

VIEWS IN ORACLE, Understanding Views & Use

Relational Views and Standard Views

SIMPLE VIEWS and COMPLEX VIEWS in PLSQL

Column Definitions in VIEWS

Using VIEWS for DML Operations

Forced Views, CHECK Constraints in Views

Creation of READ ONLY VIEWS – Realtime Use

TOP-N Analysis, (Indexed) Materialized Views

IN-LINE VIEWS. Manipulations with Triggers

Purity Levels in Oracle – with Examples

User_Source Dictionary Table Packages

Creating PACKAGE Specifications with PLSQL

PACKAGE Body – Examples with Big Data Tables

Private and Public Objects in PACKAGE

Types of Exceptions: User Defined Exceptions

EXCEPTIONS in PL/SQL with Real-time

Event Handling and Error Handling Techniques

Important Error Code Values in PL/SQL

Chapter 16 : PERFORMANCE (QUERY) TUNING

Indexes – Definition and Architecture

B Tree Concept in Indexes

Clustered and Non clustered Indexes

Primary Key and Clustered Index

Unique Key and Non Clustered Indexes

Materialized Views – Indexed Views

Partitioning Tables using PL/SQL

Working with Very Large Tables

Range Partitions and Hash Partition

List Partition and Composite Partition

Parallel Query Process with Partitions

Performance Tuning Advantages

Chapter 17 : LOCKS in ORACLE TABLES

Open Transactions and Lock Concepts

Lock Types and Locking Mechanism

Row Level Locks for Table Data Access

Multiple Constraints on Columns

Adding Constraints to Tables

Enabling – Dropping Constraints

Disabling Constraints on Tables

Querying for Constraints Information

Data Validations using Constraints

Constraints Limitations and Usage

Chapter 8 : SUB QUERIES, NESTED QUERIES

Sub Queries in Real-world

Dynamic Conditions with Sub Queries

Sub Queries and Nested Sub Queries

Inner Select and Outer Select Queries

Usage of Sub Queries with WHERE, HAVING

Impact of Having Clause in Sub Queries

IN, ANY SOME, ALL Operators in Sub Queries

PAIR WISE and NON PAIR WISE in Sub Queries

NULLs and Correlated Sub Queries in SQL

Data Retrieval with EXISTS, NOT EXISTS

Chapter 9 : QUERIES & JOINS

Need for Joins and Table Comparisons

SET Operations on Tables and Joins

Join Types : Equi Join, Simple Join

Inner Join and Query Conditions

Cross Join (Cartesian Join), Conditions

Non-Equi Join and Self Join

Outer Joins Types, Advantages and Usage

Cross Joins Advantages and Limitations

Self Joins, Merge Joins and Sub Queries

Using Aliases in Joins and Conditions

Using Inner Self Joins with HAVING JOINS and GROUP BY Queries

Chapter 10 : DCL, TCL COMMANDS

Understanding DCL and TCL Commands

Transaction Concepts in Databases with SQL

Transaction Types and Uses

Commit and Rollback Operations

Nested Transactions with Savepoints

Database Read Consistency with SQL

 

DDL and DML Operations with Transactions

Chapter 11 : PSEUDO COLUMNS in ORACLE

Understanding Pseudo Columns in Oracle

Type of Pseudo Columns in Oracle

CURRVAL AND NEXTVAL, LEVEL

ROWID, ROWNUM

Table Level Locks and Query Blocking

Shared Lock and Real-time Use

DeadLock : Detection, Prevention, Avoidance

Implicit Locking Concepts in Oracle with PLSQL

LOCK Hints and Query Blocking

Object Technology and Applicative Use

OOPS-Object Instances, Creation of Objects

Creating User Defined Data Types

Creating Object Tables in Oracle

Inserting rows in Table using Object

Retrieving data from Object Based Tables

Calling a Method, Indexing Abstract Data type Attributes in PLSQL

Chapter 19 : USING COLLECTIONS

Advantages of Collections

Ref Cursor (Dynamic Cursor), Weak Ref Cursor

Strong Ref Cursors and Nested Tables

Associative Arrays, VARRAYS/VARYING arrays

Creating tables using Nested Tables

Inserting, Updating & Deleting

Nested, Table Records

Chapter 20 : ORACLE BUILT-IN PACKAGES

DBMS_OUTPUT Package

DBMS_DDL Package

DBMS_JOB Package

DBMD_SCHEDULER Package

UTL_FILE Package

UTL_MAIL Package

Architecture

Physical Structures Logical structures

DB Memory Structures

Chapter 21 : ORACLE UTILITIES

Oracle Data Pump

SQL * Loader

External Tables

—————————————————–

Course includes:

Real-time Case Studies

Interview Questions

Resume Guidance

Placement Support

 

 

chat_bubble_outlineReviews

Average Rating

0
No Votes 0 Votes
15,000.00 GST
0 Ratings

Detailed Rating

5 Stars
0
4 Stars
0
3 Stars
0
2 Stars
0
1 Stars
0

There are no reviews yet.

Be the first to review “Sql & plsql developer”

Your email address will not be published. Required fields are marked *