999 - Oracle Database: SQL and PL/SQL Fundamentals


999 - Oracle Database: SQL and PL/SQL Fundamentals


Price
Length : 5 days
$3,675.00

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.

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.

Learn To:

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

Hands-On Practices

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.

Course Bundle

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

Prerequisite

  • Familiarity with programming concepts
  • Familiarity with data processing concepts and techniques

Audience:

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

Course objectives

  • 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

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

Class Dates:

Classes being scheduled,
call 1-800-755-0142 or

Contact Us with your request.

On Site Class Available

ExpertONE Learning Solutions Provider Microsoft Certified Partner Logo GSA Advantage