Oracle Database 12C SQL and PL/SQL Fundamentals

Learn how to write queries against single and multiple tables, manipulate data in tables & create database objects.
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.

Course Overivew

Oracle Database SQL Certified Associate and demonstrate understanding of fundamental SQL concepts needed to undertake any database project.
Passing the exam illustrates depth of knowledge of SQL and its use when working with the Oracle Database server.
Gain a working knowledge of queries , insert, update and delete SQL statements as well as some Data Definition language and Data Control Language, the optimizer, tales and indexes, data modeling and normalization.
By passing this exam, a certified individual proves fluency in and a solid understanding of SQL language, data modeling and using SQL to create amd manipulate tables in an Oracle Database.
Qualified candidates have knowledge of general computing concepts, knowledge of command line interfaces and experience working in command line.

Read Less

Course Benefits:

Course Outline

Module 1: Introduction

Overview of Oracle Database 12c and related products
Overview of relational database management concepts and terminologies
Introduction to SQL and its development environments
The HR schema and the tables used in this course
Oracle Database documentation and additional resources

Module 2: Working with Oracle Cloud Exadata Express Cloud Service

Introduction to Oracle Database Exadata Express Cloud Service
Accessing Cloud Database using SQL Workshop
Connecting to Exadata Express using Database Clients

Module 3: Retrieve Data using the SQL SELECT Statement

List the capabilities of SQL SELECT statements
Generate a report of data from the output of a basic SELECT statement
Use arithmetic expressions and NULL values in the SELECT statement
Invoke Column aliases
Concatenation operator, literal character strings, alternative quote operator, and the DISTINCT keyword
Display the table structure using the DESCRIBE command

Module 4: Restricted and Sorted Data

Write queries with a WHERE clause to limit the output retrieved
Describe the comparison operators and logical operators
Describe the rules of precedence for comparison and logical operators
Usage of character string literals in the WHERE clause
Write queries with an ORDER BY clause
Sort the output in descending and ascending order
Substitution Variables

Module 5: Usage of Single-Row Functions to Customize Output

List the differences between single row and multiple row functions
Manipulate strings using character functions
Manipulate numbers with the ROUND, TRUNC, and MOD functions
Perform arithmetic with date data
Manipulate dates with the DATE functions

Module 6: 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
Apply the NVL, NULLIF, and COALESCE functions to data
Usage of conditional IF THEN ELSE logic in a SELECT statement

Module 7: Aggregated Data Using the Group Function

Usage of the aggregation functions in SELECT statements to produce meaningful reports
Describe the AVG, SUM, MIN, and MAX function
How to handle Null Values in a group function?
Divide the data in groups by using the GROUP BY clause
Exclude groups of date by using the HAVING clause

Module 8: Display Data From Multiple Tables

Write SELECT statements to access data from more than one table
Join Tables Using SQL:1999 Syntax
View data that does not meet a join condition by using outer joins
Join a table to itself by using a self join
Create Cross Joins

Module 9: Usage of Subqueries to Solve Queries

Use a Subquery to Solve a Problem
Single-Row Subqueries
Group Functions in a Subquery
Multiple-Row Subqueries
Use the ANY and ALL Operator in Multiple-Row Subqueries
Use the EXISTS Operator

Module 10: SET Operators

Describe the SET operators
Use a SET operator to combine multiple queries into a single query
Describe the UNION, UNION ALL, INTERSECT, and MINUS Operators
Use the ORDER BY Clause in Set Operations

Module 11: Data Manipulation

Add New Rows to a Table
Change the Data in a Table
Use the DELETE and TRUNCATE Statements
How to save and discard changes with the COMMIT and ROLLBACK statements
Implement Read Consistency
Describe the FOR UPDATE Clause

Module 12: DDL Statements to Create and Manage Tables

Categorize Database Objects
Create Tables
Describe the data types
Understand Constraints
Create a table using a subquery
How to alter a table?
How to drop a table?

Module 13: Other Schema Objects

Create, modify, and retrieve data from a view
Perform Data manipulation language (DML) operations on a view
How to drop a view?
Create, use, and modify a sequence
Create and drop indexes
Create and drop synonyms

Module 14: 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

Module 15: 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

Module 16: Write Anonymous PL/SQL blocks

Basic PL/SQL Block Syntax Guidelines
How to comment code?
SQL Functions in PL/SQL
Data Type Conversion
Nested Blocks
Operators in PL/SQL

Module 17: SQL statements in PL/SQL block

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?

Module 18: Control Structures

Conditional processing Using IF Statements
Conditional processing Using CASE Statements
Simple Loop Statement
While Loop Statement
For Loop Statement
The Continue Statement

