Oracle Database 19c: SQL Workshop

Introduction

• Objectives
• Lesson Agenda
• Course Objectives
• Icons Used in This Course
• Course Roadmap
• Appendixes and Practices Used in the Course
• Lesson Agenda
• Oracle Database 19c: Focus Areas
• Oracle Database 19c
• MySQL: A Modern Database for the Digital Age
• High Scalability with MySQL
• MySQL-Supported Operating Systems
• MySQL Enterprise Edition
• Why MySQL Enterprise Edition?
• Oracle Premier Support for MySQL
• MySQL and Oracle Integration
• Lesson Agenda
• 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
• Lesson Agenda
• Human Resources (HR) Application
• Tables Used in This Course
• Tables Used in the Course
• Lesson Agenda
• 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
• Development Environments for SQL in MySQL
• Lesson Agenda
• Oracle Database Documentation
• Additional Resources for Oracle
• Oracle University: Oracle SQL Training
• Oracle SQL Certification
• MySQL Websites
• MySQL Community Resources
• Oracle University: MySQL Training
• MySQL Certification
• Summary
• Practice 1: Overview

Retrieving Data Using the SQL SELECT Statement

• Course Roadmap
• Objectives
• Lesson Agenda
• 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
• Executing SQL Statements in MySQL Workbench
• Executing SQL Statements in mysql Command-line Client
• Selecting Specific Columns
• Selecting from dual with Oracle Database
• Selecting Constant Expressions in MySQL
• Lesson Agenda
• Arithmetic Expressions
• Using Arithmetic Operators
• Operator Precedence
• Defining a Null Value
• Null Values in Arithmetic Expressions
• Lesson Agenda
• Defining a Column Alias
• Using Column Aliases
• Lesson Agenda
• Concatenation Operator in Oracle
• Concatenation Function in MySQL – CONCAT()
• Literal Character Strings
• Using Literal Character Strings in Oracle
• Using Literal Character Strings in MySQL
• Alternative Quote (q) Operator in Oracle
• Including a Single Quotation Mark in a String with an Escape Sequence
• in MySQL
• Duplicate Rows
• Lesson Agenda
• Displaying Table Structure by Using the DESCRIBE Command
• Displaying Table Structure by Using Oracle SQL Developer
• Displaying Table Structure by Using MySQL Workbench
• Summary
• Practice 2: Overview

Restricting and Sorting Data

• Course Roadmap
• Objectives
• Lesson Agenda
• 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
• Lesson Agenda
• Rules of Precedence
• Lesson Agenda
• Using the ORDER BY Clause
• Sorting
• Lesson Agenda
• SQL Row Limiting Clause
• Using SQL Row Limiting Clause in a Query in Oracle
• SQL Row Limiting Clause: Example in Oracle
• Using SQL Row Limiting Clause in a Query in MySQL
• SQL Row Limiting Clause: Example in MySQL
• Lesson Agenda
• 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
• Lesson Agenda
• Using the DEFINE Command in Oracle
• Using the VERIFY Command in Oracle
• Using the SET Statement in MySQL
• Summary
• Practice 3: Overview

Using Single-Row Functions to Customize Output

• Course Roadmap
• Objectives
• HR Application Scenario
• Lesson Agenda
• SQL Functions
• Two Types of SQL Functions
• Single-Row Functions
• Lesson Agenda
• Character Functions
• Case-Conversion Functions
• Using Case-Conversion Functions in WHERE Clauses in Oracle
• Case-Insensitive Queries in MySQL
• Character-Manipulation Functions
• Using Character-Manipulation Functions
• Lesson Agenda
• Nesting Functions
• Nesting Functions: Example
• Lesson Agenda
• Numeric Functions
• Using the ROUND Function
• Using the TRUNC Function in Oracle
• Using the TRUNCATE Function in MySQL
• Using the MOD Function
• Lesson Agenda
• 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
• Lesson Agenda
• Working with Dates in MySQL Databases
• Displaying the Current Date in MySQL
• Lesson Agenda
• Date-Manipulation Functions in Oracle
• Using Date Functions in Oracle
• Using ROUND and TRUNC Functions with Dates in Oracle
• Date-Manipulation Functions in MySQL
• Using Date Functions in MySQL
• Extracting the Month or Year Portion of Dates in MySQL
• Summary
• Practice 4: Overview

