Oracle SQL and PLSQL Training in Chennai

Oracle sql and plsql Training in Chennai This Oracle Database 12c PL SQL training in Chennai teaches you the basics of database programming in PL/SQL with appropriate PL/SQL advanced training with real time coding examples. You can use these free Oracle PLSQL Live Projects as your guide to practice, learn, for training, or reference while programming with PL SQL. I will be making more Oracle PL SQL programming training as often as possible to share my knowledge in PL SQL and help you in learning PL SQL language and syntax better. This Oracle PL SQL Training helps students to score 100% in Completing OCA and OCP exams

Even though the programming concepts discussed in this Oracle SQL and PLSQL Training in Chennai are specific to Oracle PL SQL. The concepts like cursors, functions and stored procedures can be used in other database systems like Sybase , Microsoft SQL server etc, with some change in SQL syntax. This Oracle PL/SQL training in Chennai will be growing regularly; let us know if any topic related to PL SQL needs to be added or you can also share your knowledge on PL SQL with us. Lets share our knowledge about PL SQL with others..

Greens Technology Adyar provides Oracle SQL, PLSQL, Query Optimization in Oracle Database 12c Release 1 (12.1) Performance Tuning training in Chennai to professionals and corporates on advanced SQL, PL SQL with XML, Bulk Collect in Oracle , Advanced Index Compression, Oracle Database 12c Analytic SQL for Data Warehousing and Performance Tuning.

Oracle Database 12c SQL and PLSQL Training in Chennai

Oracle is a relational database technology developed by Oracle.

PLSQL stands for "Procedural Language extensions to SQL", and is an extension of SQL that is used in Oracle. PLSQL is closely integrated into the SQL language, yet it adds programming constructs that are not native to SQL.

Our Oracle PL SQL training in Chennai will start with the basics of Oracle such as how to retrieve and manipulate data. Then we will move to the more advanced topics such as how to create tables, functions, procedures, triggers, tablespaces, and schemas. We will conclude with a review of the functions that are proprietary to Oracle.

With this Oracle PL SQL training in Chennai, you should be on your way to becoming proficient in Oracle and PLSQL

Free Demo Class: Try two Oracle FREE CLASS to see for yourself the quality of training.

We are the Leading Oracle SQL, PL/SQL training center in Chennai. We offer best oracle SQL, PL/SQL training with real-time project scenarios. We can guarantee classes that makes you as a Oracle Database SQL Expert.

An impeccable ORACLE SQL course that is exclusively designed with Basics through Advanced SQL Concepts. OCA Certification and Interview Guidance are provided during the course. All our training sessions are Completely Practical and Real Time.

Oracle SQL and PL/SQL Expert Training: Learn SQL from the same team involved in Oracle product development.

Are you a PL/SQL developer or Oracle technologist who uses PL/SQL, SQL or APEX? Do you want to hone your skills, deepen your knowledge of these technologies - and along the way have fun and get placed? Then you have come to the right place!

Oracle SQL and PLSQL Certification Training

  • 1Z0-061 Oracle Database 12c: SQL Fundamentals
  • 1Z0-047 Oracle Database SQL Expert
  • 1Z0-051 Oracle Database 11g: SQL Fundamentals I
  • 1Z0-147 Program with PL/SQL
  • 1Z0-144 Oracle Database 11g: Program with PL/SQL
  • 1Z0-146 Oracle Database 11g: Advanced PL/SQL

Oracle SQL Training Course Contents :

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

2. 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

3. 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

4. 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.

5. Multi-row Functions

  • Group Functions Rules, SUM, MIN, MAX, COUNT, AVG
  • Creating Groups of Data: GROUP BY Clause
  • Filtering Group Results: The HAVING Clause

6. Sub-queries

  • Single-Row Subqueries- Rules, Operators : = > >= < <= <>
  • Null Values in a Subquery
  • Multi-Row Subqueries- Rules, Operators : IN, ANY , ALL

7. Reporting data using interface commands

  • pagesize, linesize , column heading , column format , colsep
  • tTitle , bTitle , break on column, spool , CSV file generation, Text file generation

8. Data Manipulation Language DML and Transaction Control Language TCL

  • DML : INSERT, UPDATE, DELETE, MERGE
  • TCL : COMMIT, ROLLBACK, SAVEPOINT

9. Data Definition Language - DDL

  • DDL : CREATE, ALTER, RENAME, DROP, TRUNCATE
  • DEFAULT OPTION.
  • Constrain table copy

10. 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

11. 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

12. Other Database Objects

  • Sequence- NEXTVAL and CURRVAL
  • Index - When to Create an Index, When Not to Create an Index.
  • Synonyms

