Oracle Database: SQL and PL/SQL Fundamentals Ed 2 Training

Live Online & Classroom Enterprise Certification Training

This Database SQL & PL/SQL Fundamentals training teaches you the fundamentals of relational databases, SQL & PL/SQL programming language. Learn to write queries against single and multiple tables, manipulate data in tables & create database objects.

Looking for a private batch ?

REQUEST A CALLBACK

Certified Trainer
Authorized Courseware
Completion Certificate from ATP

Enterprise Reporting
Lifetime Access
CloudLabs
24x7 Support
Real-time code analysis and feedback

What is SQL and PL/SQL Fundamentals Ed 2 Certification Training about?

This Oracle Database: SQL and PL/SQL Fundamentals training delivers the fundamentals of SQL and PL/SQL along with the benefits of the programming languages using Oracle Database technology. You'll explore the concepts of relational databases.

Ensure fast, reliable, secure and easy to manage performance. Optimize database workloads, lower IT costs and deliver a higher quality of service by enabling consolidation onto database clouds.

Demonstrations and hands-on practice reinforce the fundamental concepts that you'll learn in this course. By enrolling in this course, you'll begin using Oracle SQL Developer to develop these program units. SQL*Plus is available as optional tools.

Note: This course is a combination of Oracle Database: SQL Workshop I and Oracle Database: PL/SQL Fundamentals courses.

What are the objectives of SQL and PL/SQL Fundamentals Ed 2 Certification Training ?

Run data definition language (DDL) statements to create and manage schema objects
Run data manipulation statements (DML) to update data in the Oracle Database
Use PL/SQL programming constructs and conditionally control code flow (loops, control structures, and explicit cursors)
Use cursors to process rows
Create reports of sorted and restricted data
Describe stored procedures and functions
Describe the features and syntax of PL/SQL
Design PL/SQL anonymous block that execute efficiently
Display data from multiple tables using the ANSI SQL 99 JOIN syntax
Create reports of aggregated data
Employ SQL functions to generate and retrieve customized data
Handle runtime errors
Retrieve row and column data from tables with the SELECT statement

Who is SQL and PL/SQL Fundamentals Ed 2 Certification Training for?

Functional Implementer
Portal Developer
Reports Developer
PL/SQL Developer
Application Developers
Forms Developer
Forms Developer
Technical Consultant

What are the prerequisites for SQL and PL/SQL Fundamentals Ed 2 Certification Training?

Suggested Prerequisite

Familiarity with programming concepts
Familiarity with data processing concepts and techniques

Available Training Modes

Live Online Training

40 Hours

Classroom Training

5 Days

Course Outline Expand All

Expand All

Introduction

Introduction to SQL and its development environments

The HR schema and the tables used in this course

Overview of Oracle Database 12c and related products

Overview of relational database management concepts and terminologies

Oracle Database documentation and additional resources

Working with Oracle Cloud Exadata Express Cloud Service

Accessing Cloud Database using SQL Workshop

Connecting to Exadata Express using Database Clients

Introduction to Oracle Database Exadata Express Cloud Service

Retrieve Data using the SQL SELECT Statement

Invoke Column aliases

Generate a report of data from the output of a basic SELECT statement

Concatenation operator, literal character strings, alternative quote operator, and the DISTINCT keyword

Use arithmetic expressions and NULL values in the SELECT statement

List the capabilities of SQL SELECT statements

Display the table structure using the DESCRIBE command

Restricted and Sorted Data

Describe the comparison operators and logical operators

Write queries with a WHERE clause to limit the output retrieved

Write queries with an ORDER BY clause

Sort the output in descending and ascending order

Describe the rules of precedence for comparison and logical operators

Usage of character string literals in the WHERE clause

Substitution Variables

Usage of Single-Row Functions to Customize Output

Manipulate dates with the DATE functions

Perform arithmetic with date data

List the differences between single row and multiple row functions

Manipulate numbers with the ROUND, TRUNC, and MOD functions

Manipulate strings using character functions

Conversion Functions and Conditional Expressions

Describe implicit and explicit data type conversion

Describe the TO_CHAR, TO_NUMBER, and TO_DATE conversion functions

Nesting multiple functions

Usage of conditional IF THEN ELSE logic in a SELECT statement

Apply the NVL, NULLIF, and COALESCE functions to data

Aggregated Data Using the Group Functions

Divide the data in groups by using the GROUP BY clause

How to handle Null Values in a group function?

Exclude groups of date by using the HAVING clause

Usage of the aggregation functions in SELECT statements to produce meaningful reports

Describe the AVG, SUM, MIN, and MAX function

Display Data From Multiple Tables

Join a table to itself by using a self join

Join Tables Using SQL:1999 Syntax

Create Cross Joins

View data that does not meet a join condition by using outer joins

Write SELECT statements to access data from more than one table

Usage of Subqueries to Solve Queries