Using Conversion Functions and Conditional Expressions

• Course Roadmap
• Objectives
• Lesson Agenda
• Conversion Functions
• Implicit Data Type Conversion of Strings to Numbers
• Implicit Data Type Conversion of Numbers to Strings
• Lesson Agenda
• 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
• Lesson Agenda
• Using the CAST() function in Oracle
• Explicit Data Type Conversion of Strings to Numbers in MySQL
• Explicit Data Type Conversion of Numbers to Strings in MySQL
• Lesson Agenda
• General Functions
• NVL Function (Oracle) and IFNULL() Function (MySQL)
• Using the NVL Function in Oracle
• Using the NVL2 Function in Oracle
• Using the IFNULL Function in MySQL
• Using the NULLIF Function
• Using the COALESCE Function
• Lesson Agenda
• Conditional Expressions
• CASE Expression
• Using the CASE Expression
• Searched CASE Expression
• DECODE Function in Oracle
• Using the DECODE Function
• Lesson Agenda
• JSON_QUERY Function
• JSON_TABLE Function
• JSON_VALUE Function
• Summary
• Practice 5: Overview

Reporting Aggregated Data Using the Group Functions

• Course Roadmap
• Objectives
• Lesson Agenda
• 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
• Group Functions and Null Values in MySQL
• Lesson Agenda
• Creating Groups of Data
• Creating Groups of Data: GROUP BY Clause Syntax
• 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
• Lesson Agenda
• Nesting Group Functions in Oracle
• Summary
• Practice 6: Overview

Displaying Data from Multiple Tables Using Joins

• Course Roadmap
• Objectives
• Lesson Agenda
• Why Join?
• Obtaining Data from Multiple Tables
• Types of Joins
• Joining Tables Using SQL Syntax
• Lesson Agenda
• Creating Natural Joins
• Retrieving Records with Natural Joins
• 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
• Creating Joins with the ON Clause
• Retrieving Records with the ON Clause
• Creating Three-Way Joins
• Applying Additional Conditions to a Join
• Lesson Agenda
• Joining a Table to Itself
• Self-Joins Using the ON Clause
• Lesson Agenda
• Nonequijoins
• Retrieving Records with Nonequijoins
• Lesson Agenda
• Returning Records with No Direct Match Using OUTER Joins
• INNER Versus OUTER Joins
• LEFT OUTER JOIN
• RIGHT OUTER JOIN
• FULL OUTER JOIN in Oracle
• Lesson Agenda
• Cartesian Products
• Generating a Cartesian Product
• Creating Cross Joins
• Summary
• Practice 7: Overview

Using Subqueries to Solve Queries

• Course Roadmap
• Objectives
• Lesson Agenda
• Using a Subquery to Solve a Problem
• Subquery Syntax
• Using a Subquery
• Rules and Guidelines for Using Subqueries
• Types of Subqueries
• Lesson Agenda
• 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
• Lesson Agenda
• 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
• Lesson Agenda
• Null Values in a Subquery
• Summary
• Practice 8: Overview

Using Set Operators

