Oracle SQL & PL SQL

Call Us: +91 95000 19355

oracle sql plsq

Essha Technologies is one of the best Oracle training institutions in Chennai, which can provide a brilliant training service for Oracle SQL & PL SQL course with the best real-time projects. We assure that our students can get a broad knowledge in SQL &PL/SQL (Procedural Language/Structured Query Language) at a least fee. By giving an expert training for SQL &PL/SQL languages, our students will be whiz among others.

 

  1. Basic DATABASE Concept and SQL

Basic history of database concept: DBMS, RDBMS, ORDBMS

Advantage of ORACLE database and version information

Interface tools usage: sqlplus, isqlplus, sqldeveloper, Toad

SQL Language overview : DQL, DML, DDL, DCL, TCL

What is the usage of ANSI standard.

SELECT Command – Column Alias Rules, String data,

Concatenations with various data

Null Value handling with number and characters,

Arithmetic Operator

Concatenation Operator,

Eliminating Duplicate Rows

 

  1. Restricting and Sorting Data

WHERE Clause – Character Strings and Dates, number

General Comparison Conditions = > >= < <= <>

Other Comparison BETWEEN , IN , LIKE , NULL

Logical Conditions AND OR NOT

ORDER BY Clause, Sorting by Column Alias , Column Position, Multiple Columns

  1. Single-Row Functions

Character Functions: UPPER, LOWER, INITCAP, LENGTH, SUBSTR, INSTR, LPAD, RPAD, CONCAT, LTRIM, RTRIM, TRIM, REPLACE, TRANSLATE, REVERSE

Number Functions: ROUND, TRUNC, MOD, POWER, CEIL , FLOOR, ABS

Dates Functions: SYSDATE, MONTHS_BETWEEN, NEXT_DAY, LAST_DAY, ADD_MONTHS, ROUND, TRUNC, Arithmetic on Date

Conversion Functions: Implicit Data-Type Conversion & Explicit Data-Type Conversion, TO_CHAR ,TO_NUMBER ,TO_DATE

General Functions: NVL , NVL2 , NULLIF, COALESCE

CASE Expression, DECODE

Nested function with real-time usage

 

  1. JOINS

EQUI JOIN / SIMPLE JOIN / NORMAL JOIN

ANSI JOIN, LEFT OUTER, RIGHT OUTER, FULL OUTER

NATURAL JOIN, NATURAL OUTER JOINS

INNER JOIN, JOIN … USING clause, JOIN … ON clause,

CROSS JOIN, NON-EQUI JOIN, SELF JOIN

ORACLE STANDARD OUTER JOINS.

Multi table Joins, Complex Joins – How to simplified complex joins.

 

  1. Multi-row Functions

Group Functions Rules, SUM, MIN, MAX, COUNT, AVG

Creating Groups of Data: GROUP BY Clause

Filtering Group Results: The HAVING Clause

 

  1. Sub-queries

Single-Row Subqueries- Rules, Operators : = > >= < <= <>

Null Values in a Subquery

Multi-Row Subqueries- Rules, Operators : IN, ANY , ALL

 

  1. Reporting data using interface commands

pagesize, linesize , column heading , column format , colsep

tTitle , bTitle , break on column, spool , CSV file generation, Text file generation

 

  1. Data Manipulation Language DML and Transaction Control Language TCL

DML : INSERT, UPDATE, DELETE, MERGE

TCL : COMMIT, ROLLBACK, SAVEPOINT

 

  1. Data Definition Language – DDL

DDL : CREATE, ALTER, RENAME, DROP, TRUNCATE

DEFAULT OPTION.

Constrain table copy

 

  1. Constraints

NOT NULL, UNIQUE, PRIMARY KEY, FOREIGN KEY, CHECK

Column Level Constraint, Table Level Constraint – Naming constraints and usage

Adding a Constraint, Dropping a Constraint,

Disabling Constraints, Enabling Constraints

Validating Constraints

 

  1. Views

Simple Views and Complex Views – Create, Drop, Source Code

Rules for Performing DML Operations on a View

WITH CHECK OPTION , WITH READ ONLY

Inline Views

Materialized View – Create, Refresh, Drop – Usage

 

  1. Other Database Objects

Sequence- NEXTVAL and CURRVAL

