PL/SQL Course content

Introduction to Oracle Database
  •  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
  •  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 in SELECT statements to produce meaningful reports
  •  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 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 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
  •  Create an External Table 
  •  Query External Tables
Manage Objects with Data Dictionary Views
  •  Explain the data dictionary
  •  Use the Dictionary Views
  •  USER_OBJECTS and ALL_OBJECTS Views
  •  Table and Column Information
  •  Query the dictionary views for constraint information
  •  Query the dictionary views for view, sequence, index and synonym information
  •  Add a comment to a table
  •  Query the dictionary views for comment information
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
Retrieve Data Using Sub-queries
  •  Multiple-Column Subqueries
  •  Pairwise and Nonpairwise 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
OLAP Queries

PSEUDO COLUMNS

Hierarchical Queries

Introduction to PL/SQL
  •  PL/SQL Overview
  •  Benefits of PL/SQL Subprograms
  • Overview of the Types of PL/SQL blocks
  •  Create a Simple Anonymous Block
  •  Generate 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 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
  •  Comment Code
  •  Deployment of SQL Functions in PL/SQL
  •  How to convert Data Types?
  •  Nested Blocks 
  •  Identify the Operators in PL/SQL
Interaction with the Oracle Server
  •  Invoke SELECT Statements in PL/SQL to Retrieve data
  •  Data Manipulation in the Server Using PL/SQL
  • SQL Cursor concept
  • Usage of SQL Cursor Attributes to Obtain Feedback on DML
  •  Save and Discard Transactions
Control Structures
  •  Conditional processing Using IF Statements
  •  Conditional processing Using CASE Statements
  •  Use simple Loop Statement
  •  Use While Loop Statement
  •  Use For Loop Statement
  •  Describe the Continue Statement
Composite Data Types
  •  Use PL/SQL Records
  • The %ROWTYPE Attribute
  •  Insert and Update with PL/SQL Records
  • Associative Arrays (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
  •  Explicit Cursor Attributes
  •  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 and Functions
  •  Understand 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
Create Stored Procedures
  •  Create a Modularized and Layered Subprogram Design
  •  Modularize Development With PL/SQL Blocks
  •  Describe the PL/SQL Execution Environment
  •  Identity the benefits of Using PL/SQL Subprograms
  •  List the differences Between Anonymous Blocks and Subprograms
  •  Create, Call, and Remove Stored Procedures Using the CREATE Command and SQL Developer
  • Implement Procedures Parameters and Parameters Modes
  •  View Procedures Information Using the Data Dictionary Views and SQL Developer
Create Stored Functions
  •  Create, Call, and Remove a Stored Function Using the CREATE Command and SQL  Developer
  •  Identity the advantages of Using Stored Functions in SQL Statements
  • List the steps to create a stored function
  •  Implement User-Defined Functions in SQL Statements
  •  Identity the restrictions when calling Functions from SQL statements
  •  Control Side Effects when calling Functions from SQL Expressions
  • View Functions Information
Create Packages
  •  Identity the advantages of Packages
  •  Describe Packages
  •  List 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 Package Constructs
  •  View PL/SQL Source Code Using the Data Dictionary
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
  •  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)
  • Statement Level Triggers Versus Row Level Triggers
  • Create Instead of and Disabled Triggers
  • How to Manage, Test, and Remove Triggers?
Create Compound, DDL, and Event Database Triggers 
  •  What are Compound Triggers?
  •  Identify the Timing-Point Sections of a Table Compound Trigger
  •  Compound Trigger Structure for Tables and Views
  •  Implement a Compound Trigger to Resolve the Mutating Table Error
  •  Compare Database Triggers to Stored Procedures
  •  Create Triggers on DDL Statements
  •  Create Database-Event and System-Event Triggers
  •  System Privileges Required to Manage Triggers