INTRO TO SQL

Baraa Khatib Salkini

YouTube | DATA WITH BARAA
SQL Course | Intro to SQL
SQL Course | Intro to SQL
SQL Course | Intro to SQL |DBMS & SQL Server
SQL Course | Intro to SQL |Types of Databases
SQL Course | Intro to SQL |Database Structure
SQL Course | Intro to SQL |How Tables are Stored
SQL Course | Intro to SQL |Types of SQL Commands
SQL Course | Intro to SQL |Why Learn SQL?
 SQL COMPONENTS

Baraa Khatib Salkini

YouTube | DATA WITH BARAA
SQL Course | SELECT Query
 -- Retrieve Customers Data
SELECT
name,
LOWER(country)

FROM customers
WHERE country = 'Italy'

SQL Course | SELECT Query |SQL Components

 SQL STATEMENT

-- Retrieve Customers Data

SELECT
name,
LOWER(country)

FROM customers
WHERE country = 'Italy'

SQL Course | SELECT Query |SQL Components

 Comment -- Retrieve Customers Data
SELECT
name,
LOWER(country)

FROM customers
WHERE country = 'Italy'

SQL Course | SELECT Query |SQL Components

 -- Retrieve Customers Data
SELECT
name,
Clauses LOWER(country)

FROM customers
WHERE country = 'Italy'

SQL Course | SELECT Query |SQL Components

 -- Retrieve Customers Data
SELECT
name,
Keywords LOWER(country)

FROM customers
WHERE country = 'Italy'

SQL Course | SELECT Query |SQL Components

 -- Retrieve Customers Data
SELECT
name,
Function
LOWER(country)

FROM customers
WHERE country = 'Italy'

SQL Course | SELECT Query |SQL Components

 -- Retrieve Customers Data
SELECT
name,
LOWER(country) identifiers

FROM customers
WHERE country = 'Italy'

SQL Course | SELECT Query |SQL Components

 -- Retrieve Customers Data
SELECT
name,
LOWER(country) Operator

FROM customers
WHERE country = 'Italy'

SQL Course | SELECT Query |SQL Components

 -- Retrieve Customers Data
SELECT
name,
LOWER(country) Value

FROM customers
WHERE country = 'Italy'

SQL Course | SELECT Query |SQL Components

 QUERY DATA
SELECT STATEMENT

Baraa Khatib Salkini

YouTube | DATA WITH BARAA
SQL Course | SELECT Query
 Types
SQL Commands

DQL DDL DML

Data Query Data Definition Data Manipulation
Language Language Language

SELECT CREATE INSERT

DROP UPDATE

ALTER DELETE

SQL Course | SELECT Query

SQL Course | SELECT Query
SQL Course | SELECT Query
SQL Course | SELECT Query | SELECT ALL
SQL Course | SELECT Query | SELECT Few Columns
SQL Course | SELECT Query | WHERE
SQL Course | SELECT Query | ORDER BY
SQL Course | SELECT Query | GROUP BY
SQL Course | SELECT Query | HAVING
SQL Course | SELECT Query | DISTINCT
SQL Course | SELECT Query | TOP
SQL Course | SELECT Query | TOP
 BONUS
Sketches

Baraa Khatib Salkini

YouTube | DATA WITH BARAA
SQL Course | SELECT Query
 DDL
Data Definition Language
Define Your Database

Baraa Khatib Salkini

YouTube | DATA WITH BARAA
SQL Course | DDL Commands
 Types
SQL Commands

DQL DDL DML

Data Query Data Definition Data Manipulation
Language Language Language

SELECT CREATE INSERT

DROP UPDATE

ALTER DELETE

SQL Course | DDL Commands

SQL Course | DDL Commands
 DML
Data Manipulation Language
Change Your Data

Baraa Khatib Salkini

YouTube | DATA WITH BARAA
SQL Course | DML Commands
 Types
SQL Commands

DQL DDL DML

Data Query Data Definition Data Manipulation
Language Language Language

SELECT CREATE INSERT

DROP UPDATE

ALTER DELETE

SQL Course | DML Commands

SQL Course | DML Commands
SQL Course | DML Commands |INSERT Methods
 INSERT
Syntax

OPTIONAL: If no columns are specified, SQL expects values for all columns

INSERT INTO table_name (column1, column2, column3,…)

VALUES (value1, value2, value3,…)

,(value1, value2, value3,…) Multiple Inserts

Rule  Match the number of columns and Values.

SQL Course | DML Commands |INSERT

 UPDATE
Syntax

UPDATE table_name
SET column1 = value1,
column2 = value2

WHERE <condition>

NOTE  Always use WHERE to avoid UPDATING all rows unintentionally

SQL Course | DML Commands |UPDATE

 DELETE
Syntax

DELETE FROM table_name

WHERE <condition>

NOTE  Always use WHERE to avoid DELETING all rows unintentionally

SQL Course | DML Commands |DELETE

 Filtering Data
WHERE CONDITIONS

Baraa Khatib Salkini

YouTube | DATA WITH BARAA
SQL Course | FILTERING DATA
 WHERE
operators

Comparison Logical Range Membership Search

Operators Operators Operator Operator Operator

= AND BETWEEN IN LIKE

<> =! OR NOT IN
>
NOT
>=
<
<=

SQL Course | Filtering Data

 WHERE
operators

Comparison Logical Range Membership Search

Operators Operators Operator Operator Operator

= AND BETWEEN IN LIKE

<> =! OR NOT IN
>
NOT
>=
<
<=

SQL Course | Filtering Data | Comparison Operators

 Comparison
Operators

= Checks if two values are equal

<> != Checks if two values are not equal

> Checks if a value is greater than another value.

>= Checks if a value is greater than or equal to another value

< Checks if a value is less than another value

<= Checks if a value is less than or equal to another value

SQL Course | Filtering Data | Comparison Operators

SQL Course | Filtering Data | Comparison Operators
SQL Course | Filtering Data | Comparison Operators
 WHERE
operators

Comparison Logical Range Membership Search

Operators Operators Operator Operator Operator

= AND BETWEEN IN LIKE

<> =! OR NOT IN
>
NOT
>=
<
<=

SQL Course | Filtering Data | Logical Operators

 Logical
Operators

AND All conditions must be TRUE

OR At least one condition must be TRUE

NOT (Reverse) Excludes matching values

BETWEEN Check if a value is within a range

IN Check if a value exists in a list

LIKE Search for a pattern in text

SQL Course | Filtering Data | Logical Operators

SQL Course | Filtering Data | AND Operator
SQL Course | Filtering Data | OR Operator
SQL Course | Filtering Data | NOT Operator
 WHERE
operators

Comparison Logical Range Membership Search

Operators Operators Operator Operator Operator

= AND BETWEEN IN LIKE

<> =! OR NOT IN
>
NOT
>=
<
<=

SQL Course | Filtering Data | BETWEEN Operator

SQL Course | Filtering Data | BETWEEN Operator
 WHERE
operators

Comparison Logical Range Membership Search

Operators Operators Operator Operator Operator

= AND BETWEEN IN LIKE

<> =! OR NOT IN
>
NOT
>=
<
<=

SQL Course | Filtering Data | IN & NOT IN Operators

SQL Course | Filtering Data | IN & NOT IN Operators
 WHERE
operators

Comparison Logical Range Membership Search

Operators Operators Operator Operator Operator

= AND BETWEEN IN LIKE

<> =! OR NOT IN
>
NOT
>=
<
<=

SQL Course | Filtering Data | LIKE Operator

SQL Course | Filtering Data | LIKE Operator
 SQL JOINS
Combining Data

Baraa Khatib Salkini

YouTube | DATA WITH BARAA
SQL Course | JOINS
SQL Course | Combining Data The Two Methods
 What is SQL
JOIN ?

SQL Course | Joins

SQL Course | Joins | Why We Join Tables
 Joins Possibilities

SQL Course | Joins

 Basic
Join Types

SQL Course | Joins

SQL Course | Joins
 Alternative To RIGHT JOIN

SQL Course | Joins

 Advanced
Join Types

SQL Course | Joins

 Alternative To RIGHT Anti JOIN

SQL Course | Joins

 How I Join Multiple Tables

SQL Course | Joins

 How I Join Multiple Tables

SQL Course | Joins

 Inner Join Multiple Tables

SQL Course | Joins

 SET Operators
Combining Data

Baraa Khatib Salkini

YouTube | DATA WITH BARAA
SQL Course | SET Operators
 SET OPERATORS

SQL Course | SET Operators

SQL Course | SET Operators
 CTE SYNTAX
SELECT
FirstName
LastName
FROM Customers 1st SELECT Statement
JOIN Clause
WHERE Clause
GROUP BY Clause

SET Operator UNION