• Course Roadmap
• Objectives
• Lesson Agenda
• Set Operators
• Set Operator Rules
• Oracle Server and Set Operators
• Lesson Agenda
• Tables Used in This Lesson
• Lesson Agenda
• UNION Operator
• Using the UNION Operator
• UNION ALL Operator
• Using the UNION ALL Operator
• Lesson Agenda
• INTERSECT Operator
• Using the INTERSECT Operator
• Lesson Agenda
• MINUS Operator
• Using the MINUS Operator
• Lesson Agenda
• Matching SELECT Statements in Oracle
• Matching the SELECT Statement: Example in Oracle
• Matching SELECT Statements in MySQL
• Matching the SELECT Statement: Example in MySQL
• Lesson Agenda
• Using the ORDER BY Clause in Set Operations in Oracle
• Using the ORDER BY Clause in Set Operations in Oracle: Example
• Using the ORDER BY Clause with UNION in MySQL
• Using the ORDER BY Clause with UNION: Example in MySQL
• Summary
• Practice 9: Overview
• 10A Managing Tables Using DML Statements in Oracle
• Course Roadmap 10A-2
• Objectives 10A-3
• HR Application Scenario 10A-4
• Lesson Agenda 10A-5
• Data Manipulation Language 10A-6
• Adding a New Row to a Table 10A-7
• INSERT Statement Syntax 10A-8
• Inserting New Rows 10A-9
• Inserting Rows with Null Values 10A-10
• Inserting Special Values 10A-11
• Inserting Specific Date and Time Values 10A-12
• Creating a Script 10A-13
• Copying Rows from Another Table 10A-14
• Lesson Agenda 10A-15
• Changing Data in a Table 10A-16
• UPDATE Statement Syntax 10A-17
• Updating Rows in a Table 10A-18
• Updating Two Columns with a Subquery 10A-19
• Updating Rows Based on Another Table 10A-20
• Lesson Agenda 10A-21
• Removing a Row from a Table 10A-22
• DELETE Statement 10A-23
• Deleting Rows from a Table 10A-24
• Deleting Rows Based on Another Table 10A-25
• TRUNCATE Statement 10A-26
• Lesson Agenda 10A-27
• Database Transactions 10A-28
• Database Transactions: Start and End 10A-29
• Advantages of the COMMIT and ROLLBACK Statements 10A-30
• Explicit Transaction Control Statements 10A-31
• Rolling Back Changes to a Marker 10A-32
• Implicit Transaction Processing 10A-33
• State of Data Before COMMIT or ROLLBACK 10A-34
• State of Data After COMMIT 10A-35
• Committing Data 10A-36
• State of Data After ROLLBACK 10A-37
• State of Data After ROLLBACK: Example 10A-38
• Statement-Level Rollback 10A-39
• Lesson Agenda 10A-40
• Read Consistency 10A-41
• Implementing Read Consistency 10A-42
• Lesson Agenda 10A-43
• FOR UPDATE Clause in a SELECT Statement 10A-44
• FOR UPDATE Clause: Examples 10A-45
• LOCK TABLE Statement 10A-46
• Summary 10A-47
• Practice 10a: Overview 10A-48
• 10B Managing Tables Using DML Statements in MySQL
• Course Roadmap 10B-2
• Objectives 10B-3
• HR Application Scenario 10B-4
• Lesson Agenda 10B-5
• Data Manipulation Language 10B-6
• Adding a New Row to a Table 10B-7
• INSERT Statement Syntax 10B-8
• Inserting New Rows: Listing Column Names 10B-9
• Inserting New Rows: Omitting Column Names 10B-10
• Inserting Rows with Null Values 10B-11
• Inserting Special Values in MySQL 10B-12
• Inserting Specific Date and Time Values in MySQL 10B-13
• Inserting and Reformatting Specific Date and Time Values in MySQL 10B-14
• Copying Rows from Another Table 10B-15
• Lesson Agenda 10B-16
• Changing Data in a Table 10B-17
• UPDATE Statement Syntax 10B-18
• Updating Rows in a Table 10B-19
• Updating Rows Based on Another Table 10B-20
• Quiz 10B-21
• Lesson Agenda 10B-22
• Removing a Row from a Table 10B-23
• DELETE Statement 10B-24
• Deleting Rows from a Table 10B-25
• Deleting Rows Based on Another Table 10B-26
• TRUNCATE Statement 10B-27
• Lesson Agenda 10B-28
• Multiple-Statement Transactions 10B-29
• Transaction Diagram 10B-30
• AUTOCOMMIT and Transaction Control Statements 10B-31
• Committing Data in a Transaction 10B-32
• Rolling Back Changes 10B-33
• Rolling Back Changes to a Marker 10B-34
• Lesson Agenda 10B-35
• Consistent Reads 10B-36
• Lesson Agenda 10B-37
• FOR UPDATE Clause in a SELECT Statement 10B-38
• FOR UPDATE Clause: Examples 10B-39
• Summary 10B-40
• 11A Introduction to Data Definition Language in Oracle
• Course Roadmap 11A-2
• Objectives 11A-3
• HR Application Scenario 11A-4
• Lesson Agenda 11A-5
• Database Objects 11A-6
• Naming Rules for Tables and Columns 11A-7
• Lesson Agenda 11A-8
• CREATE TABLE Statement 11A-9
• Creating Tables 11A-10
• Lesson Agenda 11A-11
• Data Types 11A-12
• Datetime Data Types 11A-14
• DEFAULT Option 11A-15
• Lesson Agenda 11A-16
• Including Constraints 11A-17
• Constraint Guidelines 11A-18
• Defining Constraints 11A-19
• Defining Constraints: Example 11A-20
• NOT NULL Constraint 11A-21
• UNIQUE Constraint 11A-22
• PRIMARY KEY Constraint 11A-24
• FOREIGN KEY Constraint 11A-25
• FOREIGN KEY Constraint: Keywords 11A-27
• CHECK Constraint 11A-28
• CREATE TABLE: Example 11A-29
• Violating Constraints 11A-30
• Lesson Agenda 11A-32
• Creating a Table Using a Subquery 11A-33
• Lesson Agenda 11A-35
• ALTER TABLE Statement 11A-36
• Adding a Column 11A-38
• Modifying a Column 11A-39
• Dropping a Column 11A-40
• SET UNUSED Option 11A-41
• Read-Only Tables 11A-43
• Lesson Agenda 11A-44
• Dropping a Table 11A-45
• Summary 11A-46
• Practice 11a: Overview 11A-47
• 11B Introduction to Data Definition Language in MySQL
• Course Roadmap 11B-2
• Objectives 11B-3
• HR Application Scenario 11B-4
• Lesson Agenda 11B-5
• Creating a Database: Syntax 11B-6
• MySQL Naming Conventions 11B-7
• Lesson Agenda 11B-8
• CREATE TABLE Statement 11B-9
• Lesson Agenda 11B-10
• Data Types: Overview 11B-11
• Numeric Data Types 11B-12
• Date and Time Data Types 11B-13
• String Data Types 11B-14
• Lesson Agenda 11B-15
• Indexes, Keys, and Constraints 11B-16
• Table Indexes 11B-17
• Primary Keys 11B-18
• Unique Key Constraints 11B-19
• Foreign Key Constraints 11B-20
• Foreign Key Constraint: Example Tables 11B-21
• FOREIGN KEY Constraint: Example Statement 11B-22
• FOREIGN KEY Constraint: Referential Actions 11B-23
• Secondary Indexes 11B-24
• Lesson Agenda 11B-25
• Column Options 11B-26
• Lesson Agenda 11B-27
• Creating a Table Using a Subquery 11B-28
• Creating a Table Using a Subquery: Example 11B-29
• Lesson Agenda 11B-30
• ALTER TABLE Statement 11B-31
• ALTER TABLE Statement: Add, Modify, or Drop Columns 11B-32
• Adding a Column 11B-33
• Modifying a Column 11B-34
• Dropping a Column 11B-35
• ALTER TABLE Statement: Add an Index or Constraint 11B-36
• ALTER TABLE to Add a Constraint or Index: Example 11B-37
• Creating Indexes by Using the CREATE INDEX Statement 11B-38
• Viewing Index Definitions by Using the SHOW INDEX Statement 11B-39
• Showing How a Table Was Created with the SHOW CREATE TABLE
• Statement 11B-40
• Lesson Agenda 11B-41
• Dropping a Table 11B-42
• Summary 11B-43

