OD-AI-SQLW: Oracle AI Database SQL Workshop

Module 1: Introduction

• Objectives
• Overview of Oracle Database 23c and Related Products
• Oracle Database 23c: Focus Areas
• Oracle Database 23c
• Overview of Relational Database Management Concepts and Terminologies
• Relational and Object Relational Database Management Systems
• Data Storage on Different Media
• Relational Database Concept
• Definition of a Relational Database
• Data Models
• Entity Relationship Model
• Entity Relationship Modeling Conventions
• Relating Multiple Tables
• Relational Database Terminology
• Human Resources (HR) Schema and the Tables Used in This Course
• Human Resources (HR) Application
• Tables Used in This Course
• Tables Used in the Course
• Introduction to SQL and Its Development Environments
• Using SQL to Query Your Database
• How SQL Works
• SQL Statements Used in the Course
• Development Environments for SQL in Oracle
• Introduction to Oracle Live SQL
• Oracle Database 23c SQL Documentation and Additional Resources
• Oracle Database Documentation
• Additional Resources for Oracle
• Oracle University: Oracle SQL Training
• Oracle SQL Certification
• Summary

Module 2: Retrieving Data Using the SQL SELECT Statement

• Course Roadmap
• Objectives
• Capabilities of SQL SELECT Statements
• HR Application Scenario
• Writing SQL Statements
• Basic SELECT Statement
• Selecting All Columns
• Executing SQL Statements with Oracle SQL Developer and SQL*Plus
• Column Heading Defaults in SQL Developer and SQL*Plus
• Selecting Specific Columns
• Selecting from dual with Oracle Database
• New Feature in 23c: SELECT Without FROM Clause
• Arithmetic Expressions and NULL Values in the SELECT statement
• Arithmetic Expressions
• Using Arithmetic Operators
• Operator Precedence
• Defining a Null Value
• Null Values in Arithmetic Expressions
• Column Aliases
• Defining a Column Alias
• Using Column Aliases
• Use of the Concatenation Operator, Literal Character Strings, Alternative Quote Operator, and the DISTINCT Keyword
• Concatenation Operator in Oracle
• Literal Character Strings
• Using Literal Character Strings in Oracle
• Alternative Quote (q) Operator in Oracle
• Duplicate Rows
• DESCRIBE Command
• Displaying Table Structure by Using the DESCRIBE Command
• Displaying Table Structure by Using Oracle SQL Developer
• Summary

Module 3: Restricting and Sorting Data

• Course Roadmap
• Objectives
• Limiting Rows
• Limiting Rows by Using a Selection
• Limiting Rows That Are Selected
• Using the WHERE Clause
• Character Strings and Dates
• Comparison Operators
• Using Comparison Operators
• Range Conditions Using the BETWEEN Operator
• Using the IN Operator
• Pattern Matching Using the LIKE Operator
• Combining Wildcard Symbols
• Using NULL Conditions
• Defining Conditions Using Logical Operators
• Using the AND Operator
• Using the OR Operator
• Using the NOT Operator
• Rules of Precedence for Operators in an Expression
• Rules of Precedence
• Sorting Rows Using the ORDER BY Clause
• Using the ORDER BY Clause
• Sorting
• SQL Row Limiting Clause in a Query
• SQL Row Limiting Clause
• Using SQL Row Limiting Clause in a Query in Oracle
• SQL Row Limiting Clause: Example in Oracle
• Substitution Variables in Oracle
• Using the Single-Ampersand Substitution Variable
• Character and Date Values with Substitution Variables
• Specifying Column Names, Expressions, and Text
• Using the Double-Ampersand Substitution Variable
• Using the Ampersand Substitution Variable in SQL*Plus
• Assigning Values to Variables
• Using the DEFINE Command in Oracle
• Using the VERIFY Command in Oracle
• Summary

Module 4: Using Single-Row Functions to Customize Output

