International Certification Program: Oracle Developer SQL Workshop + PL/SQL OCP

Topics

- Course objectives, roadmap, and appendixes used in the course
- Overview of Oracle Database 19c and related products
- Overview of relational database management concepts and terminologies
- Human Resource (HR) Schema and the tables used in the course
- Introduction to SQL and its development environments
- Oracle Database 19c SQL Documentation and Additional Resources

- Capabilities of SQL SELECT statements
- Arithmetic expressions and NULL values in the SELECT statement
- Column aliases
- Use of the concatenation operator, literal character strings, the alternative quote operator, and the DISTINCT keyword
- DESCRIBE command

- Limiting rows with:
-> The WHERE clause
-> The comparison operators using =, -> Logical conditions using AND, OR, and NOT operators

- Rules of precedence for operators in an expression
- Sorting rows using the ORDER BY clause
- SQL row limiting clause in a query
- Substitution variables in Oracle
- Assigning values to variables

- Single-row SQL functions
- Character functions
- Nesting functions
- Number functions
- Working with dates in Oracle Databases
- Working with dates in MySQL Databases
- Date functions

- Single-row SQL functions
- Character functions
- Nesting functions
- Number functions
- Working with dates in Oracle Databases
- Working with dates in MySQL Databases
- Date functions

- Implicit and explicit data type conversion
- TO_CHAR, TO_DATE, TO_NUMBER functions in Oracle
- Using the CAST() function in MySQL
- General functions (NVL, NVL2, NULLIF, COALESCE)
- Conditional expressions (CASE, Searched CASE, DECODE)
- JSON functions: (JSON_QUERY, JSON_TABLE, JSON_VALUE)

- Group functions:
-> Types and syntax
-> Use AVG, SUM, MIN, MAX, COUNT
-> Use the DISTINCT keyword within group functions
- NULL values in a group function
- Grouping rows: (GROUP BY clause, Having clause)

- Displaying Data from Multiple Tables Using Joins
- Types of JOINS and their syntax
- Natural join
- Join with the USING clause
- Join with the ON clause
- Self-join
- Nonequijoins
- OUTER join ( LEFT OUTER JOIN, RIGHT OUTER JOIN, FULL OUTER JOIN )
- Cartesian product ( Cross join )

- Subquery: Types, syntax, and guidelines
- Single-row subqueries:
-> Group functions in a subquery
-> HAVING clause with subqueries
-> Multiple-row subqueries
- Using All or Any Operator

-> Multiple-column subqueries
-> Null values in a subquery

- Set operators: Types and guidelines
- Tables used in this lesson
- UNION and UNION ALL operator
- INTERSECT operator
- MINUS operator
- Matching SELECT statements
- Using the ORDER BY clause in set operations

- Adding new rows in a table ( INSERT statement )
- Changing data in a table ( UPDATE statement)
- Removing rows from a table: ( DELETE and TRUNCATE statement)
- Database transaction control using COMMIT, ROLLBACK, and SAVEPOINT
- Read consistency
- Manual Data Locking

- Adding new rows in a table ( INSERT statement )
- Changing data in a table ( UPDATE statement)
- Removing rows from a table: ( DELETE and TRUNCATE statement)
- Database transaction control using COMMIT, ROLLBACK, and SAVEPOINT
- Read consistency
- Manual Data Locking

- Database objects ( Naming rules )
- CREATE TABLE statement
- Data types
- Overview of constraints: NOT NULL, UNIQUE, PRIMARY KEY, FOREIGN KEY, CHECK constraints
- Creating a table using a subquery
- ALTER TABLE statement
- DROP TABLE statement

- Database objects (Naming rules)
- CREATE TABLE statement
- Data types
- Indexes, keys, and constraints
- Column options
- Creating a table using a subquery
- ALTER TABLE statement
- DROP TABLE statement

- Introduction to data dictionary

- Querying the dictionary views for the following:
-> Table information
-> Column information
-> Constraint information
- Adding a comment to a table and querying the dictionary views for comment information

- Overview of sequences
- Overview of synonyms
- Overview of indexes

- Overview of views
- Creating, modifying, and retrieving data from a view
- Data manipulation language (DML) operations on a view
- Dropping a view

- Managing constraints
- Creating and using temporary tables
- Creating and using external tables

- Retrieving data by using a subquery as a source
- Writing a multiple-column subquery
- Using scalar subqueries in SQL
- Solving problems with correlated subqueries
- Using the EXISTS and NOT EXISTS operators
- Using the WITH clause

- Retrieving data by using a subquery as a source
- Writing a multiple-column subquery
- Using scalar subqueries in SQL
- Solving problems with correlated subqueries
- Using the EXISTS and NOT EXISTS operators
- Using the WITH clause

- Using subqueries to manipulate data
- Inserting values by using a subquery as a target
- Using the WITH CHECK OPTION keyword on DML statements
- Using correlated subqueries to update and delete rows

- System privileges
- Creating a role
- Object privileges
- Revoking object privileges

- Specifying explicit default values in the INSERT and UPDATE statements
- Using the following types of multitable INSERTs:
-> Unconditional INSERT
-> Conditional INSERT ALL
-> Conditional INSERT FIRST
-> Pivoting INSERT

- Merging rows in a table
- Performing flashback operations
- Tracking the changes in data over a period of time

- CURRENT_DATE, CURRENT_TIMESTAMP, and LOCALTIMESTAMP
- INTERVAL data types
- Using the following functions:
-> EXTRACT
-> TZ_OFFSET
-> FROM_TZ
-> TO_TIMESTAMP
-> TO_YMINTERVAL
-> TO_DSINTERVAL

Topics

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

- 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

- 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

- 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

- 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

- 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

- 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

- 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

- 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

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