Introduction to Data Dictionary Views

• Course Roadmap
• Objectives
• Lesson Agenda
• Why Data Dictionary?
• Data Dictionary
• Data Dictionary Structure
• How to Use Dictionary Views
• USER_OBJECTS and ALL_OBJECTS Views
• USER_OBJECTS View
• Lesson Agenda
• Table Information
• Column Information
• Constraint Information
• USER_CONSTRAINTS: Example
• Querying USER_CONS_COLUMNS
• Lesson Agenda
• Adding Comments to a Table
• Summary
• Practice 12: Overview

Creating Sequences, Synonyms, and Indexes

• Course Roadmap
• Objectives
• Lesson Agenda
• 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
• Lesson Agenda
• Synonyms
• Creating a Synonym for an Object
• Creating and Removing Synonyms
• Synonym Information
• Lesson Agenda
• 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
• Practice 13: Overview

Creating Views

• Course Roadmap
• Objectives
• Lesson Agenda
• Why Views?
• Database Objects
• What Is a View?
• Advantages of Views
• Simple Views and Complex Views
• Lesson Agenda
• Creating a View
• Retrieving Data from a View
• Modifying a View
• Creating a Complex View
• View Information
• Lesson Agenda
• 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
• Lesson Agenda
• Removing a View
• Summary
• Practice 14: Overview

