Databricks SQL

Training Materials

All Databricks SQL training students receive comprehensive courseware.

Software Requirements

Attendees will write applications using the Databricks service running on the cloud. 

Outline

• Introduction to SQL
  • Introduction
  • 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 First in Ascending Mode (Default)
  • Order By with Nulls Last
  • Order By with Nulls First
  • 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
  • A Missing Comma can by Mistake become an Alias
  • Comments using Double Dashes are Single Line Comments
  • Comments for Multi-Lines
  • Comments for Multi-Lines As Double Dashes Per Line
• The WHERE Clause
  • The WHERE Clause limits Returning Rows
  • Numbers Don't Need Single Quotes
  • Not Equal
  • Searching for null Values Using Equality Returns Nothing
  • Is NULL
  • IS Not Null
  • Using Greater Than Or Equal To (>=)
  • AND in the WHERE Clause
  • Troubleshooting AND
  • OR in the WHERE Clause
  • Troubleshooting OR
  • WHY OR Must Utilize the Column Name Each Time
  • Troubleshooting Character Data
  • Troubleshooting Character Data Continued
  • 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 Results
  • 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
  • LIKE uses Wildcards Percent ‘%’ and Underscore ‘_’
  • Another Example of UPPER and LOWER
  • Using LIKE for all Cases with Lower and Upper
  • Using ILIKE Handle Case Issues
  • LIKE command Underscore is Wildcard for one Character
  • Finding Anyone Whose name End in 'Y'
  • Escape Character in the LIKE Command changes Wildcards
  • Escape Characters Turn off Wildcards in the LIKE Command
  • The REPLACE Function
• Distinct, Group By and Top
  • 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?
  • Top Command
  • Top Command and Order By
• Aggregation
  • Quiz – You 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 come back?
  • Answer – How many rows come back?
  • Casting a Data Type
  • Troubleshooting Aggregates
  • GROUP BY Delivers One Row Per Group
  • GROUP BY dept_no or GROUP BY Column Number
  • 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
  • ANY_VALUE
  • GROUP BY GROUPING SETS
  • GROUP BY ROLLUP
  • GROUP BY ROLLUP Answer Set
  • GROUP BY CUBE
  • GROUP BY CUBE Answer Set
• Joining Tables
  • Nexus Builds Your Join SQL Automatically
  • A Two-Table Join Using Traditional Syntax
  • Two-Table join using Traditional Syntax with Table Alias
  • You Can Fully Qualify All Columns
  • A Two-Table Join Using ANSI Syntax
  • Both Queries have the same Results and Performance
  • Quiz – Can You Finish the Join Syntax?
  • Quiz – Can You Find the Error?
  • Super Quiz – Can You Find the Difficult Error?
  • Super Quiz – Can You Find the Difficult Error?
  • Quiz – Which Rows from Both Tables Won’t Return?
  • Left Outer Join
  • Left Outer Join Results
  • Right Outer Join
  • Right Outer Join Example and Results
  • Full Outer Join
  • Full Outer Join Results
  • Which Tables are Left Tables and Which are Right?
  • Answer - Which Tables are Left Tables and Which are Right?
  • INNER JOIN with Additional AND Clause
  • ANSI INNER JOIN with Additional AND Clause
  • ANSI INNER JOIN with Additional WHERE Clause
  • OUTER JOIN with Additional WHERE Clause
  • 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 CROSS JOIN Answer Set
  • The Self Join
  • The Self Join with ANSI Syntax
  • An Associative Table is a Bridge that Joins Two Tables
  • Quiz – Can you Write the 3-Table Join?
  • Answer to Quiz – Can you Write the 3-Table Join?
  • Quiz – Can you Write the 3-Table Join Using ANSI Syntax?
  • Answer – Can you Write the 3-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 5-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