13. DCL COMMANDS

  • Creating Users
  • Granting / Revoking Privileges
  • Creating and Granting Privileges to a Role

14. Dictionary Tables

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

15. SET Operators

  • UNION ,
  • UNION ALL ,
  • INTERSECT ,
  • MINUS

16. Advanced Date-time Functions

  • TIME ZONES ,
  • SYSDATE, SYSTIMESTAMP,
  • CURRENT_DATE , CURRENT_TIMESTAMP
  • SESSIONTIMEZONE ,
  • Storing time zone data in Table
  • EXTRACT ,
  • TO_YMINTERVAL

17. Advanced GROUP BY Clause

  • Group by with ROLLUP,
  • Group by with CUBE,
  • GROUPING SETS

18. Advanced Subqueries

  • Pairwise Comparison Subquery ,
  • Nonpairwise Comparison Subquery
  • Correlated Subqueries,
  • Correlated UPDATE,
  • Correlated DELETE
  • EXISTS , NOT EXISTS Operator

19. Hierarchical Retrieval

  • Walking the Tree: From the Bottom Up , From the Top Down
  • LEVEL Pseudo column,
  • Connect by prior,

20. Multi-table Insert

  • Unconditional INSERT ALL
  • Conditional INSERT ALL
  • Conditional FIRST INSERT

21. DATA LOADER –

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

22. 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

23. Backup

  • Export / Import SCHEMA

24. General discussion

  • What is migration?
  • Migration Estimating, Planning, Preparation – Simple Scenario / Complex Scenario.


SQL Statement Tuning Training Course Content

Exploring the Oracle Database Architecture

  • Describe the major architectural components of Oracle Database server
  • Explain memory structures
  • Describe background processes
  • Correlate logical and physical storage structures

Introduction to SQL Tuning

  • Describe what attributes of a SQL statement can make it perform poorly
  • Describe the Oracle tools that can be used to tune SQL
  • Explain the tuning tasks

Introduction to the Optimizer

  • Describe the execution steps of a SQL statement
  • Explain the need for an optimizer
  • Explain the various phases of optimization
  • Control the behavior of the optimizer

Interpreting Execution Plans

  • Gather execution plans
  • Display execution plans, display xplan
  • Interpret execution plans

Application Tracing

  • Configure the SQL Trace facility to collect session statistics
  • Use the trcsess utility to consolidate SQL trace files
  • Format trace files using the tkprof utility
  • Interpret the output of the tkprof command

Optimizer Operations

  • Describe the SQL operations for tables and indexes
  • Describe the possible access paths for tables and indexes

Optimizer: Join Operations

  • Describe the SQL operations for joins
  • Describe the possible access paths for joins

Other Optimizer Operations

  • Describe Clusters, In-List, Sorts, Filters and Set Operations
  • Use Result Cache operations

Case Study: Star Transformation

  • Define a star schema, a star query plan without transformation and a star query plan after transformation

Optimizer Statistics

  • Gather optimizer statistics
  • Gather system statistics
  • Set statistic preferences
  • Use dynamic sampling
  • Manipulate optimizer statistics

Using Bind Variables

  • Explain the benefits of using bind variables
  • Use bind peeking
  • Use adaptive cursor sharing

SQL Tuning Advisor

  • Describe statement profiling
  • Use SQL Tuning Advisor

Using SQL Access Advisor

  • Use SQL Access Advisor

Automating SQL Tuning

  • Use Automatic SQL Tuning

SQL Plan Management

  • Manage SQL performance through changes
  • Set up SQL Plan Management
  • Set up various SQL Plan Management scenarios

Using Optimizer Hints

  • Use hints when appropriate
  • Specify hints for Optimizer mode, Query transformation, Access path, Join orders, Join methods and Views

Parallel Queries


Parallel Processing Concepts

  • Explain what parallel processing is and why is it useful

Basics of Parallel Execution

  • Describe operations that can be parallelized
  • Explain parallel execution theory
  • Understand impact of initiali zation parameter on parallel execution

Manual DOP Management

  • Understand an explain plan of a parallel query
  • Understand an explain plan of parallel DML and DDL

Simplified Auto DOP

  • Understand the new parameters of Auto DOP
  • Explain when to use Auto DOP
  • Use Auto DOP

Statement Queuing

  • Explain statement queuing, concurrency and DBRM

In-Memory Parallel execution

  • Use in-memory parallel execution

Data Warehouse Administration


Partitioning Concepts

  • Explain the available partitioning strategies
  • Explain partition pruning
  • Implement partition enhancements in star query optimization