• Course Roadmap
• Objectives
• HR Application Scenario
• Single-Row SQL Functions
• SQL Functions
• Two Types of SQL Functions
• Single-Row Functions
• Character Functions
• Case-Conversion Functions
• Using Case-Conversion Functions in WHERE Clauses in Oracle
• Character-Manipulation Functions
• Using Character-Manipulation Functions
• Nesting Functions
• Nesting Functions: Example
• Number Functions
• Numeric Functions
• Using the ROUND Function
• Using the TRUNC Function in Oracle
• Using the MOD Function
• Working with Dates in Oracle Databases
• RR Date Format in Oracle
• Using the SYSDATE Function in Oracle
• Using the CURRENT_DATE and CURRENT_TIMESTAMP Functions in Oracle
• Arithmetic with Dates in Oracle
• Using Arithmetic Operators with Dates in Oracle
• Date Functions
• Date-Manipulation Functions in Oracle
• Using Date Functions in Oracle
• Using ROUND and TRUNC Functions with Dates in Oracle
• Summary

Module 5: Using Conversion Functions and Conditional Expressions

• Course Roadmap
• Objectives
• Implicit and Explicit Data Type Conversion
• Conversion Functions
• Implicit Data Type Conversion of Strings to Numbers
• Implicit Data Type Conversion of Numbers to Strings
• TO_CHAR, TO_DATE, and TO_NUMBER Functions in Oracle
• Using the TO_CHAR Function with Dates
• Elements of the Date Format Model
• Using the TO_CHAR Function with Dates
• Using the TO_CHAR Function with Numbers
• Using the TO_NUMBER and TO_DATE Functions
• Using TO_CHAR and TO_DATE Functions with the RR Date Format
• General Functions
• NVL Function
• Using the NVL Function in Oracle
• Using the NVL2 Function in Oracle
• Using the NULLIF Function
• Using the COALESCE Function
• Conditional Expressions
• CASE Expression
• Using the CASE Expression
• Searched CASE Expression
• DECODE Function in Oracle
• Using the DECODE Function
• JSON Functions
• JSON_QUERY Function
• JSON_TABLE Function
• JSON_VALUE Function
• Summary

Module 6: Reporting Aggregated Data Using the Group Functions

• Course Roadmap
• Objectives
• Group Functions
• Types of Group Functions
• Group Functions: Syntax
• Using the AVG and SUM Functions
• Using the MIN and MAX Functions
• Using the COUNT Function
• Using the DISTINCT Keyword
• Group Functions and Null Values in Oracle
• Grouping Rows
• Creating Groups of Data
• Creating Groups of Data: GROUP BY Clause Syntax
• Using the GROUP BY Clause
• Using the GROUP BY Column Position
• Using the GROUP BY Clause
• Grouping by More Than One Column
• Using the GROUP BY Clause on Multiple Columns
• Illegal Queries Using Group Functions
• Illegal Queries Using Group Functions in a WHERE Clause
• Restricting Group Results
• Restricting Group Results with the HAVING Clause
• Using the HAVING Clause
• Nesting Group Functions
• Nesting Group Functions in Oracle
• Summary

Module 7: Displaying Data from Multiple Tables Using Joins

• Course Roadmap
• Objectives
• Types of Joins and Their Syntax
• Why Join?
• Obtaining Data from Multiple Tables
• Types of Joins
• Joining Tables Using SQL Syntax
• Natural Join
• Creating Natural Joins
• Retrieving Records with Natural Joins
• Join with the USING Clause
• Creating Joins with the USING Clause
• Joining Column Names
• Retrieving Records with the USING Clause
• Qualifying Ambiguous Column Names
• Using Table Aliases with the USING Clause in Oracle
• Join with the ON Clause
• Creating Joins with the ON Clause
• Retrieving Records with the ON Clause
• Creating Three-Way Joins
• Applying Additional Conditions to a Join
• Self-Join
• Joining a Table to Itself
• Self-Joins Using the ON Clause
• Nonequijoins
• Retrieving Records with Nonequijoins
• Outer Join
• Returning Records with No Direct Match Using OUTER Joins
• INNER Versus OUTER Joins
• LEFT OUTER JOIN
• RIGHT OUTER JOIN
• FULL OUTER JOIN in Oracle
• Cartesian Product
• Cartesian Products
• Generating a Cartesian Product
• Creating Cross Joins
• Summary

