# Databricks SQL

$199.00

Title: Databricks SQL

Format: e-Book

ISBN: 978-1-940540-61-0

Publisher: Coffing Data Warehousing

Pub Date: October 2023

## Description

The Databricks SQL book is a 600-page masterpiece for anyone working with Databricks SQL. Every SQL command and option has wonderful explanations and examples.

# Topics:

• ORDER BY NULLS FIRST NULLS LAST

• Advanced ORDER BY Statements

• Like and ilike

• Top

• Group By Grouping Sets, Rollup, and Cube

• 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

**Databricks SQL Outline**

**Chapter 1 – 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

**Chapter 2 – 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

**Chapter 3 – 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

**Chapter 4 – 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

**Chapter 5 – 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?

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 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 to 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

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

**Chapter 6 – 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 Continued

Ntile Percentile

Another Ntile Example

Using Quantiles (Partitions of Four)

NTILE With a Partition

NTILE With a Qualify Statement

Using FIRST_VALUE

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

CUME_DIST

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_CONT With PARTITION and (0.4)

PERCENTILE_DISC Function Description and Syntax

PERCENTILE_DISC Example

PERCENTILE_DISC Example with Percentage Change

PERCENTILE_DISC With PARTITION Example

PERCENTILE_DISC With PARTITION and (0.4)

**Chapter 8 – 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

**Chapter 9 – 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

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 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 to 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

Answer to Quiz – Write the Triple Subquery

Using a Correlated Exists

How a Correlated Exists Matches Up

The Correlated NOT Exists

**Chapter 10 – 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

**Chapter 11 – 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

Answer – Extreme CASE Challenge

Answer – CASE Challenge

**Chapter 12 – 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?

**Chapter 13 – Set Operators**

Rules of Set Operators

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 – Except Explained Logically

Quiz – Testing Your Knowledge

Answer – 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

**Chapter 14 – 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

**Chapter 15 – 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

**Chapter 16 – Statistical Aggregate Functions**

The Stats Table

The KURTOSIS Function

A KURTOSIS Example

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

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

**Chapter 17 – Mathematical Functions**

Numeric Manipulation Functions

ABS

ACOS

ACOSH

ASIN

ASINH

ATAN

ATAN2

ATANH

CBRT

Ceil

COS

COSH

COT

DEGREES

DIV

EXP

FACTORIAL

Floor

LN

LOG

MOD

PI

POW or POWER

RADIANS

ROUND

SIGN

SIN

SINH

SQRT

TAN

TANH