Business SQL - Cheat Sheet

A quick reference guide for key SQL concepts, organized by course modules.

1. 🔍 SQL Basics

1.1 Databases: Supercharged Excel Workbooks

Key Comparisons:

Excel Term	SQL/Database Term
Worksheet/Table	Table
Column	Column/Field
Row	Record
Workbook	Database

Why Use Databases?

Handles millions of rows
Faster analysis
Multi-user access
Better data integrity

Excel vs SQL Tasks:

Excel Task	SQL Equivalent
Filter rows	`WHERE` clause
Select columns	`SELECT` statement
Sort data	`ORDER BY` clause
Calculated columns	Expressions in `SELECT`
PivotTable	`GROUP BY` aggregates

1.2 Your First SELECT Statement

Basic Syntax:

sql

SELECT * FROM table_name;

Key Points:

SELECT * retrieves all columns
FROM specifies the table to query
Equivalent to opening a worksheet in Excel

Example:

sql

SELECT * FROM sales;

Returns:

SaleDate	Product	Amount
2024-05-01	Apples	120
2024-05-01	Oranges	80

1.3 Selecting Specific Columns

Basic Syntax:

sql

SELECT column1, column2 FROM table_name;

Column Aliases:

sql

SELECT column1 AS alias1, column2 AS alias2 FROM table_name;

Key Points:

List columns separated by commas after SELECT
Use AS to rename columns in results
Order of columns in query determines output order

Examples:

sql

SELECT Product, Amount FROM sales;

Returns:

Product	Amount
Apples	120
Oranges	80

sql

SELECT Product AS Item, Amount AS Total FROM sales;

Returns:

Item	Total
Apples	120
Oranges	80

2. ⚙️ Data Filtering

2.1 WHERE: The SQL Filter

Basic Syntax:

sql

SELECT columns FROM table_name
WHERE condition;

Comparison Operators:

Operator	Meaning	Example
=	Equals	Product = 'Apples'
<>	Not equal	Product <> 'Oranges'
>	Greater than	Amount > 100
<	Less than	Amount < 100
>=	Greater or eq.	Amount >= 100
<=	Less or eq.	Amount <= 100

NULL Handling:

sql

WHERE column IS NULL
WHERE column IS NOT NULL

COALESCE Example:

sql

SELECT *, COALESCE(Amount, 0) AS Amount_Defaulted
FROM sales
WHERE COALESCE(Amount, 0) > 100;

Key Points:

WHERE filters rows based on conditions
Use IS NULL/IS NOT NULL for NULL checks
COALESCE provides default values for NULLs

2.2 Number & Range Filters

Range Filtering:

sql

WHERE column BETWEEN value1 AND value2

Multiple Values:

sql

WHERE column IN (value1, value2, ...)

Combining Conditions:

sql

WHERE condition1 AND condition2
WHERE condition1 OR condition2

Examples:

sql

SELECT * FROM sales
WHERE Amount BETWEEN 80 AND 120;

sql

SELECT * FROM sales
WHERE Product IN ('Apples', 'Oranges');

sql

SELECT * FROM sales
WHERE Product = 'Apples' AND Amount > 100;

Key Points:

BETWEEN includes both endpoints
IN matches any value in the list
AND/OR combine multiple conditions

2.3 Text Search with LIKE

Pattern Matching:

sql

WHERE column LIKE '%pattern%'

Wildcards:

% matches any number of characters
_ matches a single character

Examples:

sql

-- Contains "App"
WHERE Product LIKE '%App%'

-- Starts with "App"
WHERE Product LIKE 'App%'

-- Ends with "es"
WHERE Product LIKE '%es'

-- Case-insensitive search
WHERE LOWER(Product) LIKE '%app%'

Key Points:

LIKE enables flexible text pattern matching
Similar to Excel's "contains" filter
Use LOWER() for case-insensitive searches

2.4 Multi-Condition Filtering

Combining Conditions:

sql

WHERE condition1 AND condition2
WHERE condition1 OR condition2

Parentheses Grouping:

sql

WHERE (condition1 AND condition2) OR condition3

Examples:

sql

-- AND example
WHERE Product = 'Apples' AND Amount > 100

-- OR example
WHERE Product = 'Apples' OR Product = 'Oranges'

-- Complex grouping
WHERE (Product = 'Apples' AND Amount > 100) OR Product = 'Oranges'

Common Mistakes:

Using = NULL instead of IS NULL
Forgetting parentheses in complex conditions
Forgetting quotes around text values
Ignoring case sensitivity

Key Points:

Use AND/OR to combine conditions
Parentheses control evaluation order
Always use IS NULL for NULL checks
Quote text values and consider case sensitivity

3. 🔄 Data Transformation

3.1 Sorting with ORDER BY

Basic Syntax:

sql

SELECT columns FROM table_name
ORDER BY column1 [ASC|DESC];

Sorting Directions:

ASC - Ascending (A-Z, 0-9) - Default
DESC - Descending (Z-A, 9-0)

Examples:

sql

-- Single column sort
SELECT * FROM sales
ORDER BY Amount DESC;

-- Multi-column sort
SELECT * FROM sales
ORDER BY Product ASC, Amount DESC;

Key Points:

ORDER BY sorts the final result set
Can sort by multiple columns in sequence
Similar to Excel's sort functionality

3.2 Result Paging with LIMIT

Basic Syntax:

sql

SELECT columns FROM table_name
LIMIT number_of_rows;

Common Uses:

Previewing data without loading full results
Improving query performance
Exporting data samples
Preventing overload from large result sets

Examples:

sql

-- Get first 100 rows
SELECT * FROM sales LIMIT 100;

-- Get top 10 highest amounts
SELECT * FROM sales
ORDER BY Amount DESC
LIMIT 10;

Key Points:

LIMIT controls how many rows are returned
Often used with ORDER BY for meaningful results
Many tools apply default LIMITs for safety

3.3 Column Calculations

Basic Syntax:

sql

SELECT column1, column2, expression AS alias FROM table_name;

Common Operations:

Arithmetic: +, -, *, /
Functions: ROUND(), UPPER(), LOWER()
Concatenation: || or CONCAT()

Examples:

sql

-- Simple calculation
SELECT Product, Amount, Amount * 1.2 AS Amount_With_Tax FROM sales;

-- Using functions
SELECT Product, UPPER(Product) AS Product_Upper FROM sales;

-- Rounding numbers
SELECT Product, ROUND(Amount, 0) AS Rounded_Amount FROM sales;

Key Points:

Calculations are done in the SELECT clause
Use AS to name calculated columns
Supports most Excel-like operations and functions

3.4 Text Manipulation

Basic Syntax:

sql

SELECT text_function(column) FROM table_name;

Common Text Functions:

Concatenation: || or CONCAT()
Substrings: LEFT(), RIGHT(), SUBSTRING()
Case conversion: UPPER(), LOWER()
Length: LENGTH() or LEN()

Examples:

sql

-- Concatenation
SELECT Product || ' - $' || Amount AS Product_Price FROM sales;

-- Substrings
SELECT LEFT(Product, 3) AS First3, SUBSTRING(Product, 2, 3) AS Mid3 FROM sales;

-- Case conversion
SELECT UPPER(Product) AS UpperCase, LOWER(Product) AS LowerCase FROM sales;

-- Text length
SELECT Product, LENGTH(Product) AS Name_Length FROM sales;

Key Points:

Similar functionality to Excel text functions
Syntax may vary slightly between database systems
Useful for formatting and cleaning text data

3.5 Smart Categorization with CASE

Basic Syntax:

sql

SELECT column,
  CASE
    WHEN condition1 THEN result1
    WHEN condition2 THEN result2
    ELSE default_result
  END AS new_column
FROM table_name;

Common Uses:

Creating categories from numeric ranges
Mapping values to labels
Handling NULL values with defaults
Implementing conditional logic

Examples:

sql

-- Simple categorization
SELECT Product, Amount,
  CASE
    WHEN Amount >= 100 THEN 'High'
    WHEN Amount >= 50 THEN 'Medium'
    ELSE 'Low'
  END AS Amount_Category
FROM sales;

-- Value mapping
SELECT Product,
  CASE
    WHEN Product = 'Apples' THEN 'Fruit'
    WHEN Product = 'Carrots' THEN 'Vegetable'
    ELSE 'Other'
  END AS Product_Type
FROM sales;

-- NULL handling
SELECT Product,
  CASE
    WHEN Amount IS NULL THEN 0
    ELSE Amount
  END AS Amount_Filled
FROM sales;

Key Points:

Similar to Excel's IF/IFS functions
Evaluates conditions in order
Requires END to close the CASE statement
Use AS to name the resulting column

4. 📊 Data Aggregation

4.1 Basic Aggregation Functions

Basic Syntax:

sql

SELECT aggregate_function(column) FROM table_name;

Common Functions:

Function	What it does	Example
`SUM()`	Adds up values	`SUM(Amount)`
`AVG()`	Calculates average	`AVG(Amount)`
`COUNT()`	Counts rows	`COUNT(*)`
`MIN()`	Finds smallest value	`MIN(Amount)`
`MAX()`	Finds largest value	`MAX(Amount)`

Examples:

sql

-- Total sales amount
SELECT SUM(Amount) AS Total_Sales FROM sales;

-- Multiple aggregates
SELECT
  COUNT(*) AS Row_Count,
  SUM(Amount) AS Total_Sales,
  AVG(Amount) AS Avg_Sale,
  MIN(Amount) AS Min_Sale,
  MAX(Amount) AS Max_Sale
FROM sales;

Key Points:

Similar to Excel's summary functions and PivotTable values
Can use multiple functions in same query
NULL values are ignored in calculations

4.2 GROUP BY: The Game Changer

Basic Syntax:

sql

SELECT column, aggregate_function(column)
FROM table_name
GROUP BY column;

Examples:

sql

-- Total sales by product
SELECT Product, SUM(Amount) AS Total_Sales
FROM sales
GROUP BY Product;

-- Total sales per day
SELECT SaleDate, SUM(Amount) AS Total_Sales
FROM sales
GROUP BY SaleDate
ORDER BY SaleDate;

Key Rules:

Every non-aggregated column in SELECT must be in GROUP BY
Similar to Excel PivotTable row labels
Can group by multiple columns (comma separated)

Key Points:

GROUP BY lets you summarize data by categories
Essential for creating meaningful reports
Often used with ORDER BY for sorted results

4.3 Multi-level Grouping

Basic Syntax:

sql

SELECT column1, column2, aggregate_function(column)
FROM table_name
GROUP BY column1, column2;

Examples:

sql

-- Total sales by product and date
SELECT Product, SaleDate, SUM(Amount) AS Total_Sales
FROM sales
GROUP BY Product, SaleDate
ORDER BY Product, SaleDate;

Key Points:

Similar to nested row labels in Excel PivotTables
Order of columns in GROUP BY affects grouping
Each combination of grouped columns gets its own row

4.4 Filtering Groups with HAVING

Basic Syntax:

sql

SELECT column, aggregate_function(column)
FROM table_name
GROUP BY column
HAVING condition;

Examples:

sql

-- Products with total sales over 200
SELECT Product, SUM(Amount) AS Total_Sales
FROM sales
GROUP BY Product
HAVING SUM(Amount) > 200;

-- Combining WHERE and HAVING
SELECT Product, SUM(Amount) AS Total_Sales
FROM sales
WHERE SaleDate BETWEEN '2024-05-01' AND '2024-05-31'
GROUP BY Product
HAVING Total_Sales > 200;

Key Points:

HAVING filters groups after aggregation (WHERE filters rows before)
Can reference aggregate functions or column aliases
Similar to filtering PivotTable results in Excel

5. 📅 Advanced Techniques

5.1 Date Handling

Basic Date Filtering:

sql

SELECT * FROM sales
WHERE SaleDate BETWEEN '2024-05-01' AND '2024-05-31';

Date Extraction Functions:

sql

SELECT
  SaleDate,
  YEAR(SaleDate) AS Sale_Year,
  MONTH(SaleDate) AS Sale_Month,
  DAY(SaleDate) AS Sale_Day
FROM sales;

Grouping by Date Parts:

sql

SELECT
  YEAR(SaleDate) AS Year,
  MONTH(SaleDate) AS Month,
  SUM(Amount) AS Total_Sales
FROM sales
GROUP BY YEAR(SaleDate), MONTH(SaleDate)
ORDER BY Year, Month;

Date Arithmetic:

sql

-- Adding days
SELECT SaleDate, SaleDate + 7 AS Week_Later FROM sales;

-- Date difference in days
SELECT EndDate - StartDate AS Days_Diff FROM sales;

Common Database Variations:

Function	PostgreSQL/Oracle	SQL Server	MySQL	Snowflake
Extract Year	`YEAR(date)`	`YEAR(date)`	`YEAR(date)`	`YEAR(date)`
Format Date	`TO_CHAR(date, 'YYYY-MM')`	`FORMAT(date, 'yyyy-MM')`	`DATE_FORMAT(date, '%Y-%m')`	`TO_VARCHAR(date, 'YYYY-MM')`
Add Days	`date + n`	`DATEADD(day, n, date)`	`DATE_ADD(date, INTERVAL n DAY)`	`DATEADD(day, n, date)`

Key Points:

Date functions vary by database - check your documentation
Similar capabilities to Excel date functions
Essential for time-based analysis and reporting

5.2 NULL Value Solutions

