# Oracle SQL Training

For pricing and availability

Contact: Tera-Tom Coffing

Email: Tom.Coffing@CoffingDW.com

Phone: 513 300-0341

## Description

You can expect three things from a Tom Coffing Oracle SQL class: hands-on, hands-on, and hands-on training. Students will begin each chapter covering the fundamentals and continue to step up to the most advanced techniques in a building block approach. Each student can create tables on an Oracle system, run thousands of queries, and join many tables combined with subqueries, derived tables, date functions and formats, and advanced analytics. Welcome to one of the best classes you will ever attend.

## Reference Guide

The Oracle SQL book by Tom Coffing and Leona Coffing is a 600-page masterpiece of SQL that will be the backbone of the class and provide an excellent reference guide for all your development on an Oracle system.

## Objectives

Whether you are new to SQL or already have enormous experience, Tom Coffing will enrich your knowledge immensely. One thing is for sure: you will be extremely confident and proficient at writing SQL on an Oracle system.

## Audience, prerequisites, and duration

This course is for everyone who has the opportunity to work on an Oracle system, from beginners to experts. There are never any prerequisites in a Tom Coffing classroom because Tom has mastered mixing new and experienced students within the same classroom. The course ranges from 2-5 days, depending on your needs, but the more days you choose, the more incredible the outcome will be, as we will customize the classes based on your needs.

## Topics

Order By Nulls First Null Last

Advanced ORDER BY Statements

Like

Rownum

Sample

Traditional Joins Vs. ANSI Joins

Left, Right, and Full Outer Joins

Date Functions

Rank

Row_Number

Moving Difference

Ntile

First_Value

Lead and Lag

Cume_Dist

Format Functions

Temporary Tables

Subqueries

Strings

Interrogating the Data

Views

Set Operators

Creating Tables

Data Manipulation Language (DML)

Statistical Aggregate Functions

Mathematical Functions

Oracle SQL Course Outline

Chapter 1 – Introduction to SQL

Introduction

Setting Your Default Database

SELECT * (All Columns) in a Table

SELECT Specific Columns in a Table

Commas in the Front or Back?

Place your Commas in front for better Debugging Capabilities

Sort the Data with the ORDER BY Keyword

Use a Column Name or Number in an ORDER BY Statement

Two Examples of ORDER BY using Different Techniques

Changing the ORDER BY to Descending Order

NULL Values Sort Last in Ascending Mode (Default)

Using the Nulls First Command

NULL Values Sort First in Descending Mode (DESC)

Using the Nulls Last Command

Major Sort vs. Minor Sort

Multiple Sort Keys using Names vs. Numbers

An Order By That Uses an Expression

Sorts are Alphabetical, NOT Logical

Using A Valued CASE Statement to Sort Logically

Using A Searched CASE Statement to Sort Logically

Quiz – Can you Add a Minor Sort?

Answer – Can you Add a Minor Sort?

Order By Decode

Quiz – Can you Add Two Minor Sorts Using Decode?

Answer – Can you Add Two Minor Sorts Using Decode?

How to ALIAS a Column Name

Using an Alias in the ORDER BY Clause

Using an Alias in the ORDER BY Clause with Decode

A Missing Comma Can Become an Alias by Mistake

Comments using Double Dashes are Single Line Comments

Comments for Multi-Lines

Comments for Multi-Lines As Double Dashes Per Line

Comments are a Great Technique for Finding SQL Errors

Chapter 2 – The WHERE Clause

The WHERE Clause limits Returning Rows

Using a Column ALIAS in the WHERE Clause Errors

Numbers Don’t Need Single Quotes

Not Equal

Searching for NULL Values Using Equality Returns Nothing

Use IS NULL or IS NOT NULL when dealing with NULLs

Use IS NOT NULL To Eliminate NULLs

Comparisons Against a Null Value

Using Greater Than Or Equal To (>=)

AND in the WHERE Clause

Troubleshooting AND

OR in the WHERE Clause

WHY OR must utilize the Column Name Each Time

Troubleshooting Character Data

Using Different Columns in an AND Statement

Quiz – How Many Rows will Return?

Answer to Quiz – How Many Rows will Return?

What is the Order of Precedence?

Using Parentheses to change the Order of Precedence

Using an IN List in place of OR