Module 19: 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

Module 20: 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

Module 21: 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

Module 22: 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

What you will learn

Write queries against single and multiple tables, manipulate data in tables and create database objects.
Use single row functions to customize output.
Invoke conversion functions and conditional expressions.
Use group functions to report aggregated data.
Create PL/SQL blocks of application code that can be shared by multiple forms, reports and data management applications.
Develop anonymous PL/SQL blocks, stored procedures and functions.
Declare identifiers and trap exceptions.
Use DML statements to manage data.
Use DDL statements to manage database objects.
Declare PL/SQL Variables.
Conditionally control code flow (loops, control structures).
Describe stored procedures and functions.
Retrieve row and column data from tables.

Audience profile

Developer
Implementer

Download
Course
Brochure

Prerequisites

This course is available at :

Classroom Training

Cairo

Giza

Onsite

Online Training

Virtual Interactive Instructor LED

Self-Paced Training

WHY CHOOSE CLS

Experience

We have been in the market since 1995, and we kept accumulating experience in the training business, and providing training for more than 200,000 trainees ever since, in Egypt, and the MENA region.

Premium Facilities

CLS facilities are well-equipped with strong hardware and software technologies that aid both students and trainers lead very effective smooth training programs.

Customer Support

We provide our clients with the best solutions, customized to their specific needs and goals. Our team is highly qualified to answer whatever questions you have.

Global Accredited

CLS is an authorized and accredited partner by technology leaders. This means that our training programs are of the highest quality source materials.

Up To Date

We keep tabs on every change in the market and the technology field, so our training programs will always be updated up to the World-class latest standards, and adapted to the global shape-shifting job market.

Certified Instructors

We select the best instructors, who are certified from trustworthy international vendors. They share their professional experience with the Trainees, so they can have a clear hands-on experience.

Our Strategic Clients

Over 200,000 Gradutes From CLS

Amr Mostafa

An employee of the Security Department at the Ministry of Electricity

I`m attending now CEH Training with Eng Mohamed Hamdy ,CISSP Training with Eng Mohamed Gohar, I really learned a lot from him , everything here in CLS is very satisfying including facilities .

Play Video

Lamiaa Medhat

CIO

We took a series of courses as the digital Transformation Unit of the ministry . we just finished CRISC Certification Training with DR Adel Abdel Meneim . Thank you CLS for all your efforts, we really appreciate it

Play Video

Ahmed Salah

Senior Cyber Security Engineer

Me and my colleagues are working in a government Organization, We took a no. of cyber security trainings with CLS starting with CEH and CISSP. we liked every thing the instructors, the stuff and whole environment

Play Video

Ferras Hassan

Head of the Programming Department at Bashayer Energy Company

I`m attending ASP.NET Core with MVC Training with Eng Mohamed Hesham , I really learned a lot from him , everything here in CLS is very satisfying including facilities .Thanks you all team.

Play Video

Mohamed Ahmed Ali

Systems management specialist

Qualifying the cadres of digital transformation units in government agencies moving to the administrative capital .Thanks CLS

Play Video

Zeinab Salah

Software Developer at Bashayer Energy Company

I`m attending ASP.NET Core with MVC Training with Eng Mohamed Hesham , I really learned a lot from him , everything here in CLS is very satisfying including facilities .Thanks you all team.

Play Video

Ibrahim Khalaf

IT Infrastructure and Security Manger

I`m attending now CRISC Training with DR Adel Abdel Meneim , I really learned a lot from him , everything here in CLS is very satisfying including facilities , locations and the team.

Play Video

Samar Shams ElDin

Programmer at Bashayer Energy Company

I`m attending ASP.NET Core with MVC Training with Eng Mohamed Hesham , I really learned a lot from him , everything here in CLS is very satisfying including facilities .Thanks you all team.

Oracle Database 12C SQL and PL/SQL Fundamentals

Course Overivew

Prerequisites

This course is available at :

Classroom Training

Online Training

WHY CHOOSE CLS

Experience

Premium Facilities

Customer Support

Global Accredited

Up To Date

Certified Instructors

Our Strategic Clients

Over 200,000 Gradutes From CLS

Amr Mostafa

Lamiaa Medhat

Ahmed Salah

Ferras Hassan

Mohamed Ahmed Ali

Zeinab Salah

Ibrahim Khalaf

Samar Shams ElDin

Business Management →

IT Networks and ITSM →

Data Management →

Information Security →

Software Development →

Digital Transformation →

Graphic Design →

Engineering Design →

Quick links

Join Our News Letter

Student Application For

Business Application For