Use the ANY and ALL Operator in Multiple-Row Subqueries

Use a Subquery to Solve a Problem

Group Functions in a Subquery

Single-Row Subqueries

Multiple-Row Subqueries

Use the EXISTS Operator

SET Operators

Use the ORDER BY Clause in Set Operations

Describe the UNION, UNION ALL, INTERSECT, and MINUS Operators

Use a SET operator to combine multiple queries into a single query

Describe the SET operators

Data Manipulation

Add New Rows to a Table

Change the Data in a Table

How to save and discard changes with the COMMIT and ROLLBACK statements

Describe the FOR UPDATE Clause

Use the DELETE and TRUNCATE Statements

Implement Read Consistency

DDL Statements to Create and Manage Tables

Describe the data types

Create Tables

How to drop a table?

Understand Constraints

How to alter a table?

Create a table using a subquery

Categorize Database Objects

Other Schema Objects

How to drop a view?

Create and drop synonyms

Create and drop indexes

Create, use, and modify a sequence

Create, modify, and retrieve data from a view

Perform Data manipulation language (DML) operations on a view

Introduction to PL/SQL

Create a Simple Anonymous Block

Generate the Output from a PL/SQL Block

List the benefits of PL/SQL Subprograms

PL/SQL Overview

Overview of the Types of PL/SQL blocks

PL/SQL Identifiers

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

Bind Variables

%TYPE Attribute

Usage of the Declarative Section to Define Identifiers

Sequences in PL/SQL Expressions

Scalar Data Types

Use of variables to store data

Write Anonymous PL/SQL blocks

SQL Functions in PL/SQL

Basic PL/SQL Block Syntax Guidelines

How to comment code?

Nested Blocks

Operators in PL/SQL

Data Type Conversion

SQL statements in PL/SQL block

The SQL Cursor concept

Data Manipulation in the Server Using PL/SQL

How to save and discard transactions?

Learn to use SQL Cursor Attributes to Obtain Feedback on DML

SELECT Statements in PL/SQL to Retrieve data

Control Structures

The Continue Statement

Conditional processing Using CASE Statements

While Loop Statement

Simple Loop Statement

For Loop Statement

Conditional processing Using IF Statements

Composite Data Types

Associative Arrays (INDEX BY Tables)

INDEX BY Table of Records

Insert and Update with PL/SQL Records

The %ROWTYPE Attribute

PL/SQL Records

INDEX BY Table Methods

Explicit Cursors

Fetching data from the Cursor

FOR UPDATE Clause and WHERE CURRENT Clause

Cursor FOR loop

Understand Explicit Cursors

How to open the Cursor?

How to close the Cursor?

Explicit Cursor Attributes

Declare the Cursor

Exception Handling

Propagate Exceptions

RAISE_APPLICATION_ERROR Procedure

Trap User-Defined Exceptions

Trap Predefined Oracle Server Errors

Trap Non-Predefined Oracle Server Errors

Handle Exceptions with PL/SQL

What are Exceptions?

Stored Procedures and Functions

What are Stored Procedures and Functions?

Execute a Simple Procedure

Create a Simple Procedure with IN parameter

Execute a Simple Function

Create a Simple Procedure

Differentiate between anonymous blocks and subprograms

Create a Simple Function

Who is the instructor for this training?

The Trainer is Oracle certified Instructor with extensive domain experience, including years of experience training & mentoring professionals in the industry.

SQL and PL/SQL Fundamentals Ed 2 Certification Training - Certification & Exam

SpringPeople is the Authorized Training Partner of Oracle.
The training fees is exclusive of exam cost.
For any queries; feel free to reach us at oracle@springpeople.com

Reviews

My outlook on training changed completely after attending SpringPeople BPC training. The content, the trainer and infrastructure at SpringPeople were top notch and perfectly in tune with the industry requirements. Regardless to say, training is now something that I look forward to to. Kudos to everyone at SpringPeople!

Shweta Priya

Sony

I attended the 3-day AngularJs training at SpringPeople. The trainer was an industry veteran with vast experience in the subject. Notably, the hands-on training, and the Q&A session stood out. Overall, I found SpringPeople a great place to learn with excellent facilities and great trainers. Would recommend SpringPeople to my colleagues and friends.

Swati Singh

I attended the training on API Design for Mulesoft. The sessions were well planned and value-laden. I benefited immensely from the hands-on experience enabled through virtual labs. I would like to specifically commend the efficiency of the support team who were always available to resolve my concerns.

Nikhil Kohli

Stryker

I attended the jQuery training batch, conducted by Mr. Vijay, an SME who did a thorough coverage of all the essentials. He took us through concepts such as jQuery animations, event handlers, plugins, and jQuery-UI by small programs, very easily. The sessions were useful and well structured. By the end of the training, I was well equipped to develop a SPA on Product Management System. Overall, the learning experience at SpringPeople was great!

Heena Rajan

Mindtree