SELECT
FirstName
LastName
FROM Employees 2nd SELECT Statement
JOIN Clause
WHERE Clause
GROUP BY Clause
ORDER BY
can be used only at the
end to sort the final Result ORDER BY FirstName
SQL Course | SET Operators
 SET RULES

SQL Course | SET Operators

 UNION ALL vs UNION

•
•
•

SQL Course | SET Operators

 How UNION Works

SQL Course | SET Operators

 How UNION ALL Works

SQL Course | SET Operators

 How EXCEPT Works

SQL Course | SET Operators

 How INTERSECT Works

SQL Course | SET Operators

 SET OPERATORS

UNION UNION ALL EXCEPT/MINUS INTERSECT

Returns All rows from Returns All rows from Return unique rows in Return only the
both sets, elimination both sets, including first set that are not in common rows
duplicates duplicates second table between two sets

SELECT FirstName, LastName SELECT FirstName, LastName SELECT FirstName, LastName SELECT FirstName, LastName
FROM Customers FROM Customers FROM Customers FROM Customers

UNION UNION ALL EXCEPT INTERSECT

SELECT FirstName, LastName SELECT FirstName, LastName SELECT FirstName, LastName SELECT FirstName, LastName
FROM Employees FROM Employees FROM Employees FROM Employees

SQL Course | SET Operators

 SET USE CASE
Combine Information

SQL Course | SET Operators

 SET USE CASE
Delta Detection

SQL Course | SET Operators

 SET USE CASE
Data completeness Check

SQL Course | SET Operators

 SQL FUNCTIONS

Baraa Khatib Salkini

YouTube | DATA WITH BARAA
SQL Course | SQL Functions
 What are
Functions?

SQL Course | SQL Functions

 Nested Function

SQL Course | SQL Functions

 SQL
Runs outer
Functions
query

Single-Row Multi-Row
Functions Functions

String Numeric Aggregate Window

Functions Functions Functions Functions
(Basics) (Advanced)
Date & Time NULL
Functions Functions

SQL Course | SQL Functions

 STRING
FUNCTIONS

Baraa Khatib Salkini

YouTube | DATA WITH BARAA
SQL Course | String Functions
 String
Functions

Manipulation String
Calculation
Extraction

CONCAT LEN LEFT

UPPER RIGHT

LOWER SUBSTRING

TRIM

REPLACE

SQL Course | String Functions

 CONCAT Combines multiple strings into one

UPPER Converts all characters to uppercase

LOWER Converts all characters to lowercase

TRIM Removes Leading and Trailing spaces

String REPLACE Replaces specific character with a new character

Functions
LEN Counts how many characters

LEFT Extracts specific Number of Characters from the start

RIGHT Extracts specific Number of Characters from the End

Substring Extracts a part of string at a specified position

 CONCAT

SQL Course | String Functions | CONCAT

 LOWER & UPPER

SQL Course | String Functions | LOWER & UPPER

 TRIM

SQL Course | String Functions | TRIM

 REPLACE

SQL Course | String Functions | REPLACE

 LEN

SQL Course | String Functions | LEN

 LEFT & RIGHT

SQL Course | String Functions | LEFT & RIGHT

 SUBSTRING

SQL Course | String Functions | SUBSTRING

 NUMERIC
FUNCTIONS

Baraa Khatib Salkini

YouTube | DATA WITH BARAA
SQL Course | Number Functions
 SQL
Runs outer
Functions
query

Single-Row Multi-Row
Functions Functions

String Numeric Aggregate Window

Functions Functions Functions Functions
(Basics) (Advanced)
Date & Time NULL
Functions Functions

SQL Course | Numeric Functions

SQL Course | Numeric Functions
 DATE & TIME
FUNCTIONS

Baraa Khatib Salkini

YouTube | DATA WITH BARAA
SQL Course | Date & Time Funcitons
 SQL
Runs outer
Functions
query

Single-Row Multi-Row
Functions Functions

String Numeric Aggregate Window

Functions Functions Functions Functions
(Basics) (Advanced)
Date & Time NULL
Functions Functions

SQL Course | Date & Time Functions

SQL Course | Date & Time Functions
 Date & Time
Runs outer query
Functions

Part
Runs outer Format
Runs &
outer Runs outer
Runs outer
Calculations Validation
Extraction
query Casting
query query
query

DAY FORMAT DATEADD ISDATE

MONTH CONVERT DATEDIFF

YEAR CAST

DATEPART

DATENAME

DATETRUNC
EOMONTH

SQL Course | Date & Time Funcitons

SQL Course | Date & Time Funcitons
 PARTS
EXTRACTION

Baraa Khatib Salkini

YouTube | DATA WITH BARAA
SQL Course | Date & Time Funcitons
 Quick Functions
YEAR, MONTH, DAY

SQL Course | Date & Time Funcitons

 DATEPART

SQL Course | Date & Time Funcitons

 DATENAME

SQL Course | Date & Time Funcitons

 DATETRUNC

SQL Course | Date & Time Funcitons

 EOMONTH

SQL Course | Date & Time Funcitons

 PART EXTRACTION
Syntax

DAY(date)
Runs outer query
DATEPART(part,
Runs outer query date)
MONTH(date)
Runs outer query
DATENAME(part,
Runs outer query date)
YEAR(date)
Runs outer query
DATETRUNC(part,
Runs outer query date)
EOMONTH(date)
Runs outer query

SQL Course | Date & Time Funcitons

 DATA TYPES

SQL Course | Date & Time Funcitons

 DATE PARTS

SQL Course | Date & Time Funcitons

 How to Choose the Right Function?

SQL Course | Date & Time Funcitons

 2025-08-20
[Link].840 Date Parts

INT String Datetime2

Part Abbre. DATEPART DATENMAME DATETRUNC
year yy, yyyy 2025 2025 2025-01-01 [Link]

quarter qq,q 3 3 2025-07-01 [Link]

month mm,m 8 August 2025-08-01 [Link]

DATEPART dayofyear dy,y 232 232 2025-08-20 [Link]

day dd, d 20 20 2025-08-20 [Link]

DATENAME weekday dw 4 Wednesday Not supported
week wk,ww 34 34 2025-08-17 [Link]

DATETUNC iso_week ns 34 34 2025-08-18 [Link]

hour hh 9 9 2025-08-20 [Link]

minute mi,n 45 45 2025-08-20 [Link]

second ss,s 21 21 2025-08-20 [Link]

millisecond ms 0 0 2025-08-20 [Link]
microsecond msc 0 0 2025-08-20 [Link]
nanosecond ns 0 0 Not supported
iso_week isowk, isoww 0 +00:00 Not supported
 DATE
FORMATS

Baraa Khatib Salkini

YouTube | DATA WITH BARAA
SQL Course | Date & Time Funcitons
 FORMAT
Syntax Syntax

FORMAT(value,
Runs outer query format [,culture])
Optional
Examples
FORMAT(OrderDate,'dd/MM/yyyy')
Runs outer query

FORMAT(OrderDate,'dd/MM/yyyy','ja-JP')
Runs outer query

FORMAT(1234.56,
Runs outer query 'D' ,'fr-FR')

Default Cultuer = ‘en-US’

SQL Course | Date & Time Funcitons
 FORMAT
Use Case

SQL Course | Date & Time Funcitons

 FORMAT
Use Case

SQL Course | Date & Time Funcitons

 2025-08-20
Date & Time
[Link]
Runs outer
Format query
Speicifiers

Format Description Result

D Full day name
d Day of the month 8/20/2025
dd Day of the month (two-digit) 20
ddd Abbreviated day name Wed
dddd Full day name Wednesday
M Month number 44044
MM Month number (two-digit) 8
FORMAT MMM Abbreviated month name Aug
MMMM Full month name August
yy Year (two-digit) 25
yyyy Year (four-digit) 2025
hh Hour (12-hour format, two-digit) 06
HH Hour (24-hour format, two-digit) 18
m Minutes August 20
mm Minutes (two-digit) 55
s Seconds 2025-08-20T[Link]
ss Seconds (two-digit) 45
f Fractional seconds (one digit) Wednesday, August 20, 2025 6:55 PM
ff Fractional seconds (two digits) 00
fff Fractional seconds (three digits) 000
tt AM/PM designator PM

SQL Course | Date & Time Funcitons

 2025-08-20
Number
[Link]
Runs outer
Format query
Speicifiers

Format Description Query Result

N Numeric default SELECT FORMAT(1234.56, 'N') 1,234.56
P Percentage SELECT FORMAT(1234.56, 'P') 123,456.00 %
C Currency SELECT FORMAT(1234.56, 'C') $1,234.56
E Scientific notation SELECT FORMAT(1234.56, 'E') 1,23E+09
FORMAT F Fixed-point SELECT FORMAT(1234.56, 'F') 1234.56
N0 Numeric no decimals SELECT FORMAT(1234.56, 'N0') 1,235
N1 Numeric one decimal SELECT FORMAT(1234.56, 'N1') 1,234.6
N2 Numeric two decimals SELECT FORMAT(1234.56, 'N2') 1,234.56
N , de_DE Numeric (German) SELECT FORMAT(1234.56, 'N', 'de-DE') 1.234,56
N, en_US Numeric (US) SELECT FORMAT(1234.56, 'N', 'en-US') 1,234.56