The IN List is an Excellent Technique

IN List vs. OR Brings the Same Resul

The IN List Can Use Character Data

Using a NOT IN List

Null Values in a NOT IN List Return No Rows

A Technique for Handling Nulls with a NOT IN List

Technique 2 for Handling Nulls with a NOT IN List

The BETWEEN Statement is Inclusive

The NOT BETWEEN Statement is also Inclusive

The BETWEEN Statement Works for Character Data

The Like Command Wildcards are Percent and Underscore

LIKE command Underscore is Wildcard for one Character

CASE Matters with the LIKE Command

Using LIKE for all Cases with Lower and Upper

Another Example of UPPER and LOWER

LIKE Command to Find Multiple Characters

LIKE Command to Find Either Character

Finding Anyone Whose First Name Ends in ‘y’

Finding Anyone Whose Last Name Ends in ‘y’

Escape Character in the LIKE Command changes Wildcards

Escape Characters Turn off Wildcards in the LIKE Command

Chapter 3 – Distinct, Group By, Rownum, and Sample

The Distinct Command

DISTINCT vs. GROUP BY

Quiz – How many rows come back from the Distinct?

Answer – How many rows come back from the Distinct?

Limiting Rows with ROWNUM

Simple Random Sample

Simple Random Sample With Seed

Chapter 4 – Aggregation

Quiz – Calculate the Answer Set in your Mind

Answer – Calculate the Answer Set in your Mind

Quiz 2 – Calculate the Answer Set in your Mind

Answer Quiz 2 – Calculate the Answer Set in your Mind

There are Five Aggregates

Quiz – How many Rows Return?

Answer – How many Rows Return?

Casting a Data Type

Troubleshooting Aggregates

GROUP BY Delivers One Row Per Group

Limiting Rows and Improving Performance with WHERE

WHERE Clause in Aggregation limits unneeded Calculations

Keyword HAVING tests Aggregates after they are Totaled

Keyword HAVING is like an Extra WHERE Clause for Totals

Chapter 5 – Joining Tables

Nexus Builds Your Join SQL Automatically

A Two-Table Join Using Traditional Syntax

Two-Table Join Using a Table Alias to Fully Qualify Columns

We Alias Tables To Fully Qualify Columns in Both Tables

You Fully Qualify All Columns For Clarity

A Join using ANSI Syntax

Each Syntax Has the Same Results and Performance

A Best Practice is to Fully Qualify All Columns

Quiz – Troubleshooting an Oracle Join

Answer – Troubleshooting an Oracle Join

Quiz – Can You Finish the Join Syntax?

Answer to Quiz – Can You Finish the Join Syntax?

Quiz – Can You Find the Error?

Answer to Quiz – Can You Find the Error?

Super Quiz – Can You Find the Difficult Error?

Answer to Super Quiz – Can You Find the Difficult Error?

Quiz – Which Rows from Both Tables Won’t Return?

Answer– Which Rows from Both Tables Won’t Return?

LEFT OUTER JOIN

LEFT OUTER Join Results

LEFT OUTER JOIN Using Oracle Syntax (+)

RIGHT OUTER JOIN

RIGHT OUTER Join Example and Results

RIGHT OUTER JOIN Using (+)

FULL OUTER JOIN

FULL OUTER Join Results

Quiz – Are the Tables Left or Right Tables?

Answer – Are the Tables Left or Right Tables?

Traditional INNER JOIN with Additional AND Clause

ANSI INNER JOIN with Additional AND Clause

ANSI INNER JOIN with an Additional WHERE Clause

OUTER JOIN with Additional WHERE Clause

Beware – OUTER JOIN with Additional AND Clause

The DREADED Product Join

The DREADED Product Join Results

Cartesian Product Join with Traditional Syntax

Cartesian Product Join with ANSI Syntax

The CROSS JOIN

The SELF JOIN

A SELF JOIN with ANSI Syntax

An Associative Table is a Bridge that Joins Two Tables

Quiz – Can you Write the Three-Table Join?

Answer to Quiz – Can you Write the Three-Table Join?

Quiz –Write the Three-Table Join Using ANSI Syntax?

Answer –Write the Three-Table Join to ANSI Syntax?

Quiz – Can you Place the ON Clauses at the End?

