₹18,000.00

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.

Category:

Course Overview

Course Objective

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 Learn

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

  • Administrator
  • Analyst
  • Developer
  • Systems Administrator

Prerequisite

What are the pre-requisites for this Course?

  • Basic Computer Knowledge

Course Content

1. Stored Procedures and Functions

  • 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

3. Simple SELECTs

  • 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

5. Subqueries, Joins and Unions

  • Subqueries
  • Joins
    • Table Aliases
    • Multi-table Joins
  • Using Joins
  • Outer Joins
  • Unions
    • UNION ALL
    • UNION Rules
  • Working with Unions

7. Users and Schemas

  • 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

9. Using Subqueries

  • Simple Subqueries
    • Definition
    • Nested Subquery
  • Inline Views
    • Subquery in the HAVING clause
  • Correlated Subqueries
  • Scalar Subquery
  • Exercise: Subqueries

11. Rollup & Cube

  • About Group Processing
  • EMP_DETAILS_VIEW
  • Simple GROUP BY
  • ROLLUP Function
  • GROUPING Function
  • Using Cube
  • Exercise: Practice GROUP BY, ROLLUP and CUBE

13. Conditional Processing

  • The DECODE Function
  • The Case Expression
  • Exercise: Conditional Processing

15. SQL Functions (Non-Character)

  • 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

23. The Environment for PL/SQL Development

  • Prerequisites
    • File Naming Conventions
  • Connecting to Oracle
    • SQL*Developer
    • SQL*Plus
  • Executing PL/SQL Code
  • SQL Developer Configuration
  • The PL/SQL Development Cycle

25. Declaring Variables

  • Variable Usage
  • Variable Data Types
  • Variable Naming
  • Variable Assignment
    • Variable Display
  • Complex Variable Types
  • More Information
  • Variable Declaration, Initialization and Display

27. Handling Exceptions

  • 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

29. Nested Blocks

  • Nesting Blocks
  • Scope of Variables
  • Scope of Exceptions
  • Nested Subprograms
  • Exception Propagation in Nested Blocks
  • Block Variable Visibility

31. Packages

  • Structure of Packages
  • Purpose of Packages
  • Wrap Utility
  • Positional vs Named Parameter Notation
  • Subprograms Omitted from Specifications
  • Using PL/SQL Packages

33. Data Retrieval Techniques

  • Cursor Review
  • Cursor Parameters
  • Cursor Variables
  • Dynamic SQL
  • Employee Report by State/Province

2. Relational Database Basics

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

4. Advanced SELECTs

  • 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

6. If time allows, one or more of the following may be covered:

  • Conditional Processing with CASE
  • INSERT, UPDATE, DELETE
  • Student Challenges – Design your own reports

8. Pseudo Columns & Functions

  • Using The Dual Table
  • Functions
  • Exercise: Use the SYSCONTEXT function
  • Pseudo-Columns
  • Using Rowid
  • Using Rownum
  • Exercise: Use ROWNUM

10. Joining Tables

  • 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

12. Using Set Operator

  • Set Operators Defined
  • Relationship to Mathematical Set Theory
  • Restrictions on Set Operators
  • Exercise: Set Operators Exercise

14. SQL Functions (Character)

  • What Are SQL Functions?
  • Character Functions
    • CONCAT
    • LENGTH
    • INSTR
    • REPLACE
    • UPPER
    • LOWER
    • INITICAP
    • LPAD
    • RPAD
    • TRIM
    • TO_CHAR
    • SOUNDEX
  • Regular Expressions
  • Exercise: Character Functions

16. SQL Data Manipulation Language

  • The INSERT Statement
    • INSERT Statement Categories
  • The UPDATE Statement
  • The DELETE Statement
  • Transactions
  • Complex Table References
  • The MERGE statement
  • Exercise: Data Manipulation Language (DDL)

24. PL/SQL Basics

  • PL/SQL Block
  • Anonymous Block Structure
  • Named Block Structure
  • Executing Blocks
  • Calling PL/SQL Functions
  • Executing PL/SQL Blocks and Functions

26. Within the Block

  • 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

28. Use of SQL in PL/SQL

  • Implicit Cursors
  • %TYPE and %ROWTYPE Attributes
  • EXECUTE IMMEDIATE statement
  • Cursors Attributes
  • Implicit Cursor
  • Explicit Cursors
  • Explicit Cursor
  • Cursor FOR loop

30. Introducing Subprograms

  • 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

32. Database Triggers

  • 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

Who are the trainers?

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

How do I join the live sessions?

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.

What if I miss a class?

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.

How will I execute the practical?

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

Will you help us in building our own Lab Setup in our local Desktop/Laptop?

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

What is the difference between live online and pre-recorded video training?

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.

What if I have queries after completion of the class?

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

Is the course material accessible after completion of the course?

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

Any offer/discount that I can avail?

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.

Will I get the refund if I cancel my enrollment?

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

There are no reviews yet.

Be the first to review “Oracle 19c : Learn SQL & PL/SQL”

related products