🗄️ SQL Query Cheat Sheet
Complete SQL Reference for Business Data Analysis
AiPro Institute™ Members Only
📚 SQL Fundamentals
What is SQL?
SQL (Structured Query Language) is the standard language for managing and querying databases. It's essential for working with data stored in systems like MySQL, PostgreSQL, SQL Server, Oracle, and more. If your company has a database, SQL is how you talk to it.
✓ Why Learn SQL for Business:
- Direct Data Access: Query databases without waiting for IT/analysts
- Ad-Hoc Analysis: Answer business questions in real-time
- Report Generation: Create custom reports and dashboards
- Data Integration: Combine data from multiple sources
- Performance: Handle millions of rows efficiently
Basic SQL Structure
SELECT column1, column2 -- What columns to retrieve
FROM table_name -- Which table to query
WHERE condition -- Filter rows
GROUP BY column -- Group rows for aggregation
HAVING condition -- Filter groups
ORDER BY column -- Sort results
LIMIT n; -- Limit number of rows
💡 Execution Order: SQL executes in this order: FROM → WHERE → GROUP BY → HAVING → SELECT → ORDER BY → LIMIT. This explains why you can't use column aliases in WHERE but can in HAVING.
🔍 SELECT Queries (Retrieving Data)
Basic SELECT
Basic EssentialSelect All Columns
-- Select everything from customers table
SELECT *
FROM customers;
-- Limit to first 10 rows
SELECT *
FROM customers
LIMIT 10;
Select Specific Columns
-- Select only name and email
SELECT first_name, last_name, email
FROM customers;
-- Column aliases for better readability
SELECT
first_name AS "First Name",
last_name AS "Last Name",
email AS "Email Address"
FROM customers;
Calculated Columns
-- Calculate revenue
SELECT
product_name,
price,
quantity,
price * quantity AS revenue
FROM sales;
-- String concatenation
SELECT
first_name || ' ' || last_name AS full_name,
email
FROM customers;
DISTINCT (Unique Values)
-- Get unique countries
SELECT DISTINCT country
FROM customers;
-- Count unique values
SELECT COUNT(DISTINCT customer_id) AS total_customers
FROM orders;
WHERE Clause (Filtering)
Basic EssentialComparison Operators
-- Equal to
SELECT * FROM products WHERE category = 'Electronics';
-- Greater than / Less than
SELECT * FROM orders WHERE total_amount > 1000;
SELECT * FROM products WHERE price <= 50;
-- Not equal
SELECT * FROM customers WHERE status != 'Inactive';
SELECT * FROM customers WHERE status <> 'Inactive';
-- Between range
SELECT * FROM products
WHERE price BETWEEN 50 AND 200;
-- In a list
SELECT * FROM customers
WHERE country IN ('USA', 'Canada', 'Mexico');
Multiple Conditions (AND, OR, NOT)
-- AND: Both conditions must be true
SELECT * FROM products
WHERE category = 'Electronics' AND price < 500;
-- OR: Either condition can be true
SELECT * FROM customers
WHERE country = 'USA' OR country = 'Canada';
-- NOT: Negate condition
SELECT * FROM orders
WHERE NOT status = 'Cancelled';
-- Complex conditions with parentheses
SELECT * FROM products
WHERE (category = 'Electronics' OR category = 'Computers')
AND price > 100
AND in_stock = TRUE;
Pattern Matching (LIKE)
-- % = any sequence of characters
-- _ = single character
-- Starts with 'John'
SELECT * FROM customers WHERE first_name LIKE 'John%';
-- Ends with '.com'
SELECT * FROM customers WHERE email LIKE '%.com';
-- Contains 'sales'
SELECT * FROM customers WHERE company LIKE '%sales%';
-- Exactly 5 characters
SELECT * FROM products WHERE sku LIKE '_____';
-- Case-insensitive (varies by database)
SELECT * FROM customers WHERE LOWER(email) LIKE '%@gmail.com';
NULL Values
-- Find NULL values
SELECT * FROM customers WHERE phone IS NULL;
-- Find NOT NULL values
SELECT * FROM customers WHERE phone IS NOT NULL;
-- Replace NULL with default value
SELECT
first_name,
COALESCE(phone, 'No phone') AS phone
FROM customers;
⚠️ Important: Never use
= NULL or != NULL. Always use IS NULL or IS NOT NULL because NULL represents "unknown" and doesn't equal anything (not even itself).
Sorting Results (ORDER BY)
Basic
-- Sort ascending (A-Z, 0-9, default)
SELECT * FROM products
ORDER BY price ASC;
-- Sort descending (Z-A, 9-0)
SELECT * FROM products
ORDER BY price DESC;
-- Sort by multiple columns
SELECT * FROM customers
ORDER BY country ASC, last_name ASC;
-- Sort by calculated column
SELECT
product_name,
price * quantity AS revenue
FROM sales
ORDER BY revenue DESC;
-- NULL handling (NULLS FIRST or NULLS LAST)
SELECT * FROM customers
ORDER BY phone DESC NULLS LAST;
📊 Aggregate Functions
Common Aggregate Functions
Basic EssentialCOUNT, SUM, AVG, MIN, MAX
-- Count total rows
SELECT COUNT(*) AS total_orders
FROM orders;
-- Count non-NULL values
SELECT COUNT(phone) AS customers_with_phone
FROM customers;
-- Sum of column
SELECT SUM(total_amount) AS total_revenue
FROM orders;
-- Average
SELECT AVG(price) AS average_price
FROM products;
-- Minimum and Maximum
SELECT
MIN(price) AS lowest_price,
MAX(price) AS highest_price
FROM products;
-- Multiple aggregates together
SELECT
COUNT(*) AS total_orders,
SUM(total_amount) AS total_revenue,
AVG(total_amount) AS avg_order_value,
MIN(order_date) AS first_order,
MAX(order_date) AS last_order
FROM orders;
GROUP BY (Grouping & Aggregation)
Intermediate EssentialBasic Grouping
-- Total sales per product category
SELECT
category,
SUM(revenue) AS total_sales,
COUNT(*) AS num_products
FROM products
GROUP BY category;
-- Average order value per customer
SELECT
customer_id,
AVG(total_amount) AS avg_order_value,
COUNT(*) AS total_orders
FROM orders
GROUP BY customer_id;
Multiple Columns Grouping
-- Sales by category and region
SELECT
category,
region,
SUM(revenue) AS total_sales,
COUNT(*) AS num_orders
FROM sales
GROUP BY category, region
ORDER BY total_sales DESC;
HAVING Clause (Filter Groups)
-- Find customers with more than 5 orders
SELECT
customer_id,
COUNT(*) AS total_orders,
SUM(total_amount) AS lifetime_value
FROM orders
GROUP BY customer_id
HAVING COUNT(*) > 5;
-- Categories with average price > $50
SELECT
category,
AVG(price) AS avg_price,
COUNT(*) AS num_products
FROM products
GROUP BY category
HAVING AVG(price) > 50;
-- Multiple HAVING conditions
SELECT
category,
SUM(revenue) AS total_sales,
COUNT(*) AS num_products
FROM sales
GROUP BY category
HAVING SUM(revenue) > 10000 AND COUNT(*) > 10;
💡 WHERE vs HAVING: Use WHERE to filter rows before grouping, HAVING to filter groups after aggregation. WHERE is faster because it reduces data before grouping.
🔗 JOINs (Combining Tables)
Types of JOINs
INNER JOIN (Most Common)
Intermediate EssentialReturns only matching rows from both tables.
-- Get orders with customer information
SELECT
o.order_id,
o.order_date,
o.total_amount,
c.first_name,
c.last_name,
c.email
FROM orders o
INNER JOIN customers c ON o.customer_id = c.customer_id;
-- Join multiple tables
SELECT
o.order_id,
c.first_name,
c.last_name,
p.product_name,
oi.quantity,
oi.price
FROM orders o
INNER JOIN customers c ON o.customer_id = c.customer_id
INNER JOIN order_items oi ON o.order_id = oi.order_id
INNER JOIN products p ON oi.product_id = p.product_id;
LEFT JOIN (LEFT OUTER JOIN)
IntermediateReturns all rows from left table, matching rows from right table (NULL if no match).
-- Get all customers and their orders (including customers with no orders)
SELECT
c.customer_id,
c.first_name,
c.last_name,
COUNT(o.order_id) AS total_orders,
SUM(o.total_amount) AS lifetime_value
FROM customers c
LEFT JOIN orders o ON c.customer_id = o.customer_id
GROUP BY c.customer_id, c.first_name, c.last_name;
-- Find customers with NO orders
SELECT c.*
FROM customers c
LEFT JOIN orders o ON c.customer_id = o.customer_id
WHERE o.order_id IS NULL;
RIGHT JOIN (Less Common)
Returns all rows from right table, matching rows from left table.
-- Same as LEFT JOIN but reversed
SELECT *
FROM orders o
RIGHT JOIN customers c ON o.customer_id = c.customer_id;
FULL OUTER JOIN
Returns all rows from both tables (NULLs where no match).
-- Get all customers and all orders
SELECT *
FROM customers c
FULL OUTER JOIN orders o ON c.customer_id = o.customer_id;
SELF JOIN
Join table to itself (useful for hierarchical data).
-- Find employees and their managers
SELECT
e.employee_id,
e.first_name AS employee_name,
m.first_name AS manager_name
FROM employees e
LEFT JOIN employees m ON e.manager_id = m.employee_id;
📦 Subqueries
Subqueries in WHERE Clause
Intermediate
-- Find products more expensive than average
SELECT product_name, price
FROM products
WHERE price > (SELECT AVG(price) FROM products);
-- Find customers who placed orders in 2024
SELECT *
FROM customers
WHERE customer_id IN (
SELECT DISTINCT customer_id
FROM orders
WHERE order_date >= '2024-01-01'
);
Subqueries in FROM Clause
-- Calculate percentage of total for each category
SELECT
category,
total_sales,
(100.0 * total_sales / overall_total) AS percentage
FROM (
SELECT
category,
SUM(revenue) AS total_sales
FROM sales
GROUP BY category
) category_sales
CROSS JOIN (
SELECT SUM(revenue) AS overall_total FROM sales
) total;
📅 Date & Time Functions
Common Date Operations
Intermediate
-- Current date/time
SELECT
CURRENT_DATE, -- 2024-01-15
CURRENT_TIMESTAMP, -- 2024-01-15 10:30:45
NOW(); -- Current timestamp
-- Extract parts of date
SELECT
order_date,
EXTRACT(YEAR FROM order_date) AS year,
EXTRACT(MONTH FROM order_date) AS month,
EXTRACT(DAY FROM order_date) AS day
FROM orders;
-- Date arithmetic
SELECT
order_date,
order_date + INTERVAL '7 days' AS due_date,
order_date - INTERVAL '30 days' AS one_month_ago
FROM orders;
-- Date filtering
SELECT * FROM orders
WHERE order_date >= '2024-01-01'
AND order_date < '2025-01-01';
-- Last 30 days
SELECT * FROM orders
WHERE order_date >= CURRENT_DATE - INTERVAL '30 days';
🔤 String Functions
Text Manipulation
-- Concatenation
SELECT first_name || ' ' || last_name AS full_name
FROM customers;
-- Upper/Lower case
SELECT
UPPER(email) AS email_upper,
LOWER(email) AS email_lower
FROM customers;
-- Length
SELECT product_name, LENGTH(product_name) AS name_length
FROM products;
-- Substring
SELECT
email,
SUBSTRING(email, 1, POSITION('@' IN email) - 1) AS username
FROM customers;
-- Trim whitespace
SELECT TRIM(' hello '); -- 'hello'
-- Replace
SELECT REPLACE(phone, '-', '') AS clean_phone
FROM customers;
🔀 CASE Statements (Conditional Logic)
IF-THEN-ELSE in SQL
Intermediate
-- Simple CASE
SELECT
product_name,
price,
CASE
WHEN price < 50 THEN 'Budget'
WHEN price < 200 THEN 'Standard'
ELSE 'Premium'
END AS price_category
FROM products;
-- Customer segmentation
SELECT
customer_id,
first_name,
last_name,
total_purchases,
CASE
WHEN total_purchases > 10000 THEN 'VIP'
WHEN total_purchases > 5000 THEN 'Gold'
WHEN total_purchases > 1000 THEN 'Silver'
ELSE 'Bronze'
END AS customer_tier
FROM customers;
-- CASE in aggregation
SELECT
COUNT(CASE WHEN status = 'Active' THEN 1 END) AS active_count,
COUNT(CASE WHEN status = 'Inactive' THEN 1 END) AS inactive_count
FROM customers;
🪟 Window Functions
Advanced Analytics
Advanced
-- Running total
SELECT
order_date,
total_amount,
SUM(total_amount) OVER (ORDER BY order_date) AS running_total
FROM orders;
-- Rank products by price within category
SELECT
category,
product_name,
price,
RANK() OVER (PARTITION BY category ORDER BY price DESC) AS price_rank
FROM products;
-- Moving average (last 3 months)
SELECT
month,
revenue,
AVG(revenue) OVER (
ORDER BY month
ROWS BETWEEN 2 PRECEDING AND CURRENT ROW
) AS moving_avg
FROM monthly_sales;
💼 Common Business Query Patterns
Top N Analysis
-- Top 10 customers by revenue
SELECT
c.customer_id,
c.first_name,
c.last_name,
SUM(o.total_amount) AS lifetime_value
FROM customers c
JOIN orders o ON c.customer_id = o.customer_id
GROUP BY c.customer_id, c.first_name, c.last_name
ORDER BY lifetime_value DESC
LIMIT 10;
Cohort Analysis
-- Monthly cohort of new customers
SELECT
DATE_TRUNC('month', first_order_date) AS cohort_month,
COUNT(DISTINCT customer_id) AS cohort_size
FROM (
SELECT
customer_id,
MIN(order_date) AS first_order_date
FROM orders
GROUP BY customer_id
) first_orders
GROUP BY cohort_month
ORDER BY cohort_month;
Growth Metrics
-- Month-over-month growth
WITH monthly_revenue AS (
SELECT
DATE_TRUNC('month', order_date) AS month,
SUM(total_amount) AS revenue
FROM orders
GROUP BY month
)
SELECT
month,
revenue,
LAG(revenue) OVER (ORDER BY month) AS prev_month_revenue,
revenue - LAG(revenue) OVER (ORDER BY month) AS growth,
ROUND(
(100.0 * (revenue - LAG(revenue) OVER (ORDER BY month)) /
LAG(revenue) OVER (ORDER BY month)),
2
) AS growth_pct
FROM monthly_revenue;
⚡ Performance Optimization Tips
✓ Index Your Columns: Create indexes on columns used in WHERE, JOIN, and ORDER BY clauses
✓ Use WHERE Instead of HAVING: Filter rows before aggregation when possible
✓ Limit Results: Use LIMIT to retrieve only needed rows
✓ Avoid SELECT *: Specify only columns you need
✓ Use EXISTS Instead of IN: For subqueries, EXISTS is often faster
⚠️ Common Performance Killers:
- Functions on indexed columns in WHERE clause
- SELECT DISTINCT on large datasets
- Multiple OR conditions (use IN instead)
- LIKE queries starting with % (%abc%)
- Cartesian joins (missing JOIN conditions)
📖 Quick Reference Table
| Operation | Syntax | Example |
|---|---|---|
| Select All | SELECT * FROM table |
Get all columns |
| Filter | WHERE column = value |
Filter by condition |
| Sort | ORDER BY column DESC |
Sort descending |
| Group | GROUP BY column |
Aggregate by groups |
| Count | COUNT(*) |
Count rows |
| Sum | SUM(column) |
Total sum |
| Average | AVG(column) |
Calculate average |
| Join Tables | JOIN table ON condition |
Combine tables |
| NULL Check | IS NULL / IS NOT NULL |
Find missing values |
| Pattern Match | LIKE '%value%' |
Search text |