SQL Course | Date & Time Funcitons

 CONVERT

Syntax

CONVERT(data_type,
Runs outer query value [,style])

Optional
Examples
CONVERT(INT,
Runs outer query'124')

CONVERT(VARCHAR,
Runs outer query OrderDate, '34')

Default Style = 0

SQL Course | Date & Time Funcitons

 Date Time Datetime2
# Format Example # Format Example # Format Example
1 mm/dd/yy 12/30/25 8 hh:mm:ss [Link] 0 Mon dd yyyy hh:mm AM/PM Dec 30 2025 12:38AM
2 [Link] 25.12.30 14 hh:mm:ss:nnn [Link] 9 Mon dd yyyy hh:mm:ss:nnn AM/PM Dec 30 2025 [Link]M
3 dd/mm/yy 30/12/2025 24 hh:mm:ss [Link] 13 dd Mon yyyy hh:mm:ss:nnn AM/PM 30 Dec 2025 [Link]M
4 [Link] 30.12.25 108 hh:mm:ss [Link] 20 yyyy-mm-dd hh:mm:ss 2025-12-30 [Link]
5 dd-mm-yy 30/12/2025 114 hh:mm:ss:nnn [Link] 21 yyyy-mm-dd hh:mm:ss:nnn 2025-12-30 [Link].840
6 dd-Mon-yy 30-Dec-25 22 mm/dd/yy hh:mm:ss AM/PM 12/30/25 [Link] AM
7 Mon dd, yy Dec 30, 25 25 yyyy-mm-dd hh:mm:ss:nnn 2025-12-30 [Link].840
10 mm-dd-yy 12-30-25 26 yyyy-dd-mm hh:mm:ss:nnn 2025-30-12 [Link].840
11 yy/mm/dd 25/12/1930 27 mm-dd-yyyy hh:mm:ss:nnn 12-30-2025 [Link].840
12 yymmdd 251230 Date
Runs&outer
Time 28
29
mm-yyyy-dd hh:mm:ss:nnn
dd-mm-yyyy hh:mm:ss:nnn
12-2025-30 [Link].840
30-12-2025 [Link].840
23 yyyy-mm-dd 30/12/2025
31 yyyy-dd-mm 2025-30-12 Styles
query 30 dd-yyyy-mm hh:mm:ss:nnn 30-2025-12 [Link].840
32 mm-dd-yyyy 12-30-2025 100 Mon dd yyyy hh:mm AM/PM Dec 30 2025 12:38AM
33 mm-yyyy-dd 12-2025-30 109 Mon dd yyyy hh:mm:ss:nnn AM/PM Dec 30 2025 [Link]M
34 dd-mm-yyyy 30/12/2025 CONVERT 113 dd Mon yyyy hh:mm:ss:nnn 30 Dec 2025 [Link]
35 dd-yyyy-mm 30-2025-12 120 yyyy-mm-dd hh:mm:ss 2025-12-30 [Link]
101 mm/dd/yyyy 12/30/2025 121 yyyy-mm-dd hh:mm:ss:nnn 2025-12-30 [Link].840
102 [Link] 2025.12.30 126 yyyy-mm-dd T hh:mm:ss:nnn 2025-12-30T[Link].840
103 dd/mm/yyyy 30/12/2025 127 yyyy-mm-dd T hh:mm:ss:nnn 2025-12-30T[Link].840
104 [Link] 30.12.2025
105 dd-mm-yyyy 30/12/2025 2025-08-20
106 dd Mon yyyy 30-Dec-25 [Link].840
107 Mon dd, yyyy Dec 30, 2025
110 mm-dd-yyyy 12-30-2025
111 yyyy/mm/dd 30/12/2025
112 yyyymmdd 20251230
 CAST

Syntax

CAST(value AS data_type)
Runs outer query

Examples
CAST('123' AS INT)
Runs outer query

CAST('2025-08-20'
Runs outer query AS DATE)

No format can be specified

SQL Course | Date & Time Funcitons

 DATE
CALCULATIONS

Baraa Khatib Salkini

YouTube | DATA WITH BARAA
SQL Course | Date & Time Funcitons
 DATEADD

Syntax

DATEADD(part,
Runs outer query interval, date)

Examples
DATEADD(year,
Runs outer query 2, OrderDate)

DATEADD(month,
Runs outer query -4, OrderDate)

SQL Course | Date & Time Funcitons

 DATEDIFF

SQL Course | Date & Time Funcitons

 DATEDIFF

Syntax

DATEDIFF(part,
Runs outer query start_date, end_date)

Examples
DATEDIFF(year,
Runs outer query OrderDate, ShipDate)

DATEDIFF(day,
Runs outer query OrderDate, ShipDate)

SQL Course | Date & Time Funcitons

 ISDATE

Syntax

ISDATE(value)
Runs outer query

ISDATE('2025-08-20')
Runs outer query

ISDATE(2025)
Runs outer query

SQL Course | Date & Time Funcitons

 NULL
FUNCTIONS

Baraa Khatib Salkini

YouTube | DATA WITH BARAA
SQL Course | NULL Funcitons
 SQL
Runs outer
Functions
query

Single-Row Multi-Row
Functions Functions

String Numeric Aggregate Window

Functions Functions Functions Functions
(Basics) (Advanced)
Date & Time NULL
Functions Functions

SQL Course | NULL Functions

 What are NULLS?

SQL Course | NULL Functions

 Where NULLS Come From?

SQL Course | NULL Functions

 NULL FUNCTIONS

SQL Course | NULL Functions

SQL Course | NULL Functions
 ISNULL

replaces NULL with the specified replacement value.

SQL Course | NULL Functions

 COALESCE

returns the first non-NULL value from the given expressions.

SQL Course | NULL Functions

 NULLIF

returns NULL if both values are equal; otherwise, it returns first value.

SQL Course | NULL Functions

 IS NULL

check if a value is NULL.

SQL Course | NULL Functions

 IS NULL

In SQL, use IS NULL instead of = NULL to correctly filter rows with NULL values.

SQL Course | NULL Functions

 JOINS & IS NULL

SQL Course | NULL Functions

 NULL vs Empty vs Blank

SQL Course | NULL Functions

 CASE STATEMENT
CASE WHEN

Baraa Khatib Salkini

YouTube | DATA WITH BARAA
SQL Course | Case Statement
 CASE STATEMENT
Syntax

The start of logic

Result, if the condition is true

CASE

Condition to be evaluated WHEN condition1 THEN result1

WHEN condition2 THEN result2

Default Value (Optional):

…
if none of the WHEN ELSE result
conditions are true

END

The End of logic

SQL Course | Case Statement

 Use Case: Derive New Columns

The CASE statement in SQL categorizes values based on conditions

High
Sales

100
CASE
WHEN Sales >= 100 THEN 'High' 50
Medium
WHEN Sales >= 50 THEN 'Medium'
75
ELSE 'Low'
90
END
150
Low

SQL Course | Case Statement

 Transformation & Standardization

The CASE statement in SQL is used for data transformation and

standardization by mapping specific values to standardized formats.

CASE
WHEN Country = 'Germany' THEN 'DE' CASE
WHEN Country = 'F' THEN 'Female'
WHEN Country = 'France' THEN 'FR'
WHEN Country = 'M' THEN 'Male'
WHEN Country = 'Italy' THEN 'IT'
ELSE 'n/a'
ELSE 'n/a'
END
END

Country CaseWhen Gender CaseWhen

Germany DE F Female

France FR M Male

Italy IT Null n/a

SQL Course | Case Statement

 Column Name
to be evaluated (Only One) Column Value
To be compared

CASE CASE Country

WHEN Country = 'Germany' THEN 'DE' WHEN 'Germany' THEN 'DE'
WHEN Country = 'India' THEN 'IN' WHEN 'India' THEN 'IN'
WHEN Country = 'United States' THEN 'US' WHEN 'United States' THEN 'US'
WHEN Country = 'France' THEN 'FR' WHEN 'France' THEN 'FR'
WHEN Country = 'Italy' THEN 'IT' WHEN 'Italy' THEN 'IT'
ELSE 'n/a' ELSE 'n/a'

END END

Full Form Quick Form

SQL Course | Case Statement

SQL Course | Case Statement
SQL Course | Case Statement
 AGGREGATE
FUNCTIONS

Baraa Khatib Salkini

YouTube | DATA WITH BARAA
SQL Course | Aggregate Functions
 Aggregate