Answer – Can you Place the ON Clauses at the End?

The Five-Table Join – Logical Insurance Model

Quiz – Write a Five Table Join Using ANSI Syntax

Answer – Write a Five Table Join Using ANSI Syntax

Quiz – Write a Five Table Join Using Traditional Syntax

Answer – Write a Five Table Join Using Non-ANSI Syntax

Quiz – Re-Write this putting the ON clauses at the END

Answer – Re-Write this putting the ON clauses at the END

Chapter 6 – Date Functions

Migrate Any Database to Oracle and Vice Versa

Getting the System Date

CURRENT_DATE

CURRENT_DATE and CURRENT_TIMESTAMP

CURRENT_DATE and CURRENT_TIMESTAMP

Current_Timestamp and Local_Timestamp With Precision

Using FROM_TZ

Add or Subtract Days from a Date

The ADD_MONTHS Command

ADD_MONTHS to Add a Year to a Date

ADD_MONTHS to Add Five Years to a Date

A List of Formatting Dates Options

Formatting a Date With TO_CHAR

TO_DATE

Formatting Numbers

The TO_CHAR Command to Format Numbers

TO_CHAR to Format To Locale Currency

More TO_CHAR Options for Formatting Numbers

The EXTRACT Command

EXTRACT from DATES and TIME

Implied Extract of Day, Month, and Year using TO_CHAR

The ROUND Command

Another ROUND Example

Using CASE and Extract to Reformat Dates

LAST_DAY

NEXT_DAY

MONTHS_BETWEEN

The Trunc Command

Adding Days and Minutes

How To Get the Difference in Hours

Using Intervals

How a Simple Interval Handles Leap Year

Advanced Intervals You Should Know

Chapter 7 – Temporary Tables

Nexus Joins Oracle Tables with Excel Worksheets

CREATING A Derived Table

Derived Query Examples with Two Different Techniques

Most Derived Tables Are Used To Join To Other Tables

The Three Components of a Derived Table

Visualize This Derived Table

Quiz – Derived Table Challenge

Answer to Quiz – Derived Table Challenge

An Example of Two Derived Tables Using WITH

WITH RECURSIVE Derived Table Hierarchy

Recursive Derived Table Query

Recursive Derived Table Definition

Recursive Derived Table Seeding

Recursive Derived Table Looping

Nexus Joins Oracle Tables with Excel Worksheets

CREATING A Derived Table

Derived Query Examples with Two Different Techniques

Most Derived Tables Are Used To Join To Other Tables

The Three Components of a Derived Table

Visualize This Derived Table

Quiz – Derived Table Challenge

Answer to Quiz – Derived Table Challenge

An Example of Two Derived Tables Using WITH

WITH RECURSIVE Derived Table Hierarchy

Recursive Derived Table Query

Recursive Derived Table Definition

Recursive Derived Table Looping

Recursive Derived Table Looping in Slow Motion

Recursive Derived Table Looping Continued

Recursive Derived Table Looping Continued

Recursive Derived Table Ends the Looping

Recursive Derived Table Final SELECT

Recursive Results On Nexus in Dark Mode

Recursive Final Answer Set

Creating a Global Temporary Table

Global Temporary Table Definitions Persist

ON COMMIT DELETE ROWS Example

Creating and Populating a Global Temporary Table

Creating a Global Temporary Table Using a CTAS

Creating a Temporary Table From Another’s Space

Global Temp Tables That Populate Some Rows or Columns

Dropping a Global Temporary Table

Chapter 8 – Subqueries

An IN List the Cousin of a Subquery

An IN List Never has Duplicates – Just like a Subquery

An IN List Ignores Duplicates

The Infamous Subquery

The Three Steps of How a Basic Subquery Works

These are Equivalent Queries

The Answer Set from the Subquery

Quiz- Answer the Difficult Question

Answer to Quiz- Answer the Difficult Question

Should you use a Subquery or a Join?

Quiz – Write the Subquery

Answer to Quiz- Write the Subquery

Quiz – Write the More Difficult Subquery

Answer to Quiz – Write the More Difficult Subquery

Quiz – Write the Extreme Subquery

Answer To Quiz – Write the Extreme Subquery

Quiz – Write the Subquery with an Aggregate