Managing Schema Objects

• Course Roadmap
• Objectives
• Lesson Agenda
• Adding a Constraint Syntax
• Adding a Constraint
• Dropping a Constraint
• Dropping a Constraint ONLINE
• ON DELETE Clause
• Cascading Constraints
• Renaming Table Columns and Constraints
• Disabling Constraints
• Enabling Constraints
• Constraint States
• Deferring Constraints
• Difference Between INITIALLY DEFERRED and INITIALLY IMMEDIATE
• DROP TABLE … PURGE
• Lesson Agenda
• Using Temporary Tables
• Temporary Table
• Temporary Table Characteristics
• Creating a Global Temporary Table
• Creating a Private Temporary Table
• Lesson Agenda
• External Tables
• Creating a Directory for the External Table
• Creating an External Table
• Creating an External Table by Using ORACLE_LOADER
• Querying External Tables
• Creating an External Table by Using ORACLE_DATAPUMP: Example
• Summary
• Practice 15: Overview

Retrieving Data by Using Subqueries

• Course Roadmap
• Objectives
• Lesson Agenda
• Retrieving Data by Using a Subquery as a Source
• Lesson Agenda
• Multiple-Column Subqueries
• Column Comparisons
• Pairwise Comparison Subquery
• Nonpairwise Comparison Subquery
• Lesson Agenda
• Scalar Subquery Expressions
• Scalar Subqueries: Examples
• Lesson Agenda
• Correlated Subqueries
• Using Correlated Subqueries: Example 1
• Using Correlated Subqueries: Example 2
• Lesson Agenda
• Using the EXISTS Operator
• Find All Departments That Do Not Have Any Employees
• Lesson Agenda
• WITH Clause
• WITH Clause: Example
• Recursive WITH Clause
• Recursive WITH Clause: Example
• Summary
• Practice 16: Overview

Manipulating Data by Using Subqueries

• Course Roadmap
• Objectives
• Lesson Agenda
• Using Subqueries to Manipulate Data
• Lesson Agenda
• Inserting by Using a Subquery as a Target
• Lesson Agenda
• Using the WITH CHECK OPTION Keyword on DML Statements
• Lesson Agenda
• Correlated UPDATE
• Using Correlated UPDATE
• Correlated DELETE
• Using Correlated DELETE
• Summary
• Practice 17: Overview

Controlling User Access

• Course Roadmap
• Objectives
• Lesson Agenda
• Controlling User Access
• Privileges
• System Privileges
• Creating Users
• User System Privileges
• Granting System Privileges
• Lesson Agenda
• What Is a Role?
• Creating and Granting Privileges to a Role
• Changing Your Password
• Lesson Agenda
• Object Privileges
• Granting Object Privileges
• Passing On Your Privileges
• Confirming Granted Privileges
• Lesson Agenda
• Revoking Object Privileges
• Summary
• Practice 18: Overview

Manipulating Data Using Advanced Queries

