Oracle Database 23ai: SQL Workshop Training in Bahrain

Oracle Database 23ai: SQL Workshop is designed for developers and database administrators who want to build a solid understanding of relational database concepts and practical SQL usage.

The course begins with the fundamentals of relational databases and the SQL language, then progresses to querying one or multiple tables, filtering and sorting data, manipulating table contents, and creating database objects. Participants also learn how to create and work with sequences, synonyms, indexes, and views, manage schema objects, retrieve and modify data through subqueries, control user access, and handle date and time data across different time zones.

Note: The course is applicable to client environments using Oracle Database 18c, 19c, 21c, and 23ai.

Participants are expected to have basic familiarity with:

• Data processing concepts and techniques
• Programming languages

This course is intended for:

• Developers
• Database Administrators

By the end of the course, participants will be able to:

• Explain the core concepts of Oracle Database 23c and related products.
• Understand relational database management concepts and terminology.
• Use SQL effectively to query Oracle databases.
• Retrieve, restrict, and sort data with SQL statements.
• Customize query output with single-row functions.
• Convert data types and apply conditional expressions.
• Produce aggregated reports with group functions.
• Retrieve data from multiple tables by using joins.
• Solve more complex queries with subqueries.
• Manipulate result sets with set operators.
• Manage table data through DML statements.
• Create and maintain database objects such as sequences, synonyms, indexes, and views.
• Control user privileges and access.
• Work with date and time data across multiple time zones.

1. Introduction

• Overview of Oracle Database 23c and related products
• Relational Database Management concepts and terminology
• Introduction to SQL and SQL development environments
• Oracle Database 23c SQL documentation and additional learning resources

2. Retrieving Data with the SQL SELECT Statement

• Capabilities of SQL SELECT statements
• Arithmetic expressions and NULL values in SELECT
• Using column aliases
• The DESCRIBE command

3. Restricting and Sorting Data

• Restricting returned rows with the WHERE clause
• Sorting query results with the ORDER BY clause
• Using the SQL Row Limiting Clause
• Working with substitution variables
• Assigning values to variables

4. Customizing Output with Single-Row Functions

• Single-row SQL functions
• Character functions
• Number functions
• Working with date values
• Date functions

5. Using Conversion Functions and Conditional Expressions

• Implicit and explicit data type conversion
• TO_CHAR, TO_DATE, and TO_NUMBER
• General-purpose functions: NVL, NVL2, NULLIF, COALESCE
• Conditional expressions with CASE and DECODE
• JSON functions

6. Reporting Aggregated Data with Group Functions

• Group functions
• Grouping rows
• Nested group functions

7. Displaying Data from Multiple Tables with Joins

• Join types and syntax
• Natural Join
• Joins using the USING clause
• Joins using the ON clause
• Self-Joins
• Nonequijoins
• Outer Joins
• Cartesian Products

8. Solving Queries with Subqueries

• Subquery types, syntax, and usage guidelines
• Single-row subqueries
• Multiple-row subqueries
• Multiple-column subqueries
• Handling NULL values in subqueries

9. Using Set Operators

• Set operator types and usage guidelines
• UNION and UNION ALL
• INTERSECT
• MINUS
• Matching SELECT statements
• Applying ORDER BY to set operation results

10. Managing Tables with DML Statements

• Adding rows to a table
• Updating table data
• Removing rows from a table
• Database transaction control
• Read consistency
• Manual data locking

11. Introduction to Data Definition Language

• Database objects
• The CREATE TABLE statement
• Constraints
• The ALTER TABLE statement
• The DROP TABLE statement

12. Introduction to Data Dictionary Views

• Understanding the Data Dictionary
• Querying dictionary views
• Adding comments to tables

13. Creating Sequences, Synonyms, and Indexes

• Creating, maintaining, and using sequences
• Creating private and public synonyms
• Creating and maintaining indexes

14. Creating Views

• Overview of views
• Creating, modifying, and retrieving data through views
• Performing DML operations on views
• Dropping views

15. Managing Schema Objects

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

16. Retrieving Data with Subqueries

• Using a subquery as a data source
• Using scalar subqueries in SQL
• Solving problems with correlated subqueries
• Using EXISTS and NOT EXISTS
• Working with the WITH clause

17. Manipulating Data with Subqueries

• Using subqueries in data manipulation operations
• Inserting values by using a subquery as the target
• Applying WITH CHECK OPTION in DML statements
• Updating and deleting rows with correlated subqueries

18. Controlling User Access

• Distinguishing system privileges from object privileges
• Creating roles
• Granting and revoking object privileges

19. Manipulating Data with Advanced Queries

• Specifying explicit default values in INSERT and UPDATE statements
• Performing Multitable INSERT operations
• Merging rows in a table
• Performing Flashback operations
• Tracking changes in data over time

20. Managing Data Across Different Time Zones

• CURRENT_DATE, CURRENT_TIMESTAMP, and LOCALTIMESTAMP
• DBTIMEZONE and SESSIONTIMEZONE
• TIMESTAMP and INTERVAL data types
• Additional date functions
• Handling date and time values with SYSDATE and SYSTIMESTAMP