Oracle Database 23ai: SQL Workshop

COURSE OUTLINE

MODULE 01 Course Overview

• Course Overview – Oracle Database 23ai: SQL Workshop
• Course Practice Environment: Security Credentials

MODULE 02 Oracle Database 23ai SQL Enhancements

• Oracle Database 23ai SQL New Features
• Returning clause of UPDATE and MERGE Statements, IF (NOT) EXISTS

MODULE 03 Introduction to SQL and Database Concepts

• Overview of Oracle Database 23ai and Related Products
• Overview of Relational Database Management Concepts and Terminologies
• Human Resources (HR) Schema and the Tables Used in This Course
• Introduction to SQL and Its Development Environments
• Oracle Database 23ai SQL Documentation and Additional Resources
• Practices for Lesson 1: Introduction

MODULE 04 Retrieving Data Using the SQL SELECT Statement

• Capabilities of SQL SELECT Statements
• Arithmetic Expressions and NULL Values in the SELECT statement
• Column Aliases
• Use of the Concatenation Operator, Literal Character Strings, Alternative Quote Operator, and the DISTINCT Keyword
• DESCRIBE Command
• Practices for Lesson 2: Retrieving Data Using the SQL SELECT Statement

MODULE 05 Restricting and Sorting Data

• Limiting Rows
• 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
• Practices for Lesson 3: Restricting and Sorting Data

MODULE 06 Using Single-Row Functions to Customize Output

• Single-Row SQL Functions
• Character Functions
• Nesting Functions
• Number Functions
• Working with Dates in Oracle Databases
• Date Functions
• Practices for Lesson 4: Using Single- Row Functions to Customize Output

MODULE 07 Using Conversion Functions and Conditional Expressions

• Implicit and Explicit Data Type Conversion
• TO_CHAR, TO_DATE, and TO_NUMBER Functions in Oracle
• General Functions
• Conditional Expressions
• Practices for Lesson 5: Using Conversion Functions and Conditional Expressions

MODULE 08 Reporting Aggregated Data Using the Group Functions

• JSON Functions
• Reporting Aggregated Data Using the Group Functions/Group Functions
• Grouping Rows
• Nesting Group Functions
• Practices for Lesson 6: Reporting Aggregated Data Using the Group Functions

MODULE 09 Displaying Data from Multiple Tables Using Joins

• Displaying Data from Multiple Tables Using Joins
• Natural Join
• Join with the USING Clause
• Join with the ON Clause
• Self-Join
• Nonequijoins
• Outer Join
• Cartesian Product
• Practices for Lesson 7: Displaying Data from Multiple Tables Using Joins

MODULE 10 Using Subqueries to Solve Queries

• Using Subqueries to Solve Queries
• Single-Row Subqueries
• Multiple-Row Subqueries
• Multiple-Column Subqueries
• Null Values in a Subquery
• Practices for Lesson 8: Using Subqueries to Solve Queries

MODULE 11 Using Set Operators

• Using Set Operators
• Tables Used in This Lesson
• UNION and UNION ALL Operators
• INTERSECT Operator
• MINUS Operator
• Matching SELECT Statements
• Using the ORDER BY Clause in Set Operations
• Practices for Lesson 9: Using Set Operators

MODULE 12 Manipulating Data

• Managing Tables Using DML Statements
• Changing Data in a Table
• Removing Rows from a Table
• Database Transaction Control
• Read Consistency
• Manual Data Locking
• Practices for Lesson 10: Manipulating Data

MODULE 13 Introduction to Data Definition Language

• Introduction to Data Definition Language
• CREATE TABLE Statement
• Data Types
• Constraints: Overview
• Creating a Table by Using a Subquery
• ALTER TABLE Statement
• DROP TABLE Statement
• Practices for Lesson 11: Introduction to Data Definition Language

MODULE 14 Introduction to Data Dictionary Views

• Introduction to Data Dictionary
• Querying the Dictionary Views
• Adding a Comment to a Table and Querying the Dictionary Views
• Practices for Lesson 12: Introduction to Data Dictionary Views

MODULE 15 Creating Sequences, Synonyms, and Indexes

• Create, Maintain, and Use Sequences
• Create Private and Public Synonyms
• Create and Maintain Indexes
• Practices for Lesson 13: Creating Sequences, Synonyms, and Indexes

MODULE 16 Creating Views

• Creating Views
• Creating, Modifying, and Retrieving Data from a View
• Data Manipulation Language (DML) Operations on a View
• Dropping a View
• Practices for Lesson 14: Creating Views

MODULE 17 Managing Schema Objects

• Managing Schema Objects
• Create and Use Temporary Tables
• Creating and Using External Tables
• Practices for Lesson 15: Managing Schema Objects

MODULE 18 Retrieving Data Using Subqueries

• 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
• Practices for Lesson 16: Retrieving Data Using Subqueries

MODULE 19 Manipulating Data Using Subqueries

• Manipulating Data by Using Subqueries
• 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
• Practices for Lesson 17: Manipulating Data Using Subqueries

MODULE 20 Controlling User Access

• Controlling User Access
• Creating a Role
• Object Privileges
• Revoking Object Privileges
• Practices for Lesson 18: Controlling User Access

MODULE 21 Manipulating Data Using Advanced Queries

• Manipulating Data Using Advanced Queries
• Multitable INSERTS
• Merging Rows in a Table
• Performing Flashback Operations
• Tracking the Changes in Data Over a Period of Time
• Practices for Lesson 19: Manipulating Data Using Advanced Queries

MODULE 22  Managing Data in Different Time Zones

• Managing Data in Different Time Zones
• INTERVAL Data Types
• Aggregation over INTERVAL types
• Additional Date Functions
• SYSDATE and SYSTIMESTAMP Data Handling
• Practices for Lesson 20: Managing Data in Different Time Zones

MODULE 23 Appendix A Through J

• Appendix A Through J Overview
• Practices for Appendix A Through J: Additional Practices and Solutions