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.

Benefits to You

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.

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 and JDeveloper are 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.

 

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

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

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

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

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

Aggregated Data Using the Group Functions

  • 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

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

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

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

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

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?

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

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

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

Write Executable Statements

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

Interaction with the Oracle Server

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

Control Structures

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

Usage of 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

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

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

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

 

Introduction

  • Course Objectives
  • Course Agenda
  • Describe the Human Resources (HR) Schema
  • PL/SQL development environments available in this course
  • Introduction to SQL Developer

Introduction to PL/SQL

  • Overview of PL/SQL
  • Identify the benefits of PL/SQL Subprograms
  • Overview of the types of PL/SQL blocks
  • Create a Simple Anonymous Block
  • How to generate output from a PL/SQL Block?

Declare PL/SQL Identifiers

  • List the different Types of Identifiers in a PL/SQL subprogram
  • Usage of the Declarative Section to Define Identifiers
  • Use variables to store data
  • Identify Scalar Data Types
  • The %TYPE Attribute
  • What are Bind Variables?
  • Sequences in PL/SQL Expressions

Write Executable Statements

  • Describe Basic PL/SQL Block Syntax Guidelines
  • Learn to Comment the Code
  • Deployment of SQL Functions in PL/SQL
  • How to convert Data Types?
  • Describe Nested Blocks
  • Identify the Operators in PL/SQL

Interaction with the Oracle Server

  • Invoke SELECT Statements in PL/SQL
  • Retrieve Data in PL/SQL
  • SQL Cursor concept
  • Avoid Errors by using Naming Conventions when using Retrieval and DML Statements
  • Data Manipulation in the Server using PL/SQL
  • Understand the SQL Cursor concept
  • Use SQL Cursor Attributes to Obtain Feedback on DML
  • Save and Discard Transactions

Control Structures

  • Conditional processing using IF Statements
  • Conditional processing using CASE Statements
  • Describe simple Loop Statement
  • Describe While Loop Statement
  • Describe For Loop Statement
  • Use the Continue Statement

Composite Data Types

  • Use PL/SQL Records
  • The %ROWTYPE Attribute
  • Insert and Update with PL/SQL Records
  • INDEX BY Tables
  • Examine INDEX BY Table Methods
  • Use INDEX BY Table of Records

Explicit Cursors

  • What are Explicit Cursors?
  • Declare the Cursor
  • Open the Cursor
  • Fetch data from the Cursor
  • Close the Cursor
  • Cursor FOR loop
  • The %NOTFOUND and %ROWCOUNT Attributes
  • Describe the FOR UPDATE Clause and WHERE CURRENT Clause

Exception Handling

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

Stored Procedures

  • Create a Modularized and Layered Subprogram Design
  • Modularize Development With PL/SQL Blocks
  • Understand the PL/SQL Execution Environment
  • List the benefits of using PL/SQL Subprograms
  • List the differences between Anonymous Blocks and Subprograms
  • Create, Call, and Remove Stored Procedures
  • Implement Procedures Parameters and Parameters Modes
  • View Procedure Information

Stored Functions and Debugging Subprograms

  • Create, Call, and Remove a Stored Function
  • Identify the advantages of using Stored Functions
  • Identify the steps to create a stored function
  • Invoke User-Defined Functions in SQL Statements
  • Restrictions when calling Functions
  • Control side effects when calling Functions
  • View Functions Information
  • How to debug Functions and Procedures?

Packages

  • Listing the advantages of Packages
  • Describe Packages
  • What are the components of a Package?
  • Develop a Package
  • How to enable visibility of a Package’s Components?
  • Create the Package Specification and Body using the SQL CREATE Statement and SQL Developer
  • Invoke the Package Constructs
  • View the PL/SQL Source Code using the Data Dictionary

Deploying Packages

  • Overloading Subprograms in PL/SQL
  • Use the STANDARD Package
  • Use Forward Declarations to solve Illegal Procedure Reference
  • Implement Package Functions in SQL and Restrictions
  • Persistent State of Packages
  • Persistent State of a Package Cursor
  • Control side effects of PL/SQL Subprograms
  • Invoke PL/SQL Tables of Records in Packages

Implement Oracle-Supplied Packages in Application Development

  • What are Oracle-Supplied Packages?
  • Examples of some of the Oracle-Supplied Packages
  • How does the DBMS_OUTPUT Package work?
  • Use the UTL_FILE Package to Interact with Operating System Files
  • Invoke the UTL_MAIL Package
  • Write UTL_MAIL Subprograms

Dynamic SQL

  • The Execution Flow of SQL
  • What is Dynamic SQL?
  • Declare Cursor Variables
  • Dynamically Executing a PL/SQL Block
  • Configure Native Dynamic SQL to Compile PL/SQL Code
  • How to invoke DBMS_SQL Package?
  • Implement DBMS_SQL with a Parameterized DML Statement
  • Dynamic SQL Functional Completeness

Design Considerations for PL/SQL Code

  • Standardize Constants and Exceptions
  • Understand Local Subprograms
  • Write Autonomous Transactions
  • Implement the NOCOPY Compiler Hint
  • Invoke the PARALLEL_ENABLE Hint
  • The Cross-Session PL/SQL Function Result Cache
  • The DETERMINISTIC Clause with Functions
  • Usage of Bulk Binding to Improve Performance

Triggers

  • Describe Triggers
  • Identify the Trigger Event Types and Body
  • Business Application Scenarios for Implementing Triggers
  • Create DML Triggers using the CREATE TRIGGER Statement and SQL Developer
  • Identify the Trigger Event Types, Body, and Firing (Timing)
  • Differences between Statement Level Triggers and Row Level Triggers
  • Create Instead of and Disabled Triggers
  • How to Manage, Test and Remove Triggers?

Creating Compound, DDL, and Event Database Triggers

  • What are Compound Triggers?
  • Identify the Timing-Point Sections of a Table Compound Trigger
  • Understand the Compound Trigger Structure for Tables and Views
  • Implement a Compound Trigger to Resolve the Mutating Table Error
  • Comparison of Database Triggers to Stored Procedures
  • Create Triggers on DDL Statements
  • Create Database-Event and System-Events Triggers
  • System Privileges Required to Manage Triggers

PL/SQL Compiler

  • What is the PL/SQL Compiler?
  • Describe the Initialization Parameters for PL/SQL Compilation
  • List the new PL/SQL Compile Time Warnings
  • Overview of PL/SQL Compile Time Warnings for Subprograms
  • List the benefits of Compiler Warnings
  • List the PL/SQL Compile Time Warning Messages Categories
  • Setting the Warning Messages Levels: Using SQL Developer, PLSQL_WARNINGS Initialization Parameter, and the DBMS_WARNING Package Subprograms
  • View Compiler Warnings: Using SQL Developer, SQL*Plus, or the Data Dictionary Views

Manage Dependencies

  • Overview of Schema Object Dependencies
  • Query Direct Object Dependencies using the USER_DEPENDENCIES View
  • Query an Object’s Status
  • Invalidation of Dependent Objects
  • Display the Direct and Indirect Dependencies
  • Fine-Grained Dependency Management in Oracle Database 12c
  • Understand Remote Dependencies
  • Recompile a PL/SQL Program Unit