Answer to Quiz- Write the Subquery with an Aggregate

Quiz- Write the Correlated Subquery

Answer to Quiz- Write the Correlated Subquery

The Basics of a Correlated Subquery

The Top Query always runs first in a Correlated Subquery

Correlated Subquery Example vs. a Join with a Derived Table

Quiz- A Second Chance To Write a Correlated Subquery

Answer – A Second Chance to Write a Correlated Subquery

Quiz- A Third Chance To Write a Correlated Subquery

Answer – A Third Chance to Write a Correlated Subquery

Quiz- Last Chance To Write a Correlated Subquery

Answer – Last Chance to Write a Correlated Subquery

Quiz – Write the Extreme Correlated Subquery

Answer To Quiz – Write the Extreme Correlated Subquery

NOT IN Subquery Returns Nothing when NULLs are Present

Fixing a NOT IN Subquery with Null Values

Quiz- Write the NOT IN Subquery

Answer to Quiz- Write the NOT Subquery

Quiz – Write the Subquery using a WHERE Clause

Answer – Write the Subquery using a WHERE Clause

Quiz – Write the Subquery with Two Parameters

Answer – Write the Subquery with Two Parameters

Quiz – Write the Two Parameter Subquery With an Aggregate

Answer – Two Parameter Subquery and an Aggregate

How the Double Parameter Subquery Works

More on how the Double Parameter Subquery Works

Quiz – Write the Triple Subquery

Answer to Quiz – Write the Triple Subquery

IN is equivalent to =ANY

Using a Correlated Exists

How a Correlated Exists Matches Up

The Correlated NOT Exists

Chapter 9 – Analytic and Window Functions

Nexus Gives You Oracle Analytics for Free

ROW_NUMBER

Quiz – How did the Row_Number Reset?

Answer – How did the Row_Number Reset?

Using a Subquery and a WHERE Clause

Quiz – Return Two Students Per Class_Code with Highest Grades

Answer – Return Two Students Per Class_Code with Highest Grades

Using a Derived Table

RANK

Dense_Rank

RANK vs. DENSE_RANK

Getting RANK to Sort in DESC Order

RANK() OVER and PARTITION BY

RANK() OVER, PARTITION BY, and a Subquery

Using a Derived Table

DENSE_RANK() OVER and PARTITION BY

PERCENT_RANK() OVER with 14 rows in Calculation

PERCENT_RANK() OVER with 21 rows in Calculation

PERCENT_RANK and PARTITION BY

Cumulative Sum

Cumulative Sum – Major and Minor Sort Keys

Reset with a PARTITION BY Statement

Totals and Subtotals through Partition By

Moving Sum

Moving SUM every 3-rows vs. a Continuous Average

Partition By Resets the Calculations

SUM(SUM(n))

Moving Average

The Moving Window is Current Row and Preceding n Rows

How Moving Average Handles the Order By

Quiz – How is that Total Calculated?

Answer to Quiz – How is that Total Calculated?

Quiz – How is that 4th Row Calculated?

Answer to Quiz – How is that 4th Row Calculated?

Moving Average every 3-rows Vs. a Continuous Average

The Partition By Statement

Partition By Resets an ANSI Analytic

Moving Difference

Moving Difference With Lag

Moving Difference with Partition By

Finding a Value of a Column in the Next Row with MIN

Finding a Next Row Value with MIN and PARTITION BY

Finding Multiple Values of a Column in Upcoming Rows

Finding The Next Date using MAX

COUNT OVER for a Sequential Number

COUNT OVER using ROWS UNBOUNDED PRECEDING

The MAX OVER Command

MAX OVER with PARTITION BY Reset

The MIN OVER Command

The MIN OVER Command with PARTITION BY

Finding Gaps Between Dates

CSUM For Each Product_ID For the First Three Days

FIRST_VALUE

Using FIRST_VALUE

FIRST_VALUE With Partitioning

Daily_Sales Minus FIRST_VALUE With Partitioning

FIRST_VALUE After Sorting by the Highest Value

FIRST_VALUE with Row_Number and A Subquery

FIRST_VALUE and a Derived Table

Last_Value Can Be Confusing

Last_Value Working Properly

Last_Value With Partitioning

Last_Value And First_Value with Partitioning

First and Last Value Difference

Using LEAD