• Course Roadmap
• Objectives
• Lesson Agenda
• Explicit Default Feature: Overview
• Using Explicit Default Values
• Lesson Agenda
• E-Commerce Scenario
• Multitable INSERT Statements: Overview
• Types of Multitable INSERT Statements
• Multitable INSERT Statements
• Unconditional INSERT ALL
• Conditional INSERT ALL: Example
• Conditional INSERT ALL
• Conditional INSERT FIRST: Example
• Conditional INSERT FIRST
• Pivoting INSERT
• Lesson Agenda
• MERGE Statement
• MERGE Statement Syntax
• Merging Rows: Example
• Lesson Agenda
• FLASHBACK TABLE Statement
• Using the FLASHBACK TABLE Statement
• Lesson Agenda
• Tracking Changes in Data
• Flashback Query: Example
• Flashback Version Query: Example
• VERSIONS BETWEEN Clause
• Summary
• Practice 19: Overview

Managing Data in Different Time Zones

• Course Roadmap
• Objectives
• Lesson Agenda
• E-Commerce Scenario
• Time Zones
• TIME_ZONE Session Parameter
• CURRENT_DATE, CURRENT_TIMESTAMP, and LOCALTIMESTAMP
• Comparing Date and Time in a Session’s Time Zone
• DBTIMEZONE and SESSIONTIMEZONE
• TIMESTAMP Data Types
• TIMESTAMP Fields
• Difference Between DATE and TIMESTAMP
• Comparing TIMESTAMP Data Types
• Lesson Agenda
• INTERVAL Data Types
• INTERVAL Fields
• INTERVAL YEAR TO MONTH: Example
• INTERVAL DAY TO SECOND Data Type: Example
• Lesson Agenda
• EXTRACT
• TZ_OFFSET
• FROM_TZ
• TO_TIMESTAMP
• TO_YMINTERVAL
• TO_DSINTERVAL
• Daylight Saving Time (DST)
• Summary
• Practice 20: Overview

Conclusion

