SQL+PL/SQL

PL/SQL Training includes procedural language components like loops and conditions that permit declaration of constants and variables.

Chapter 1 : Introduction to SQL

Introduction to Oracle Database

  • List the features of Oracle Database 12c
  • Discuss the basic design, theoretical, and physical aspects of a relational database
  • Categorize the different types of SQL statements
  • Describe the data set used by the course
  • Log on to the database using SQL Developer environment
  • Save queries to files and use script files in SQL Developer

 

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 Select All Columns
  • Select Specific Columns
  • Use Column Heading Defaults
  • Use Arithmetic Operators
  • Understand Operator Precedence
  • Learn the DESCRIBE command to display the table structure

Learn to Restrict and Sort Data

 

  • Write queries that contain a WHERE clause to limit the output retrieved
  • List the comparison operators and logical operators that are used in a WHERE clause
  • Describe the rules of precedence for comparison and logical operators
  • Use character string literals in the WHERE clause
  • Write queries that contain an ORDER BY clause to sort the output of a SELECT statement
  • Sort output in descending and ascending order

Usage of Single-Row Functions to Customize Output

  • Describe the differences between single row and multiple row functions
  • Manipulate strings with character function in the SELECT and WHERE clauses
  • Manipulate numbers with the ROUND, TRUNC, and MOD functions
  • Perform arithmetic with date data
  • Manipulate dates with the DATE functions

Invoke Conversion Functions and Conditional Expressions

  • Describe implicit and explicit data type conversion
  • Use the TO_CHAR, TO_NUMBER, and TO_DATE conversion functions
  • Nest multiple functions
  • Apply the NVL, NULLIF, and COALESCE functions to data
  • Use conditional IF THEN ELSE logic in a SELECT statement

Aggregate Data Using the Group Functions

  • Use the aggregation functions to produce meaningful reports
  • Divide the retrieved data in groups by using the GROUP BY clause
  • Exclude groups of data by using the HAVING clause

Display Data From Multiple Tables Using Joins

 

  • Write SELECT statements to access data from more than one table
  • View data that generally does not meet a join condition by using outer joins
  • Join a table to itself by using a self-join

Use Sub-queries to Solve Queries

  • Describe the types of problem that sub-queries can solve
  • Define sub-queries
  • List the types of sub-queries
  • Write single-row and multiple-row sub-queries

The SET Operators

  • Describe the SET operators
  • Use a SET operator to combine multiple queries into a single query
  • Control the order of rows returned

Data Manipulation Statements

  • Describe each DML statement
  • Insert rows into a table
  • Change rows in a table by the UPDATE statement
  • Delete rows from a table with the DELETE statement
  • Save and discard changes with the COMMIT and ROLLBACK statements
  • Explain read consistency

Use of DDL Statements to Create and Manage Tables

  • Categorize the main database objects
  • Review the table structure
  • List the data types available for columns
  • Create a simple table
  • Decipher how constraints can be created at table creation
  • Describe how schema objects work

Other Schema Objects

  • Create a simple and complex view
  • Retrieve data from views
  • Create, maintain, and use sequences
  • Create and maintain indexes
  • Create private and public synonyms

Control User Access

  • Differentiate system privileges from object privileges
  • Create Users
  • Grant System Privileges
  • Create and Grant Privileges to a Role
  • Change Your Password
  • Grant Object Privileges
  • How to pass on privileges
  • Revoke Object Privileges

Management of Schema Objects

  • Add, Modify, and Drop a Column
  • Add, Drop, and Defer a Constraint
  • How to enable and Disable a Constraint?
  • Create and Remove Indexes
  • Create a Function-Based Index
  • Perform Flashback Operations
  • Create an External Table by Using ORACLE_LOADER and by Using ORACLE_DATAPUMP
  • Query External Tables

Manipulate Large Data Sets

  • Use Subqueries to Manipulate
  • Data Retrieve Data Using a Subquery as Source
  • Insert Using a Subquery as a Target
  • Usage of the WITH CHECK OPTION Keyword on DML Statements
  • List the types of Multitable INSERT Statements
  • Use Multitable INSERT Statements
  • Merge rows in a table
  • Track Changes in Data over a period of time

Data Management in different Time Zones

  • Time Zones
  • CURRENT_DATE, CURRENT_TIMESTAMP, and LOCALTIMESTAMP
  • Compare Date and Time in a Session’s Time Zone
  • DBTIMEZONE and SESSIONTIMEZONE
  • Difference between DATE and TIMESTAMP
  • INTERVAL Data Types
  • Use EXTRACT, TZ_OFFSET and FROM_TZ
  • Invoke TO_TIMESTAMP,TO_YMINTERVAL and TO_DSINTERVAL

Retrieve Data Using Sub-queries

  • Multiple-Column Subqueries
  • Pairwise and Non-pairwise Comparison
  • Scalar Subquery Expressions
  • Solve problems with Correlated Subqueries
  • Update and Delete Rows Using Correlated Subqueries
  • The EXISTS and NOT EXISTS operators
  • Invoke the WITH clause
  • The Recursive WITH clause

Regular Expression Support

  • Use the Regular Expressions Functions and Conditions in SQ
  • L Use Meta Characters with Regular Expressions
  • Perform a Basic Search using the REGEXP_LIKE function
  • Find patterns using the REGEXP_INSTR function
  • Extract Substrings using the REGEXP_SUBSTR function
  • Replace Patterns Using the REGEXP_REPLACE function
  • Usage of Sub-Expressions with Regular Expression Support
Implement the REGEXP_COUNT function

    Chapter 2 : Oracle Database Program with PL/SQL

    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 Wr

    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 Statemen
    • 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 Curso
    • r 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

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

    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 Developr
    • r 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
    • s 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/SQ
    • L 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