SQL Glossary: 80+ SQL Terms, Commands & Functions Explained

| Command | Definition | Example | |---------|------------|---------| | SELECT | Retrieve data from database | SELECT * FROM customers (get all columns) | | FROM | Specify table to query | SELECT name FROM customers | | WHERE | Filter rows based on condition | SELECT * FROM orders WHERE amount > 1000 | | AND | Combine multiple conditions (all must be true) | WHERE status = 'shipped' AND amount > 500 | | OR | At least one condition must be true | WHERE city = 'Mumbai' OR city = 'Delhi' | | IN | Match any value in list | WHERE city IN ('Mumbai', 'Delhi', 'Bangalore') | | BETWEEN | Range filter (inclusive) | WHERE date BETWEEN '2026-01-01' AND '2026-12-31' | | LIKE | Pattern matching (% = wildcard) | WHERE email LIKE '%@gmail.com' (ends with gmail.com) | | IS NULL | Check for missing values | WHERE phone IS NULL (find customers without phone) | | IS NOT NULL | Check for non-missing values | WHERE email IS NOT NULL | | DISTINCT | Remove duplicates, return unique values | SELECT DISTINCT city FROM customers | | ORDER BY | Sort results | ORDER BY date DESC (newest first) | | LIMIT | Restrict number of rows returned | LIMIT 10 (return only first 10 rows) | | OFFSET | Skip rows | LIMIT 10 OFFSET 20 (rows 21-30, used for pagination) | | AS | Rename column or table (alias) | SELECT amount * 1.18 AS amount_with_tax |

Learn more about SELECT, WHERE, ORDER BY

| JOIN Type | Definition | Example Use Case | |-----------|------------|------------------| | INNER JOIN | Return only matching rows from both tables | Get orders with customer details (exclude orders without customer match — data error) | | LEFT JOIN | All rows from left table + matching from right | Get all customers with their orders (include customers with 0 orders) | | RIGHT JOIN | All rows from right table + matching from left | Rarely used (can rewrite as LEFT JOIN by swapping tables) | | FULL OUTER JOIN | All rows from both tables (NULL if no match) | Find customers without orders AND orders without customers | | CROSS JOIN | Cartesian product (every row from A × every row from B) | Generate all product-size combinations (Small, Medium, Large × T-shirt, Jeans, Shoes) | | SELF JOIN | Join table to itself | Find pairs of customers from same city |

Syntax:

SELECT o.order_id, c.name
FROM orders o
INNER JOIN customers c ON o.customer_id = c.id
WHERE o.date > '2026-01-01';

Learn more about SQL JOINs

| Function | Definition | Example | |----------|------------|---------| | COUNT() | Count number of rows | SELECT COUNT(*) FROM orders (total orders) | | COUNT(DISTINCT) | Count unique values | SELECT COUNT(DISTINCT customer_id) FROM orders (unique customers who ordered) | | SUM() | Add up values | SELECT SUM(amount) FROM orders (total revenue) | | AVG() | Calculate average | SELECT AVG(amount) FROM orders (average order value) | | MIN() | Find smallest value | SELECT MIN(date) FROM orders (first order date) | | MAX() | Find largest value | SELECT MAX(amount) FROM orders (largest order) | | GROUP BY | Group rows for aggregation | SELECT city, COUNT(*) FROM customers GROUP BY city | | HAVING | Filter grouped results (like WHERE but for aggregations) | SELECT city, COUNT(*) FROM customers GROUP BY city HAVING COUNT(*) > 100 |

Example: Revenue by city

SELECT
  city,
  COUNT(*) AS order_count,
  SUM(amount) AS total_revenue,
  AVG(amount) AS avg_order_value
FROM orders
JOIN customers ON orders.customer_id = customers.id
GROUP BY city
HAVING SUM(amount) > 100000
ORDER BY total_revenue DESC;

Learn more about GROUP BY and Aggregations