Module 8: Using Subqueries to Solve Queries

• Course Roadmap
• Objectives
• Subquery: Types, Syntax, and Guidelines
• Using a Subquery to Solve a Problem
• Subquery Syntax
• Using a Subquery
• Rules and Guidelines for Using Subqueries
• Types of Subqueries
• Single-Row Subqueries
• Executing Single-Row Subqueries
• Using Group Functions in a Subquery
• HAVING Clause with Subqueries
• What is wrong with this statement?
• No Rows Returned by the Inner Query
• Multiple-Row Subqueries
• Using the ANY Operator in Multiple-Row Subqueries
• Using the ALL Operator in Multiple-Row Subqueries
• Multiple-Column Subqueries
• Multiple-Column Subquery: Example
• Null Values in a Subquery
• Summary

Module 9: Using Set Operators

• Course Roadmap
• Objectives
• Set Operators: Types and Guidelines
• Set Operators
• Set Operator Rules
• Oracle Server and Set Operators
• Tables Used in This Lesson
• UNION and UNION ALL Operators
• UNION Operator
• Using the UNION Operator
• UNION ALL Operator
• Using the UNION ALL Operator
• INTERSECT Operator
• Using the INTERSECT Operator
• MINUS Operator
• Using the MINUS Operator
• Matching SELECT Statements
• Matching SELECT Statements in Oracle
• Matching the SELECT Statement: Example in Oracle
• Using the ORDER BY Clause in Set Operations
• Using the ORDER BY Clause in Set Operations in Oracle
• Using the ORDER BY Clause in Set Operations in Oracle: Example
• Summary

Module 10: Managing Tables Using DML Statements

• Course Roadmap
• Objectives
• HR Application Scenario
• Adding New Rows in a Table
• Data Manipulation Language
• Adding a New Row to a Table
• INSERT Statement Syntax
• Inserting New Rows
• INSERT Statement Syntax
• Inserting Rows with Null Values
• Inserting Special Values
• Inserting Specific Date and Time Values
• Creating a Script
• Copying Rows from Another Table
• Changing Data in a Table
• UPDATE Statement Syntax
• Updating Rows in a Table
• Updating Two Columns with a Subquery
• Updating Rows Based on Another Table
• Enhancement in 23c
• RETURNING Clause in 23c: Example
• Removing Rows from a Table
• Removing a Row from a Table
• DELETE Statement
• Deleting Rows from a Table
• Deleting Rows Based on Another Table
• Enhancements to UPDATE and DELETE Statements in 23c
• UPDATE: Example
• DELETE: Example
• TRUNCATE Statement
• Database Transaction Control
• Database Transactions
• Database Transactions: Start and End
• Advantages of the COMMIT and ROLLBACK Statements
• Explicit Transaction Control Statements
• Rolling Back Changes to a Marker
• Implicit Transaction Processing
• State of Data Before COMMIT or ROLLBACK
• State of Data After COMMIT
• State of Data After ROLLBACK
• State of Data After ROLLBACK: Example
• Statement-Level Rollback
• Read Consistency
• Implementing Read Consistency
• Manual Data Locking
• FOR UPDATE Clause in a SELECT Statement
• FOR UPDATE Clause: Examples
• LOCK TABLE Statement
• Summary

Module 11: Introduction to Data Definition Language