Functions

Data
Types

Any Types COUNT Counts the number of rows

SUM Add up all values in a column

Only
Numbers
AVG Find the average of values

MAX Gets the highest value

Any Types
MIN Gets the lowest value

SQL Course | Aggregate Functions

 Aggregate
Functions

SQL Course | Aggregate Functions

 WINDOW FUNCTIONS
BASICS

Baraa Khatib Salkini

YouTube | DATA WITH BARAA
SQL Course | Window Functions
SQL Course | Window Functions Basics
 GROUP BY

Aggregates and groups rows based on column/s into summary rows

SQL Course | Window Functions Basics |GROUP BY vs WINDOW

 WINDOW
Functions

Compute aggregates but keep details of individual rows at the same time

SQL Course | Window Functions Basics |GROUP BY vs WINDOW

 GROUP BY Window
Functions

It collapse groups of rows into a single row It doesn’t collapse rows into a single row
(Group-Level-Calculations) (Row-Level-Calculations)

SQL Course | Window Functions Basics |GROUP BY vs WINDOW

 GROUP BY WINDOW
Functions Functions

COUNT(expr) COUNT(expr)
SUM(expr) SUM(expr)
Aggregate MAX(expr) Aggregate
Functions
MAX(expr)
Functions
MIN(expr) MIN(expr)
MIN(expr) MIN(expr)

ROW_NUMBER()
RANK()

Rank DENSE_RANK()
Functions CUME_DIST()
PERCENT_RANK()
NTILE(n)

LEAD(expr,offset,default)
Value LAG(expr,offset,default)
(Analytics)
Functions FIRST_VALUE(expr)
FIRST_VALUE(expr)
SQL Course | Window Functions Basics |GROUP BY vs WINDOW
SQL Course | Window Functions Basics |GROUP BY vs WINDOW
 Window
Syntax

Over Clause

Window Partition Order Frame

Function Clause Clause Clause

SQL Course | Window Functions Basics

 Window
Syntax

AVG(Sales) OVER ( PARTITION BY Category ORDER BY OrderDate ROWS UNBOUNDED PRECEDING )

 Window
Syntax

Calculation used
on the Window
Window
Function

AVG(Sales) OVER ( PARTITION BY Category ORDER BY OrderDate ROWS UNBOUNDED PRECEDING )

SQL Course | Window Functions Basics |Functions

SQL Course | Window Functions Basics |Functions
 COUNT(expr)
SUM(expr)
Aggregate MAX(expr)
Functions
MIN(expr)
MIN(expr)

ROW_NUMBER()
RANK()

Window Rank DENSE_RANK()

Functions Functions CUME_DIST()
PERCENT_RANK()
NTILE(n)

LEAD(expr,offset,default)
Value LAG(expr,offset,default)
(Analytics)
Functions FIRST_VALUE(expr)
FIRST_VALUE(expr)

SQL Course | Window Functions Basics |Functions

 Window
Syntax

Calculation used
on the Window
Window
Function

AVG(Sales) OVER ( PARTITION BY Category ORDER BY OrderDate ROWS UNBOUNDED PRECEDING )

Function
Expression

SQL Course | Window Functions Basics |Expression

SQL Course | Window Functions Basics |Expression
 Window
Expressions

Empty RANK() OVER (ORDER BY OrderDate)

Column AVG(Sales) OVER (ORDER BY OrderDate)

Number NTEIL(2) OVER (ORDER BY OrderDate)

Multiple Arguments LEAD(Sales,2,10) OVER (ORDER BY OrderDate)

Conditional Logic SUM(CASE WHEN Sales > 100 THEN 1 ELSE 0 END) OVER (ORDER BY OrderDate)

SQL Course | Window Functions Basics |Expression

 Expression

COUNT(expr) All Data Type

SUM(expr)
Aggregate MAX(expr)
Functions Numeric
MIN(expr)
MIN(expr)

ROW_NUMBER()
RANK()

Window Rank DENSE_RANK() Empty

Functions Functions CUME_DIST()

PERCENT_RANK()
NTILE(n) Numeric

LEAD(expr,offset,default)
Value LAG(expr,offset,default)
(Analytics) All Data Type
Functions FIRST_VALUE(expr)
FIRST_VALUE(expr)
SQL Course | Window Functions Basics |Expression
 Window
Syntax

Calculation used Define the

on the Window Window
Window Over
Function Clause

AVG(Sales) OVER ( PARTITION BY Category ORDER BY OrderDate ROWS UNBOUNDED PRECEDING )

Function
Expression

SQL Course | Window Functions Basics |OVER

SQL Course | Window Functions Basics |OVER
 Window
Syntax

Calculation used Define the

on the Window Window
Window Over
Function Clause

AVG(Sales) OVER ( PARTITION BY Category ORDER BY OrderDate ROWS UNBOUNDED PRECEDING )

Function Partition
Expression Clause

Divides the dataset into

windows (Partitions)

SQL Course | Window Functions Basics |Partition By

SQL Course | Window Functions Basics |Partition By
 Partition By

PARTITION BY divides the rows into groups, based on the column/s

SUM(Sales) OVER() SUM(Sales) OVER(PARTITION BY Product)

Calculation is done Window

on entier Dataset (1)

Calculation is
done individually
on each window

Window
(2)

SQL Course | Window Functions Basics |Partition By

 Partition By

PARTITION BY divides the rows into groups, based on the column/s

Without Total sales across all rows (Entire Result Set)

Partition By SUM(Sales) OVER ()

Total sales for each Product

Partition By
Single Column SUM(Sales) OVER (PARTITION BY Product)

Total sales for each combination of Product and Order Status

Partition By
Combined-Columns SUM(Sales) OVER (PARTITION BY Product, OrderStatus)

SQL Course | Window Functions Basics |Partition By

 Partition
Expression
Clause

COUNT(expr) All Data Type

SUM(expr)
Aggregate MAX(expr)
Functions Numeric
MIN(expr)
MIN(expr)

ROW_NUMBER()
RANK()

Window Rank DENSE_RANK() Empty Optional

Functions Functions CUME_DIST()

PERCENT_RANK()
NTILE(n) Numeric

LEAD(expr,offset,default)
Value LAG(expr,offset,default)
(Analytics) All Data Type
Functions FIRST_VALUE(expr)
FIRST_VALUE(expr)

SQL Course | Window Functions Basics |Partition By

 Window
Syntax

Calculation used Define the

on the Window Window
Window Over
Function Clause

AVG(Sales) OVER ( PARTITION BY Category ORDER BY OrderDate ROWS UNBOUNDED PRECEDING )

Function Partition Order

Expression Clause Clause

Divides the dataset into Sort the data

windows (Partitions) in a window

SQL Course | Window Functions Basics |Order By

SQL Course | Window Functions Basics |Order By
 Partition Order
Expression
Clause Clause

COUNT(expr) All Data Type

SUM(expr)
Aggregate MAX(expr) Optional
Functions Numeric
MIN(expr)
MIN(expr)

ROW_NUMBER()
RANK()

Window Rank DENSE_RANK() Empty Optional

Required
Functions Functions CUME_DIST()
PERCENT_RANK()
NTILE(n) Numeric

LEAD(expr,offset,default)
Value LAG(expr,offset,default)
(Analytics) All Data Type Required
Functions FIRST_VALUE(expr)
FIRST_VALUE(expr)
 Window
Syntax

Calculation used Define the

on the Window Window
Window Over
Function Clause

AVG(Sales) OVER ( PARTITION BY Category ORDER BY OrderDate ROWS UNBOUNDED PRECEDING )

Function Partition Order Frame

Expression Clause Clause Clause

Divides the dataset into Sort the data Define a subset of

windows (Partitions) in a window rows in a window

SQL Course | Window Functions Basics |Frame

 Frame Clause

Entire Data Entire Data Entire Data

1# Window 1# Window Frame

2# Window 2# Window
Frame

SQL Course | Window Functions Basics |Frame

 Partition Order Frame
Expression
Clause Clause Clause

COUNT(expr) All Data Type

SUM(expr)
Aggregate AVG(expr) Optional Optional
Functions Numeric
MAX(expr)
MIN(expr)

ROW_NUMBER()
RANK()

Window Rank DENSE_RANK() Empty Optional

Required Not allowed
Functions Functions CUME_DIST()
PERCENT_RANK()
NTILE(n) Numeric

LEAD(expr,offset,default)
Not allowed
Value LAG(expr,offset,default)
(Analytics) All Data Type Required
Functions FIRST_VALUE(expr) Optional

Should be
FIRST_VALUE(expr) used
 Frame

AVG(Sales) OVER (PARTITION BY Category ORDER BY OrderDate

ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING)

Frame Types Frame Boundary Frame Boundary