| Function | Definition | Example Use Case | |----------|------------|------------------| | ROW_NUMBER() | Assign unique sequential number to each row (within partition) | Rank customers by revenue (1, 2, 3, ...) | | RANK() | Rank with gaps for ties (1, 2, 2, 4) | Rank students by score (ties get same rank, next rank skips) | | DENSE_RANK() | Rank without gaps for ties (1, 2, 2, 3) | Rank products by sales (ties don't skip next rank) | | NTILE(n) | Divide rows into n equal groups (quartiles, deciles) | Split customers into 4 groups by LTV (top 25%, 25-50%, 50-75%, bottom 25%) | | LAG() | Access previous row value | Compare this month revenue vs last month | | LEAD() | Access next row value | Compare this month revenue vs next month (forecasting) | | FIRST_VALUE() | Get first value in window | Find first order date for each customer | | LAST_VALUE() | Get last value in window | Find most recent order date for each customer | | SUM() OVER | Running total | Cumulative revenue by day | | AVG() OVER | Moving average | 7-day moving average of daily sales |

Syntax:

SELECT
  date,
  revenue,
  SUM(revenue) OVER (ORDER BY date) AS cumulative_revenue,
  LAG(revenue) OVER (ORDER BY date) AS previous_day_revenue,
  revenue - LAG(revenue) OVER (ORDER BY date) AS day_over_day_change
FROM daily_sales;

Example: Top 3 customers by revenue in each city

WITH ranked_customers AS (
  SELECT
    city,
    customer_id,
    SUM(amount) AS total_revenue,
    ROW_NUMBER() OVER (PARTITION BY city ORDER BY SUM(amount) DESC) AS rank
  FROM orders
  JOIN customers ON orders.customer_id = customers.id
  GROUP BY city, customer_id
)
SELECT * FROM ranked_customers WHERE rank <= 3;

Learn more about Window Functions

| Term | Definition | Example | |------|------------|---------| | Subquery | Query nested inside another query | SELECT * FROM orders WHERE customer_id IN (SELECT id FROM customers WHERE city = 'Mumbai') | | Correlated Subquery | Subquery references outer query (runs once per row) | Find customers who spent more than avg in their city | | CTE (Common Table Expression) | Temporary named result set (WITH clause) | Break complex query into readable steps |

Example: CTE for revenue analysis

WITH customer_revenue AS (
  SELECT
    customer_id,
    SUM(amount) AS total_revenue
  FROM orders
  GROUP BY customer_id
),
avg_revenue AS (
  SELECT AVG(total_revenue) AS avg_rev FROM customer_revenue
)
SELECT
  c.customer_id,
  c.total_revenue,
  c.total_revenue - a.avg_rev AS revenue_vs_avg
FROM customer_revenue c
CROSS JOIN avg_revenue a
WHERE c.total_revenue > a.avg_rev * 1.5;

Learn more about Subqueries and CTEs

| Function | Definition | Example | |----------|------------|---------| | DATE_TRUNC() | Truncate to unit (day, week, month, year) | DATE_TRUNC('month', order_date) → 2026-01-01 | | EXTRACT() | Extract part of date (year, month, day, hour) | EXTRACT(MONTH FROM order_date) → 3 (March) | | DATE_ADD() | Add interval to date | DATE_ADD(order_date, INTERVAL 7 DAY) → 7 days later | | DATE_SUB() | Subtract interval from date | DATE_SUB(CURRENT_DATE, INTERVAL 30 DAY) → 30 days ago | | DATEDIFF() | Difference between two dates (in days) | DATEDIFF('2026-03-24', '2026-01-01') → 83 days | | CURRENT_DATE | Today's date | WHERE order_date = CURRENT_DATE | | CURRENT_TIMESTAMP | Current date + time | WHERE created_at > CURRENT_TIMESTAMP - INTERVAL '1 hour' | | DATE_FORMAT() | Format date as string | DATE_FORMAT(order_date, '%Y-%m') → 2026-03 |

Example: Monthly revenue

SELECT
  DATE_TRUNC('month', order_date) AS month,
  COUNT(*) AS order_count,
  SUM(amount) AS revenue
FROM orders
WHERE order_date >= DATE_SUB(CURRENT_DATE, INTERVAL 12 MONTH)
GROUP BY DATE_TRUNC('month', order_date)
ORDER BY month;

| Function | Definition | Example | |----------|------------|---------| | CONCAT() | Combine strings | CONCAT(first_name, ' ', last_name) → John Doe | | UPPER() | Convert to uppercase | UPPER(email) → JOHN@GMAIL.COM | | LOWER() | Convert to lowercase | LOWER(city) → bangalore | | TRIM() | Remove leading/trailing spaces | TRIM(name) → John (removes spaces) | | LENGTH() | String length | LENGTH(email) → 15 | | SUBSTRING() | Extract part of string | SUBSTRING(email, 1, 4) → john (first 4 chars) | | REPLACE() | Replace substring | REPLACE(phone, '-', '') → Remove dashes | | SPLIT_PART() | Split string by delimiter | SPLIT_PART(email, '@', 2) → gmail.com (domain) | | LIKE | Pattern matching (% = any chars, _ = single char) | WHERE email LIKE '%@gmail.com' | | REGEXP | Regular expression matching (advanced patterns) | WHERE phone REGEXP '^[0-9]{10}$' (10-digit phone) |

Example: Clean email domains

SELECT
  LOWER(TRIM(SPLIT_PART(email, '@', 2))) AS email_domain,
  COUNT(*) AS user_count
FROM users
WHERE email IS NOT NULL
GROUP BY LOWER(TRIM(SPLIT_PART(email, '@', 2)))
ORDER BY user_count DESC;

| Function | Definition | Example | |----------|------------|---------| | CASE WHEN | If-then-else logic | Categorize customers by spend | | IF() (MySQL) | Simple if-else | IF(amount > 1000, 'High', 'Low') | | COALESCE() | Return first non-NULL value | COALESCE(phone, email, 'No contact') | | NULLIF() | Return NULL if values are equal | NULLIF(discount, 0) (replace 0 with NULL) | | IFNULL() (MySQL) | Replace NULL with value | IFNULL(phone, 'Missing') |

Example: Customer segmentation

SELECT
  customer_id,
  SUM(amount) AS total_spent,
  CASE
    WHEN SUM(amount) >= 10000 THEN 'VIP'
    WHEN SUM(amount) >= 5000 THEN 'Premium'
    WHEN SUM(amount) >= 1000 THEN 'Regular'
    ELSE 'Low Value'
  END AS customer_segment
FROM orders
GROUP BY customer_id;

| Term | Definition | Example | |------|------------|---------| | Table | Collection of rows (records) and columns (fields) | Customers table: id, name, email, city | | Row (Record) | Single entry in table | One customer: id=1, name=John, email=john@gmail.com | | Column (Field) | Attribute of entity | Email column: contains email addresses | | Primary Key | Unique identifier for each row | customer_id (no two customers have same ID) | | Foreign Key | Column linking to another table's primary key | orders.customer_id links to customers.id | | Index | Data structure for fast lookups | Index on email column → Search by email 100× faster | | View | Saved query (virtual table) | CREATE VIEW active_customers AS SELECT * FROM customers WHERE status = 'active' | | Schema | Database structure (tables, columns, types) | Orders schema: order_id INT, customer_id INT, amount DECIMAL, date DATE | | Constraint | Rule enforcing data integrity | NOT NULL (column must have value), UNIQUE (no duplicates), CHECK (amount > 0) | | Transaction | Group of SQL statements (all succeed or all fail) | Transfer money: Deduct from A, Add to B (both must succeed) |