Tecnosoft | Training Instistute Website

Oracle (SQL/PL/SQL)

Home > Courses > Oracle (SQL/PL/SQL)



Online Fee


Classroom Fee


Students Enrolled



Upcoming Batch Schedule for Online Training

Tecnosoft provides flexible timings to all our students. Here are the Online Training Schedule in our branches. If this schedule doesn’t match please let us know. We will try to arrange appropriate timings based on your flexible timings.

  • 27-04-2020 Monday (Monday - Friday)Weekdays Regular 08:00 AM (IST)(Class 1Hr - 1:30Hrs) / Per SessionCourse Fees
  • 30-04-2020 Thursday (Monday - Friday)Weekdays Regular 08:00 AM (IST)(Class 1Hr - 1:30Hrs) / Per SessionCourse Fees
  • 25-04-2020Saturday (Saturday - Sunday)Weekend Regular11:00 AM (IST) (Class 3Hrs) / Per SessionCourse Fees
  • 25-04-2020Saturday (Saturday - Sunday)Weekend Fast-track 10:00 AM (IST)(Class 6Hrs - 7Hrs) / Per SessionCourse Fees
Tecnosoft Calender
Can’t find a batch you were looking for?

Enroll Now

Get Instant access to 5000+ Online courses

Course Curriculam

Database Introduction

  • Oracle Corporation Products
  • What is sever Software
  • What is Application Software?
  • Package Vs. Language
  • What is Database?
  • Objective of Database

Database System

  • What is DBMS
  • Oracle Database Architecture
  • Oracle Database
  • Oracle Instance/Engine
  • Importance of RAC(Real Application Cluster)
  • Usage of Listener Service
  • What is Database Application?
  • Frontend Vs. backend
  • What is ODBC?
  • Role of ODBC Drivers in Database System
  • Client/Server Technology

Database Servers

  • OLTP DB Server
  • OLAP DB Server

Different types of Database Management System

  • FMS
  • Drawbacks of FMS
  • DBMS
  • Drawbacks of DBMS
  • E.F. CoddRules(12)
  • What is Entity and Attribute?
  • Drawbacks of RDBMS
  • Difference between DBMS, RDBMS and ORDBMS

Introduction to SQL

  • What is SQL?
  • Procedural vs. Non-procedural Languages
  • Role of SQL in RDBMS
  • Features of SQL
  • What is SQL*PLUS
  • PLUS Commands

Different Types of SQl Languages

  • DDL Commands : Create, Alter, Drop
  • Truncate, Rename, Purge, Flashback
  • DML Commands : Insert, Insert all, Upadate, Delete, Merge
  • DRL Commands : Select
  • TCL Commands : Commit, Rollback, Savepoint
  • DCL Commands : Grant , Revoke
  • System Control Commands : Alter System
  • Session Control Commands: Alter session

Oracle Data Types

Creation of Table(Create Command)

  • Naming Rules
  • Creating a table
  • Creating a table based on another table
  • Creating table with Default Values
  • Viewing Table structure
  • What is Data Dictionary
  • Metadata
  • Schema
  • User
  • What is SQL Buffer

DML Command – Insert

  • Insertion of data
  • Insertion of data at runtime [address method]
  • Insertion of nulls [implicit &Explicit method]
  • What is null
  • Overriding nulls with user defined values
  • Multiple inserts
  • Insert all Commands

DRL/DQL- Select Command

  • Selection of data
  • Projection
  • Creating column alias name
  • Usage of column alias names

Copying Table Data

  • Copying Rows fromone table to another table
  • Creating table based on another table with /without data

Distinct Clause

  • Eliminating Duplicate Values

Order By Clause (Sorting Data)

  • Sorting data based on single column
  • Sorting data based on multiple columns
  • Sorting columns with Column posting and alias names
  • Sorting null values

Where Clause

  • Filtering Data

SQL Operators

  • Arithmetic Operators
  • Relational Operators
  • Relational Negation Operators
  • Logical Operators
  • Set Operators( Vertical Joins)


Single Row Functions

Numeric Functions

Character Functions

Date Functions

Data Conversion Functions

Miscellaneous/General Functions

OLAP Functions

Rank(),dense_rank(), row_number(),rollup(),cube()

New Functions

Nvl2(),null if(),coalesce(),extract(),case,………

Group/Aggregate Functions



Integrity Constraints

Data Integrity

Data Redundancy

Data Inconsistency


Function Vs Transitive Dependency

Types of constraints

Domain Integrity Constraints

Not Null,Check

Entity Integrity Constarints

Unique, Primary Key

Referential Integrity Constraints


Levels of Constraints

Column Level Constraint

Table level Constraint

Composite primary Key Constraint

Unique vs. primary Key

Primary Key vs. Foreign Key

Using Alter Command (DDl)

Enabling/Disabling Constraints

Adding/Dropping Constraints

Viewing Constraints



Alter, Drop, truncate, Rename, purge, Flashback

Flash Back Queries

Flashback Tables

Recyclebin Command

Purge Command

Read only Tables

Read/write tables



What is Join

Join Vs. Reference Constraint

Types of joins

