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