Index – When to Create an Index, When Not to Create an Index.

Synonyms

 

  1. DCL COMMANDS

Creating Users

Granting / Revoking Privileges

Creating and Granting Privileges to a Role

 

  1. Dictionary Tables

Tables, Views, Synonyms, Index, Sequence, Constrains, Source and other Dictionary

 

  1. SET Operators

UNION ,

UNION ALL ,

INTERSECT ,

MINUS

  1. Advanced Date-time Functions

TIME ZONES ,

SYSDATE, SYSTIMESTAMP,

CURRENT_DATE , CURRENT_TIMESTAMP

SESSIONTIMEZONE ,

Storing time zone data in Table

EXTRACT ,

TO_YMINTERVAL

 

  1. Advanced GROUP BY Clause

Group by with ROLLUP,

Group by with CUBE,

GROUPING SETS

 

  1. Advanced Subqueries

Pairwise Comparison Subquery ,

Nonpairwise Comparison Subquery

Correlated Subqueries,

Correlated UPDATE,

Correlated DELETE

EXISTS , NOT EXISTS Operator

 

  1. Hierarchical Retrieval

Walking the Tree: From the Bottom Up , From the Top Down

LEVEL Pseudo column,

Connect by prior,

 

  1. Multi-table Insert

Unconditional INSERT ALL

Conditional INSERT ALL

Conditional FIRST INSERT

 

  1. DATA LOADER –

SQLLDR – Loading CSV file / Flat file into ORACLE table.

 

  1. Analytic Functions

WM_CONCAT, LAG, LEAD, RANK, DENSE_RANK

Query_by partition_clause with sum, min, max, avg, count,

order_by_clause with sum, min, max, avg, count,

Psudo column : Rownum, Rowid, – Elimination duplicate data

Connect by rownum , Connect by Level – Generating random numbers, random dates,

Quote Operator syntax and usage

Introduction to PL/SQL

 

PL/SQL Overview

List the benefits of PL/SQL Subprograms

Overview of the Types of PL/SQL blocks

Create a Simple Anonymous Block

Generate the Output from a PL/SQL Block

PL/SQL Identifiers

 

List the different Types of Identifiers in a PL/SQL subprogram

Usage of the Declarative Section to Define Identifiers

Use of variables to store data

Scalar Data Types

%TYPE Attribute

Bind Variables

Sequences in PL/SQL Expressions

Write Executable Statements

 

Basic PL/SQL Block Syntax Guidelines

How to comment code?

SQL Functions in PL/SQL

Data Type Conversion

Nested Blocks

Operators in PL/SQL

Interaction with the Oracle Server

 

SELECT Statements in PL/SQL to Retrieve data

Data Manipulation in the Server Using PL/SQL

The SQL Cursor concept

Learn to use SQL Cursor Attributes to Obtain Feedback on DML

How to save and discard transactions?

Control Structures

 

Conditional processing Using IF Statements

Conditional processing Using CASE Statements

Simple Loop Statement

While Loop Statement

For Loop Statement

The Continue Statement

Usage of Composite Data Types

 

PL/SQL Records

The %ROWTYPE Attribute

Insert and Update with PL/SQL Records

Associative Arrays (INDEX BY Tables)

INDEX BY Table Methods

INDEX BY Table of Records

Explicit Cursors

 

Understand Explicit Cursors

Declare the Cursor

How to open the Cursor?

Fetching data from the Cursor

How to close the Cursor?

Cursor FOR loop

Explicit Cursor Attributes

FOR UPDATE Clause and WHERE CURRENT Clause

Exception Handling

 

What are Exceptions?

Handle Exceptions with PL/SQL

Trap Predefined Oracle Server Errors

Trap Non-Predefined Oracle Server Errors

Trap User-Defined Exceptions

Propagate Exceptions

RAISE_APPLICATION_ERROR Procedure

Stored Procedures and Functions

 

What are Stored Procedures and Functions?

Differentiate between anonymous blocks and subprograms

Create a Simple Procedure

Create a Simple Procedure with IN parameter

Create a Simple Function

Execute a Simple Procedure

Execute a Simple Function

Do not miss bonus page with web elements and short codes for realization in your projects. You will get good content. Lorem Ipsum is simply dummy text of the printing.

Other Courses

Back to Top