(Lower Value) (Higher Value)
ROWS
CURRENT ROW CURRENT ROW
RANGE
N PRECEDING N FOLLOWING
UNBOUNDED PRECEDING UNBOUNDED FOLLOWING

 Frame Clause can only be used together with order by clause.

Rules
 Lower Value must be BEFORE the higher Value.

SQL Course | Window Functions Basics |Frame

 Frame

Entire Partion is Used

No

ORDER BY Used?
Runs outer query

Yes

RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW

SQL Course | Window Functions Basics |Frame

 Frame

SQL Course | Window Functions Basics |Frame

 Window
Rules

SQL Course | Window Functions Basics |Rules

 Window
Rules

Inner Window Function

AVG( SUM(Sales) OVER () ) OVER (ORDER BY OrderDate )

Outer Window Function

Not allowed to nest

window functions !

SQL Course | Window Functions Basics |Rules

 Window Functions

Aggregate Rank Value

SUM() ROW_NUMBER() LAG()

AVG() RANK() LEAD()
COUNT() DENSE_RANK() FIRST_VALUE()
MAX() NTILE() LAST_VALUE()
MIN() CUME_DIST()
PERCENT_RANK()

Return a specific value in a

Perform calculations on a Assign a rank to each row
window to be compared
set of rows and return a in a window
with the value of current
single aggregated value
row
for each row
 WINDOW AGGREGATE
FUNCTIONS

Baraa Khatib Salkini

YouTube | DATA WITH BARAA
SQL Course | Window Aggregate Functions
 Aggregation is combining multiple
values into a single summary

SQL Course | Window Aggregate Functions

 Aggregate
Functions

AVG(Sales) OVER (PARTITION BY ProductID ORDER BY Sales)

Expression Partition By Order By

is required Is Optional Is Optional
(Only Numeric Values)

SQL Course | Window Aggregate Functions

 Aggregate
Functions

Partition Order Frame

Expression
Clause Clause Clause

COUNT(expr) All Data Type

SUM(expr) NumericValues

Aggregate AVG(expr) NumericValues Optional Optional Optional

Functions
MIN(expr) NumericValues

MAX(expr) NumericValues

SQL Course | Window Aggregate Functions

 Aggregate
Functions

COUNT(expr) Returns the number of Rows in a window COUNT(*) OVER (PARTITION BY Porduct)

SUM(expr) Returns the sum of values in a window SUM(Sales) OVER (PARTITION BY Porduct)

Aggregate
AVG(expr) Returns the average of values in a window SUM(Sales) OVER (PARTITION BY Porduct)
Functions

MIN(expr) Returns the minimum value in a window SUM(Sales) OVER (PARTITION BY Porduct)

MAX(expr) Returns the maximum value in a window SUM(Sales) OVER (PARTITION BY Porduct)

SQL Course | Window Aggregate Functions

 COUNT Function

Returns the number of Rows in a window

COUNT(*) OVER(PARTITION BY Product)

Product Sales Count

Caps 20 3
Caps 10 3 3 Orders for Caps
Caps 5 3

Gloves 30 3
Gloves 70 3 3 Orders for Gloves
Gloves 40 3

SQL Course | Window Aggregate Functions | COUNT

 COUNT Function

Count the number of Rows Count the number of Rows

including NULLs excluding NULLs

COUNT(*) OVER(PARTITION BY Product)

COUNT(Sales) OVER(PARTITION BY Product)
COUNT(1) OVER(PARTITION BY Product)

Column

Product Sales Count Product Sales Count

Caps 20 3 Caps 20 3
Caps 10 3 Caps 10 3
Caps 5 3 Caps 5 3
This Row
This Row Gloves 30 3 won’t be 30
Gloves 2
is counted counted
Gloves 70 3 Gloves 70 2
Gloves NULL 3 Gloves NULL 2

SQL Course | Window Aggregate Functions | COUNT

 COUNT Function

SQL Course | Window Aggregate Functions | COUNT

 SUM Function

Returns the sum of values in a window

SUM(Sales) OVER(PARTITION BY Product)

* Is not allowed!

Product Sales SUM

Caps 20 35
Caps 10 35 20 + 10 + 5= 35
Caps 5 35

Gloves 30 140
Gloves 70 140 30 + 70 + 40= 140
Gloves 40 140

SQL Course | Window Aggregate Functions | SUM

 SUM Function

SQL Course | Window Aggregate Functions | SUM

 COUNT Function

Perform calculations on a set of rows and return a single aggregated value for each row

Product Sales Product Sales SUM

Caps 20 20 + 10 + 5= 35 Caps 20 35
Caps 10 35 Caps 10 35
Caps 5 Caps 5 35

Gloves 30 30 + 70 + 40= 140 Gloves 30 140

Gloves 70 140 Gloves 70 140
Gloves 40 Gloves 40 140

SQL Course | Window Aggregate Functions | COUNT

 AVG Function

Returns the average of values in a window

AVG(Sales) OVER(PARTITION BY Product)

* Is not allowed!

Product Sales Avg

Caps 20 11
20 + 10 + 5
Caps 10 11 = 11
3
Caps 5 11

Gloves 30 46
30 + 70 + 40
Gloves 70 46 = 46
3
Gloves 40 46

SQL Course | Window Aggregate Functions | AVG

 AVG Function

Default Average Function Deal with Nulls using COALESCE to

exclude NULLs include NULLs

AVG(Sales) OVER(PARTITION BY Product) AVG(COALESCE(Sales,0)) OVER(PARTITION BY Product)

Replace NULL with 0

Product Sales AVG Product Sales AVG

Caps 20 11 Caps 20 11
20 + 10 + 5 20 + 10 + 5
Caps 10 11 = 11 Caps 10 11 = 11
3 3
Caps 5 11 Caps 5 11

Gloves 30 50 Gloves 30 50
30 + 70 30 + 70 + 0
Gloves 70 50 = 50 Gloves 70 50 = 33
2 3
Gloves NULL 50 Gloves 0 50

SQL Course | Window Aggregate Functions | AVG

 AVG Function

SQL Course | Window Aggregate Functions | AVG

 MIN MAX

Returns the minimum value in a window Returns the maximum value in a window

MIN(Sales) OVER(PARTITION BY Product) MAX(Sales) OVER(PARTITION BY Product)

Product Sales MIN Product Sales MAX

Caps 20 5 5 is the Caps 20 20 20 is the

Caps 10 5 lowest sales Caps 10 20 highest sales
Caps 5 5 for Caps for Caps
Caps 5 20

Gloves 30 30 30 70
30 is the Gloves
70 is the
Gloves 70 30 lowest sales Gloves 70 70 highest sales
Gloves 40 30 for Gloves Gloves 40 70 for Gloves

SQL Course | Window Aggregate Functions | MIN & MAX

 MAX & MIN Function

SQL Course | Window Aggregate Functions | MAX& MIN

SQL Course | Window Aggregate Functions |Cumulative Analysis
SQL Course | Window Aggregate Functions
 Running Total Rolling Total
Summarize all values from Summarize a fixed number of consecutive
the first row up to the current row rows calculated within a moving window

SUM(Sales) OVER( SUM(Sales) OVER(

ORDER BY Month) ORDER BY Month ROWS 2 PRECEDING)
Default Frame
RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW

Month Sales SUM Month Sales SUM

Current Current
Row Jan 20 20 Row Jan 20 20
Feb 10 Feb 10
Mar 30 Mar 30
Apr 5 Apr 5
Jun 70 Jun 70
Jul 40 Jul 40

SQL Course | Window Aggregate Functions |Cumulative Analysis

 Running Total Rolling Total
Summarize all values from Summarize a fixed number of consecutive
the first row up to the current row rows calculated within a moving window

SUM(Sales) OVER( SUM(Sales) OVER(

ORDER BY Month) ORDER BY Month ROWS 2 PRECEDING)
Default Frame
RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW

Month Sales SUM Month Sales SUM

Jan 20 20 Jan 20 20
Current Current
Feb 10 30 Feb 10 30
Row Row
Mar 30 Mar 30
Apr 5 Apr 5
Jun 70 Jun 70
Jul 40 Jul 40

SQL Course | Window Aggregate Functions |Cumulative Analysis

 Running Total Rolling Total
Summarize all values from Summarize a fixed number of consecutive
the first row up to the current row rows calculated within a moving window

SUM(Sales) OVER( SUM(Sales) OVER(

ORDER BY Month) ORDER BY Month ROWS 2 PRECEDING)
Default Frame
RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW

Month Sales SUM Month Sales SUM

Jan 20 20 Jan 20 20
Feb 10 30 Feb 10 30
Current Current
Row Mar 30 60 Row Mar 30 60

Apr 5 Apr 5
Jun 70 Jun 70
Jul 40 Jul 40

SQL Course | Window Aggregate Functions |Cumulative Analysis

 Running Total Rolling Total