• Course Goals
• Oracle University: Oracle SQL Training
• Oracle University: MySQL Training
• Oracle SQL References
• MySQL Websites
• Your Evaluation
• Thank You
• Q&A Session
• A Table Descriptions
• B Using SQL Developer
• Objectives B-2
• What Is Oracle SQL Developer? B-3
• Specifications of SQL Developer B-4
• SQL Developer 17.4.1 Interface B-5
• Creating a Database Connection B-7
• Browsing Database Objects B-10
• Displaying the Table Structure B-11
• Browsing Files B-12
• Creating a Schema Object B-13
• Creating a New Table: Example B-14
• Using the SQL Worksheet B-15
• Executing SQL Statements B-18
• Saving SQL Scripts B-19
• Executing Saved Script Files: Method 1 B-20
• Executing Saved Script Files: Method 2 B-21
• Formatting the SQL Code B-22
• Using Snippets B-23
• Using Snippets: Example B-24
• Using the Recycle Bin B-25
• Debugging Procedures and Functions B-26
• Database Reporting B-27
• Creating a User-Defined Report B-28
• External Tools B-29
• Setting Preferences B-30
• Data Modeler in SQL Developer B-31
• Summary B-32
• C Using SQL*Plus
• Objectives C-2
• SQL and SQL*Plus Interaction C-3
• SQL Statements Versus SQL*Plus Commands C-4
• SQL*Plus: Overview C-5
• Logging In to SQL*Plus C-6
• Displaying the Table Structure C-7
• SQL*Plus Editing Commands C-9
• Using LIST, n, and APPEND C-11
• Using the CHANGE Command C-12
• SQL*Plus File Commands C-13
• Using the SAVE and START Commands C-14
• SERVEROUTPUT Command C-15
• Using the SQL*Plus SPOOL Command C-16
• Using the AUTOTRACE Command C-17
• Summary C-18
• D Commonly Used SQL Commands
• Objectives D-2
• Basic SELECT Statement D-3
• SELECT Statement D-4
• WHERE Clause D-5
• ORDER BY Clause D-6
• GROUP BY Clause D-7
• Data Definition Language D-8
• CREATE TABLE Statement D-9
• ALTER TABLE Statement D-10
• DROP TABLE Statement D-11
• GRANT Statement D-12
• Privilege Types D-13
• REVOKE Statement D-14
• TRUNCATE TABLE Statement D-15
• Data Manipulation Language D-16
• INSERT Statement D-17
• UPDATE Statement Syntax D-18
• DELETE Statement D-19
• Transaction Control Statements D-20
• COMMIT Statement D-21
• ROLLBACK Statement D-22
• SAVEPOINT Statement D-23
• Joins D-24
• Types of Joins D-25
• Qualifying Ambiguous Column Names D-26
• Natural Join D-27
• Equijoins D-28
• Retrieving Records with Equijoins D-29
• Additional Search Conditions Using the AND No equijoin WHERE Operators D-30
• Retrieving Records with Nonequijoins D-31
• Retrieving Records by Using the USING Clause D-32
• Retrieving Records by Using the ON Clause D-33
• Left Outer Join D-34
• Right Outer Join D-35
• Full Outer Join D-36
• Self-Join: Example D-37
• Cross Join D-38
• Summary D-39
• E Generating Reports by Grouping Related Data
• Objectives E-2
• Review of Group Functions E-3
• Review of the GROUP BY Clause E-4
• Review of the HAVING Clause E-5
• GROUP BY with ROLLUP and CUBE Operators E-6
• ROLLUP Operator E-7
• ROLLUP Operator: Example E-8
• CUBE Operator E-9
• CUBE Operator: Example E-10
• GROUPING Function E-11
• GROUPING Function: Example E-12
• GROUPING SETS E-13
• GROUPING SETS: Example E-15
• Composite Columns E-17
• Composite Columns: Example E-19
• Concatenated Groupings E-21
• Concatenated Groupings: Example E-22
• Summary E-23
• F Hierarchical Retrieval
• Objectives F-2
• Sample Data from the EMPLOYEES Table F-3
• Natural Tree Structure F-4
• Hierarchical Queries F-5
• Walking the Tree F-6
• Walking the Tree: From the Bottom Up F-8
• Walking the Tree: From the Top Down F-9
• Ranking Rows with the LEVEL Pseudocolumn F-10
• Formatting Hierarchical Reports Using LEVEL and LPAD F-11
• Pruning Branches F-13
• Summary F-14
• G Writing Advanced Scripts
• Objectives G-2
• Using SQL to Generate SQL G-3
• Creating a Basic Script G-4
• Controlling the Environment G-5
• The Complete Picture G-6
• Dumping the of a Table to a File G-7
• Generating a Dynamic Predicate G-9
• Summary G-11
• H Oracle Database Architectural Components
• Objectives H-2
• Oracle Database Architecture: Overview H-3
• Oracle Database Server Structures H-4
• Connecting to the Database H-5
• Interacting with an Oracle Database H-6
• Oracle Memory Architecture H-8
• Process Architecture H-10
• Database Writer Process H-12
• Log Writer Process H-13
• Checkpoint Process H-14
• System Monitor Process H-15
• Process Monitor Process H-16
• Oracle Database Storage Architecture H-17
• Logical and Physical Database Structures H-19
• Processing a SQL Statement H-21
• Processing a Query H-22
• Shared Pool H-23
• Database Buffer Cache H-25
• Program Global Area H-26
• Processing a DML Statement H-27
• Redo Log Buffer H-29
• Rollback Segment H-30
• COMMIT Processing H-31
• Summary of the Oracle Database Architecture H-33
• Summary H-34
• I Regular Expression Support
• Objectives I-2
• What Are Regular Expressions? I-3
• Benefits of Using Regular Expressions I-4
• Using the Regular Expressions Functions and Conditions in SQL and PL/SQL I-5
• What Are Metacharacters? I-6
• Using Metacharacters with Regular Expressions I-7
• Regular Expressions Functions and Conditions: Syntax I-9
• Performing a Basic Search by Using the REGEXP_LIKE Condition I-10
• Replacing Patterns by Using the REGEXP_REPLACE Function I-11
• Finding Patterns by Using the REGEXP_INSTR Function I-12
• Extracting Substrings by Using the REGEXP_SUBSTR Function I-13
• Subexpressions I-14
• Using Subexpressions with Regular Expression Support I-15
• Why Access the nth Subexpression? I-16
• REGEXP_SUBSTR: Example I-17
• Using the REGEXP_COUNT Function I-18
• Regular Expressions and Check Constraints: Examples I-19
• Quiz I-20
• Summary I-21