Materialized Views

  • Use summaries to improve performance
  • Differentiate materialized view types


Oracle PL/SQL Training Syllabus


Introduction

  • Course Objectives
  • Course Agenda
  • Human Resources (HR) Schema
  • Introduction to SQL Developer

Introduction to PL/SQL

  • PL/SQL Overview
  • Benefits of PL/SQL Subprograms
  • Overview of the Types of PL/SQL blocks
  • Create a Simple Anonymous Block
  • Generate 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 variables to store data
  • Identify Scalar Data Types
  • The %TYPE Attribute
  • What are Bind Variables?
  • Sequences in PL/SQL Expressions

Write Executable Statements

  • Describe Basic PL/SQL Block Syntax Guidelines
  • Comment Code
  • Deployment of SQL Functions in PL/SQL
  • How to convert Data Types?
  • Nested Blocks
  • Identify the Operators in PL/SQL

Interaction with the Oracle Server

  • Invoke SELECT Statements in PL/SQL to Retrieve data
  • Data Manipulation in the Server Using PL/SQL
  • SQL Cursor concept
  • Usage of SQL Cursor Attributes to Obtain Feedback on DML
  • Save and Discard Transactions

Control Structures

  • Conditional processing Using IF Statements
  • Conditional processing Using CASE Statements
  • Use simple Loop Statement
  • Use While Loop Statement
  • Use For Loop Statement
  • Describe the Continue Statement

Composite Data Types

  • Use PL/SQL Records
  • The %ROWTYPE Attribute
  • Insert and Update with PL/SQL Records
  • Associative Arrays (INDEX BY Tables)
  • Examine INDEX BY Table Methods
  • Use INDEX BY Table of Records

Explicit Cursors

  • What are Explicit Cursors?
  • Declare the Cursor
  • Open the Cursor
  • Fetch data from the Cursor
  • Close the Cursor
  • Cursor FOR loop
  • Explicit Cursor Attributes
  • FOR UPDATE Clause and WHERE CURRENT Clause

Exception Handling

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

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

Create Stored Procedures

  • Create a Modularized and Layered Subprogram Design
  • Modularize Development With PL/SQL Blocks
  • Describe the PL/SQL Execution Environment
  • Identity the benefits of Using PL/SQL Subprograms
  • List the differences Between Anonymous Blocks and Subprograms
  • Create, Call, and Remove Stored Procedures Using the CREATE Command and SQL Developer
  • Implement Procedures Parameters and Parameters Modes
  • View Procedures Information Using the Data Dictionary Views and SQL Developer

Create Stored Functions

  • Create, Call, and Remove a Stored Function Using the CREATE Command and SQL Developer
  • Identity the advantages of Using Stored Functions in SQL Statements
  • List the steps to create a stored function
  • Implement User-Defined Functions in SQL Statements
  • Identity the restrictions when calling Functions from SQL statements
  • Control Side Effects when calling Functions from SQL Expressions
  • View Functions Information

Create Packages

  • Identity the advantages of Packages
  • Describe Packages
  • List the components of a Package
  • Develop a Package
  • How to enable visibility of a Package’s components?
  • Create the Package Specification and Body Using the SQL CREATE Statement and SQL Developer
  • Invoke Package Constructs
  • View PL/SQL Source Code Using the Data Dictionary

Packages

  • Overloading Subprograms in PL/SQL
  • Use the STANDARD Package
  • Use Forward Declarations to Solve Illegal Procedure Reference
  • Implement Package Functions in SQL and Restrictions
  • Persistent State of Packages
  • Persistent State of a Package Cursor
  • Control Side Effects of PL/SQL Subprograms
  • Invoke PL/SQL Tables of Records in Packages

Implement Oracle-Supplied Packages in Application Development

  • What are Oracle-Supplied Packages?
  • Examples of Some of the Oracle-Supplied Packages
  • How Does the DBMS_OUTPUT Package Work?
  • Use the UTL_FILE Package to Interact With Operating System Files
  • Invoke the UTL_MAIL Package
  • Write UTL_MAIL Subprograms

Dynamic SQL

  • The Execution Flow of SQL
  • What is Dynamic SQL?
  • Declare Cursor Variables
  • Dynamically executing a PL/SQL Block
  • Configure Native Dynamic SQL to Compile PL/SQL Code
  • Invoke DBMS_SQL Package
  • Implement DBMS_SQL with a Parameterized DML Statement
  • Dynamic SQL Functional Completeness