Understanding NULL:

Represents missing/unknown data (like Excel blanks)
Not equal to zero or empty string

Filtering NULLs:

sql

-- Find NULL values
SELECT * FROM sales WHERE Amount IS NULL;

-- Exclude NULL values
SELECT * FROM sales WHERE Amount IS NOT NULL;

Replacing NULLs:

sql

-- COALESCE provides default values
SELECT Product, COALESCE(Amount, 0) AS Amount_Filled FROM sales;

-- CASE statement alternative
SELECT Product,
  CASE
    WHEN Amount IS NULL THEN 'Missing'
    ELSE CAST(Amount AS VARCHAR)
  END AS Amount_Status
FROM sales;

Key Points:

Always use IS NULL/IS NOT NULL for NULL checks
COALESCE is the standard way to handle NULLs
CASE provides more flexible NULL handling

5.3 Subquery Fundamentals

What are Subqueries?

Queries nested inside other queries
Similar to helper tables/formulas in Excel
Can be used in WHERE, IN, EXISTS, SELECT clauses

Basic Subquery Syntax:

sql

-- Filter with subquery result
SELECT * FROM sales
WHERE Amount > (SELECT AVG(Amount) FROM sales);

-- IN with subquery
SELECT * FROM sales
WHERE Product IN (SELECT Product FROM featured_products);

-- NOT IN with subquery
SELECT * FROM sales
WHERE Product NOT IN (SELECT Product FROM discontinued_products);

NULL Handling with NOT IN:

sql

-- Safe NOT IN (filters NULLs)
SELECT * FROM sales
WHERE Product NOT IN (
  SELECT Product FROM discontinued_products
  WHERE Product IS NOT NULL
);

-- Alternative using COALESCE
SELECT * FROM sales
WHERE Product NOT IN (
  SELECT COALESCE(Product, '???') FROM discontinued_products
);

Key Points:

Subqueries enable dynamic filtering
NOT IN with NULLs can cause unexpected results
Always handle NULLs in subqueries used with NOT IN

6. 🤝 Table Joins

6.1 INNER JOIN Basics

Excel Analogy: Similar to VLOOKUP between sheets

Basic Syntax:

sql

SELECT t1.column1, t2.column2
FROM table1 t1
INNER JOIN table2 t2
  ON t1.common_column = t2.common_column;

Example:

sql

-- Combine sales with product details
SELECT s.ProductID, p.ProductName, s.Amount
FROM sales s
INNER JOIN products p
  ON s.ProductID = p.ProductID;

Table Aliasing Best Practices:

Use short, meaningful aliases (e.g., s for sales, p for products)
Be consistent with aliases throughout query
Helps disambiguate same-named columns

Key Points:

INNER JOIN returns only matching rows from both tables
Join condition (ON clause) is mandatory
Can join on any matching columns, not just IDs

6.2 LEFT JOIN in Practice

Excel Analogy: Like VLOOKUP that keeps rows with #N/A errors

Basic Syntax:

sql

SELECT t1.column1, t2.column2
FROM table1 t1
LEFT JOIN table2 t2
  ON t1.common_column = t2.common_column;

Example: Include all sales, even without product info

sql

SELECT s.ProductID, p.ProductName, s.Amount
FROM sales s
LEFT JOIN products p
  ON s.ProductID = p.ProductID;

Finding Missing Matches:

sql

-- Products with no sales
SELECT p.ProductID, p.ProductName
FROM products p
LEFT JOIN sales s ON p.ProductID = s.ProductID
WHERE s.ProductID IS NULL;

-- Sales with no product info
SELECT s.ProductID, s.Amount
FROM sales s
LEFT JOIN products p ON s.ProductID = p.ProductID
WHERE p.ProductID IS NULL;

Key Points:

LEFT JOIN keeps all rows from the left table
Missing matches show as NULL values
Use WHERE IS NULL to find unmatched rows

6.3 Multi-Table Joins

Excel Analogy: Like chaining multiple VLOOKUPs across sheets

Basic Syntax:

sql

SELECT t1.col, t2.col, t3.col
FROM table1 t1
JOIN table2 t2 ON t1.key = t2.key
JOIN table3 t3 ON t1.key = t3.key;

Example: Join sales, products, and customers

sql

SELECT s.SaleID, s.Amount, p.ProductName, c.CustomerName
FROM sales s
INNER JOIN products p ON s.ProductID = p.ProductID
INNER JOIN customers c ON s.CustomerID = c.CustomerID;

Best Practices:

Use clear, consistent table aliases (s, p, c)
Join tables in logical order (start with primary table)
Test joins incrementally (add one join at a time)

Key Points:

Can join as many tables as needed
Each join requires an ON condition
Aliases are essential for readability

6.4 Handling Join Duplicates

Common Causes:

Duplicate values in join columns
Many-to-many relationships
Data quality issues

Diagnosing Duplicates:

sql

-- Check for duplicate keys
SELECT join_column, COUNT(*)
FROM table
GROUP BY join_column
HAVING COUNT(*) > 1;

Example Problem:

sql

-- May produce duplicate rows if products has duplicates
SELECT s.SaleID, p.ProductName, s.Amount
FROM sales s
JOIN products p ON s.ProductID = p.ProductID;

Solutions:

Fix data quality at source (remove duplicates)
Use DISTINCT (temporary fix):

sql

SELECT DISTINCT s.SaleID, p.ProductName, s.Amount
FROM sales s
JOIN products p ON s.ProductID = p.ProductID;

Ensure join columns are unique keys

Key Points:

Joins multiply rows when duplicates exist
Always check for duplicates before joining
Prefer fixing data over using DISTINCT

6.5 Migrating Multi-Table Excel to SQL

Excel to SQL Equivalents:

Excel Task	SQL Equivalent
VLOOKUP across sheets	`JOIN`
Combine multiple tables	Multiple `JOIN`s
Remove duplicates	`SELECT DISTINCT`
Filter after joining	`WHERE` after `JOIN`

Example Migration:

sql

-- Excel: Combine sales, products, customers with VLOOKUPs
-- SQL equivalent:
SELECT s.SaleID, s.Amount, p.ProductName, c.CustomerName
FROM sales s
JOIN products p ON s.ProductID = p.ProductID
JOIN customers c ON s.CustomerID = c.CustomerID;

Migration Benefits:

Handles complex relationships more reliably
Automates repetitive multi-table analysis
Provides single source of truth for teams

Key Points:

SQL joins replace error-prone VLOOKUPs
Start by replicating common multi-sheet reports
Document your queries for team collaboration

Business SQL - Cheat Sheet ​

1. 🔍 SQL Basics ​

1.1 Databases: Supercharged Excel Workbooks ​

1.2 Your First SELECT Statement ​

1.3 Selecting Specific Columns ​

2. ⚙️ Data Filtering ​

2.1 WHERE: The SQL Filter ​

2.2 Number & Range Filters ​

2.3 Text Search with LIKE ​

2.4 Multi-Condition Filtering ​

3. 🔄 Data Transformation ​

3.1 Sorting with ORDER BY ​

3.2 Result Paging with LIMIT ​

3.3 Column Calculations ​

3.4 Text Manipulation ​

3.5 Smart Categorization with CASE ​

4. 📊 Data Aggregation ​

4.1 Basic Aggregation Functions ​

4.2 GROUP BY: The Game Changer ​

4.3 Multi-level Grouping ​

4.4 Filtering Groups with HAVING ​

5. 📅 Advanced Techniques ​

5.1 Date Handling ​

5.2 NULL Value Solutions ​

5.3 Subquery Fundamentals ​

6. 🤝 Table Joins ​

6.1 INNER JOIN Basics ​

6.2 LEFT JOIN in Practice ​

6.3 Multi-Table Joins ​

6.4 Handling Join Duplicates ​

6.5 Migrating Multi-Table Excel to SQL ​

Business SQL - Cheat Sheet

1. 🔍 SQL Basics

1.1 Databases: Supercharged Excel Workbooks

1.2 Your First SELECT Statement

1.3 Selecting Specific Columns

2. ⚙️ Data Filtering

2.1 WHERE: The SQL Filter

2.2 Number & Range Filters

2.3 Text Search with LIKE

2.4 Multi-Condition Filtering

3. 🔄 Data Transformation

3.1 Sorting with ORDER BY

3.2 Result Paging with LIMIT

3.3 Column Calculations

3.4 Text Manipulation

3.5 Smart Categorization with CASE

4. 📊 Data Aggregation

4.1 Basic Aggregation Functions

4.2 GROUP BY: The Game Changer

4.3 Multi-level Grouping

4.4 Filtering Groups with HAVING

5. 📅 Advanced Techniques

5.1 Date Handling

5.2 NULL Value Solutions

5.3 Subquery Fundamentals

6. 🤝 Table Joins

6.1 INNER JOIN Basics

6.2 LEFT JOIN in Practice

6.3 Multi-Table Joins

6.4 Handling Join Duplicates

6.5 Migrating Multi-Table Excel to SQL