Using LEAD with a PARTITION Statement

Using LEAD With an Offset of 2

Using LEAD With an Offset of 2 and a PARTITION

Using LAG

Using LAG with a PARTITION Statement

Using LAG With an Offset of 2

Using Two LAG Statements

Using LAG With an Offset of 2 and a PARTITION

CUME_DIST

CUME_DIST With a Tie Value

CUME_DIST With a Subquery

CUME_DIST and a Derived Table

CUME_DIST and a Partition By Statement

CUME_DIST with 14 Rows

CUME_DIST With a Partition on 7 Rows

How Ntile Works

Ntile Quintile Example

Ntile Median Example

Ntile Percentile

Ntile Percentile In DESC Order

Using Quantiles (Partitions of Four)

Using Deciles (Partitions of Ten)y

NTILE Tertile With a Partition

MEDIAN Example

MEDIAN with Partitioning and a WHERE Clause

MEDIAN with Partitioning

PERCENTILE_CONT Function Description and Syntax

Result Information About PERCENTILE_CONT

PERCENTILE_CONT Function Arguments

PERCENTILE_CONT Example

PERCENTILE_CONT Example with Percentage Change

PERCENTILE_CONT With PARTITION Example

PERCENTILE_CONT With PARTITION and (0.4)

PERCENTILE_DISC Function Description and Syntax

PERCENTILE_DISC Function Arguments

PERCENTILE_DISC Example

PERCENTILE_DISC Example with Percentage Change

PERCENTILE_DISC With PARTITION Example

PERCENTILE_DISC With PARTITION and (0.4)

LISTAGG Basic Example

LISTAGG With a Pipe-Separated List

LISTAGG With a Comma-Separated List in Groups

LISTAGG With a Comma-Separated List in Groups

NTH_VALUE Function and Syntax

NTH_VALUE Arguments

NTH_VALUE Function and Syntax

NTH_VALUE With Partition

NTH_VALUE With Partition and Ignore Nulls

RATIO_TO_REPORT Function

RATIO_TO_REPORT Example

Width_Bucket

Chapter 10 – Strings

UPPER and lower Functions

INITCAP

The Length Command Counts Characters

The LENGTHB Command

The TRIM Command Trims Leading and Trailing Spaces

The RTRIM and LTRIM Command Trims Spaces

Trim and Trailing is Case Sensitive

How to TRIM Trailing Letters

Concatenation

Concatenation

The SUBSTR Command

How SUBSTR Works with NO ENDING POSITION

Using SUBSTR and LENGTH Together

The INSTR Command finds a Letters Position

The INSTR Command is brilliant with SUBSTR

LISTAGG

LISTAGG To Show the Web Pages Visited Per Customer

LISTAGG For a Targeted Marketing Campaign

The TRANSLATE Command

LPAD and RPAD

The REPLACE Function

REGEXP_REPLACE

REGEXP_REPLACE Example

Another REGEXP_REPLACE Example

REGEXP_INSTR

REGEXP_LIKE

REGEXP_SUBSTR

SOUNDEX Function to Find a Sound

How Soundex Works

The REVERSE String Function

The ASCII Function

Chapter 11 – Interrogating the Data

Quiz – Fill in the Answers for the NULLIF Command

Answer – Fill in the Answers for the NULLIF Command

The COALESCE Command

COALESCE is Equivalent to this CASE Statement

The Perfect Table for a Coalesce

COALESCE in a Real-World Example

The Basics of CAST (Convert And Store)

A Rounding Example Using CAST

CAST will Round Values Up or Down

Valued Case vs. Searched Case

Combining Searched Case and Valued Case

Nested Case

The DECODE Command

DECODE Vs. CASE Examples

A Trick for getting a Horizontal Case

Put a CASE in the ORDER BY

Using A Searched CASE Statement to Sort Logically

Quiz – Can you Add a Minor Sort?

Answer – Can you Add a Minor Sort?

Order By DECODE

Quiz – Can you Add Two Minor Sorts Using Decode?

Answer – Can you Add Two Minor Sorts Using Decode?

CASE Challenge

Answer – CASE Challenge

Chapter 12 – Views

The Fundamentals of Views

The Rules of Oracle Views Part 1

The Rules of Oracle Views Part 2