Summarize all values from Summarize a fixed number of consecutive
the first row up to the current row rows calculated within a moving window

SUM(Sales) OVER( SUM(Sales) OVER(

ORDER BY Month) ORDER BY Month ROWS 2 PRECEDING)
Default Frame
RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW

Month Sales SUM Month Sales SUM

Moving
Jan 20 20
Window ! Jan 20 20
Feb 10 30 Feb 10 30

Mar 30 60 Mar 30 60
Current Current
Row Apr 5 65 Row Apr 5 45
Jun 70 Jun 70
Jul 40 Jul 40

SQL Course | Window Aggregate Functions |Cumulative Analysis

 Running Total Rolling Total
Summarize all values from Summarize a fixed number of consecutive
the first row up to the current row rows calculated within a moving window

SUM(Sales) OVER( SUM(Sales) OVER(

ORDER BY Month) ORDER BY Month ROWS 2 PRECEDING)
Default Frame
RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW

Month Sales SUM Month Sales SUM

Jan 20 20 Jan 20 20
Feb 10 30 Feb 10 30

Mar 30 60 Mar 30 60

Apr 5 65 Apr 5 45
Current Current
Row Jun 70 135 Row Jun 70 105
Jul 40 Jul 40

SQL Course | Window Aggregate Functions |Cumulative Analysis

 Running Total Rolling Total
Summarize all values from Summarize a fixed number of consecutive
the first row up to the current row rows calculated within a moving window

SUM(Sales) OVER( SUM(Sales) OVER(

ORDER BY Month) ORDER BY Month ROWS 2 PRECEDING)
Default Frame
RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW

Month Sales SUM Month Sales SUM

Jan 20 20 Jan 20 20
Feb 10 30 Feb 10 30

Mar 30 60 Mar 30 60

Apr 5 65 Apr 5 45
Jun 70 135 Jun 70 105
Current Current
Row Jul 40 175
Row
Jul 40 115

SQL Course | Window Aggregate Functions |Cumulative Analysis

 Comparision Use Cases

Compare the current value and aggregated value of window functions

Month Sales

Jan 20
Highest Lowest Average
Feb 10 Total Value Value
Current Row Mar 30 175 70 5 29
SUM MAX MIN AVG
Apr 5
Jun 70
Jul 40

SQL Course | Window Aggregate Functions

 WINDOW RANKING
FUNCTIONS

Baraa Khatib Salkini

YouTube | DATA WITH BARAA
SQL Course | Window Rank Functions
SQL Course | Window Ranking Functions
 Ranking Function

RANK() OVER(PARTITION BY ProductID ORDER BY Sales)

Expression Partition By Order By

must be empty Is Optional Is required

SQL Course | Window Ranking Functions

 Ranking Function

Partition Order Frame

Expression
Clause Clause Clause

ROW_NUMBER()

RANK()

DENSE_RANK() Empty
Rank Optional Required Not allowed
Functions
CUME_DIST()

PERCENT_RANK()

NTILE(n) Number

SQL Course | Window Ranking Functions

 Ranking Function

ROW_NUMBER() Assign a unique number to each in a window ROW_NUMBER() OVER (ORDER BY Sales)

RANK() Assign a rank to each row in a window, with gaps RANK() OVER (ORDER BY Sales)

DENSE_RANK() Assign a rank to each row in a window, without gaps DENSE_RANK() OVER (ORDER BY Sales)
Rank
Functions calculates the cumulative distribution of a value within
CUME_DIST() a set of values
CUME_DIST() OVER (ORDER BY Sales)

PERCENT_RANK() Returns the percentile ranking number of a row. PERCENT_RANK() OVER (ORDER BY Sales)

Divides the rows into a specified number of

NTILE(n) approximately equal groups
NTILE(2) OVER (ORDER BY Sales)

SQL Course | Window Ranking Functions

 ROW_NUMBER

Assign a unique sequential integer to each row with in a window

ROW_NUMBER() OVER (ORDER BY Sales DESC)

Sales Rank

100 1

80 2 Row_Number() assigns a
We have
a tie here! 80 3
unique rank to each of row

50 4

20 5

SQL Course | Window Ranking Functions

 RANK

Assign a rank to each row with in a window

RANK() OVER(ORDER BY Sales DESC)

Sales Rank

100 1

80 2 RANK () assigns a same

We have
a tie here! 80 2 rank for both of them

50 4 RANK () leaves a GAP in

20 5
Ranking after a tie

SQL Course | Window Ranking Functions

 DENSE_RANK

Assign a rank to each row with in a window, but does not leave gaps in the ranking

DENSE_RANK() OVER(ORDER BY Sales DESC)

Sales Rank

100 1

80 2 DENSE_RANK () assigns a
We have
a tie here! 80 2 same rank for both of them

50 3 DENSE_RANK () doesn’t leaves

20 5
a GAP in Ranking after a tie

SQL Course | Window Ranking Functions

 Which One To Use?

Handle tied values?

Runs outer query

Yes No

Leave ROW_NUMBER()
Runsgaps in query
outer ranking?

Yes No

RANK() DENSE_RANK()

SQL Course | Window Ranking Functions

SQL Course | Window Ranking Functions | NTILE
 NTILE Use Case

SQL Course | Window Ranking Functions | NTILE

 NTILE

Divides the rows into a specified number of approximately equal groups (buckets)

NTILE(2) OVER (ORDER BY Sales DESC)

Number of Buckets

NTILE
Sales
(2) Number of Rows
Bucket Size =
100 1 (Nr of Rows in each Bucket) Number of Buckets
Bucket (1)
80 1
4
80 1
Bucket Size = =2
2
Bucket (2)
50 2

SQL Course | Window Ranking Functions | NTILE

 NTILE

Divides the rows into a specified number of approximately equal groups (buckets)

NTILE(2) OVER (ORDER BY Sales DESC)

Number of Buckets

NTILE
Sales
(2) Number of Rows
Bucket Size =
100 1 (Nr of Rows in each Bucket) Number of Buckets

Bucket (1) 80 1
5
80 1 Bucket Size = = 2.5
2
50 2
Bucket (2)
20 2 Larger groups come first then smaller groups

SQL Course | Window Ranking Functions | NTILE

 NTILE

Divides the rows into a specified number of approximately equal groups (buckets)

NTILE(3) OVER (ORDER BY Sales DESC)

Number of Buckets

NTILE
Sales
(3) Number of Rows
Bucket Size =
100 1 (Nr of Rows in each Bucket) Number of Buckets
Bucket (1)
80 1
5
80 1 Bucket Size = = 1.7
3
Bucket (2)
50 2

Bucket (3) 20 2 Larger groups come first then smaller groups

SQL Course | Window Ranking Functions | NTILE

 CUME_DIST

SQL Course | Window Ranking Functions | CUME_DIST

SQL Course | Window Ranking Functions | CUME_DIST & PERCENT_RANK
 CUME_DIST

Calculates the relative position of a specified value in a group of values.

CUME_DIST() OVER (ORDER BY Sales)

Sales Dist
Number of Rows less than or equal to X
Current Row
CUME_DIST(x) =
20 0,2 Total Number of Rows

50

60 1
CUME_DIST(20) = = 0,2
5
80

100

SQL Course | Window Ranking Functions | CUME_DIST

 CUME_DIST

Calculates the relative position of a specified value in a group of values.

CUME_DIST() OVER (ORDER BY Sales)

Sales Dist
Number of Rows less than or equal to X
CUME_DIST(x) =
20 0,2 Total Number of Rows

50 0,4

Current Row 60 0,6 3

CUME_DIST(60) = = 0,6
5
80

100

SQL Course | Window Ranking Functions | CUME_DIST

 CUME_DIST

Calculates the relative position of a specified value in a group of values.

CUME_DIST() OVER (ORDER BY Sales)

Sales Dist
Number of Rows less than or equal to X
CUME_DIST(x) =
20 0,2 Total Number of Rows

50 0,4

60 0,6 5
CUME_DIST(100) = =1
5
80 0,8

Current Row 100 1 It returns values greater than 0 and less and equal to 1

SQL Course | Window Ranking Functions | CUME_DIST

 PERCENT_RANK

Returns the percentile ranking number of a row

PERCENT_RANK() OVER (ORDER BY Sales)

Sales Rank Dist

Rank of X-1
1
PERCENT_RANK(x) =
Current Row 20 0
Total Number of Rows- 1
50 2

60 3 0
PERCENT_RANK(20) = =0
4
80 4

100 5

SQL Course | Window Ranking Functions | PERCENT_RANK

 PERCENT_RANK

Returns the percentile ranking number of a row

PERCENT_RANK() OVER (ORDER BY Sales)

Sales Rank Dist

Rank of X-1
1
PERCENT_RANK(x) =
20 0
Total Number of Rows- 1
50 2 0,25

Current Row 60 3 0,5 2

