SQL & PL/SQL - Innozant MIS & Data Analytics

Administrator Expert


No prior technical experience is required! This course teaches SQL & PL/SQL from the very basics

SQL is Structured Query Language, It is used to communicate with database for storing, manipulating and retrieving data stored in a relational database.

PL/SQL is basically a procedural language, which provides the functionality of decision making, iteration and many more features of procedural programming languages. … One can create a PL/SQL unit such as procedures, functions, packages, triggers, and types, which are stored in the database for reuse by applications

What you will learn

  • Become an expert in SQL & Learn how to code in SQL
  • Create, design, and operate with SQL databases
  • The ability to take control of your dataset – insert, update, and delete records from your database
  • Handle complex SQL joins with ease
  • Approach more advanced topics in programming like SQL’s triggers, sequences, local and global variables, indexes, and more
  • Become a proficient SQL user by writing flawless and efficient queries
  • Create an executable section and write control structures.
  • Create and manage procedures, functions, packages and triggers.
  • Some Advanced SQL Techniques
  • Use SQL Developer Efficiently for PL/SQL
  • Learn PL/SQL Architecture Very Well
  • Create Your Dynamic SQL & PL/SQL Queries
  • Learn PL/SQL Object Oriented Programming
  • Learn PL/SQL Tuning for Performance
  • Learn & Master PL/SQL by Coding with Hundreds of Practical Examples!
  • Work with composite data types and cursors utilising Oracle supplied packages in application development including exception handling, manage dependencies and large objects

Download Brochure

Topics for this course

59h 59m 59s

Training Duration Regular: 4 Months


List the Oracle database 11g to 12c main features

An overview of components internet platform app server and developer soap

Describe relational and object relational database designs

Normalization concept

Define the term date models

Review the system development life cycle

Describe different means of sorting data

Show how multiple tables can be related

Writing Complex query and tune SQL Query

Describe how SQL communicates to the database

Introducing SQL basic commands

Introduction of SQL

Writing basic select statements

Restricting and sorting of data

Introducing SQL function

Single row function/ scalar function

Multiple row function/ aggregate function/ group by function

Conditional expression

Using substitution on variable

Introducing SQL commands

Using DDL statements

Managing tables

Data manipulation operation

Understanding transaction

Using transaction control statements

Overview of locks

Using flashback and purge command

Granting and revoking system and Object privileges

Designing Tables by using key constraints

Deferred constraints

Retrieving data from more than one table using join operation.

Aggregating data using group functions

Introducing the view

Introducing the indexes

Introducing the synonyms

Introducing with sequence and used with database

Introducing some queries

Single row sub query, multiple rows of query

Correlated sub query

Top -n analysis

Using the set operators

Inserting and updating data

Deleting data

Creating other schema objects

Managing objects with data dictionary views

Date and time function

Rollup and cube after using group by function

Multiple insertion and the types

Hierarchical tree

SQL loader

Advanced SQL

Overview of analytical function

Introducing in the line view

Introduction of window function

Introduction of window function

Materialized view

Overview of semi join and anti-join

Embedding search query with DML operation

PL/SQL : What is a procedural language?

Difference between procedural language and object oriented language

Introducing PL SQL

PL SQL engine architecture

Overview of the types of PL SQL blocks.

Create and execute a simple and anonymous code block

Declaring PL SQL identifiers variable constant

Writing simple code block in PL SQL

Fetching data from SQL and PL/SQL

Interacting with the server Oracle server

Writing control structures.

Working with the composite data types

Creating and using subtypes and returning Clause.

Introducing cursor and its types

Handling exceptions

Handling error condition by using compiler directives

Overview of named block and oops in PL/SQL.

Creating stored procedures

Creating stored functions

Local subprogram and remote server program and no copy data types.

Inline function and handle restriction of user defined function

Creating packages and the influence of package

Overloading of package

Code encryption by using PL/SQL wrapper

Manipulating large objects

Introducing triggers and its types

Handling mutating and mutating error

Introducing server error Trriger

Creating audit table and session auditing by Trigger

Handle file operation ( File handling)

Advanced PL SQL

Working with href( reference cursor)

Introduction of collection and why it is used

Creating and using associative array nested table Varray

Handling context switching

Save exception

Tuning PL SQL code

Using name spaces

Overview of stored package

Overview of object types in SQL

  • Course level: Expert
  • Duration 59h 59m 59s