Oracle 19c : Learn SQL & PL/SQL

PL/SQL is a combination of SQL along with the procedural features of programming languages. It was developed by Oracle Corporation in the early 90’s to enhance the capabilities of SQL. PL/SQL is one of three key programming languages embedded in the Oracle Database, along with SQL itself and Java. This tutorial will give you great understanding on PL/SQL to proceed with Oracle database and other advanced RDBMS concepts.

Course Overview

What will you learn?

  • Identify the major structural components of the Oracle Database 12c
  • Create reports of aggregated data
  • Write SELECT statements that include queries
  • Retrieve row and column data from tables
  • Run data manipulation statements (DML) in Oracle Database 12c
  • Create tables to store data
  • Utilize views to display data
  • Control database access to specific objects
  • Manage schema objects
  • Display data from multiple tables using the ANSI SQL 99 JOIN syntax
  • Manage objects with data dictionary views
  • Write multiple-column sub-queries
  • Employ SQL functions to retrieve customized data
  • Use scalar and correlated sub-queries
  • Create reports of sorted and restricted data

Who should go for this training?
The following professionals can go for this course:

  • Administrator
  • Analyst
  • Developer
  • Systems Administrator

What are the pre-requisites for this Course?

  • Basic Computer Knowledge

Course Content

  • Creating Subprograms
  • Modifying Subprograms
  • Removing Subprograms
  • Application Maintenance
  • Exercise Title
  • Parameters
  • Cursors as Parameters
  • Subprogram Development Techniques
  • Addressing Compilation Errors
  • Directives for Debugging
  • Issues with Booleans
  • Integrated Development Environments
  • PL/SQL Subprograms with Parameters
  • Introduction to the Northwind Database
  • Some Basics
    • Comments
    • Whitespace and Semi-colons
    • Case Sensitivity
  • SELECTing All Columns in All Rows
  • Exploring the Tables
  • SELECTing Specific Columns
  • Sorting Records
    • Sorting By a Single Column
    • Sorting By Multiple Columns
    • Sorting By Column Position
    • Ascending and Descending Sorts
  • The WHERE Clause and Operator Symbols
    • Checking for Equality
    • Checking for Inequality
    • Checking for Greater or Less Than
    • Checking for NULL
    • WHERE and ORDER BY
  • Using the WHERE clause to check for equality or inequality
  • Using the WHERE clause to check for greater or less than
  • Checking for NULL
  • Using WHERE and ORDER BY Together
  • The WHERE Clause and Operator Words
    • The BETWEEN Operator
    • The IN Operator
    • The LIKE Operator
    • The NOT Operator
  • More SELECTs with WHERE
  • Checking Multiple Conditions
    • AND
    • OR
    • Order of Evaluation
  • Writing SELECTs with Multiple Conditions
  • Subqueries
  • Joins
    • Table Aliases
    • Multi-table Joins
  • Using Joins
  • Outer Joins
  • Unions
    • UNION ALL
    • UNION Rules
  • Working with Unions
  • Introduction to Users and Schemas
    • Definitions and Client Tools
    • SQL Developer Login
  • About the HR Schema
    • Sample Schemas
    • HR Schema Entities
  • Creating An Application Schema
  • Exercise: Create a User
  • Simple Subqueries
    • Definition
    • Nested Subquery
  • Inline Views
    • Subquery in the HAVING clause
  • Correlated Subqueries
  • Scalar Subquery
  • Exercise: Subqueries
  • About Group Processing
  • EMP_DETAILS_VIEW
  • Simple GROUP BY
  • ROLLUP Function
  • GROUPING Function
  • Using Cube
  • Exercise: Practice GROUP BY, ROLLUP and CUBE
  • The DECODE Function
  • The Case Expression
  • Exercise: Conditional Processing
  • Numeric Functions
    • The TO_CHAR Function with numbers
  • Date/Time Functions
  • Date Format Functions
  • Date Arithmetic Functions
  • Null Value Functions
  • Exercise: SQL Non-Character Functions
  • Prerequisites
    • File Naming Conventions
  • Connecting to Oracle
    • SQL*Developer
    • SQL*Plus
  • Executing PL/SQL Code
  • SQL Developer Configuration
  • The PL/SQL Development Cycle
  • Variable Usage
  • Variable Data Types
  • Variable Naming
  • Variable Assignment
    • Variable Display
  • Complex Variable Types
  • More Information
  • Variable Declaration, Initialization and Display
  • Overview of Exceptions
  • Causing System Generated Exceptions
  • Handling System Generated Exceptions
  • Identifying System Generated Exceptions
  • OTHERS Exception Handler
  • User Defined Exceptions
  • Exception Handling for Invalid Salary
  • Nesting Blocks
  • Scope of Variables
  • Scope of Exceptions
  • Nested Subprograms
  • Exception Propagation in Nested Blocks
  • Block Variable Visibility
  • Structure of Packages
  • Purpose of Packages
  • Wrap Utility
  • Positional vs Named Parameter Notation
  • Subprograms Omitted from Specifications
  • Using PL/SQL Packages
  • Cursor Review
  • Cursor Parameters
  • Cursor Variables
  • Dynamic SQL
  • Employee Report by State/Province
  • Brief History of SQL
  • Relational Databases
    • Tables
    • Rows
    • Columns
    • Relationships
    • Datatypes
    • Primary Keys
    • Foreign Keys
    • Relational Database Management System
  • Popular Databases
    • Commercial Databases
    • Popular Open Source Databases
    • Valid Object References
  • SQL Statements
    • Database Manipulation Language (DML)
    • Database Definition Language (DDL)
    • Database Control Language (DCL)
  • Calculated Fields
    • Concatenation
    • Mathematical Calculations
    • Aliases
  • Calculating Fields
  • Aggregate Functions and Grouping
    • Aggregate Functions
    • Grouping Data
    • Selecting Distinct Records
  • Working with Aggregate Functions
  • Built-in Data Manipulation Functions
    • Common Math Functions
    • Common String Functions
    • Common Date Functions
  • Data Manipulation Functions
  • Conditional Processing with CASE
  • INSERT, UPDATE, DELETE
  • Student Challenges – Design your own reports
  • Using The Dual Table
  • Functions
  • Exercise: Use the SYSCONTEXT function
  • Pseudo-Columns
  • Using Rowid
  • Using Rownum
  • Exercise: Use ROWNUM
  • Review of Joins
  • Equijoins
    • Inner Joins
    • Outer Joins
  • Cross Joins
  • Reflexive Join
  • Non-Key Join
  • Natural Joins
  • Semijoins and Antijoins
  • Using Named Subqueries
  • Exercise: Join Exercises
  • Set Operators Defined
  • Relationship to Mathematical Set Theory
  • Restrictions on Set Operators
  • Exercise: Set Operators Exercise
  • What Are SQL Functions?
  • Character Functions
    • CONCAT
    • LENGTH
    • INSTR
    • REPLACE
    • UPPER
    • LOWER
    • INITICAP
    • LPAD
    • RPAD
    • TRIM
    • TO_CHAR
    • SOUNDEX
  • Regular Expressions
  • Exercise: Character Functions
  • The INSERT Statement
    • INSERT Statement Categories
  • The UPDATE Statement
  • The DELETE Statement
  • Transactions
  • Complex Table References
  • The MERGE statement
  • Exercise: Data Manipulation Language (DDL)
  • PL/SQL Block
  • Anonymous Block Structure
  • Named Block Structure
  • Executing Blocks
  • Calling PL/SQL Functions
  • Executing PL/SQL Blocks and Functions
  • Conditional Processing
    • The IF Statement
    • The CASE Statement
  • Iterative Processing
    • The LOOP Statement
    • The WHILE Statement
    • The FOR Statement
  • Salary Classification using Conditional Statements
  • Salary Increases using Loops
  • Implicit Cursors
  • %TYPE and %ROWTYPE Attributes
  • EXECUTE IMMEDIATE statement
  • Cursors Attributes
  • Implicit Cursor
  • Explicit Cursors
  • Explicit Cursor
  • Cursor FOR loop
  • Anonymous blocks
  • Types of Subprograms
  • Finding Subprograms through SQLDeveloper
  • Finding Subprograms in the Oracle Data Dictionary
  • Object Dependencies
  • Subprogram information in the Oracle Data Dictionary
  • Purpose of Triggers
  • Invocation of Triggers
  • Coding Triggers
  • Validation Trigger
  • Modifying Triggers
  • Viewing Triggers
  • Enabling/Disabling Triggers
  • Trigger Errors
  • System and User Event Triggers
  • Logon Counter Trigger