PERCENT_RANK(60) = = 0.5
4
80 4

100 5

SQL Course | Window Ranking Functions | PERCENT_RANK

 PERCENT_RANK

Returns the percentile ranking number of a row

PERCENT_RANK() OVER (ORDER BY Sales)

Sales Rank Dist

Rank of X-1
1
PERCENT_RANK(x) =
20 0
Total Number of Rows- 1
50 2 0,25

60 3 0,5 4
PERCENT_RANK(100) = =1
4
80 4 0,75

Current Row 100 5 1 It returns values between 0 and 1

SQL Course | Window Ranking Functions | PERCENT_RANK

 PERCENT_RANK

Returns the percentile ranking number of a row

PERCENT_RANK() OVER (ORDER BY Sales)

Sales Rank Dist

Lowest Position
20 1 0

50 2 0,25

60 3 0,5

80 4 0,75 Highest Position

Current Row 100 5 1

SQL Course | Window Ranking Functions | PERCENT_RANK

 WINDOW VALUE
FUNCTIONS

Baraa Khatib Salkini

YouTube | DATA WITH BARAA
SQL Course | Window Value Functions
 Window Functions

Aggregate Rank Value

SUM() ROW_NUMBER() LAG()

AVG() RANK() LEAD()
COUNT() DENSE_RANK() FIRST_VALUE()
MAX() NTILE() LAST_VALUE()
MIN() CUME_DIST()
PERCENT_RANK()

Return a specific value in a

Perform calculations on a Assign a rank to each row
window to be compared
set of rows and return a in a window
with the value of current
single aggregated value
row
for each row
SQL Course | Window Value Functions
 Value Functions

LEAD(expr,offset,default) Returns the value from a previews row LEAD(Sales,2,0) OVER (ORDER BY OrderDate)

Value LAG(expr,offset,default) Returns the value from a subsequent row LAG(Sales,2,0) OVER (ORDER BY OrderDate)
(Analytics)
Functions FIRST_VALUE(expr) Returns the first value in a window FIRST_VALUE(Sales) OVER (ORDER BY OrderDate)

LAST_VALUE(expr) Returns the last value in a window FIRST_VALUE(Sales) OVER (ORDER BY OrderDate)

SQL Course | Window Value Functions

 Value Functions

Partition Order Frame

Expression
Clause Clause Clause

LEAD(expr,offset,default)
Not allowed
Value LAG(expr,offset,default)
(Analytics) All Data Type Optional Required
Functions FIRST_VALUE(expr) Optional

Should be
LAST_VALUE(expr) used

SQL Course | Window Value Functions

SQL Course | Window Value Functions
 Value Functions

LAG (2) LEAD (2) FIRST_VALUE () Last_VALUE ()

Jan 100

Feb 50

Mar 75

Current Row Apr 90 50 110 100 180

Jun 150

Jul 110

Aug 180

SQL Course | Window Value Functions

 Value Functions

Return a specific value in a window to be compared with the value of current row

FIRST_VALUE () Jan 100

Feb 50

Mar 75 LAG (2)

Apr 90
Current Row Jun 150

Jul 110

Aug 180 LEAD (2)

Sep 200

LAST_VALUE () Oct 150

SQL Course | Window Value Functions

 LEAD & LAG

Partition By Order By
Is Optional Is Required

LEAD(Sales, 2 , 10 ) OVER(PARTITION BY ProductID ORDER BY OrderDate)

Default Value (Optional)

Expression Returns default value if next/previous row is not available!
is required Default = NULL
(Any Data Type)
Offset (Optional)
Number of rows forward or backward from current row
default = 1

SQL Course | Window Value Functions |LEAD & LAG

 LEAD & LAG

SQL Course | Window Value Functions |LEAD & LAG

 LEAD LAG

Access Next Row Access Previous Row

LEAD(Sales) OVER( ORDER BY Month) LEAD(Sales) OVER( ORDER BY Month)

First Row has No Previous Row !

Month Sales LEAD Month Sales LAG
Current Current
Row Jan 20 20 Jan 20 NULL
Row
Feb 10 Feb 10
Mar 30 Mar 30
Apr 5 Apr 5

SQL Course | Window Value Functions |LEAD & LAG

 LEAD LAG

Access Next Row Access Previous Row

LEAD(Sales) OVER( ORDER BY Month) LEAD(Sales) OVER( ORDER BY Month)

First Row has No Previous Row !

Month Sales LEAD Month Sales LAG

Jan 20 20 Jan 20 NULL

Current
Row
Feb 10 30 Current Feb 10 20
Row
Mar 30 Mar 30
Apr 5 Apr 5

SQL Course | Window Value Functions |LEAD & LAG

 LEAD LAG

Access Next Row Access Previous Row

LEAD(Sales) OVER( ORDER BY Month) LEAD(Sales) OVER( ORDER BY Month)

First Row has No Previous Row !

Month Sales LEAD Month Sales LAG

Jan 20 20 Jan 20 NULL

Feb 10 30 Feb 10 20
Current Current
Row Mar 30 5 Mar 30 10
Row
Apr 5 Apr 5

SQL Course | Window Value Functions |LEAD & LAG

 LEAD LAG

Access Next Row Access Previous Row

LEAD(Sales) OVER( ORDER BY Month) LEAD(Sales) OVER( ORDER BY Month)

First Row has No Previous Row !

Month Sales LEAD Month Sales LAG

Jan 20 20 Jan 20 NULL

Feb 10 30 Feb 10 20
Mar 30 5 Mar 30 10
Current Current
Row Apr 5 NULL Apr 5 30
Row

Last Row has No Next Row !

SQL Course | Window Value Functions |LEAD & LAG

 FIRST & LAST

SQL Course | Window Value Functions |FIRST & LAST

 ADVANCED SQL
Techniques

Baraa Khatib Salkini

YouTube | DATA WITH BARAA
SQL Course | Challenges & Solutions of Complex SQL Projects
SQL Course | Database Architecture
 Subquery

Baraa Khatib Salkini

YouTube | DATA WITH BARAA
SQL Course | Subquery
 How
Subquery
Works

SQL Course | Subquery

 SUBQUERY

SQL Course | Subquery

SQL Course | Subquery
SQL Course | Subquery Result Types
SQL Course | Subquery | How DB Execute Subquery
 Subquery in
FROM Clause

Main Query

SELECT column1, column2,…

FROM ( SELECT column FROM table1 WHERE condition )AS alias

Subquery

SQL Course | Subquery |FROM

 Subquery in
FROM Clause
 Subquery in
SELECT Clause
Main Query

SELECT
Column1,

( SELECT column FROM table1 WHERE condition ) AS alias

FROM table1

Subquery
Rules
Only Scalar Subqueries are allowed to be used

SQL Course | Subquery|SELECT

Subquery in
SELECT
Clause
 Subquery in
WHERE Clause
Comparison Operators
Main Query

SELECT column1, column2,…

FROM table1

WHERE column =( SELECT column FROM table2 WHERE condition )

Rules Subquery
Only Scalar Subqueries are allowed to be used

SQL Course | Subquery|WHERE Comparison

 Subquery in
WHERE Clause
Comparison Operators

= Equal WHERE Sales = (SELECT AVG(Sales) FROM ORDERS)

!= <> Not Equal WHERE Sales != (SELECT AVG(Sales) FROM ORDERS)

> Greater than WHERE Sales > (SELECT AVG(Sales) FROM ORDERS)

< Less than WHERE Sales < (SELECT AVG(Sales) FROM ORDERS)

>= Greater than or equal to WHERE Sales >= (SELECT AVG(Sales) FROM ORDERS)

<= Less than or equal to WHERE Sales <= (SELECT AVG(Sales) FROM ORDERS)

SQL Course | Subquery|WHERE Comparison

 Comparison
Operators

Subquery in
WHERE Clause
Comparison Operators
 Subquery in
WHERE Clause
In Operator
Main Query

SELECT column1, column2,…

FROM table1

WHERE column IN ( SELECT column FROM table2 WHERE condition )

Subquery

SQL Course | Subquery|WHERE IN

 Logical
Operators

IN Checks if a value matches any value in a list WHERE Sales IN (SELECT …)

NOT IN Checks if a value does not matches any value in a list WHERE Sales NOT IN (SELECT …)

EXISTS Checks if subquery returns any rows WHERE EXISTS (SELECT …)

NOT EXISTS Checks if subquery returns no rows WHERE NOT EXISTS (SELECT …)

ANY Returns true if a value matches any value in a list. WHERE Sales < ANY (SELECT …)

ALL Returns true if a value matches all values in a list. WHERE Sales > ALL (SELECT …)
 Subquery in
WHERE Clause
In Operator
 Subquery in
WHERE Clause
ALL Operators

Main Query

SELECT column1, column2,…

FROM table1

