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