Modes of Training

Classroom Training

Live interactive sessions delivered in our classroom by our expert trainers with real-time scenarios.

Online Training

Learn from anywhere over the internet, joining the live sessions delivered by our expert trainers.

Self-Pace Training

Learn through pre-recorded video sessions delivered by experts at your own pace and timing.

Frequently Asked Questions

Our trainer is an OCP & OCM certified consultant and has a significant amount of experience in working with the technology, having 18yrs of experience.

Once you get registered, our back-end team will share you the details to join the session live over an online portal which can be accessed through a browser.

Each of our live sessions is recorded. In case if you miss any, you can request us to share the link to that particular session.

For practical execution, our trainer/technical team will provide server access details to the student

Yes. We do provide the step-by-step document which you can follow and if required our technical team will assist you.

Live-Online training is where you can have a live session with the trainer and clarify queries parallelly.

Pre-recorded sessions are the recorded videos that will be provided to you that you can see, listen, and learn anytime at ur feasible place. For doubts in the videos, you can mail the trainer regarding the same.

You can contact our support team, or just drop an email to help@sarinfotechindia.com with your queries.

The course material and recorded videos which are provided during the course period. You can download it anytime.

Visit our website regularly to check discounts offers from time to time. However, we provide a discount for single participants & special discounts for 2 or more participants.

* If the request for cancelation is made within 2 days of enrolment for class, 100% refunded.

* If the request made after 2 days, then Refund is made after deduction of the administration fee.