• Date Functions
  • Migrate Any Database to Databricks and Vice Versa
  • Current_Date
  • Current_Date, Current_Timestamp, and Current_Timezone
  • Now() Function
  • Add or Subtract From a Date
  • Date Function
  • To_Date Function
  • To_Timestamp Function
  • Add or Subtract Days From a Date
  • Subtract Two Dates for a Difference in Days
  • Subtract Two Dates for a Difference in Days
  • MONTHS_BETWEEN
  • The ADD_MONTHS Command
  • Using the ADD_MONTHS Command to Add 1 Year
  • Using the ADD_MONTHS Command to Add 5 Years
  • The EXTRACT Command
  • The EXTRACT Command
  • EXTRACT from DATES and TIME
  • Day, Month, Year, DayofMonth, DayofWeek, and DayofYear
  • Using CASE and Extract to Reformat Dates
  • Using CAST and SUBSTRING to Reformat Dates
  • The Date_Part Function
  • Date_Format Function
  • More Date_Format Examples
  • Datediff Example
  • Dateadd
  • Incrementing Time Values Using the Dateadd Function
  • Date_Sub Function
  • The Date_Trunc Function
  • Date_Trunc Command With Time
  • Date_Trunc Command With Dates
  • Last_Day
  • Advanced Tricks for Month
  • Clever Tricks for Month
  • Make_Date
  • Make_Timestamp
  • Using Day, Month, and Year intervals
  • The Basics of a Simple Interval
  • Determining if the Current_Date is a Leap Year
  • Determining if the Current_Timestamp is a Leap Year
  • Make_Interval
  • Try_Divide Function
  • Chapter 7 – Analytic and Window Functions
  • Nexus Gives You Databricks Analytics for Free
  • ROW_NUMBER
  • Quiz – How did the Row_Number Reset?
  • Answer – How did the Row_Number Reset?
  • QUALIFY
  • Top Two Students Per class_code Using a Derived Table
  • RANK
  • Dense_Rank
  • Getting RANK to Sort in DESC Order
  • RANK() OVER and PARTITION BY
  • RANK() OVER, PARTITION BY, and QUALIFY
  • RANK() OVER and a Derived Table
  • RANK() OVER and a WITH Derived Table
  • RANK vs. DENSE_RANK
  • DENSE_RANK() OVER and PARTITION BY
  • PERCENT_RANK() OVER with 14 rows in Calculation
  • PERCENT_RANK() OVER with 21 rows in Calculation
  • PERCENT_RANK() OVER and PARTITION BY
  • Cumulative Sum
  • Cumulative Sum with CAST
  • Cumulative Sum – The Sort Explained
  • Cumulative Sum – Rows Unbounded Preceding Explained
  • Cumulative Sum – Making Sense of the Data
  • 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
  • Moving Average
  • The Moving Window is Current Row and Preceding
  • 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 OLAP
  • Moving Difference
  • Moving Difference with Partition By
  • 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 The Next Date using MAX
  • Finding Multiple Values of a Column in Upcoming Rows
  • 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
  • Different Windowing Options
  • How Ntile Works
  • Ntile in DESC Mode
  • Ntile
  • Ntile Percentile
  • Another Ntile Example
  • Using Quantiles (Partitions of Four)
  • NTILE With a Partition
  • NTILE With a Qualify Statement
  • Using FIRST_VALUE
  • FIRST_VALUE With Partitioning
  • Daily_Sales Minus FIRST_VALUE With Partitioning
  • FIRST_VALUE With Partitioning
  • FIRST_VALUE After Sorting by the Highest Value
  • FIRST_VALUE with Partitioning
  • Using LAST_VALUE
  • LAST_VALUE – Current Row
  • First_Value Review
  • Last_Value Can Be Confusing
  • Last_Value Now Makes Sense
  • Last_Value With Partitioning
  • Last_Value And First_Value with Partitioning
  • First and Last Value Difference Between Today's Daily_Sales
  • 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 Two LAG Statements
  • Using LAG With an Offset of 2
  • Using LAG With an Offset of 2 and a PARTITION
  • CUME_DIST and Qualify
  • CUME_DIST With Ties
  • CUME_DIST and Partition By
  • CUME_DIST With a Partition on the Sales_Table
  • CURRENT ROW AND UNBOUNDED FOLLOWING
  • Different Windowing Options
  • MEDIAN Example
  • MEDIAN with Partitioning and a WHERE Clause
  • MEDIAN with Partitioning
  • PERCENTILE_CONT Function Description and Syntax
  • Final Result Information About PERCENTILE_CONT
  • PERCENTILE_DISC Function Arguments
  • PERCENTILE_CONT Example
  • PERCENTILE_CONT Example with Percentage Change
  • PERCENTILE_CONT With PARTITION Example
  • PERCENTILE_DISC Function Description and Syntax
  • PERCENTILE_DISC Example
  • PERCENTILE_DISC Example with Percentage Change
  • PERCENTILE_DISC With PARTITION Example