• Course Roadmap
• Objectives
• HR Application Scenario
• Database Objects
• Naming Rules for Tables and Columns
• CREATE TABLE Statement
• Creating Tables
• Staging Tables
• Creating Staging Tables: Methods
• Using Staging Tables: Example
• Data Types
• Datetime Data Types
• SQL*Plus Support for Boolean Data Type in 23c
• Creating a Table with Boolean Type and Inserting Data: Example
• Querying the Table Containing BOOLEAN Values in SQL Developer: Example
• Default Output in SQL*Plus: Example
• Using the COLUMN Command to Customize Output: Example
• DEFAULT Option
• 23c Enhancement to SQL Statements
• UPDATE Option to the Existing DEFAULT ON NULL Clause: Example
• Updating demo_t and Setting ename to the ‘missing name’ Value: Example
• DEFAULT ON NULL with IDENTITY Columns and Update Column c2 to <sequence>.nextval: Example
• Constraints: Overview
• Including Constraints
• Constraint Guidelines
• Defining Constraints
• Defining Constraints: Example
• NOT NULL Constraint
• UNIQUE Constraint
• PRIMARY KEY Constraint
• FOREIGN KEY Constraint
• FOREIGN KEY Constraint: Keywords
• CHECK Constraint
• CREATE TABLE: Example
• IF (NOT) EXISTS Enhancement
• IF NOT EXISTS Clause: Example
• Violating Constraints
• Creating a Table by Using a Subquery
• ALTER TABLE Statement
• Adding a Column
• Modifying a Column
• Dropping a Column
• SET UNUSED Option
• Read-Only Tables
• DROP TABLE Statement
• Dropping a Table
• Dropping the DEMO Table If It Exists: Example
• Summary

Module 12: Introduction to Data Dictionary Views

• Introduction to Data Dictionary
• Why data dictionary?
• Data Dictionary
• Data Dictionary Structure
• How to Use Dictionary Views
• USER_OBJECTS and ALL_OBJECTS Views
• USER_OBJECTS View
• Querying the Dictionary Views
• Table Information
• Column Information
• Constraint Information
• USER_CONSTRAINTS: Example
• Querying USER_CONS_COLUMNS
• Adding a Comment to a Table and Querying the Dictionary Views
• Adding Comments to a Table
• Summary

Module 13: Creating Sequences, Synonyms, and Indexes

• Create, Maintain, and Use Sequences
• E-Commerce Scenario
• Database Objects
• Referencing Another User’s Tables
• Sequences
• CREATE SEQUENCE Statement: Syntax
• Creating a Sequence
• NEXTVAL and CURRVAL Pseudocolumns
• Using a Sequence
• SQL Column Defaulting Using a Sequence
• Caching Sequence Values
• Modifying a Sequence
• Guidelines for Modifying a Sequence
• Sequence Information
• Create Private and Public Synonyms
• Synonyms
• Creating a Synonym for an Object
• Creating and Removing Synonyms
• Synonym Information
• Create and Maintain Indexes
• Indexes
• How are indexes created?
• Creating an Index
• CREATE INDEX with the CREATE TABLE Statement
• Function-Based Indexes
• Creating Multiple Indexes on the Same Set of Columns
• Creating Multiple Indexes on the Same Set of Columns: Example
• Index Information
• USER_INDEXES: Examples
• Querying USER_IND_COLUMNS
• Removing an Index
• Summary

Module 14: Creating Views

• Overview of Views
• Why views?
• Database Objects
• What is a view?
• Advantages of Views
• Simple Views and Complex Views
• Creating, Modifying, and Retrieving Data from a View
• Creating a View
• Retrieving Data from a View
• Modifying a View
• Creating a Complex View
• View Information
• Data Manipulation Language (DML) Operations on a View
• Rules for Performing DML Operations on a View
• Rules for Performing Modify Operations on a View
• Rules for Performing Insert Operations Through a View
• Using the WITH CHECK OPTION Clause
• Denying DML Operations
• Dropping a view
• Removing a view
• Summary

Module 15: Managing Schema Objects

• Manage Constraints
• Adding a Constraint: Syntax
• Adding a Constraint
• Dropping a Constraint
• Dropping a Constraint: ONLINE
• ON DELETE Clause
• Cascading Constraints
• Cascading Const