Cross Join (Cartesian Join)

Inner Join

Inner Equi Join

Inner Non- Equi Join


Self Equi Join

Self Non-Equi Join

Outer Joins

LeftOuter Join

Right Outer Join

Full Outer Join

Natural Join




Sub Queries

What is Sub Query?

Types of Sub Queries

Single Row Sub Query

Multi Row Sub Query

Multi Colum Sub Query

In Line Query

Correlated Sub Query

Sub Query With Multiple tables

Usage of <all, >all, <any, >any operators in sub query

Sub Query Vs. Self join

Sub Query Vs. Inner join Sub queries with Updates Sub queries with Delete

Pseudo Columns

User, uid,rownum, rowed,sysdate,currents_date,nextval,cuur_val,new,old,….


Grouping the result of a query

Usage of group by clause


Having Clause Order of execution of all Clauses


DML Commands- Update

  • Data Updating
  • Updating data using sub queries

TCL Commands

  • Commit
  • Rollback
  • Save Point

DCL Commands

What is Privilege?

What is Role

Types of Privilege

System level Privileges

Object level Privileges

Granting Privileges Revoking Privileges

Viewing Privileges




PLUS Commands

Environment Setting Commands

Screen Formatting Commands



Database Objects


What is Index?

Importance of Index

Btree index

Unique index

Composite index

Function based index

Bitmap index

Viewing indexes

Dropping indexes


What is Cluster?

Importance of Cluster

Viewing Cluster

Dropping Cluster


What is Sequence?

Importance of Sequence

Usage of nextval and currval

Viewing Sequences

Dropping/altering sequences


What is synonym?

Importance of synonym

Types of Synonyms

Private Synonym

Public Synonym

Viewing/dropping synonyms



What is View

Importance of views

Types of views

Simple views

Complex Views

Forced views

View with CHECK OPTION Clause

View With READ ONLY Clause

DML operations on simple views

Instead of trigger

Compiling Views

Finding invalid views

Viewing/dropping views


Materialized View

What is materialized view

Importance of materialized view

Viewing/dropping materialized views



Rows level locks

Table level locks

Shared locks

Exclusive locks


Types of partitions

Range Partition

List partition

Hash partition


Introduction to programming languages

Introduction to Oracle PL/SQL

Procedural Vs Non –procedural Languages

Usage of PL/SQL in production Database

Key Features of PL/SQL over SQL

Architecure of PL/SQL

Types of PL/SQL Blocks

Anonymous PL/SQLBlock

Nested PL/SQL Block

Named Pl/SQL Block

Labeled PL/SQL Block

Structure of PL/SQL Block


Assignment Operator Concentration Operator PL/SQL Comments Debugging Statements


What is Variable?

Variable declaration

Variable with Default values

Not null Variables

Constant Variables

Data types

Oracle data types

ANSI Data types

PL/SQL Data types

Sub types

User defined data types

Flow Control Statements

Simple if

If..else…end if

Nested if

If…elsif…end if




Looping Control statements

While loop

Simple/infinite loop

For loop

Nested loops

Embedded SQL

What is embedded SQL

Attribute/Anchor data types


%rowtypeUsage of Embedded SQL

Cursor Management

What is cursor

Types of cursors

Implicit Cursor

Explicit Cursor

Cursor Attributes

Cursor using simple loop

Cursor using While loop

Cursor for loop

Cursor parameter

Ref Cursor

Dyanamic Ref Cursor

Static Ref Cursor

PL/SQL Records

PL/SQL Tables

Bulk Collect/Bulk Binding

Dynamic SQL

Advanced PL/SQL


Anonymous Vs. Named PL/SQL

Procedure Vs.Function


Stored procedures

What is stored procedure

How to Execute stored procedure

Procedure without parameters

Procedure with parameters

Procedure with Cursors

Procedure with in a procedure

Procedure with Default Arguments

Passing arguments in positional and Named method

Bind Variables


Types of parameters

In, Out, in out parameters

Host/Binding Variables

Viewing procedures

Granting privileges on procedures

Dropping procedure


Stored Functions

User Defined Functions

Nested Functions

Viewing Functions

Dropping Functions



What is package

Importance of package

Creating package specification

Creating package body

Private and public objects in packages

Function Overloading mechanism package


Exception Handling

What is exception

Exception types

Oracle predefined Exception

Oracle Non- predefined Exception

User Defined Exceptions



Usage of PRAGMA exception_init ()

SQL Error Code Values



What is trigger

Usage of trigger

Types of Triggers

Row Level Triggers

Statement Level Triggers

Trigger events

New and old pseudo columns usage

Instead of trigger

DDL Triggers

Trigger auditing

Enabling/Disabling Triggers

Dropping Triggers

Viewing Triggers


Object Technology

What is Object Technology

Creating objects

Creating user defined Objects

Creating Object tables

InsertingRows in a table using objects

Retrieving data from object based tables



Ref Cursor

Static Ref Cursor

Dynamic Ref Cursor

Nested tables


Creating tables using nested tables

Inserting, updating& deleting nested tables


Introduction to SQL*Loader

Introduction to Toad

Scroll to top