Design Considerations for PL/SQL Code

  • Standardize Constants and Exceptions
  • Understand Local Subprograms
  • Write Autonomous Transactions
  • Implement the NOCOPY Compiler Hint
  • Invoke the PARALLEL_ENABLE Hint
  • The Cross-Session PL/SQL Function Result Cache
  • The DETERMINISTIC Clause with Functions
  • Usage of Bulk Binding to Improve Performance

Triggers

  • Describe Triggers
  • Identify the Trigger Event Types and Body
  • Business Application Scenarios for Implementing Triggers
  • Create DML Triggers Using the CREATE TRIGGER Statement and SQL Developer
  • Identify the Trigger Event Types, Body, and Firing (Timing)
  • Statement Level Triggers Versus Row Level Triggers
  • Create Instead of and Disabled Triggers
  • How to Manage, Test, and Remove Triggers?

Create Compound, DDL, and Event Database Triggers

  • What are Compound Triggers?
  • Identify the Timing-Point Sections of a Table Compound Trigger
  • Compound Trigger Structure for Tables and Views
  • Implement a Compound Trigger to Resolve the Mutating Table Error
  • Compare Database Triggers to Stored Procedures
  • Create Triggers on DDL Statements
  • Create Database-Event and System-Event Triggers
  • System Privileges Required to Manage Triggers

The PL/SQL Compiler

  • What is the PL/SQL Compiler?
  • Describe the Initialization Parameters for PL/SQL Compilation
  • List the New PL/SQL Compile Time Warnings
  • Overview of PL/SQL Compile Time Warnings for Subprograms
  • List the benefits of Compiler Warnings
  • List the PL/SQL Compile Time Warning Messages Categories
  • Setting the Warning Messages Levels: Using SQL Developer, PLSQL_WARNINGS Initialization Parameter, and the DBMS_WARNING Package Subprograms
  • View Compiler Warnings: Using SQL Developer, SQL*Plus, or the Data Dictionary Views

Manage PL/SQL Code

  • What Is Conditional Compilation?
  • Implement Selection Directives
  • Invoke Predefined and User-Defined Inquiry Directives
  • The PLSQL_CCFLAGS Parameter and the Inquiry Directive
  • Conditional Compilation Error Directives to Raise User-Defined Errors
  • The DBMS_DB_VERSION Package
  • Write DBMS_PREPROCESSOR Procedures to Print or Retrieve Source Text
  • Obfuscation and Wrapping PL/SQL Code

Manage Dependencies

  • Overview of Schema Object Dependencies
  • Query Direct Object Dependencies using the USER_DEPENDENCIES View
  • Query an Object’s Status
  • Invalidation of Dependent Objects
  • Display the Direct and Indirect Dependencies
  • Fine-Grained Dependency Management in Oracle Database 11g
  • Understand Remote Dependencies
  • Recompile a PL/SQL Program Unit

Oracle Advanced PL/SQL Topics

Oracle Training Duration

  • Regular classroom based training available for this course on Both Weekdays / Weekends
  • Fast Track (1-1): No of hours per day can be as per convenience of participant.
  • Weekend classroom classes are available for this course.

Oracle Training Locations in Chennai

Greens Technology
15 First Street Padmanabha Nagar, Adyar, Chennai
Tel: +91- 89399 15577
      +91- 89399 25577
Email: contact@greenstechnologys.com

Greens Technology Trainers:

Worked earlier in Oracle Corporation, IBM, Google, Verizon, CSC, Satyam etc.

Oracle Training Reviews

Greens Technology Reviews given by our students already completed the training with us. Please give your feedback as well if you are a student.

for More Reviews please continue to, Oracle Reviews (Page 2) >>

Oracle PLSQL Training in Chennai - Tags


Related Tags: Oracle PLSQL Training in Chennai | Oracle PLSQL Training Institutes in Chennai | Oracle PLSQL Training Centers in Chennai | Oracle PLSQL Training Center in Chennai | Oracle PLSQL Course in Chennai | Best Oracle PLSQL Training in Chennai | Corporate Training for Oracle PLSQL | Online Training Oracle PLSQL | Oracle PLSQL training in Chennai with placement | Real Time plsql training in Chennai | Oracle sql and plsqltraining Institute in Chennai | Best sql plsql Training Institute in Chennai | Oracle sql and plsqlTraining Center in Chennai | Best Oracle sql and plsqlTraining Center in Chennai | Oracle sql and plsql Course in Chennai | Best Oracle sql and plsql Course in Chennai | Oracle sql and plsql Certification training in Chennai | Oracle sql and plsql Training | Oracle sql and plsql Online Training in Chennai | oraclechennai.in Training in Chennai