• Temporary Tables
  • CREATING A Derived Table
  • Naming the Derived Table
  • Aliasing the Column names in the Derived Table
  • CREATING A Derived Table using the WITH Command
  • Derived Query Examples with Three Different Techniques
  • Most Derived Tables Are Used To Join To Other Tables
  • The Three Components of a Derived Table
  • Visualize This Derived Table
  • Our Join Example Using The WITH Syntax
  • An Example of Two Derived Tables in a Single Query
• Subqueries
  • An IN List is much like a Subquery
  • An IN List Never has Duplicates – Just like a Subquery
  • An IN List Ignores Duplicates
  • The Subquery
  • The Three Steps of How a Basic Subquery Works
  • These are Equivalent Queries
  • The Final 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
  • Quiz – Write the Extreme Subquery
  • Quiz - Write the Subquery with an Aggregate
  • 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
  • Answer - A Second Chance to Write a Correlated Subquery
  • Answer - A Third Chance to Write a Correlated Subquery
  • Answer – Last Chance to Write a 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 Subquery
  • Quiz - Write the Subquery using a WHERE Clause
  • Quiz- Write the Subquery with Two Parameters
  • How the Double Parameter Subquery Works
  • More on how the Double Parameter Subquery Works
  • Another Example of a Double Parameter Subquery
  • Quiz – Write the Triple Subquery
  • Using a Correlated Exists
  • How a Correlated Exists Matches Up
  • The Correlated NOT Exists
• Strings
  • UPPER and lower  Functions
  • The Length Command Counts Characters
  • LENGTH and TRIM Work on Fixed Length Columns
  • The Char_Length Command Counts Characters
  • CHAR_LENGTH and OCTET_LENGTH
  • The TRIM Command trims both Leading and Trailing Spaces
  • The RTRIM and LTRIM Command Trims Spaces
  • TRIM can also TRIM Characters
  • Concatenation
  • Concat and Concat_WS for Concatenation
  • The SUBSTR and SUBSTRING Commands
  • How SUBSTR Works with NO ENDING POSITION
  • Using SUBSTR and CHAR_LENGTH Together
  • The POSITION Command finds a Letters Position
  • The POSITION Command is Brilliant with SUBSTR
  • CHARINDEX Finds a Letter(s) Position in a String
  • The CHARINDEX Command is brilliant with SUBSTRING
  • The CHARINDEX Command Using a Literal
  • LPAD and RPAD
  • The REPLACE Function
  • The ASCII Function
  • The Reverse String Function
  • The RIGHT Function
  • The LEFT and RIGHT Functions
  • REGEXP Example for Whitespace Character
  • REGEXP Example for Non-Whitespace
  • REGEXP Example for [xyz]
  • REGEXP Example Start of a String
  • REGEXP Example End of a String
  • REGEXP Example Matching Within a Range
  • REGEXP_REPLACE
  • REGEXP_REPLACE Example
  • Another REGEXP_REPLACE Example
  • REGEXP_LIKE
  • RLIKE
  • SOUNDEX Function to Find a Sound