Creating a Simple View to Restrict Sensitive Columns

Creating a Simple View to Restrict Rows

Creating a View to Join Tables Together

Sometimes We Create Views for Formatting

Simple View Concepts

How to Modify a View

The Exception to the ORDER BY Rule inside a View

Derived Columns in a View Must Have a Column Alias

The Standard Way Most Aliasing is Done

Another Way to Alias Columns in a View CREATE

What Happens When a View Column gets Aliased Twice?

A View that Errors After An ALTER

Chapter 13 – Set Operators

The Fundamentals of Oracle Set Operators

The Rules of Oracle Set Operators – Part 1

The Rules of Oracle Set Operators – Part 2

Quiz – Intersect Explained Logically

Answer – Intersect Explained Logically

Quiz – Union Explained Logically

Answer – Union Explained Logically

Quiz – Union ALL Explained Logically

Answer – Union ALL Explained Logically

Quiz – Except Explained Logically

Answer – Minus Explained Logically

Quiz – Testing Your Knowledge

Answer – Testing Your Knowledge

Rule 1 – Equal Number of Columns in Both SELECT Lists

Rule 2 – Top Query Handles all Aliases

Rule 3 – The Bottom Query does the ORDER BY

Intersect and Subquery Challenge

Answer – Intersect Challenge

UNION Vs. UNION ALL

Using UNION ALL and Literals

Using UNION ALL for Speed in Merging Data Sets

Great Trick: Place your Set Operator in a Derived Table

A Great Example of MINUS

Changing the Order of Precedence with Parentheses

Using UNION For Amazing Results

Chapter 14 – Creating Tables

Oracle Table Fundamentals

Numeric Data Types

Date Data Types

Character Data Types

Unstructured and Large Data Types

Other Oracle Data Types

The Basics of Creating a Table

An Incredible Oracle Table Example

Creating a Table With Default Values and a Check

Defining Primary Keys

Defining a Foreign Key After the Table Has Been Created

Range Partitioning

List Partitioning

Hash Partitioning

Composite Partitioning

Creating a Table Using a CTAS

Creating a Table Using a CTAS Join

Creating a Global Temporary Table Using a CTAS

Creating a Global Temporary Table Using a CTAS Join

Creating a Temporary Table From Another’s Space

Altering, Renaming, and Dropping a Table

Chapter 15 – Inserts, Updates, and Deletes

INSERT Syntax # 1

INSERT Syntax # 2

INSERT/SELECT to Build a Data Mart

UPDATE Examples

Example of Subquery UPDATE Command

Deleting Rows in a Table

Renaming a Table

Chapter 16 – Statistical Aggregate Functions

The Stats Table

The STDDEV_POP Function

STDDEV_POP Example

The STDDEV_SAMP Function

A STDDEV_SAMP Example

The VAR_POP Function

A VAR_POP Example

The VAR_SAMP Function

A VAR_SAMP Example

The CORR Function

A CORR Example

Another CORR Example so you can Compare

The VARIANCE Function

A VARIANCE Example

The COVAR_POP Function

A COVAR_POP Example

Another COVAR_POP Example so you can Compare

The COVAR_SAMP Function

A COVAR_SAMP Example

Another COVAR_SAMP Example so you can Compare

The REGR_INTERCEPT Function

A REGR_INTERCEPT Example

Another REGR_INTERCEPT Example so you can Compare

The REGR_SLOPE Function

A REGR_SLOPE Example

Another REGR_SLOPE Example so you can Compare

The REGR_AVGX Function

A REGR_AVGX Example

Another REGR_AVGX Example so you can Compare

The REGR_AVGY Function

A REGR_AVGY Example

Another REGR_AVGY Example so you can Compare

The REGR_COUNT Function

A REGR_COUNT Example

The REGR_R2 Function

A REGR_R2 Example

The REGR_SXX Function

A REGR_SXX Example

The REGR_SXY Function

A REGR_SXY Example

The REGR_SYY Function

A REGR_SYY Example

Using GROUP BY

Chapter 17 – Mathematical Functions

Numeric Manipulation Functions

ABS

ACOS

ASIN

ATAN

ATAN2

Ceil

COSH

EXP

Floor

LN

LOG

MOD

ROUND

SIGN

SINH

SQRT

TANH