WHERE column < ALL( SELECT column FROM table1 WHERE condition )

Subquery

SQL Course | Subquery|WHERE ALL

 Subquery in
WHERE Clause
ANY Operator

Main Query

SELECT column1, column2,…

FROM table1

WHERE column < ANY( SELECT column FROM table1 WHERE condition )

Subquery

SQL Course | Subquery|WHERE ANY

SQL Course | Subquery|Correlated
 Non-Correlated Subquery Correlated Subquery

Definition Subquery is independent of the main query Subquery is dependent of the main query

Executed once and its result is used by the main query Executed for each row processed by the main query
Execution
Can be executed on its Own Can't be executed on its Own.

Easy to use Easier to read Harder to read and more complex

Performance Executed only once leads to better Performance Executed multiple times leads to bad Performance

Usage Static Comparisons, Filtering with Constants Row-by-Row Comparisons, Dynamic Filtering

SQL Course | Subquery|Correlated

 Correlated Subquery in
WHERE Clause
EXISTS Operator Main Query

SELECT column1, column2,…

FROM Table2

WHERE EXISTS ( SELECT 1

FROM Table1

WHERE [Link] = [Link]

)

Subquery

SQL Course | Subquery|Correlated

 How EXISTS Works?

For each row in

Runs outer
Main query
Query

Run outer
Runs Subquery
query

No Result? returns Value ?

Row of Main Query Row of Main Query

Runs outer query Runs outer query
is excluded is included

SQL Course | Subquery|Correlated

 JOINS SUBQUERIES

SELECT *
SELECT o.*
FROM Orders
FROM Orders o
WHERE CustomerID IN
Syntax JOIN Customers c
(SELECT CustomerID
ON [Link] = [Link]
FROM Customers
AND [Link] = 'USA'
WHERE Country = 'USA')

Readability Not easy to read & maintain easy to read & maintain

Performance Fast Slow

Duplicate May lead to duplicate Safer

no risk to have duplicates

Best
Practices
Useful with Larg tables Useful with small tables

SQL Course | Subquery vs Joins

 CTE
Common Table Expression

Baraa Khatib Salkini

YouTube | DATA WITH BARAA
SQL Course | CTE
SQL Course | CTE vs Subquery
SQL Course | CTE
SQL Course | CTE
SQL Course | CTE | How DB Execute CTE
SQL Course | CTE Types
SQL Course | Standalone CTE
 Standalone CTE

SQL Course | Standalone CTE

 Standalone CTE

WITH CTE-Name AS
(
SELECT … CTE Query
FROM … - CTE Definition -

WHERE …
)

SELECT …
FROM CTE-Name Main Query
- CTE Usage -
WHERE …

SQL Course | CTE Syntax

 Multiple CTEs

SQL Course | Multiple CTE

 Multiple CTEs

WITH CTE-Name1 AS
(
CTE Query
- CTE Definition - SELECT …
FROM …
WHERE …
)
, CTE-Name2 AS
(
CTE Query
SELECT …
- CTE Definition - FROM …
WHERE …
)

SELECT …
Main Query FROM CTE-Name1
- CTE Usage - JOIN CTE-Name2
WHERE …
SQL Course | Multiple CTE
SQL Course | Nested CTE
 Nested CTEs

SQL Course | Nested CTE

 Nested CTEs

WITH CTE-Name1 AS
(
SELECT … CTE Query
Standalone CTE - CTE Definition -
FROM …
WHERE …
)
, CTE-Name2 AS
(
NESTED CTE SELECT … CTE Query
- CTE Definition -
FROM CTE-Name1
WHERE …
)

SELECT …
Main Query
FROM CTE-Name2 - CTE Usage -
WHERE …

SQL Course | Nested CTE

SQL Course | Recursive CTE
 Recursive CTE

SQL Course | Recursive CTE

 Recursive CTE

WITH CTE-Name AS
(
SELECT …
Anchor
FROM …
Query
WHERE …
CTE Query
- CTE Definition - UNION ALL
SELECT …
Recursive
FROM CTE-Name Query
WHERE [Break Condition]
)

SELECT …
Main Query FROM CTE-Name
- CTE Usage -
WHERE …

SQL Course | Recursive CTE

SQL Course | Recursive CTE
 Views
Database Object

Baraa Khatib Salkini

YouTube | DATA WITH BARAA
SQL Course | Views
SQL Course | Views | Database Structure
SQL Course | Views | 3 Layers Architecture of Database
SQL Course | Views
SQL Course | Views vs Tables
SQL Course | Views vs Tables
SQL Course | Views | Central Logic
SQL Course | Views vs CTE
SQL Course | Views vs CTE
 VIEWS

DDL
Statement
CREATE VIEW VIEW-NAME AS
(
SELECT …
FROM …
Query WHERE …
)

SQL Course | Views

 Flexibility & Dynamic

SQL Course | Views | Flexibility & Dynamic

 Hide Complexity

SQL Course | Views | Hide Complexity

 Security

SQL Course | Views | Security

 Multiple Languages

SQL Course | Views | Multiple Languages

 Virtual Data Marts

SQL Course | Views | Virtual Data Marts

 CTAS
Create Table As SELECT

Baraa Khatib Salkini

YouTube | DATA WITH BARAA
SQL Course | CTAS
SQL Course | CTAS | What are Tables
SQL Course | CTAS | What are Tables
SQL Course | CTAS | Table Types
SQL Course | CTAS vs CREATE/INSERT
SQL Course | CTAS | Table vs Views
 CREATE / INSERT
syntax

DDL CREATE TABLE Table-Name

Statement (
ID INT,
Name VARCHAR (50)
)

INSERT INTO Table-Name

Insert VALUES (1, ’Frank’)
Statement

SQL Course | CTAS | Create/INSERT Syntax

 CTAS
syntax

DDL
Statement
CREATE TABLE NAME AS
SELECT …
(
SELECT … INTO New-Table
FROM …
Query FROM …
WHERE …
WHERE …
)
MySQL | Postgres | Oracle Sql Server

SQL Course | CTAS Syntax

 Optimize Performance

SQL Course | CTAS | Optimize Performance

 Create Snapshot

You want to preserve

the current state of
data before
performing operations
that might change it.

SQL Course | CTAS | Creating Snapshot

 Physical Data Marts

SQL Course | CTAS | Physical Data Marts

 TEMPORARY
TABLE

SELECT …
INTO # New-Table
FROM …
WHERE …

Sql Server

SQL Course | TEMP Tables

 PERMENANT TEMPORARY
CREATE TABLE TABLE

CREATE TABLE TABLE-NAME AS CREATE TEMPORARY TABLE TABLE-NAME AS

( (
SELECT … SELECT …
FROM … FROM …
WHERE … WHERE …
) )

MySQL | Postgres | Oracle

SQL Course | TEMP Tables

SQL Course | TEMP Tables
 Empty Permenant Temporary
View
Table Table Table

CREATE VIEW View-Name AS SELECT … SELECT …

CREATE TABLE Table-Name
(
( INTO New-Table INTO #New-Table
SELECT …
ID INT,
FROM …
Name VARCHAR (50) FROM … FROM …
WHERE …
) WHERE … WHERE …
)

SQL Course | DB Objects

 Big Picture
All Techniques
Baraa Khatib Salkini
YouTube | DATA WITH BARAA
SQL Course | Comparison
 Stored Procedure

Baraa Khatib Salkini

YouTube | DATA WITH BARAA
SQL Course | Stored Procedure
 Stored Procedure

SQL Course | Stored Procedure

SQL Course | Stored Procedure vs Query
 Stored Procedure vs Python

SQL Course | Stored Procedure vs Python

 Stored Procedure

CREATE PROCEDURE ProcedureName AS

BEGIN
Stored Procedure
Definition
-- SQL STATEMENTS GO HERE

END

Stored Procedure
Execution (Call) EXEC ProcedureName

SQL Course | Stored Procedure | Syntax

 Error Handling
Start

BEGIN TRY
Execute
-- SQL statements that might cause an error
TRY

END TRY

Error
Error?
BEGIN CATCH
-- SQL statements To Handle The Error Execute
No CATCH
END CATCH Error

End

SQL Course | Stored Procedure | Error Handling

 Flow Control

Start

Value is Yes
NULL?

Update
No To Zero

End

SQL Course | Stored Procedure | Flow Control

 Triggers

Baraa Khatib Salkini

YouTube | DATA WITH BARAA
SQL Course | Triggers
 Triggers

SQL Course | Triggers

SQL Course | Trigger Types
 Maintaining Logs

SQL Course | Triggers | Maintaining Logs

 Triggers

CREATE TRIGGER TriggerName ON TableName

WHEN AFTER INSERT, UPDATE, DELETE
BEGIN
WHAT -- SQL STATEMENTS GO HERE
END

SQL Course | Triggers | Syntax