• Interrogating the Data
  • Quiz – Fill in the Answers for the NULLIF Command
  • Answer – Fill in the Answers for the NULLIF Command
  • COALESCE in a Real-World Example
  • The COALESCE Command
  • COALESCE is Equivalent to this CASE Statement
  • Some Great CAST (Convert And Store) Examples
  • A Rounding Example Using CAST
  • CAST will Round Values up or Down
  • Valued Case vs. Searched Case
  • Combining Searched Case and Valued Case
  • Decode
  • A Trick for getting a Horizontal Case
  • Put a Valued CASE in the ORDER BY
  • Put a Searched CASE in the ORDER BY
  • Put a Decode in the ORDER BY
  • Extreme CASE Challenge
• Views
  • The Fundamentals of Views
  • Creating a Simple View to Restrict Sensitive Columns
  • Creating a Simple View to Restrict Rows
  • Creating a View to Join Tables Together
  • Basic Rules for Views
  • How to Modify a View
  • The Exception to the ORDER BY Rule inside a View
  • Derived Columns in a View Should Contain 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?
• Set Operators
  • Rules of Set Operators
  • Quiz - Intersect Explained Logically
  • Quiz - Union Explained Logically
  • Quiz - Union ALL Explained Logically
  • Quiz - Except Explained Logically
  • Quiz - Testing Your Knowledge
  • An Equal Number of Columns in both SELECT List
  • The Top Query handles all Aliases
  • The Bottom Query does the ORDER BY
  • Intersect 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 how EXCEPT works
  • USING Multiple SET Operators in a Single Request
  • Changing the Order of Precedence with Parentheses
• Creating Tables
  • Create Table Syntax
  • Data Types
  • Create Table Examples
  • Best Practices for Partitioned Tables
  • Describe Detail Tablename
  • Not Null Constraint
  • Create a Table IF NOT EXISTS
  • Create Table AS (CTAS) Populates the Table With Data
  • Create Table AS (CTAS) can Choose Certain Columns
• Data Manipulation Language (DML)
  • INSERT Syntax # 1
  • INSERT Syntax # 2
  • INSERT Example with Multiple Rows
  • Above we have inserted multiple rows and placed null values in some of them.
  • INSERT/SELECT Command
  • INSERT/SELECT to Build a Data Mart
  • UPDATE Examples
  • Deleting Rows in a Table
• Statistical Aggregate Functions
  • The Stats Table
  • The KURTOSIS Function
  • The STDDEV_POP Function
  • The STDDEV_SAMP Function
  • The VAR_POP Function
  • The VAR_SAMP Function
  • A VAR_SAMP Example
  • The CORR Function
  • A CORR Example
  • Another CORR Example so you can Compare
  • The VARIANCE Function
  • The COVAR_POP Function
  • The COVAR_SAMP Function
  • The REGR_INTERCEPT Function
  • Another REGR_INTERCEPT Example so you can Compare
  • The REGR_SLOPE Function
  • A REGR_SLOPE Example
  • NOT IN Subquery Returns Nothing when nulls are Present
  • The REGR_AVGX Function
  • A REGR_AVGX Example
  • Another REGR_AVGX  Example so you can Compare
  • The REGR_AVGY   Function
  • A REGR_AVGY Example
  • Quiz- Write the Subquery with Two Parameters
  • The REGR_COUNT Function
  • A REGR_COUNT Example
  • The REGR_R2 Function
  • A REGR_R2 Example
  • The REGR_SXX Function
  • Answer to Quiz – Write the Triple Subquery
  • The REGR_SXY Function
  • A REGR_SXY Example
  • The REGR_SYY Function
  • A REGR_SYY Example
  • Using GROUP BY
  • APPROX_COUNT_DISTINCT
• Mathematical Functions
  • Numeric Manipulation Functions
• Conclusion