SQL Project: Banking Transactions

SQL Tutorial › SQL Projects › Project: Banking Transactions

SQL Project: Banking Transactions

ProjectProject 4 of 535 min read13 queries run on MySQL 8.4

A small bank keeps customers, accounts and every transaction in MySQL. Money is unforgiving: balances must always match the transaction history, a transfer must never debit one account without crediting the other, and unusual activity must be spotted. In this project you write the reports, the integrity checks, a transaction-safe transfer procedure and an alerting trigger, and you see ACID behaviour with real results.

In this lesson
  • Reconcile stored balances with the transaction ledger
  • Produce statements with running balances
  • Detect suspicious patterns with time-based window frames
  • Implement a safe transfer with a transaction, locking, a procedure and a trigger

The database

TableOne row perNotes
customersbank customer
accountsaccountbalance has CHECK (balance >= 0): no overdrafts
transactionsledger entrytxn_type: DEPOSIT, WITHDRAWAL, TRANSFER_OUT, TRANSFER_IN, INTEREST; a transfer creates two entries that point at each other through related_account
customers · 6 rows
customer_idfull_namecitysince
1Priya SharmaMumbai2019-04-11
2Rahul VermaDelhi2020-08-02
3Fatima SheikhHyderabad2018-01-23
4George MathewKochi2021-11-30
5Lakshmi RaoChennai2022-05-17
6Arnav GuptaPune2023-02-09
accounts · 9 rows
account_idcustomer_idaccount_typeopened_onbalance
1011Savings2019-04-11135000
1021Current2021-06-01280500
1032Savings2020-08-02129000
1043Savings2018-01-237000
1053Fixed2022-03-1553875
1064Savings2021-11-3061000
1075Savings2022-05-1793000
1086Current2023-02-09144000
1092Current2024-01-1062000
transactions · first 12 of 102 rows
txn_idaccount_idtxn_timetxn_typeamountrelated_account
11012025-01-01 10:00:00DEPOSIT50000NULL
21022025-01-01 11:00:00DEPOSIT200000NULL
31032025-01-01 12:00:00DEPOSIT50000NULL
41042025-01-01 13:00:00DEPOSIT50000NULL
51052025-01-01 14:00:00DEPOSIT50000NULL
61062025-01-01 15:00:00DEPOSIT120000NULL
71072025-01-01 16:00:00DEPOSIT75000NULL
81082025-01-01 17:00:00DEPOSIT200000NULL
91092025-01-01 18:00:00DEPOSIT50000NULL
101072025-01-03 13:47:00TRANSFER_OUT1000101
111012025-01-03 13:47:00TRANSFER_IN1000107
121062025-01-04 04:34:00TRANSFER_OUT3000108

A transaction’s effect on the balance is +amount for DEPOSIT, TRANSFER_IN and INTEREST, and −amount for WITHDRAWAL and TRANSFER_OUT. Every query below uses that rule.

bank.sql
-- sqatools.in SQL project 4: banking transactions (generated by gen_projects.py)CREATE TABLE customers (  customer_id INT PRIMARY KEY,  full_name   VARCHAR(40) NOT NULL,  city        VARCHAR(30) NOT NULL,  since       DATE NOT NULL);CREATE TABLE accounts (  account_id   INT PRIMARY KEY,  customer_id  INT NOT NULL,  account_type VARCHAR(10) NOT NULL,  opened_on    DATE NOT NULL,  balance      INT NOT NULL CHECK (balance >= 0),  FOREIGN KEY (customer_id) REFERENCES customers (customer_id));CREATE TABLE transactions (  txn_id         INT PRIMARY KEY,  account_id     INT NOT NULL,  txn_time       DATETIME NOT NULL,  txn_type       VARCHAR(12) NOT NULL,  amount         INT NOT NULL CHECK (amount > 0),  related_account INT,  FOREIGN KEY (account_id) REFERENCES accounts (account_id));INSERT INTO customers VALUES(1, 'Priya Sharma', 'Mumbai', '2019-04-11'),(2, 'Rahul Verma', 'Delhi', '2020-08-02'),(3, 'Fatima Sheikh', 'Hyderabad', '2018-01-23'),(4, 'George Mathew', 'Kochi', '2021-11-30'),(5, 'Lakshmi Rao', 'Chennai', '2022-05-17'),(6, 'Arnav Gupta', 'Pune', '2023-02-09');INSERT INTO accounts VALUES(101, 1, 'Savings', '2019-04-11', 135000),(102, 1, 'Current', '2021-06-01', 280500),(103, 2, 'Savings', '2020-08-02', 129000),(104, 3, 'Savings', '2018-01-23', 7000),(105, 3, 'Fixed', '2022-03-15', 53875),(106, 4, 'Savings', '2021-11-30', 61000),(107, 5, 'Savings', '2022-05-17', 93000),(108, 6, 'Current', '2023-02-09', 144000),(109, 2, 'Current', '2024-01-10', 62000);INSERT INTO transactions VALUES(1, 101, '2025-01-01 10:00:00', 'DEPOSIT', 50000, NULL),(2, 102, '2025-01-01 11:00:00', 'DEPOSIT', 200000, NULL),(3, 103, '2025-01-01 12:00:00', 'DEPOSIT', 50000, NULL),(4, 104, '2025-01-01 13:00:00', 'DEPOSIT', 50000, NULL),(5, 105, '2025-01-01 14:00:00', 'DEPOSIT', 50000, NULL),(6, 106, '2025-01-01 15:00:00', 'DEPOSIT', 120000, NULL),(7, 107, '2025-01-01 16:00:00', 'DEPOSIT', 75000, NULL),(8, 108, '2025-01-01 17:00:00', 'DEPOSIT', 200000, NULL),(9, 109, '2025-01-01 18:00:00', 'DEPOSIT', 50000, NULL),(10, 107, '2025-01-03 13:47:00', 'TRANSFER_OUT', 1000, 101),(11, 101, '2025-01-03 13:47:00', 'TRANSFER_IN', 1000, 107),(12, 106, '2025-01-04 04:34:00', 'TRANSFER_OUT', 3000, 108),(13, 108, '2025-01-04 04:34:00', 'TRANSFER_IN', 3000, 106),(14, 106, '2025-01-04 19:39:00', 'WITHDRAWAL', 2000, NULL),(15, 103, '2025-01-06 01:26:00', 'WITHDRAWAL', 2000, NULL),(16, 106, '2025-01-07 02:06:00', 'DEPOSIT', 2000, NULL),(17, 104, '2025-01-07 22:06:00', 'TRANSFER_OUT', 12000, 109),(18, 109, '2025-01-07 22:06:00', 'TRANSFER_IN', 12000, 104),(19, 106, '2025-01-09 15:11:00', 'TRANSFER_OUT', 1000, 104),(20, 104, '2025-01-09 15:11:00', 'TRANSFER_IN', 1000, 106),(21, 108, '2025-01-11 02:16:00', 'WITHDRAWAL', 500, NULL),(22, 103, '2025-01-13 03:56:00', 'WITHDRAWAL', 2000, NULL),(23, 104, '2025-01-15 02:01:00', 'TRANSFER_OUT', 12000, 102),(24, 102, '2025-01-15 02:01:00', 'TRANSFER_IN', 12000, 104),(25, 103, '2025-01-15 20:26:00', 'DEPOSIT', 15000, NULL),(26, 101, '2025-01-16 12:06:00', 'DEPOSIT', 2000, NULL),(27, 104, '2025-01-17 20:06:00', 'TRANSFER_OUT', 1000, 101),(28, 101, '2025-01-17 20:06:00', 'TRANSFER_IN', 1000, 104),(29, 106, '2025-01-19 07:18:00', 'TRANSFER_OUT', 7500, 101),(30, 101, '2025-01-19 07:18:00', 'TRANSFER_IN', 7500, 106),(31, 106, '2025-01-20 21:30:00', 'WITHDRAWAL', 20000, NULL),(32, 101, '2025-01-21 12:55:00', 'WITHDRAWAL', 1000, NULL),(33, 106, '2025-01-23 03:55:00', 'WITHDRAWAL', 2000, NULL),(34, 103, '2025-01-24 09:55:00', 'DEPOSIT', 5000, NULL),(35, 104, '2025-01-25 03:00:00', 'TRANSFER_OUT', 7500, 102),(36, 102, '2025-01-25 03:00:00', 'TRANSFER_IN', 7500, 104),(37, 102, '2025-01-26 12:47:00', 'WITHDRAWAL', 500, NULL),(38, 102, '2025-01-26 21:47:00', 'WITHDRAWAL', 1000, NULL),(39, 108, '2025-01-29 01:27:00', 'TRANSFER_OUT', 12000, 106),(40, 106, '2025-01-29 01:27:00', 'TRANSFER_IN', 12000, 108),(41, 107, '2025-02-01 21:52:00', 'WITHDRAWAL', 5000, NULL),(42, 108, '2025-02-03 07:32:00', 'WITHDRAWAL', 10000, NULL),(43, 104, '2025-02-05 04:57:00', 'TRANSFER_OUT', 7500, 103),(44, 103, '2025-02-05 04:57:00', 'TRANSFER_IN', 7500, 104),(45, 103, '2025-02-05 13:22:00', 'WITHDRAWAL', 500, NULL),(46, 108, '2025-02-05 18:47:00', 'WITHDRAWAL', 2000, NULL),(47, 101, '2025-02-08 00:27:00', 'DEPOSIT', 2000, NULL),(48, 101, '2025-02-09 23:52:00', 'DEPOSIT', 60000, NULL),(49, 101, '2025-02-12 11:32:00', 'DEPOSIT', 60000, NULL),(50, 102, '2025-02-14 03:32:00', 'DEPOSIT', 2000, NULL),(51, 103, '2025-02-15 22:37:00', 'DEPOSIT', 25000, NULL),(52, 104, '2025-02-17 05:24:00', 'DEPOSIT', 2000, NULL),(53, 107, '2025-02-19 09:49:00', 'DEPOSIT', 5000, NULL),(54, 108, '2025-02-20 03:14:00', 'WITHDRAWAL', 500, NULL),(55, 106, '2025-02-20 22:19:00', 'TRANSFER_OUT', 7500, 104),(56, 104, '2025-02-20 22:19:00', 'TRANSFER_IN', 7500, 106),(57, 104, '2025-02-21 07:06:00', 'WITHDRAWAL', 10000, NULL),(58, 103, '2025-02-23 09:18:00', 'WITHDRAWAL', 20000, NULL),(59, 106, '2025-02-25 09:43:00', 'WITHDRAWAL', 500, NULL),(60, 104, '2025-02-26 14:48:00', 'TRANSFER_OUT', 7500, 102),(61, 102, '2025-02-26 14:48:00', 'TRANSFER_IN', 7500, 104),(62, 108, '2025-02-28 18:13:00', 'WITHDRAWAL', 20000, NULL),(63, 106, '2025-03-02 20:38:00', 'WITHDRAWAL', 1000, NULL),(64, 102, '2025-03-05 01:03:00', 'WITHDRAWAL', 2000, NULL),(65, 104, '2025-03-07 02:03:00', 'WITHDRAWAL', 1000, NULL),(66, 103, '2025-03-08 06:43:00', 'TRANSFER_OUT', 12000, 102),(67, 102, '2025-03-08 06:43:00', 'TRANSFER_IN', 12000, 103),(68, 106, '2025-03-08 21:55:00', 'TRANSFER_OUT', 3000, 108),(69, 108, '2025-03-08 21:55:00', 'TRANSFER_IN', 3000, 106),(70, 106, '2025-03-09 01:00:00', 'WITHDRAWAL', 10000, NULL),(71, 102, '2025-03-09 23:05:00', 'DEPOSIT', 60000, NULL),(72, 104, '2025-03-11 12:10:00', 'DEPOSIT', 2000, NULL),(73, 106, '2025-03-14 22:05:00', 'WITHDRAWAL', 10000, NULL),(74, 106, '2025-03-14 22:16:00', 'WITHDRAWAL', 10000, NULL),(75, 106, '2025-03-14 22:27:00', 'WITHDRAWAL', 9500, NULL),(76, 106, '2025-03-14 22:38:00', 'WITHDRAWAL', 10000, NULL),(77, 102, '2025-03-16 03:37:00', 'WITHDRAWAL', 20000, NULL),(78, 107, '2025-03-17 04:02:00', 'WITHDRAWAL', 1000, NULL),(79, 106, '2025-03-19 00:14:00', 'DEPOSIT', 25000, NULL),(80, 103, '2025-03-20 05:54:00', 'DEPOSIT', 60000, NULL),(81, 107, '2025-03-22 00:06:00', 'TRANSFER_OUT', 3000, 104),(82, 104, '2025-03-22 00:06:00', 'TRANSFER_IN', 3000, 107),(83, 102, '2025-03-22 10:31:00', 'WITHDRAWAL', 500, NULL),(84, 101, '2025-03-24 08:36:00', 'WITHDRAWAL', 20000, NULL),(85, 108, '2025-03-26 03:23:00', 'WITHDRAWAL', 10000, NULL),(86, 101, '2025-03-26 23:03:00', 'TRANSFER_OUT', 7500, 107),(87, 107, '2025-03-26 23:03:00', 'TRANSFER_IN', 7500, 101),(88, 107, '2025-03-29 00:50:00', 'TRANSFER_OUT', 7500, 102),(89, 102, '2025-03-29 00:50:00', 'TRANSFER_IN', 7500, 107),(90, 102, '2025-03-29 19:02:00', 'TRANSFER_OUT', 3000, 101),(91, 101, '2025-03-29 19:02:00', 'TRANSFER_IN', 3000, 102),(92, 102, '2025-03-31 05:27:00', 'TRANSFER_OUT', 1000, 108),(93, 108, '2025-03-31 05:27:00', 'TRANSFER_IN', 1000, 102),(94, 107, '2025-03-31 15:27:00', 'WITHDRAWAL', 2000, NULL),(95, 108, '2025-03-31 20:27:00', 'WITHDRAWAL', 10000, NULL),(96, 105, '2025-03-31 23:59:00', 'INTEREST', 3875, NULL),(97, 101, '2025-04-02 16:07:00', 'WITHDRAWAL', 20000, NULL),(98, 106, '2025-04-03 17:12:00', 'WITHDRAWAL', 1000, NULL),(99, 108, '2025-04-05 09:24:00', 'DEPOSIT', 2000, NULL),(100, 107, '2025-04-06 07:24:00', 'DEPOSIT', 25000, NULL),(101, 101, '2025-04-07 01:49:00', 'TRANSFER_OUT', 3000, 103),(102, 103, '2025-04-07 01:49:00', 'TRANSFER_IN', 3000, 101);

Task 1: Customer holdings

SQL
SELECT c.customer_id, c.full_name,       COUNT(a.account_id) AS accounts,       GROUP_CONCAT(a.account_type ORDER BY a.account_id) AS account_types,       SUM(a.balance) AS total_balanceFROM customers cJOIN accounts a ON a.customer_id = c.customer_idGROUP BY c.customer_id, c.full_nameORDER BY total_balance DESC;
ResultMySQL 8.4
customer_idfull_nameaccountsaccount_typestotal_balance
1Priya Sharma2Savings,Current415500
2Rahul Verma2Savings,Current191000
6Arnav Gupta1Current144000
5Lakshmi Rao1Savings93000
4George Mathew1Savings61000
3Fatima Sheikh2Savings,Fixed60875
6 rows in set

Task 2: Reconcile balances with the ledger

An integrity check that auditors and testers run: does each stored balance equal the sum of its transactions?

SQL
SELECT a.account_id, a.balance AS stored_balance,       SUM(CASE WHEN t.txn_type IN ('DEPOSIT', 'TRANSFER_IN', 'INTEREST') THEN t.amount ELSE -t.amount END) AS ledger_balance,       a.balance - SUM(CASE WHEN t.txn_type IN ('DEPOSIT', 'TRANSFER_IN', 'INTEREST') THEN t.amount ELSE -t.amount END) AS differenceFROM accounts aJOIN transactions t ON t.account_id = a.account_idGROUP BY a.account_id, a.balanceORDER BY a.account_id;
ResultMySQL 8.4
account_idstored_balanceledger_balancedifference
1011350001350000
1022805002805000
1031290001290000
104700070000
10553875538750
10661000610000
10793000930000
1081440001440000
10962000620000
9 rows in set
How this query works, step by step
  1. The CASE turns each entry into a signed amount.
  2. SUM per account rebuilds the balance from the ledger.
  3. difference must be 0 for every account. Any other value means a bug or tampering, so this query belongs in an automated data-quality test.

Both legs of every transfer must exist and match:

SQL
SELECT o.txn_id AS out_txn, o.account_id AS from_acc, o.related_account AS to_acc, o.amount, i.txn_id AS in_txnFROM transactions oLEFT JOIN transactions i       ON i.txn_type = 'TRANSFER_IN' AND i.account_id = o.related_account      AND i.related_account = o.account_id AND i.amount = o.amount AND i.txn_time = o.txn_timeWHERE o.txn_type = 'TRANSFER_OUT' AND i.txn_id IS NULL;
ResultMySQL 8.4
Empty set

An Empty set means every outgoing transfer has its matching incoming entry.

Task 3: Account statement with running balance

SQL
SELECT txn_time, txn_type, amount,       SUM(CASE WHEN txn_type IN ('DEPOSIT', 'TRANSFER_IN', 'INTEREST') THEN amount ELSE -amount END)         OVER (ORDER BY txn_time, txn_id) AS running_balanceFROM transactionsWHERE account_id = 103ORDER BY txn_time, txn_id;
ResultMySQL 8.4
txn_timetxn_typeamountrunning_balance
2025-01-01 12:00:00DEPOSIT5000050000
2025-01-06 01:26:00WITHDRAWAL200048000
2025-01-13 03:56:00WITHDRAWAL200046000
2025-01-15 20:26:00DEPOSIT1500061000
2025-01-24 09:55:00DEPOSIT500066000
2025-02-05 04:57:00TRANSFER_IN750073500
2025-02-05 13:22:00WITHDRAWAL50073000
2025-02-15 22:37:00DEPOSIT2500098000
2025-02-23 09:18:00WITHDRAWAL2000078000
2025-03-08 06:43:00TRANSFER_OUT1200066000
2025-03-20 05:54:00DEPOSIT60000126000
2025-04-07 01:49:00TRANSFER_IN3000129000
12 rows in set
How this query works, step by step
  1. The signed amount is summed cumulatively in time order (Running Totals).
  2. txn_id breaks ties between entries with the same timestamp, so the order is deterministic.
  3. The last running balance equals the account’s stored balance.

Task 4: Monthly flows by account type

MySQL
SELECT DATE_FORMAT(t.txn_time, '%Y-%m') AS month, a.account_type,       SUM(CASE WHEN t.txn_type IN ('DEPOSIT', 'TRANSFER_IN', 'INTEREST') THEN t.amount ELSE 0 END) AS money_in,       SUM(CASE WHEN t.txn_type IN ('WITHDRAWAL', 'TRANSFER_OUT') THEN t.amount ELSE 0 END) AS money_outFROM transactions tJOIN accounts a ON a.account_id = t.account_idGROUP BY month, a.account_typeORDER BY month, a.account_type;
ResultMySQL 8.4
monthaccount_typemoney_inmoney_out
2025-01Current48450014000
2025-01Fixed500000
2025-01Savings39150074000
2025-02Current950032500
2025-02Savings16900058500
2025-03Current8350046500
2025-03Fixed38750
2025-03Savings100500107500
2025-04Current20000
2025-04Savings2800024000
10 rows in set

Task 5: Large transactions

SQL
SELECT t.txn_id, t.txn_time, c.full_name, t.account_id, t.txn_type, t.amountFROM transactions tJOIN accounts a  ON a.account_id = t.account_idJOIN customers c ON c.customer_id = a.customer_idWHERE t.amount >= 50000 AND t.txn_type <> 'TRANSFER_IN'ORDER BY t.amount DESC, t.txn_time;
ResultMySQL 8.4
txn_idtxn_timefull_nameaccount_idtxn_typeamount
22025-01-01 11:00:00Priya Sharma102DEPOSIT200000
82025-01-01 17:00:00Arnav Gupta108DEPOSIT200000
62025-01-01 15:00:00George Mathew106DEPOSIT120000
72025-01-01 16:00:00Lakshmi Rao107DEPOSIT75000
482025-02-09 23:52:00Priya Sharma101DEPOSIT60000
492025-02-12 11:32:00Priya Sharma101DEPOSIT60000
712025-03-09 23:05:00Priya Sharma102DEPOSIT60000
802025-03-20 05:54:00Rahul Verma103DEPOSIT60000
12025-01-01 10:00:00Priya Sharma101DEPOSIT50000
32025-01-01 12:00:00Rahul Verma103DEPOSIT50000
42025-01-01 13:00:00Fatima Sheikh104DEPOSIT50000
52025-01-01 14:00:00Fatima Sheikh105DEPOSIT50000
92025-01-01 18:00:00Rahul Verma109DEPOSIT50000
13 rows in set

Task 6: Fraud pattern: many withdrawals in one hour

Flag every withdrawal that is the 3rd (or later) withdrawal on the same account within 60 minutes. A time-based window frame counts the rows in the preceding hour:

MySQL
WITH w AS (  SELECT txn_id, account_id, txn_time, amount,         COUNT(*) OVER (PARTITION BY account_id ORDER BY txn_time                        RANGE BETWEEN INTERVAL 60 MINUTE PRECEDING AND CURRENT ROW) AS withdrawals_last_hour,         SUM(amount) OVER (PARTITION BY account_id ORDER BY txn_time                        RANGE BETWEEN INTERVAL 60 MINUTE PRECEDING AND CURRENT ROW) AS amount_last_hour  FROM transactions  WHERE txn_type = 'WITHDRAWAL')SELECT * FROM wWHERE withdrawals_last_hour >= 3ORDER BY account_id, txn_time;
ResultMySQL 8.4
txn_idaccount_idtxn_timeamountwithdrawals_last_houramount_last_hour
751062025-03-14 22:27:009500329500
761062025-03-14 22:38:0010000439500
2 rows in set
How this query works, step by step
  1. Only withdrawals are considered, per account, in time order.
  2. RANGE BETWEEN INTERVAL 60 MINUTE PRECEDING AND CURRENT ROW includes every withdrawal from the hour up to the current one, based on the timestamp value, not on a number of rows.
  3. Rows with 3 or more withdrawals in that hour are flagged. Account 106 shows a burst of ATM withdrawals late one night.

Task 7: Dormant accounts

Accounts with no activity in the 60 days before 30 April 2025:

MySQL
SELECT a.account_id, c.full_name, a.account_type, a.balance,       MAX(t.txn_time) AS last_activityFROM accounts aJOIN customers c ON c.customer_id = a.customer_idLEFT JOIN transactions t ON t.account_id = a.account_idGROUP BY a.account_id, c.full_name, a.account_type, a.balanceHAVING MAX(t.txn_time) < '2025-04-30' - INTERVAL 60 DAY OR MAX(t.txn_time) IS NULLORDER BY last_activity;
ResultMySQL 8.4
account_idfull_nameaccount_typebalancelast_activity
109Rahul VermaCurrent620002025-01-07 22:06:00
1 row in set

Task 8: Who sends money to whom

SQL
SELECT cf.full_name AS sender, ct.full_name AS receiver,       COUNT(*) AS transfers, SUM(t.amount) AS total_sentFROM transactions tJOIN accounts af  ON af.account_id = t.account_idJOIN customers cf ON cf.customer_id = af.customer_idJOIN accounts at2 ON at2.account_id = t.related_accountJOIN customers ct ON ct.customer_id = at2.customer_idWHERE t.txn_type = 'TRANSFER_OUT'GROUP BY sender, receiverORDER BY total_sent DESCLIMIT 6;
ResultMySQL 8.4
senderreceivertransferstotal_sent
Fatima SheikhPriya Sharma428000
Fatima SheikhRahul Verma219500
Arnav GuptaGeorge Mathew112000
Rahul VermaPriya Sharma112000
George MathewFatima Sheikh28500
Lakshmi RaoPriya Sharma28500
6 rows in set

Transfers between a customer’s own accounts appear with the same name on both sides.

Task 9: A safe money transfer (ACID)

A transfer touches two balances and writes two ledger entries. It must be atomic: all four changes or none. The procedure below uses a transaction, row locks and an error handler:

MySQL
DELIMITER //CREATE PROCEDURE transfer_funds(IN p_from INT, IN p_to INT, IN p_amount INT, IN p_time DATETIME)BEGIN  DECLARE v_balance INT;  DECLARE v_found INT;  DECLARE EXIT HANDLER FOR SQLEXCEPTION BEGIN ROLLBACK; RESIGNAL; END;   IF p_amount <= 0 OR p_from = p_to THEN    SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Invalid transfer';  END IF;   START TRANSACTION;  -- lock both account rows; InnoDB takes them in primary-key order, so opposite transfers cannot deadlock  SELECT COUNT(*) INTO v_found FROM accounts WHERE account_id IN (p_from, p_to) FOR UPDATE;  IF v_found <> 2 THEN    SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Unknown account';  END IF;  SELECT balance INTO v_balance FROM accounts WHERE account_id = p_from;  IF v_balance < p_amount THEN    SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Insufficient funds';  END IF;   UPDATE accounts SET balance = balance - p_amount WHERE account_id = p_from;  UPDATE accounts SET balance = balance + p_amount WHERE account_id = p_to;  INSERT INTO transactions (txn_id, account_id, txn_time, txn_type, amount, related_account)  SELECT MAX(txn_id) + 1, p_from, p_time, 'TRANSFER_OUT', p_amount, p_to FROM transactions;  INSERT INTO transactions (txn_id, account_id, txn_time, txn_type, amount, related_account)  SELECT MAX(txn_id) + 1, p_to, p_time, 'TRANSFER_IN', p_amount, p_from FROM transactions;  COMMIT;END //DELIMITER ; SELECT account_id, balance FROM accounts WHERE account_id IN (101, 107);CALL transfer_funds(101, 107, 25000, '2025-04-20 11:00:00');SELECT account_id, balance FROM accounts WHERE account_id IN (101, 107);
ResultMySQL 8.4
mysql> CREATE PROCEDURE transfer_funds(IN p_from INT, IN p_to INT, IN p_amoun…
Query OK, 0 rows affected
mysql> SELECT account_id, balance FROM accounts WHERE account_id IN (101, 107)
account_idbalance
101135000
10793000
2 rows in set
mysql> CALL transfer_funds(101, 107, 25000, '2025-04-20 11:00:00')
Query OK, 0 rows affected
mysql> SELECT account_id, balance FROM accounts WHERE account_id IN (101, 107)
account_idbalance
101110000
107118000
2 rows in set
How this query works, step by step
  1. Atomicity: both UPDATEs and both INSERTs run inside one transaction and are committed together.
  2. Consistency: the balance check and the table’s CHECK (balance >= 0) keep every balance valid.
  3. Isolation: FOR UPDATE locks the rows, so a parallel transfer from the same account waits instead of spending the same money twice.
  4. Durability: after COMMIT, InnoDB guarantees the transfer survives a crash.
  5. Account 101 loses 25,000 and 107 gains 25,000; the total money in the bank is unchanged.

A transfer larger than the balance fails, and nothing changes:

MySQL
CALL transfer_funds(108, 101, 9999999, '2025-04-20 11:05:00');
ResultMySQL 8.4
ERROR 1644 (45000): Insufficient funds
MySQL
SELECT account_id, balance FROM accounts WHERE account_id IN (101, 108);SELECT COUNT(*) AS ledger_entries_at_1105 FROM transactions WHERE txn_time = '2025-04-20 11:05:00';
ResultMySQL 8.4
mysql> SELECT account_id, balance FROM accounts WHERE account_id IN (101, 108)
account_idbalance
101110000
108144000
2 rows in set
mysql> SELECT COUNT(*) AS ledger_entries_at_1105 FROM transactions WHERE txn_…
ledger_entries_at_1105
0
1 row in set

Task 10: An alerting trigger

Write every withdrawal of 20,000 or more to an alerts table automatically, whichever application makes it:

MySQL
CREATE TABLE alerts (  alert_id   INT AUTO_INCREMENT PRIMARY KEY,  txn_id     INT NOT NULL,  account_id INT NOT NULL,  amount     INT NOT NULL,  reason     VARCHAR(40) NOT NULL); CREATE TRIGGER trg_large_withdrawalAFTER INSERT ON transactionsFOR EACH ROWINSERT INTO alerts (txn_id, account_id, amount, reason)SELECT NEW.txn_id, NEW.account_id, NEW.amount, 'Large withdrawal'FROM DUALWHERE NEW.txn_type = 'WITHDRAWAL' AND NEW.amount >= 20000; INSERT INTO transactions VALUES (500, 102, '2025-04-21 09:15:00', 'WITHDRAWAL', 30000, NULL);INSERT INTO transactions VALUES (501, 102, '2025-04-21 09:20:00', 'WITHDRAWAL', 2000, NULL);SELECT * FROM alerts;
ResultMySQL 8.4
mysql> CREATE TABLE alerts (
Query OK, 0 rows affected
mysql> CREATE TRIGGER trg_large_withdrawal
Query OK, 0 rows affected
mysql> INSERT INTO transactions VALUES (500, 102, '2025-04-21 09:15:00', 'WIT…
Query OK, 1 row affected
mysql> INSERT INTO transactions VALUES (501, 102, '2025-04-21 09:20:00', 'WIT…
Query OK, 1 row affected
mysql> SELECT * FROM alerts
alert_idtxn_idaccount_idamountreason
150010230000Large withdrawal
1 row in set
How this query works, step by step
  1. The trigger runs after every inserted transaction row (Triggers).
  2. The INSERT ... SELECT ... WHERE writes an alert only when the condition holds, without needing IF and BEGIN ... END.
  3. The 30,000 withdrawal creates an alert; the 2,000 one does not.

Your turn

Exercise: Biggest single deposit per customer

For each customer, show their largest single deposit.

Expected resultMySQL 8.4
full_namelargest_deposit
Priya Sharma200000
Arnav Gupta200000
George Mathew120000
Lakshmi Rao75000
Rahul Verma60000
Fatima Sheikh50000
6 rows in set
Hint 1

Join transactions to accounts and customers, filter txn_type = 'DEPOSIT', MAX(amount) per customer.

Exercise: Daily withdrawal totals above 20,000

List each account and day where withdrawals added up to more than 20,000.

Expected resultMySQL 8.4
account_iddaywithdrawnwithdrawals
1062025-03-14395004
1 row in set
Hint 1

Group by account_id and DATE(txn_time), filter with HAVING.

What you practised

TaskSQL skills
Reconciliation, transfer legssigned CASE, SUM, anti-join
Statement, monthly flowsrunning SUM() OVER, conditional aggregation
Fraud patternRANGE BETWEEN INTERVAL ... PRECEDING window frame
Dormant accountsLEFT JOIN, HAVING MAX(...), date arithmetic
Transfer, alertstransaction, FOR UPDATE, SIGNAL, procedure, trigger, ACID

Project FAQ

How do you implement a money transfer in SQL?

In one transaction: lock both account rows (SELECT ... FOR UPDATE), check the balance, debit one account, credit the other, write both ledger entries, then COMMIT; on any error, ROLLBACK.

How do you calculate a running balance in SQL?

Sum the signed amounts with a window: SUM(signed_amount) OVER (PARTITION BY account_id ORDER BY txn_time, txn_id).

How can SQL detect suspicious transactions?

With rules expressed as queries, such as amounts above a threshold, or several withdrawals in a short time using a window frame like RANGE BETWEEN INTERVAL 60 MINUTE PRECEDING AND CURRENT ROW.

SQL Project: E-commerce Sales Analysis

SQL Tutorial › SQL Projects › Project: E-commerce Sales Analysis

SQL Project: E-commerce Sales Analysis

ProjectProject 1 of 530 min read13 queries run on MySQL 8.4

In this project you are the data analyst of an online store. Management wants answers about 2025: how much did we sell, what is growing, which products and customers matter most, and where do we lose money? You will answer each question with one query on a realistic four-table database, using everything from GROUP BY to window functions and CTEs. Every query was run on MySQL 8.4.

In this lesson
  • Define revenue, profit and AOV correctly in SQL
  • Build monthly trends with month-over-month growth
  • Rank products and customers, including per category
  • Segment customers (repeat, RFM) and build a cohort table

The business and its database

TableOne row perKey columns
customersregistered customercustomer_id, city, signup_date
productsproduct in the cataloguecategory, list_price, unit_cost
ordersorder placed in 2025order_date, status (Delivered, Cancelled, Returned), channel
order_itemsproduct line in an orderquantity, unit_price (the price actually charged)
customers · 14 rows
customer_idfull_namecitysignup_date
1Aarav ShahPune2024-10-24
2Diya MenonKolkata2024-04-09
3Kabir AliDelhi2024-10-03
4Isha ReddyHyderabad2024-07-02
5Vihaan GuptaJaipur2024-08-27
6Anaya IyerBengaluru2024-01-25
7Reyansh DasChennai2024-12-05
8Myra KapoorMumbai2024-09-14
9Arjun NairPune2024-04-19
10Saanvi JoshiKolkata2024-11-04
11Ayaan KhanDelhi2024-04-23
12Kiara BoseHyderabad2024-05-27
13Rudra PatilJaipur2024-09-05
14Zara ThomasBengaluru2024-12-20
products · 13 rows
product_idproduct_namecategorylist_priceunit_cost
1Wireless EarbudsElectronics24991400
2Smart WatchElectronics49993100
3Bluetooth SpeakerElectronics1799950
4Power BankElectronics1299700
5Running ShoesFashion32991700
6Denim JacketFashion25991300
7Cotton T-ShirtFashion599250
8Yoga MatSports899400
9Dumbbell SetSports21991250
10Cricket BatSports34992000
11Coffee MakerHome39992400
12Table LampHome1199600
13Water BottleHome499180
orders · first 8 of 60 rows
order_idcustomer_idorder_datestatuschannel
500142025-01-02DeliveredWeb
500212025-01-04ReturnedWeb
500372025-01-17DeliveredWeb
500462025-01-20DeliveredMarketplace
500522025-01-21DeliveredMarketplace
500682025-01-30DeliveredApp
5007112025-01-30DeliveredWeb
500872025-02-01DeliveredApp
order_items · first 8 of 113 rows
order_idproduct_idquantityunit_price
5001224999
5001311799
5002513299
5003122499
5003911980
50031211199
5004132250
500473599

Business rules for this project:

  • Revenue counts only Delivered orders: SUM(quantity * unit_price).
  • Cost is quantity * unit_cost; gross profit is revenue minus cost.
  • AOV (average order value) is revenue divided by the number of delivered orders.

To follow along on your own MySQL server, create a database and run this script:

ecommerce.sql
-- sqatools.in SQL project 1: e-commerce sales analysis (generated by gen_projects.py)CREATE TABLE customers (  customer_id INT PRIMARY KEY,  full_name   VARCHAR(50) NOT NULL,  city        VARCHAR(30) NOT NULL,  signup_date DATE NOT NULL);CREATE TABLE products (  product_id   INT PRIMARY KEY,  product_name VARCHAR(50) NOT NULL,  category     VARCHAR(20) NOT NULL,  list_price   INT NOT NULL,  unit_cost    INT NOT NULL);CREATE TABLE orders (  order_id    INT PRIMARY KEY,  customer_id INT NOT NULL,  order_date  DATE NOT NULL,  status      VARCHAR(12) NOT NULL,  channel     VARCHAR(12) NOT NULL,  FOREIGN KEY (customer_id) REFERENCES customers (customer_id));CREATE TABLE order_items (  order_id   INT,  product_id INT,  quantity   INT NOT NULL,  unit_price INT NOT NULL,  PRIMARY KEY (order_id, product_id),  FOREIGN KEY (order_id) REFERENCES orders (order_id),  FOREIGN KEY (product_id) REFERENCES products (product_id));INSERT INTO customers VALUES(1, 'Aarav Shah', 'Pune', '2024-10-24'),(2, 'Diya Menon', 'Kolkata', '2024-04-09'),(3, 'Kabir Ali', 'Delhi', '2024-10-03'),(4, 'Isha Reddy', 'Hyderabad', '2024-07-02'),(5, 'Vihaan Gupta', 'Jaipur', '2024-08-27'),(6, 'Anaya Iyer', 'Bengaluru', '2024-01-25'),(7, 'Reyansh Das', 'Chennai', '2024-12-05'),(8, 'Myra Kapoor', 'Mumbai', '2024-09-14'),(9, 'Arjun Nair', 'Pune', '2024-04-19'),(10, 'Saanvi Joshi', 'Kolkata', '2024-11-04'),(11, 'Ayaan Khan', 'Delhi', '2024-04-23'),(12, 'Kiara Bose', 'Hyderabad', '2024-05-27'),(13, 'Rudra Patil', 'Jaipur', '2024-09-05'),(14, 'Zara Thomas', 'Bengaluru', '2024-12-20');INSERT INTO products VALUES(1, 'Wireless Earbuds', 'Electronics', 2499, 1400),(2, 'Smart Watch', 'Electronics', 4999, 3100),(3, 'Bluetooth Speaker', 'Electronics', 1799, 950),(4, 'Power Bank', 'Electronics', 1299, 700),(5, 'Running Shoes', 'Fashion', 3299, 1700),(6, 'Denim Jacket', 'Fashion', 2599, 1300),(7, 'Cotton T-Shirt', 'Fashion', 599, 250),(8, 'Yoga Mat', 'Sports', 899, 400),(9, 'Dumbbell Set', 'Sports', 2199, 1250),(10, 'Cricket Bat', 'Sports', 3499, 2000),(11, 'Coffee Maker', 'Home', 3999, 2400),(12, 'Table Lamp', 'Home', 1199, 600),(13, 'Water Bottle', 'Home', 499, 180);INSERT INTO orders VALUES(5001, 4, '2025-01-02', 'Delivered', 'Web'),(5002, 1, '2025-01-04', 'Returned', 'Web'),(5003, 7, '2025-01-17', 'Delivered', 'Web'),(5004, 6, '2025-01-20', 'Delivered', 'Marketplace'),(5005, 2, '2025-01-21', 'Delivered', 'Marketplace'),(5006, 8, '2025-01-30', 'Delivered', 'App'),(5007, 11, '2025-01-30', 'Delivered', 'Web'),(5008, 7, '2025-02-01', 'Delivered', 'App'),(5009, 3, '2025-02-14', 'Delivered', 'Marketplace'),(5010, 1, '2025-02-19', 'Delivered', 'App'),(5011, 4, '2025-02-23', 'Delivered', 'Web'),(5012, 3, '2025-02-26', 'Delivered', 'App'),(5013, 8, '2025-03-13', 'Delivered', 'App'),(5014, 4, '2025-03-13', 'Delivered', 'App'),(5015, 10, '2025-03-18', 'Delivered', 'Marketplace'),(5016, 7, '2025-03-19', 'Delivered', 'App'),(5017, 5, '2025-03-23', 'Delivered', 'Marketplace'),(5018, 7, '2025-04-03', 'Delivered', 'Web'),(5019, 1, '2025-04-04', 'Delivered', 'App'),(5020, 3, '2025-04-06', 'Delivered', 'Web'),(5021, 8, '2025-04-15', 'Delivered', 'Marketplace'),(5022, 6, '2025-05-03', 'Delivered', 'Web'),(5023, 8, '2025-05-05', 'Delivered', 'App'),(5024, 7, '2025-05-06', 'Delivered', 'App'),(5025, 11, '2025-05-26', 'Cancelled', 'App'),(5026, 8, '2025-05-27', 'Delivered', 'App'),(5027, 5, '2025-05-31', 'Returned', 'Marketplace'),(5028, 7, '2025-06-03', 'Delivered', 'App'),(5029, 1, '2025-06-05', 'Delivered', 'App'),(5030, 9, '2025-06-12', 'Delivered', 'App'),(5031, 4, '2025-06-18', 'Returned', 'Web'),(5032, 6, '2025-06-21', 'Delivered', 'App'),(5033, 7, '2025-06-21', 'Delivered', 'Web'),(5034, 4, '2025-07-02', 'Delivered', 'Marketplace'),(5035, 4, '2025-08-04', 'Cancelled', 'Marketplace'),(5036, 1, '2025-08-10', 'Delivered', 'Web'),(5037, 1, '2025-08-18', 'Cancelled', 'App'),(5038, 11, '2025-08-23', 'Delivered', 'Web'),(5039, 6, '2025-08-23', 'Delivered', 'App'),(5040, 1, '2025-08-26', 'Delivered', 'Web'),(5041, 11, '2025-09-01', 'Delivered', 'Marketplace'),(5042, 6, '2025-09-07', 'Delivered', 'Web'),(5043, 9, '2025-09-21', 'Delivered', 'Marketplace'),(5044, 4, '2025-09-22', 'Delivered', 'App'),(5045, 7, '2025-09-27', 'Delivered', 'Marketplace'),(5046, 12, '2025-09-27', 'Delivered', 'Marketplace'),(5047, 1, '2025-09-28', 'Delivered', 'Marketplace'),(5048, 4, '2025-10-03', 'Delivered', 'Web'),(5049, 1, '2025-10-22', 'Delivered', 'Web'),(5050, 1, '2025-10-24', 'Returned', 'Web'),(5051, 3, '2025-10-28', 'Delivered', 'App'),(5052, 2, '2025-11-15', 'Delivered', 'Web'),(5053, 8, '2025-11-22', 'Delivered', 'App'),(5054, 8, '2025-11-23', 'Delivered', 'App'),(5055, 11, '2025-11-23', 'Delivered', 'Web'),(5056, 4, '2025-11-25', 'Cancelled', 'Marketplace'),(5057, 3, '2025-12-05', 'Delivered', 'Web'),(5058, 6, '2025-12-07', 'Cancelled', 'App'),(5059, 9, '2025-12-11', 'Delivered', 'Web'),(5060, 2, '2025-12-16', 'Cancelled', 'Web');INSERT INTO order_items VALUES(5001, 2, 2, 4999),(5001, 3, 1, 1799),(5002, 5, 1, 3299),(5003, 1, 2, 2499),(5003, 9, 1, 1980),(5003, 12, 1, 1199),(5004, 1, 3, 2250),(5004, 7, 3, 599),(5005, 2, 1, 4999),(5005, 5, 3, 3299),(5005, 10, 1, 3499),(5006, 5, 1, 3299),(5007, 3, 1, 1620),(5007, 10, 1, 3499),(5008, 4, 1, 1299),(5009, 1, 3, 2499),(5009, 3, 1, 1799),(5010, 12, 3, 1199),(5011, 3, 1, 1799),(5011, 9, 2, 2199),(5012, 4, 1, 1299),(5012, 5, 1, 2970),(5012, 10, 2, 3499),(5013, 6, 3, 2340),(5013, 7, 1, 599),(5014, 2, 2, 4500),(5014, 10, 1, 3499),(5015, 11, 1, 3999),(5015, 12, 1, 1199),(5016, 4, 1, 1299),(5017, 9, 1, 2199),(5017, 10, 1, 3499),(5018, 4, 2, 1299),(5018, 10, 1, 3150),(5019, 5, 1, 3299),(5019, 6, 3, 2599),(5019, 10, 1, 3499),(5020, 1, 1, 2499),(5020, 3, 1, 1799),(5021, 2, 2, 4500),(5021, 4, 1, 1299),(5022, 2, 3, 4999),(5022, 4, 1, 1299),(5023, 3, 1, 1799),(5023, 8, 1, 899),(5023, 11, 1, 3999),(5024, 3, 2, 1799),(5024, 10, 3, 3499),(5025, 9, 1, 2199),(5026, 3, 2, 1799),(5026, 5, 1, 3299),(5026, 6, 2, 2599),(5027, 3, 1, 1799),(5027, 11, 3, 3999),(5028, 9, 1, 2199),(5029, 1, 1, 2499),(5029, 7, 1, 599),(5029, 10, 3, 3499),(5030, 4, 1, 1299),(5030, 7, 1, 599),(5031, 3, 2, 1799),(5031, 4, 3, 1170),(5032, 6, 2, 2599),(5032, 11, 1, 3999),(5033, 2, 1, 4999),(5033, 5, 1, 3299),(5033, 11, 2, 3600),(5034, 4, 1, 1299),(5035, 1, 1, 2499),(5036, 7, 1, 599),(5037, 9, 1, 2199),(5037, 11, 3, 3600),(5038, 4, 1, 1299),(5038, 5, 3, 3299),(5039, 8, 1, 899),(5040, 1, 1, 2499),(5040, 6, 1, 2599),(5041, 3, 1, 1799),(5041, 9, 1, 2199),(5042, 6, 1, 2599),(5042, 10, 2, 3150),(5043, 12, 3, 1080),(5044, 3, 1, 1799),(5045, 2, 2, 4999),(5046, 2, 1, 4500),(5046, 3, 1, 1799),(5046, 8, 2, 810),(5047, 2, 1, 4999),(5047, 8, 2, 899),(5048, 1, 1, 2250),(5048, 3, 3, 1799),(5049, 11, 3, 3999),(5050, 1, 2, 2499),(5050, 11, 1, 3999),(5051, 1, 3, 2499),(5051, 3, 3, 1799),(5052, 1, 1, 2499),(5052, 8, 3, 899),(5052, 11, 1, 3999),(5053, 2, 1, 4999),(5053, 6, 2, 2599),(5054, 12, 3, 1199),(5055, 6, 2, 2599),(5055, 8, 1, 899),(5055, 9, 1, 1980),(5056, 3, 2, 1620),(5056, 12, 1, 1199),(5057, 7, 1, 540),(5058, 4, 2, 1299),(5058, 7, 2, 599),(5059, 1, 3, 2499),(5060, 5, 1, 3299),(5060, 9, 1, 1980);

Task 1: Headline KPIs

Total delivered orders, revenue, gross profit and average order value for 2025.

MySQL
SELECT COUNT(DISTINCT o.order_id)                        AS delivered_orders,       SUM(oi.quantity * oi.unit_price)                  AS revenue,       SUM(oi.quantity * (oi.unit_price - p.unit_cost))  AS gross_profit,       ROUND(SUM(oi.quantity * oi.unit_price) / COUNT(DISTINCT o.order_id)) AS avg_order_valueFROM orders oJOIN order_items oi ON oi.order_id = o.order_idJOIN products p     ON p.product_id = oi.product_idWHERE o.status = 'Delivered';
ResultMySQL 8.4
delivered_ordersrevenuegross_profitavg_order_value
503791951634957584
1 row in set
How this query works, step by step
  1. The joins create one row per order line with its price and cost.
  2. WHERE o.status = 'Delivered' applies the revenue rule.
  3. COUNT(DISTINCT o.order_id) counts orders, not lines.
  4. Profit per line is quantity × (unit_price − unit_cost); AOV divides revenue by delivered orders.

Task 2: Order status breakdown

What share of orders is cancelled or returned, per sales channel?

MySQL
SELECT channel,       COUNT(*) AS orders,       SUM(status = 'Cancelled') AS cancelled,       SUM(status = 'Returned')  AS returned,       ROUND(100 * SUM(status <> 'Delivered') / COUNT(*), 1) AS problem_pctFROM ordersGROUP BY channelORDER BY problem_pct DESC;
ResultMySQL 8.4
channelorderscancelledreturnedproblem_pct
Marketplace152120.0
Web221318.2
App233013.0
3 rows in set
How this query works, step by step
  1. GROUP BY channel makes one row per channel.
  2. In MySQL a comparison is 1 or 0, so SUM(status = 'Cancelled') counts cancelled orders: a short form of SUM(CASE WHEN ... THEN 1 ELSE 0 END).
  3. problem_pct is the share of orders not delivered.

Task 3: Monthly revenue and growth

MySQL
WITH monthly AS (  SELECT DATE_FORMAT(o.order_date, '%Y-%m') AS month,         COUNT(DISTINCT o.order_id)          AS orders,         SUM(oi.quantity * oi.unit_price)    AS revenue  FROM orders o  JOIN order_items oi ON oi.order_id = o.order_id  WHERE o.status = 'Delivered'  GROUP BY month)SELECT month, orders, revenue,       ROUND(100 * (revenue - LAG(revenue) OVER (ORDER BY month))             / LAG(revenue) OVER (ORDER BY month), 1) AS mom_growth_pct,       SUM(revenue) OVER (ORDER BY month) AS revenue_ytdFROM monthlyORDER BY month;
ResultMySQL 8.4
monthordersrevenuemom_growth_pctrevenue_ytd
2025-01655334NULL55334
2025-02531656-42.886990
2025-035323132.1119303
2025-044349408.1154243
2025-0544918340.8203426
2025-06542387-13.8245813
2025-0711299-96.9247112
2025-084177921269.7264904
2025-09742650139.7307554
2025-10332538-23.7340092
2025-11431066-4.5371158
2025-1228037-74.1379195
12 rows in set
How this query works, step by step
  1. The CTE totals delivered revenue per month.
  2. LAG(revenue) brings in the previous month, so month-over-month growth is simple arithmetic (LAG & LEAD).
  3. SUM(revenue) OVER (ORDER BY month) is the year-to-date running total (Running Totals).

Task 4: Top 5 products by revenue

SQL
SELECT p.product_name, p.category,       SUM(oi.quantity)                 AS units,       SUM(oi.quantity * oi.unit_price) AS revenueFROM order_items oiJOIN orders o   ON o.order_id = oi.order_idJOIN products p ON p.product_id = oi.product_idWHERE o.status = 'Delivered'GROUP BY p.product_id, p.product_name, p.categoryORDER BY revenue DESCLIMIT 5;
ResultMySQL 8.4
product_namecategoryunitsrevenue
Smart WatchElectronics1677489
Cricket BatSports1654937
Wireless EarbudsElectronics1946485
Denim JacketFashion1640807
Running ShoesFashion1135960
5 rows in set

Task 5: Category revenue and margin

MySQL
SELECT p.category,       SUM(oi.quantity * oi.unit_price)                       AS revenue,       SUM(oi.quantity * (oi.unit_price - p.unit_cost))       AS profit,       ROUND(100 * SUM(oi.quantity * (oi.unit_price - p.unit_cost))                 / SUM(oi.quantity * oi.unit_price), 1)        AS margin_pctFROM order_items oiJOIN orders o   ON o.order_id = oi.order_idJOIN products p ON p.product_id = oi.product_idWHERE o.status = 'Delivered'GROUP BY p.categoryORDER BY profit DESC;
ResultMySQL 8.4
categoryrevenueprofitmargin_pct
Electronics1709666971640.8
Fashion815004000049.1
Sports787043395443.1
Home480251982541.3
4 rows in set
How this query works, step by step
  1. Revenue and profit are summed per category.
  2. Margin % = profit ÷ revenue × 100. A category can have high revenue but a thin margin, which is why both columns matter.

Task 6: Discount impact

Some lines were sold below the list price. How much revenue did discounts cost per product?

SQL
SELECT p.product_name,       SUM(oi.quantity) AS units,       SUM(oi.quantity * (p.list_price - oi.unit_price)) AS discount_givenFROM order_items oiJOIN orders o   ON o.order_id = oi.order_idJOIN products p ON p.product_id = oi.product_idWHERE o.status = 'Delivered' AND oi.unit_price < p.list_priceGROUP BY p.product_id, p.product_nameORDER BY discount_given DESC;
ResultMySQL 8.4
product_nameunitsdiscount_given
Smart Watch52495
Cricket Bat31047
Wireless Earbuds4996
Coffee Maker2798
Denim Jacket3777
Dumbbell Set2438
Table Lamp3357
Running Shoes1329
Bluetooth Speaker1179
Yoga Mat2178
Cotton T-Shirt159
11 rows in set

Task 7: Best seller in each category

MySQL
WITH product_sales AS (  SELECT p.category, p.product_name, SUM(oi.quantity) AS units  FROM order_items oi  JOIN orders o   ON o.order_id = oi.order_id  JOIN products p ON p.product_id = oi.product_id  WHERE o.status = 'Delivered'  GROUP BY p.category, p.product_name),ranked AS (  SELECT product_sales.*, RANK() OVER (PARTITION BY category ORDER BY units DESC) AS rnk  FROM product_sales)SELECT category, product_name, unitsFROM rankedWHERE rnk = 1ORDER BY category;
ResultMySQL 8.4
categoryproduct_nameunits
ElectronicsWireless Earbuds19
ElectronicsBluetooth Speaker19
FashionDenim Jacket16
HomeTable Lamp11
SportsCricket Bat16
5 rows in set

The Top N per Group pattern: rank inside each category, keep rank 1 (ties are kept).

Task 8: Products that never sold

SQL
SELECT p.product_id, p.product_name, p.categoryFROM products pWHERE NOT EXISTS (SELECT 1 FROM order_items oi WHERE oi.product_id = p.product_id);
ResultMySQL 8.4
product_idproduct_namecategory
13Water BottleHome
1 row in set

Task 9: Top customers and repeat buyers

SQL
SELECT c.customer_id, c.full_name, c.city,       COUNT(DISTINCT o.order_id)       AS orders,       SUM(oi.quantity * oi.unit_price) AS lifetime_value,       CASE WHEN COUNT(DISTINCT o.order_id) > 1 THEN 'Repeat' ELSE 'One-time' END AS buyer_typeFROM customers cJOIN orders o       ON o.customer_id = c.customer_id AND o.status = 'Delivered'JOIN order_items oi ON oi.order_id = o.order_idGROUP BY c.customer_id, c.full_name, c.cityORDER BY lifetime_value DESCLIMIT 5;
ResultMySQL 8.4
customer_idfull_namecityorderslifetime_valuebuyer_type
7Reyansh DasChennai858313Repeat
1Aarav ShahPune756278Repeat
8Myra KapoorMumbai753803Repeat
6Anaya IyerBengaluru543838Repeat
4Isha ReddyHyderabad641238Repeat
5 rows in set

How many customers are repeat buyers, one-time buyers, or have never bought?

SQL
WITH per_customer AS (  SELECT c.customer_id, COUNT(o.order_id) AS delivered_orders  FROM customers c  LEFT JOIN orders o ON o.customer_id = c.customer_id AND o.status = 'Delivered'  GROUP BY c.customer_id)SELECT CASE WHEN delivered_orders = 0 THEN 'Never bought'            WHEN delivered_orders = 1 THEN 'One-time'            ELSE 'Repeat' END AS segment,       COUNT(*) AS customersFROM per_customerGROUP BY segmentORDER BY customers DESC;
ResultMySQL 8.4
segmentcustomers
Repeat9
One-time3
Never bought2
3 rows in set
How this query works, step by step
  1. The LEFT JOIN keeps customers with no delivered order; the status condition sits in ON so it does not remove them (LEFT JOIN).
  2. COUNT(o.order_id) gives 0 for them.
  3. A CASE turns the count into a segment, and the outer query counts customers per segment.

Task 10: RFM segmentation

RFM scores customers by Recency (days since last order), Frequency (orders) and Monetary value, here as of 31 Dec 2025:

MySQL
WITH rfm AS (  SELECT o.customer_id,         DATEDIFF('2025-12-31', MAX(o.order_date)) AS recency_days,         COUNT(DISTINCT o.order_id)                AS frequency,         SUM(oi.quantity * oi.unit_price)          AS monetary  FROM orders o  JOIN order_items oi ON oi.order_id = o.order_id  WHERE o.status = 'Delivered'  GROUP BY o.customer_id),scored AS (  SELECT rfm.*,         NTILE(3) OVER (ORDER BY recency_days DESC) AS r_score,         NTILE(3) OVER (ORDER BY frequency)         AS f_score,         NTILE(3) OVER (ORDER BY monetary)          AS m_score  FROM rfm)SELECT customer_id, recency_days, frequency, monetary, r_score, f_score, m_score,       CASE WHEN r_score = 3 AND f_score >= 2 AND m_score >= 2 THEN 'Champion'            WHEN r_score = 1 THEN 'At risk'            ELSE 'Regular' END AS segmentFROM scoredORDER BY r_score DESC, m_score DESC, customer_id;
ResultMySQL 8.4
customer_idrecency_daysfrequencymonetaryr_scoref_scorem_scoresegment
838753803333Champion
326538295322Champion
1138428390322Champion
920312635321Regular
170756278233Regular
246227590212Regular
489641238232Regular
129517919211Regular
6115543838123At risk
795858313133At risk
528315698111At risk
1028815198111At risk
12 rows in set
How this query works, step by step
  1. rfm computes the three raw measures per customer.
  2. NTILE(3) splits customers into thirds for each measure: 3 is best (most recent, most frequent, highest spend). Recency is sorted descending so that a small number of days gets the high score.
  3. A CASE turns the scores into marketing segments.

Task 11: Cohort analysis

Group customers by the month of their first delivered order, and count how many of each cohort ordered again in later months:

MySQL
WITH firsts AS (  SELECT customer_id, MIN(order_date) AS first_order  FROM orders  WHERE status = 'Delivered'  GROUP BY customer_id)SELECT DATE_FORMAT(f.first_order, '%Y-%m') AS cohort,       COUNT(DISTINCT f.customer_id) AS customers,       COUNT(DISTINCT CASE WHEN o.order_date > f.first_order THEN o.customer_id END) AS came_backFROM firsts fLEFT JOIN orders o ON o.customer_id = f.customer_id AND o.status = 'Delivered'GROUP BY cohortORDER BY cohort;
ResultMySQL 8.4
cohortcustomerscame_back
2025-0166
2025-0222
2025-0320
2025-0611
2025-0910
5 rows in set
How this query works, step by step
  1. firsts finds each customer’s first delivered order date.
  2. Customers are grouped by that month (their cohort).
  3. COUNT(DISTINCT CASE WHEN o.order_date > f.first_order THEN o.customer_id END) counts customers who have at least one later order. CASE without ELSE gives NULL, which COUNT ignores.

Task 12: Revenue by city and channel (pivot)

SQL
SELECT c.city,       SUM(CASE WHEN o.channel = 'Web'         THEN oi.quantity * oi.unit_price ELSE 0 END) AS web,       SUM(CASE WHEN o.channel = 'App'         THEN oi.quantity * oi.unit_price ELSE 0 END) AS app,       SUM(CASE WHEN o.channel = 'Marketplace' THEN oi.quantity * oi.unit_price ELSE 0 END) AS marketplace,       SUM(oi.quantity * oi.unit_price) AS totalFROM orders oJOIN customers c    ON c.customer_id = o.customer_idJOIN order_items oi ON oi.order_id = o.order_idWHERE o.status = 'Delivered'GROUP BY c.cityORDER BY total DESC;
ResultMySQL 8.4
citywebappmarketplacetotal
Pune25191336851003768913
Delhi29230241611329466685
Chennai2942318892999858313
Mumbai0435041029953803
Hyderabad2564114298921849157
Bengaluru2519510096854743838
Kolkata919502359332788
Jaipur0056985698
8 rows in set

Your turn

Exercise: Quarterly revenue

Show delivered revenue per quarter of 2025 (Q1 to Q4).

Expected resultMySQL 8.4
quarterrevenue
Q1119303
Q2126510
Q361741
Q471641
4 rows in set
Hint 1

CONCAT('Q', QUARTER(o.order_date)) as the group.

Exercise: Customers who never ordered

List registered customers who have never placed any order.

Expected resultMySQL 8.4
customer_idfull_namesignup_date
13Rudra Patil2024-09-05
14Zara Thomas2024-12-20
2 rows in set
Hint 1

LEFT JOIN orders ... WHERE o.order_id IS NULL, or NOT EXISTS.

What you practised

TaskSQL skills
KPIs, marginsjoins, SUM, COUNT(DISTINCT), business rules in WHERE
Status, pivotconditional aggregation
Monthly trendCTE, DATE_FORMAT, LAG, running SUM() OVER
Top products, best per categoryORDER BY ... LIMIT, RANK() OVER (PARTITION BY)
Never sold, never orderedNOT EXISTS, LEFT JOIN ... IS NULL
Segments, RFM, cohortsCASE, NTILE, MIN, conditional COUNT(DISTINCT)
Tip

To turn this into a portfolio piece, put the script and your queries in a GitHub repository with a README that states each business question, the query and a one-line insight from the result.

Project FAQ

What SQL projects are good for a portfolio?

Projects that answer real business questions on a multi-table database, such as sales analysis, inventory, HR analytics or banking. Show joins, aggregation, window functions and CTEs, and explain the insight each query gives.

How is revenue calculated in SQL?

Sum quantity × price over the order lines that count as sales, usually excluding cancelled and returned orders: SUM(oi.quantity * oi.unit_price) with a WHERE on the order status.

What is RFM analysis in SQL?

A customer segmentation that scores Recency, Frequency and Monetary value, typically with DATEDIFF, COUNT, SUM and NTILE() window functions.

SQL Project: HR and Payroll Analytics

SQL Tutorial › SQL Projects › Project: HR & Payroll Analytics

SQL Project: HR and Payroll Analytics

ProjectProject 3 of 530 min read13 queries run on MySQL 8.4

The People team of a software company wants data instead of spreadsheets. Salaries are stored as a history (one row per change), employees report to managers, and some people have left. In this project you answer real HR questions: headcount and attrition, current payroll, who got which raise, who is underpaid compared with their team, and what the organisation chart looks like. The reference date is 31 December 2025.

In this lesson
  • Get the current value from a history table with ROW_NUMBER
  • Measure headcount, attrition and tenure
  • Analyse raises with LAG and compare pay with window averages
  • Walk the management hierarchy with a recursive CTE

The database

TableOne row perNotes
departmentsdepartment5 departments
employeesperson ever employedmanager_id points to another employee; termination_date is NULL for current staff
salary_historysalary changeannual_salary valid from effective_date until the next change
departments · 5 rows
dept_iddept_namelocation
10EngineeringBengaluru
20QAPune
30SalesMumbai
40SupportHyderabad
50PeopleDelhi
employees · 26 rows
emp_idfirst_namelast_namedept_idmanager_idjob_titlehire_datetermination_date
1AnanyaRao10NULLCTO2016-03-01NULL
2RohanKapoor201Head of QA2017-02-02NULL
3MeeraMenon301Head of Sales2017-09-03NULL
4KabirQureshi401Head of Support2019-01-29NULL
5SaraSinha501Head of People2017-07-19NULL
6VikasJoshi101Senior Engineer2020-06-11NULL
7PoojaMehta106Engineer2022-04-25NULL
8ImranJoshi106Engineer2021-03-07NULL
9LeelaNair106Senior Engineer2020-05-27NULL
10AdityaIyer106Engineer2019-10-10NULL
11NishaSharma106Engineer2023-10-202025-04-20
12FarhanSharma202Senior QA Engineer2024-01-16NULL
13GauriKhan202QA Engineer2021-12-25NULL
14HarshDas202Automation Engineer2021-03-04NULL
15JayaNair202Senior QA Engineer2020-08-15NULL
16KarthikPatel202QA Engineer2022-03-27NULL
17LataDas303Account Executive2019-08-26NULL
18ManishReddy303Sales Associate2020-11-04NULL
19NidhiBose303Account Executive2020-03-21NULL
20PranavNair303Sales Associate2021-04-21NULL
21RituKhan404Support Lead2020-12-27NULL
22SiddharthMehta404Support Agent2024-11-06NULL
23TanviJoshi404Support Agent2019-11-05NULL
24UdayIyer404Support Lead2022-01-22NULL
25VaniKhan505HR Partner2024-03-312025-10-17
26ZubinSharma505Recruiter2021-04-22NULL
salary_history · first 10 of 123 rows
emp_ideffective_dateannual_salary
12016-03-01428000
12017-04-01462000
12018-04-01480000
12019-04-01518000
12020-04-01549000
12021-04-01570000
12022-04-01638000
12023-04-01676000
12024-04-01703000
12025-04-01773000
hr.sql
-- sqatools.in SQL project 3: HR and payroll analytics (generated by gen_projects.py)CREATE TABLE departments (  dept_id   INT PRIMARY KEY,  dept_name VARCHAR(30) NOT NULL,  location  VARCHAR(30) NOT NULL);CREATE TABLE employees (  emp_id        INT PRIMARY KEY,  first_name    VARCHAR(20) NOT NULL,  last_name     VARCHAR(20) NOT NULL,  dept_id       INT NOT NULL,  manager_id    INT,  job_title     VARCHAR(30) NOT NULL,  hire_date     DATE NOT NULL,  termination_date DATE,  FOREIGN KEY (dept_id) REFERENCES departments (dept_id),  FOREIGN KEY (manager_id) REFERENCES employees (emp_id));CREATE TABLE salary_history (  emp_id         INT,  effective_date DATE,  annual_salary  INT NOT NULL,  PRIMARY KEY (emp_id, effective_date),  FOREIGN KEY (emp_id) REFERENCES employees (emp_id));INSERT INTO departments VALUES(10, 'Engineering', 'Bengaluru'),(20, 'QA', 'Pune'),(30, 'Sales', 'Mumbai'),(40, 'Support', 'Hyderabad'),(50, 'People', 'Delhi');INSERT INTO employees VALUES(1, 'Ananya', 'Rao', 10, NULL, 'CTO', '2016-03-01', NULL),(2, 'Rohan', 'Kapoor', 20, 1, 'Head of QA', '2017-02-02', NULL),(3, 'Meera', 'Menon', 30, 1, 'Head of Sales', '2017-09-03', NULL),(4, 'Kabir', 'Qureshi', 40, 1, 'Head of Support', '2019-01-29', NULL),(5, 'Sara', 'Sinha', 50, 1, 'Head of People', '2017-07-19', NULL),(6, 'Vikas', 'Joshi', 10, 1, 'Senior Engineer', '2020-06-11', NULL),(7, 'Pooja', 'Mehta', 10, 6, 'Engineer', '2022-04-25', NULL),(8, 'Imran', 'Joshi', 10, 6, 'Engineer', '2021-03-07', NULL),(9, 'Leela', 'Nair', 10, 6, 'Senior Engineer', '2020-05-27', NULL),(10, 'Aditya', 'Iyer', 10, 6, 'Engineer', '2019-10-10', NULL),(11, 'Nisha', 'Sharma', 10, 6, 'Engineer', '2023-10-20', '2025-04-20'),(12, 'Farhan', 'Sharma', 20, 2, 'Senior QA Engineer', '2024-01-16', NULL),(13, 'Gauri', 'Khan', 20, 2, 'QA Engineer', '2021-12-25', NULL),(14, 'Harsh', 'Das', 20, 2, 'Automation Engineer', '2021-03-04', NULL),(15, 'Jaya', 'Nair', 20, 2, 'Senior QA Engineer', '2020-08-15', NULL),(16, 'Karthik', 'Patel', 20, 2, 'QA Engineer', '2022-03-27', NULL),(17, 'Lata', 'Das', 30, 3, 'Account Executive', '2019-08-26', NULL),(18, 'Manish', 'Reddy', 30, 3, 'Sales Associate', '2020-11-04', NULL),(19, 'Nidhi', 'Bose', 30, 3, 'Account Executive', '2020-03-21', NULL),(20, 'Pranav', 'Nair', 30, 3, 'Sales Associate', '2021-04-21', NULL),(21, 'Ritu', 'Khan', 40, 4, 'Support Lead', '2020-12-27', NULL),(22, 'Siddharth', 'Mehta', 40, 4, 'Support Agent', '2024-11-06', NULL),(23, 'Tanvi', 'Joshi', 40, 4, 'Support Agent', '2019-11-05', NULL),(24, 'Uday', 'Iyer', 40, 4, 'Support Lead', '2022-01-22', NULL),(25, 'Vani', 'Khan', 50, 5, 'HR Partner', '2024-03-31', '2025-10-17'),(26, 'Zubin', 'Sharma', 50, 5, 'Recruiter', '2021-04-22', NULL);INSERT INTO salary_history VALUES(1, '2016-03-01', 428000),(1, '2017-04-01', 462000),(1, '2018-04-01', 480000),(1, '2019-04-01', 518000),(1, '2020-04-01', 549000),(1, '2021-04-01', 570000),(1, '2022-04-01', 638000),(1, '2023-04-01', 676000),(1, '2024-04-01', 703000),(1, '2025-04-01', 773000),(2, '2017-02-02', 256000),(2, '2018-04-01', 281000),(2, '2019-04-01', 314000),(2, '2020-04-01', 339000),(2, '2021-04-01', 372000),(2, '2022-04-01', 416000),(2, '2023-04-01', 457000),(2, '2024-04-01', 484000),(2, '2025-04-01', 542000),(3, '2017-09-03', 257000),(3, '2019-04-01', 282000),(3, '2020-04-01', 293000),(3, '2021-04-01', 310000),(3, '2022-04-01', 334000),(3, '2024-04-01', 354000),(4, '2019-01-29', 266000),(4, '2020-04-01', 287000),(4, '2021-04-01', 309000),(4, '2022-04-01', 333000),(4, '2023-04-01', 372000),(4, '2024-04-01', 401000),(4, '2025-04-01', 417000),(5, '2017-07-19', 256000),(5, '2018-04-01', 266000),(5, '2020-04-01', 297000),(5, '2021-04-01', 326000),(5, '2022-04-01', 345000),(5, '2024-04-01', 365000),(6, '2020-06-11', 173000),(6, '2021-04-01', 186000),(6, '2022-04-01', 204000),(6, '2023-04-01', 220000),(6, '2024-04-01', 237000),(6, '2025-04-01', 260000),(7, '2022-04-25', 72000),(7, '2023-04-01', 80000),(7, '2025-04-01', 83000),(8, '2021-03-07', 69000),(8, '2022-04-01', 74000),(8, '2023-04-01', 82000),(8, '2024-04-01', 86000),(8, '2025-04-01', 89000),(9, '2020-05-27', 171000),(9, '2021-04-01', 184000),(9, '2022-04-01', 202000),(9, '2023-04-01', 214000),(9, '2025-04-01', 239000),(10, '2019-10-10', 69000),(10, '2020-04-01', 71000),(10, '2021-04-01', 73000),(10, '2023-04-01', 81000),(10, '2024-04-01', 87000),(10, '2025-04-01', 90000),(11, '2023-10-20', 65000),(11, '2024-04-01', 68000),(11, '2025-04-01', 74000),(12, '2024-01-16', 167000),(12, '2025-04-01', 180000),(13, '2021-12-25', 69000),(13, '2022-04-01', 75000),(13, '2023-04-01', 78000),(13, '2024-04-01', 81000),(13, '2025-04-01', 84000),(14, '2021-03-04', 72000),(14, '2022-04-01', 80000),(14, '2023-04-01', 89000),(14, '2025-04-01', 92000),(15, '2020-08-15', 159000),(15, '2022-04-01', 178000),(15, '2023-04-01', 192000),(15, '2025-04-01', 203000),(16, '2022-03-27', 68000),(16, '2023-04-01', 74000),(16, '2024-04-01', 78000),(16, '2025-04-01', 84000),(17, '2019-08-26', 98000),(17, '2020-04-01', 105000),(17, '2021-04-01', 113000),(17, '2022-04-01', 119000),(17, '2024-04-01', 128000),(18, '2020-11-04', 74000),(18, '2021-04-01', 76000),(18, '2025-04-01', 80000),(19, '2020-03-21', 100000),(19, '2021-04-01', 106000),(19, '2023-04-01', 112000),(19, '2024-04-01', 123000),(19, '2025-04-01', 135000),(20, '2021-04-21', 66000),(20, '2022-04-01', 68000),(20, '2023-04-01', 72000),(20, '2024-04-01', 80000),(21, '2020-12-27', 119000),(21, '2022-04-01', 128000),(21, '2023-04-01', 135000),(21, '2024-04-01', 151000),(21, '2025-04-01', 163000),(22, '2024-11-06', 67000),(22, '2025-04-01', 73000),(23, '2019-11-05', 70000),(23, '2020-04-01', 74000),(23, '2021-04-01', 79000),(23, '2023-04-01', 85000),(23, '2024-04-01', 90000),(23, '2025-04-01', 93000),(24, '2022-01-22', 112000),(24, '2024-04-01', 116000),(24, '2025-04-01', 127000),(25, '2024-03-31', 102000),(26, '2021-04-22', 64000),(26, '2023-04-01', 70000),(26, '2024-04-01', 72000),(26, '2025-04-01', 76000);

Task 1: Headcount and attrition

MySQL
SELECT d.dept_name,       COUNT(*)                          AS ever_employed,       SUM(e.termination_date IS NULL)   AS current_staff,       SUM(e.termination_date IS NOT NULL) AS leavers,       ROUND(100 * SUM(e.termination_date IS NOT NULL) / COUNT(*), 1) AS attrition_pctFROM employees eJOIN departments d ON d.dept_id = e.dept_idGROUP BY d.dept_nameORDER BY attrition_pct DESC, d.dept_name;
ResultMySQL 8.4
dept_nameever_employedcurrent_staffleaversattrition_pct
People32133.3
Engineering76114.3
QA6600.0
Sales5500.0
Support5500.0
5 rows in set
How this query works, step by step
  1. Every employee row is counted once per department.
  2. termination_date IS NULL is 1 for current staff and 0 for leavers, so SUM counts them.
  3. Attrition % = leavers ÷ everyone ever employed in the department.

Task 2: Current salary from the history

The salary in force today is the row with the latest effective_date for each employee: a top-1-per-group problem (Top N per Group):

SQL
WITH latest AS (  SELECT emp_id, annual_salary, effective_date,         ROW_NUMBER() OVER (PARTITION BY emp_id ORDER BY effective_date DESC) AS rn  FROM salary_history)SELECT e.emp_id, e.first_name, e.last_name, e.job_title, l.annual_salary, l.effective_date AS sinceFROM employees eJOIN latest l ON l.emp_id = e.emp_id AND l.rn = 1WHERE e.termination_date IS NULLORDER BY l.annual_salary DESCLIMIT 8;
ResultMySQL 8.4
emp_idfirst_namelast_namejob_titleannual_salarysince
1AnanyaRaoCTO7730002025-04-01
2RohanKapoorHead of QA5420002025-04-01
4KabirQureshiHead of Support4170002025-04-01
5SaraSinhaHead of People3650002024-04-01
3MeeraMenonHead of Sales3540002024-04-01
6VikasJoshiSenior Engineer2600002025-04-01
9LeelaNairSenior Engineer2390002025-04-01
15JayaNairSenior QA Engineer2030002025-04-01
8 rows in set
How this query works, step by step
  1. ROW_NUMBER() ... ORDER BY effective_date DESC numbers each employee’s salary rows from newest to oldest.
  2. Joining on rn = 1 keeps only the current salary.
  3. WHERE termination_date IS NULL limits the list to current staff.

Task 3: Payroll by department

SQL
WITH latest AS (  SELECT emp_id, annual_salary,         ROW_NUMBER() OVER (PARTITION BY emp_id ORDER BY effective_date DESC) AS rn  FROM salary_history)SELECT d.dept_name,       COUNT(*)                    AS staff,       SUM(l.annual_salary)        AS annual_payroll,       ROUND(AVG(l.annual_salary)) AS avg_salary,       MIN(l.annual_salary)        AS min_salary,       MAX(l.annual_salary)        AS max_salaryFROM employees eJOIN latest l      ON l.emp_id = e.emp_id AND l.rn = 1JOIN departments d ON d.dept_id = e.dept_idWHERE e.termination_date IS NULLGROUP BY d.dept_nameORDER BY annual_payroll DESC;
ResultMySQL 8.4
dept_namestaffannual_payrollavg_salarymin_salarymax_salary
Engineering6153400025566783000773000
QA6118500019750084000542000
Support587300017460073000417000
Sales577700015540080000354000
People244100022050076000365000
5 rows in set

Task 4: Pay compared with the team

Who earns below their department’s average, and by how much?

SQL
WITH latest AS (  SELECT emp_id, annual_salary,         ROW_NUMBER() OVER (PARTITION BY emp_id ORDER BY effective_date DESC) AS rn  FROM salary_history),current_pay AS (  SELECT e.emp_id, e.first_name, e.dept_id, e.job_title, l.annual_salary,         ROUND(AVG(l.annual_salary) OVER (PARTITION BY e.dept_id, e.job_title)) AS role_avg  FROM employees e  JOIN latest l ON l.emp_id = e.emp_id AND l.rn = 1  WHERE e.termination_date IS NULL)SELECT emp_id, first_name, job_title, annual_salary, role_avg,       annual_salary - role_avg AS gapFROM current_payWHERE annual_salary < role_avgORDER BY gap;
ResultMySQL 8.4
emp_idfirst_namejob_titleannual_salaryrole_avggap
24UdaySupport Lead127000145000-18000
12FarhanSenior QA Engineer180000191500-11500
9LeelaSenior Engineer239000249500-10500
22SiddharthSupport Agent7300083000-10000
7PoojaEngineer8300087333-4333
17LataAccount Executive128000131500-3500
6 rows in set
How this query works, step by step
  1. AVG(...) OVER (PARTITION BY dept_id, job_title) is the average pay of people with the same role in the same department, shown on every row.
  2. The outer query keeps the people paid below that average, biggest gap first: a list for the next salary review.

Task 5: Top 2 earners per department

SQL
WITH latest AS (  SELECT emp_id, annual_salary,         ROW_NUMBER() OVER (PARTITION BY emp_id ORDER BY effective_date DESC) AS rn  FROM salary_history),ranked AS (  SELECT e.dept_id, e.first_name, e.job_title, l.annual_salary,         DENSE_RANK() OVER (PARTITION BY e.dept_id ORDER BY l.annual_salary DESC) AS rnk  FROM employees e  JOIN latest l ON l.emp_id = e.emp_id AND l.rn = 1  WHERE e.termination_date IS NULL)SELECT d.dept_name, r.first_name, r.job_title, r.annual_salary, r.rnkFROM ranked rJOIN departments d ON d.dept_id = r.dept_idWHERE r.rnk <= 2ORDER BY d.dept_name, r.rnk;
ResultMySQL 8.4
dept_namefirst_namejob_titleannual_salaryrnk
EngineeringAnanyaCTO7730001
EngineeringVikasSenior Engineer2600002
PeopleSaraHead of People3650001
PeopleZubinRecruiter760002
QARohanHead of QA5420001
QAJayaSenior QA Engineer2030002
SalesMeeraHead of Sales3540001
SalesNidhiAccount Executive1350002
SupportKabirHead of Support4170001
SupportRituSupport Lead1630002
10 rows in set

Task 6: Every raise and its percentage

MySQL
SELECT emp_id, effective_date, annual_salary,       LAG(annual_salary) OVER (PARTITION BY emp_id ORDER BY effective_date) AS previous_salary,       ROUND(100 * (annual_salary - LAG(annual_salary) OVER (PARTITION BY emp_id ORDER BY effective_date))             / LAG(annual_salary) OVER (PARTITION BY emp_id ORDER BY effective_date), 1) AS raise_pctFROM salary_historyWHERE emp_id IN (6, 12)ORDER BY emp_id, effective_date;
ResultMySQL 8.4
emp_ideffective_dateannual_salaryprevious_salaryraise_pct
62020-06-11173000NULLNULL
62021-04-011860001730007.5
62022-04-012040001860009.7
62023-04-012200002040007.8
62024-04-012370002200007.7
62025-04-012600002370009.7
122024-01-16167000NULLNULL
122025-04-011800001670007.8
8 rows in set
How this query works, step by step
  1. LAG(annual_salary) inside each employee’s history (PARTITION BY emp_id) gives the salary before each change.
  2. The first row of each employee is the hiring salary, so its previous salary and raise are NULL.

Average raise per department in the April 2025 appraisal:

MySQL
WITH changes AS (  SELECT emp_id, effective_date, annual_salary,         LAG(annual_salary) OVER (PARTITION BY emp_id ORDER BY effective_date) AS prev  FROM salary_history)SELECT d.dept_name,       COUNT(*) AS raised,       ROUND(AVG(100 * (c.annual_salary - c.prev) / c.prev), 1) AS avg_raise_pctFROM changes cJOIN employees e   ON e.emp_id = c.emp_idJOIN departments d ON d.dept_id = e.dept_idWHERE c.effective_date = '2025-04-01' AND c.prev IS NOT NULLGROUP BY d.dept_nameORDER BY avg_raise_pct DESC;
ResultMySQL 8.4
dept_nameraisedavg_raise_pct
Sales27.5
Engineering77.3
QA66.7
Support56.7
People15.6
5 rows in set

Task 7: Who missed the last appraisal?

Current employees, hired before 2025, without a salary change on 1 April 2025:

SQL
SELECT e.emp_id, e.first_name, e.job_title, MAX(s.effective_date) AS last_changeFROM employees eJOIN salary_history s ON s.emp_id = e.emp_idWHERE e.termination_date IS NULL AND e.hire_date < '2025-01-01'GROUP BY e.emp_id, e.first_name, e.job_titleHAVING MAX(s.effective_date) < '2025-04-01'ORDER BY last_change;
ResultMySQL 8.4
emp_idfirst_namejob_titlelast_change
3MeeraHead of Sales2024-04-01
5SaraHead of People2024-04-01
17LataAccount Executive2024-04-01
20PranavSales Associate2024-04-01
4 rows in set

Task 8: Tenure bands

MySQL
SELECT CASE         WHEN TIMESTAMPDIFF(YEAR, hire_date, '2025-12-31') < 2 THEN '0-1 years'         WHEN TIMESTAMPDIFF(YEAR, hire_date, '2025-12-31') < 5 THEN '2-4 years'         ELSE '5+ years'       END AS tenure_band,       COUNT(*) AS staffFROM employeesWHERE termination_date IS NULLGROUP BY tenure_bandORDER BY tenure_band;
ResultMySQL 8.4
tenure_bandstaff
0-1 years2
2-4 years8
5+ years14
3 rows in set

Average time leavers stayed before leaving:

MySQL
SELECT COUNT(*) AS leavers,       ROUND(AVG(TIMESTAMPDIFF(MONTH, hire_date, termination_date))) AS avg_months_stayedFROM employeesWHERE termination_date IS NOT NULL;
ResultMySQL 8.4
leaversavg_months_stayed
218
1 row in set

Task 9: The organisation chart

MySQL
WITH RECURSIVE org AS (  SELECT emp_id, first_name, job_title, manager_id, 1 AS lvl,         CAST(first_name AS CHAR(300)) AS path  FROM employees  WHERE manager_id IS NULL  UNION ALL  SELECT e.emp_id, e.first_name, e.job_title, e.manager_id, org.lvl + 1,         CONCAT(org.path, ' > ', e.first_name)  FROM employees e  JOIN org ON e.manager_id = org.emp_id  WHERE e.termination_date IS NULL)SELECT CONCAT(REPEAT('    ', lvl - 1), first_name, ' (', job_title, ')') AS org_chart, lvlFROM orgORDER BY path;
ResultMySQL 8.4
org_chartlvl
Ananya (CTO)1
Kabir (Head of Support)2
Ritu (Support Lead)3
Siddharth (Support Agent)3
Tanvi (Support Agent)3
Uday (Support Lead)3
Meera (Head of Sales)2
Lata (Account Executive)3
Manish (Sales Associate)3
Nidhi (Account Executive)3
Pranav (Sales Associate)3
Rohan (Head of QA)2
Farhan (Senior QA Engineer)3
Gauri (QA Engineer)3
Harsh (Automation Engineer)3
Jaya (Senior QA Engineer)3
Karthik (QA Engineer)3
Sara (Head of People)2
Zubin (Recruiter)3
Vikas (Senior Engineer)2
Aditya (Engineer)3
Imran (Engineer)3
Leela (Senior Engineer)3
Pooja (Engineer)3
24 rows in set
How this query works, step by step
  1. The anchor is the CTO, who has no manager.
  2. Each round adds the current employees who report to someone already in the chart (Recursive CTE).
  3. Sorting by the path places everyone under their manager; REPEAT indents by level.

Task 10: Span of control

SQL
SELECT m.emp_id, m.first_name, m.job_title, COUNT(e.emp_id) AS direct_reportsFROM employees mJOIN employees e ON e.manager_id = m.emp_id AND e.termination_date IS NULLGROUP BY m.emp_id, m.first_name, m.job_titleORDER BY direct_reports DESC, m.emp_id;
ResultMySQL 8.4
emp_idfirst_namejob_titledirect_reports
1AnanyaCTO5
2RohanHead of QA5
3MeeraHead of Sales4
4KabirHead of Support4
6VikasSenior Engineer4
5SaraHead of People1
6 rows in set

Task 11: Salary quartiles

SQL
WITH latest AS (  SELECT emp_id, annual_salary,         ROW_NUMBER() OVER (PARTITION BY emp_id ORDER BY effective_date DESC) AS rn  FROM salary_history),q AS (  SELECT e.emp_id, l.annual_salary, NTILE(4) OVER (ORDER BY l.annual_salary) AS quartile  FROM employees e  JOIN latest l ON l.emp_id = e.emp_id AND l.rn = 1  WHERE e.termination_date IS NULL)SELECT quartile, COUNT(*) AS staff, MIN(annual_salary) AS from_salary, MAX(annual_salary) AS to_salaryFROM qGROUP BY quartileORDER BY quartile;
ResultMySQL 8.4
quartilestafffrom_salaryto_salary
167300084000
2684000127000
36128000239000
46260000773000
4 rows in set

Your turn

Exercise: Hires per year

Count employees hired in each calendar year.

Expected resultMySQL 8.4
hire_yearhires
20161
20173
20194
20206
20215
20223
20231
20243
8 rows in set
Hint 1

Group by YEAR(hire_date).

Exercise: Starting salary vs today

For current employees, show the first salary, the current salary and the total growth in %.

Expected resultMySQL 8.4
first_namefirst_salarycurrent_salarygrowth_pct
Rohan256000542000111.7
Ananya42800077300080.6
Kabir26600041700056.8
Vikas17300026000050.3
Sara25600036500042.6
5 rows in set
Hint 1

MIN(effective_date) and MAX(effective_date) rows, or FIRST_VALUE / LAST_VALUE windows.

What you practised

TaskSQL skills
Current salary, payroll, quartilesROW_NUMBER latest-row pattern, aggregates, NTILE
Pay vs team, top earnersAVG() OVER (PARTITION BY), DENSE_RANK
Raises, missed appraisalLAG, HAVING MAX(...)
Headcount, attrition, tenureconditional SUM, TIMESTAMPDIFF, CASE bands
Org chart, span of controlrecursive CTE, self join

Project FAQ

How do you get the latest record per employee in SQL?

Number each employee’s rows with ROW_NUMBER() OVER (PARTITION BY emp_id ORDER BY effective_date DESC) and keep row number 1.

How do you calculate attrition rate in SQL?

Divide the number of employees with a termination date by the number of employees in the period, usually per department: 100 * SUM(termination_date IS NOT NULL) / COUNT(*).

How do you calculate a salary increase percentage in SQL?

Use LAG(salary) OVER (PARTITION BY emp_id ORDER BY effective_date) to get the previous salary, then 100 * (salary - previous) / previous.

SQL Stored Procedures

SQL Tutorial › Performance & Objects › Stored Procedures

SQL Stored Procedures

AdvancedLesson 63 of 6914 min read7 queries run on MySQL 8.4

A stored procedure is a named block of SQL that lives in the database and runs with CALL. It can take parameters, use variables, IF and loops, run several statements in a transaction and return result sets. Teams use procedures to keep important business operations, like placing an order, in one tested place that every application calls the same way.

In this lesson
  • Create and call a procedure with DELIMITER and CALL
  • Pass values in and out with IN and OUT parameters
  • Use variables, IF and loops
  • Handle errors and wrap work in a transaction

Why DELIMITER?

A procedure body contains several statements, each ending in ;. The mysql client would send the CREATE PROCEDURE at the first ;, cutting it off. DELIMITER // temporarily changes the statement terminator so the whole body is sent as one statement:

MySQL
DELIMITER //CREATE PROCEDURE name(...)BEGIN  statement1;  statement2;END //DELIMITER ;

MySQL Workbench and most drivers need the same trick or send the body as one statement automatically.

A first procedure

MySQL
DELIMITER //CREATE PROCEDURE list_department(IN p_dept_id INT)BEGIN  SELECT emp_id, first_name, job_title, salary  FROM employees  WHERE dept_id = p_dept_id  ORDER BY salary DESC;END //DELIMITER ; CALL list_department(2);
ResultMySQL 8.4
mysql> CREATE PROCEDURE list_department(IN p_dept_id INT)
Query OK, 0 rows affected
mysql> CALL list_department(2)
emp_idfirst_namejob_titlesalary
105VikramSales Manager105000
106NehaSales Executive54000
107ArjunSales Executive48000
3 rows in set
How this query works, step by step
  1. IN p_dept_id INT declares an input parameter. A p_ prefix keeps parameter names apart from column names.
  2. The body is a normal SELECT that uses the parameter.
  3. CALL list_department(2) runs it for department 2 and returns its result set.

OUT parameters

An OUT parameter sends a value back to the caller through a session variable:

MySQL
DELIMITER //CREATE PROCEDURE dept_stats(IN p_dept_id INT, OUT p_staff INT, OUT p_payroll INT)BEGIN  SELECT COUNT(*), SUM(salary)  INTO p_staff, p_payroll  FROM employees  WHERE dept_id = p_dept_id;END //DELIMITER ; CALL dept_stats(1, @staff, @payroll);SELECT @staff AS staff, @payroll AS payroll;
ResultMySQL 8.4
mysql> CREATE PROCEDURE dept_stats(IN p_dept_id INT, OUT p_staff INT, OUT p_p…
Query OK, 0 rows affected
mysql> CALL dept_stats(1, @staff, @payroll)
Query OK, 1 row affected
mysql> SELECT @staff AS staff, @payroll AS payroll
staffpayroll
5392000
1 row in set
How this query works, step by step
  1. SELECT ... INTO p_staff, p_payroll stores the query’s single row into the OUT parameters.
  2. The caller passes user variables @staff and @payroll to receive them.
  3. Engineering has 5 people and a payroll of 392,000.

INOUT parameters are both: the caller passes a value in and gets a changed value back.

Variables and IF

MySQL
DELIMITER //CREATE PROCEDURE stock_status(IN p_product_id INT, OUT p_status VARCHAR(20))BEGIN  DECLARE v_stock INT;  SELECT stock INTO v_stock FROM products WHERE product_id = p_product_id;  IF v_stock IS NULL THEN    SET p_status = 'unknown product';  ELSEIF v_stock = 0 THEN    SET p_status = 'out of stock';  ELSEIF v_stock < 20 THEN    SET p_status = 'low';  ELSE    SET p_status = 'ok';  END IF;END //DELIMITER ; CALL stock_status(3, @s1);CALL stock_status(5, @s2);CALL stock_status(1, @s3);CALL stock_status(99, @s4);SELECT @s1, @s2, @s3, @s4;
ResultMySQL 8.4
mysql> CREATE PROCEDURE stock_status(IN p_product_id INT, OUT p_status VARCHA…
Query OK, 0 rows affected
mysql> CALL stock_status(3, @s1)
Query OK, 1 row affected
mysql> CALL stock_status(5, @s2)
Query OK, 1 row affected
mysql> CALL stock_status(1, @s3)
Query OK, 1 row affected
mysql> CALL stock_status(99, @s4)
Query OK, 0 rows affected
mysql> SELECT @s1, @s2, @s3, @s4
@s1@s2@s3@s4
out of stocklowokunknown product
1 row in set
How this query works, step by step
  1. DECLARE v_stock INT creates a local variable (only valid inside the BEGIN ... END block).
  2. SELECT stock INTO v_stock reads the product’s stock; for an unknown id no row is found and the variable stays NULL.
  3. IF ... ELSEIF ... ELSE ... END IF picks the status.

Loops

Create a table of the next 5 invoice numbers with a WHILE loop:

MySQL
CREATE TABLE invoice_numbers (n INT);DELIMITER //CREATE PROCEDURE make_invoices(IN p_start INT, IN p_count INT)BEGIN  DECLARE i INT DEFAULT 0;  WHILE i < p_count DO    INSERT INTO invoice_numbers VALUES (p_start + i);    SET i = i + 1;  END WHILE;END //DELIMITER ; CALL make_invoices(5001, 5);SELECT * FROM invoice_numbers;
ResultMySQL 8.4
mysql> CREATE TABLE invoice_numbers (n INT)
Query OK, 0 rows affected
mysql> CREATE PROCEDURE make_invoices(IN p_start INT, IN p_count INT)
Query OK, 0 rows affected
mysql> CALL make_invoices(5001, 5)
Query OK, 1 row affected
mysql> SELECT * FROM invoice_numbers
n
5001
5002
5003
5004
5005
5 rows in set

MySQL also has REPEAT ... UNTIL ... END REPEAT and LOOP with LEAVE. Remember that one set-based INSERT ... SELECT is usually much faster than a loop.

A business operation with a transaction

Place an order: check stock, insert the order and its line, reduce stock, all or nothing:

MySQL
DELIMITER //CREATE PROCEDURE place_order(IN p_order_id INT, IN p_customer_id INT, IN p_product_id INT, IN p_qty INT)BEGIN  DECLARE v_stock INT;  DECLARE EXIT HANDLER FOR SQLEXCEPTION  BEGIN    ROLLBACK;    RESIGNAL;  END;   START TRANSACTION;  SELECT stock INTO v_stock FROM products WHERE product_id = p_product_id FOR UPDATE;  IF v_stock IS NULL OR v_stock < p_qty THEN    SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Not enough stock';  END IF;  INSERT INTO orders (order_id, customer_id, order_date, status) VALUES (p_order_id, p_customer_id, '2026-01-15', 'Pending');  INSERT INTO order_items (order_id, product_id, quantity) VALUES (p_order_id, p_product_id, p_qty);  UPDATE products SET stock = stock - p_qty WHERE product_id = p_product_id;  COMMIT;END //DELIMITER ; CALL place_order(2001, 6, 1, 2);SELECT order_id, customer_id, status FROM orders WHERE order_id = 2001;SELECT stock FROM products WHERE product_id = 1;
ResultMySQL 8.4
mysql> CREATE PROCEDURE place_order(IN p_order_id INT, IN p_customer_id INT, …
Query OK, 0 rows affected
mysql> CALL place_order(2001, 6, 1, 2)
Query OK, 0 rows affected
mysql> SELECT order_id, customer_id, status FROM orders WHERE order_id = 2001
order_idcustomer_idstatus
20016Pending
1 row in set
mysql> SELECT stock FROM products WHERE product_id = 1
stock
118
1 row in set
How this query works, step by step
  1. DECLARE EXIT HANDLER FOR SQLEXCEPTION runs if any statement fails: it rolls back and re-raises the error (RESIGNAL) so the caller sees it.
  2. SELECT ... FOR UPDATE locks the product row while we check and change it.
  3. SIGNAL SQLSTATE '45000' raises a custom error when there is not enough stock.
  4. Manoj (customer 6) orders 2 mice: the order exists and stock drops from 120 to 118.

Asking for more than is in stock raises the custom error, and nothing is saved:

MySQL
CALL place_order(2002, 6, 3, 1);
ResultMySQL 8.4
ERROR 1644 (45000): Not enough stock
MySQL
SELECT COUNT(*) AS order_2002_rows FROM orders WHERE order_id = 2002;
ResultMySQL 8.4
order_2002_rows
0
1 row in set

Manage procedures

TaskStatement
List proceduresSHOW PROCEDURE STATUS WHERE Db = 'company';
See the codeSHOW CREATE PROCEDURE place_order;
RemoveDROP PROCEDURE IF EXISTS place_order;
Changedrop and create again (there is no ALTER for the body)

Pros and cons

  • Pros: one tested implementation of a business rule; fewer round trips; permissions can be granted on the procedure instead of the tables.
  • Cons: harder to version, test and debug than application code; logic split between two places; MySQL’s procedural language is limited.

Quick quiz

1. Why is DELIMITER needed when creating a procedure in the mysql client?

2. How do you run a stored procedure?

3. How does a procedure return a single value to the caller?

Stored procedure FAQ

What is a stored procedure in MySQL?

A named, stored set of SQL statements with optional parameters and control flow, executed with CALL.

What is the difference between a stored procedure and a function?

A function returns one value and can be used inside SQL expressions. A procedure is run with CALL, can return result sets and OUT parameters, and can manage transactions. See User-Defined Functions.

How do I handle errors in a MySQL procedure?

Declare a handler: DECLARE EXIT HANDLER FOR SQLEXCEPTION BEGIN ROLLBACK; RESIGNAL; END; and raise your own errors with SIGNAL SQLSTATE '45000'.

Database Normalization (1NF, 2NF, 3NF)

SQL Tutorial › Database Design › Normalization (1NF-3NF)

Database Normalization (1NF, 2NF, 3NF)

IntermediateLesson 59 of 6914 min read2 queries run on MySQL 8.4

Normalization is the process of organising data into tables so that each fact is stored exactly once. It prevents the classic bugs of spreadsheet-style tables: a customer’s city spelt two ways, a price change that updates some rows but not others, a product that disappears when its last order is deleted. This lesson normalizes one messy table into 3NF, step by step, and builds it in MySQL.

In this lesson
  • Recognise insert, update and delete anomalies
  • Apply 1NF, 2NF and 3NF with clear tests
  • Split a flat table into related tables and join it back
  • Know when to denormalize on purpose

The starting point: one flat table

A shop starts by recording orders in one wide table, exactly as it did in a spreadsheet:

SQL
CREATE TABLE flat_orders (  order_id      INT,  order_date    DATE,  customer_name VARCHAR(50),  customer_city VARCHAR(30),  products      VARCHAR(200),  total         INT);INSERT INTO flat_orders VALUES(1, '2025-01-05', 'Ananya Sharma', 'Mumbai', 'Mechanical Keyboard x1, Notebook Pack x5', 4744),(2, '2025-01-17', 'Rohit Kumar',   'Delhi',  'Office Chair x1', 8999),(3, '2025-02-02', 'Ananya Sharma', 'Mumbai', 'Mechanical Keyboard x1, Wireless Mouse x1', 4298);SELECT * FROM flat_orders;
ResultMySQL 8.4
mysql> CREATE TABLE flat_orders (
Query OK, 0 rows affected
mysql> INSERT INTO flat_orders VALUES
Query OK, 3 rows affected
mysql> SELECT * FROM flat_orders
order_idorder_datecustomer_namecustomer_cityproductstotal
12025-01-05Ananya SharmaMumbaiMechanical Keyboard x1, Notebook Pack x54744
22025-01-17Rohit KumarDelhiOffice Chair x18999
32025-02-02Ananya SharmaMumbaiMechanical Keyboard x1, Wireless Mouse x14298
3 rows in set

The problems: anomalies

AnomalyExample in flat_orders
UpdateAnanya moves to Pune: two rows must change; miss one and the data contradicts itself.
InsertA new product cannot be recorded until someone orders it.
DeleteDelete Rohit’s only order and you lose the fact that Rohit is a customer.
Query“How many keyboards did we sell?” needs text parsing of products.

Normalization removes these problems by splitting the table.

First Normal Form (1NF)

Rule: every column holds one atomic value, and there are no repeating groups. Each row is identified by a key.

products breaks 1NF: one cell holds a list. Give each product of an order its own row:

order_idorder_datecustomer_namecustomer_cityproduct_namequantityprice
12025-01-05Ananya SharmaMumbaiMechanical Keyboard13499
12025-01-05Ananya SharmaMumbaiNotebook Pack5249
22025-01-17Rohit KumarDelhiOffice Chair18999
32025-02-02Ananya SharmaMumbaiMechanical Keyboard13499
32025-02-02Ananya SharmaMumbaiWireless Mouse1799

The key is now (order_id, product_name). The stored total is dropped: it can be calculated.

Second Normal Form (2NF)

Rule: 1NF, and every non-key column depends on the whole key, not just part of it. (Only matters when the key has several columns.)

With the key (order_id, product_name):

  • quantity depends on both: how many of this product in this order. Fine.
  • order_date, customer_name, customer_city depend on order_id alone. Partial dependency.
  • price depends on product_name alone. Partial dependency.

Move each partial dependency into its own table: orders (order_id → date, customer), products (product → price) and order_items (order_id + product → quantity).

Third Normal Form (3NF)

Rule: 2NF, and no non-key column depends on another non-key column (no transitive dependency).

In the new orders table, customer_city depends on customer_name, which depends on order_id. City is a fact about the customer, not about the order. Move customers to their own table and keep only a customer_id in orders.

Tip

The usual one-line summary of 3NF: every non-key column must depend on “the key, the whole key, and nothing but the key”.

The normalized design in MySQL

SQL
CREATE TABLE n_customers (customer_id INT PRIMARY KEY, customer_name VARCHAR(50) NOT NULL, city VARCHAR(30));CREATE TABLE n_products  (product_id INT PRIMARY KEY, product_name VARCHAR(50) NOT NULL UNIQUE, price INT NOT NULL);CREATE TABLE n_orders    (order_id INT PRIMARY KEY, order_date DATE NOT NULL,                          customer_id INT NOT NULL, FOREIGN KEY (customer_id) REFERENCES n_customers (customer_id));CREATE TABLE n_order_items (order_id INT, product_id INT, quantity INT NOT NULL,                          PRIMARY KEY (order_id, product_id),                          FOREIGN KEY (order_id) REFERENCES n_orders (order_id),                          FOREIGN KEY (product_id) REFERENCES n_products (product_id));INSERT INTO n_customers VALUES (1, 'Ananya Sharma', 'Mumbai'), (2, 'Rohit Kumar', 'Delhi');INSERT INTO n_products VALUES (1, 'Wireless Mouse', 799), (2, 'Mechanical Keyboard', 3499), (4, 'Office Chair', 8999), (6, 'Notebook Pack', 249);INSERT INTO n_orders VALUES (1, '2025-01-05', 1), (2, '2025-01-17', 2), (3, '2025-02-02', 1);INSERT INTO n_order_items VALUES (1, 2, 1), (1, 6, 5), (2, 4, 1), (3, 2, 1), (3, 1, 1); SELECT o.order_id, o.order_date, c.customer_name, c.city, SUM(oi.quantity * p.price) AS totalFROM n_orders oJOIN n_customers c    ON c.customer_id = o.customer_idJOIN n_order_items oi ON oi.order_id = o.order_idJOIN n_products p     ON p.product_id = oi.product_idGROUP BY o.order_id, o.order_date, c.customer_name, c.cityORDER BY o.order_id;
ResultMySQL 8.4
mysql> CREATE TABLE n_customers (customer_id INT PRIMARY KEY, customer_name V…
Query OK, 0 rows affected
mysql> CREATE TABLE n_products (product_id INT PRIMARY KEY, product_name VAR…
Query OK, 0 rows affected
mysql> CREATE TABLE n_orders (order_id INT PRIMARY KEY, order_date DATE NO…
Query OK, 0 rows affected
mysql> CREATE TABLE n_order_items (order_id INT, product_id INT, quantity INT…
Query OK, 0 rows affected
mysql> INSERT INTO n_customers VALUES (1, 'Ananya Sharma', 'Mumbai'), (2, 'Ro…
Query OK, 2 rows affected
mysql> INSERT INTO n_products VALUES (1, 'Wireless Mouse', 799), (2, 'Mechani…
Query OK, 4 rows affected
mysql> INSERT INTO n_orders VALUES (1, '2025-01-05', 1), (2, '2025-01-17', 2)…
Query OK, 3 rows affected
mysql> INSERT INTO n_order_items VALUES (1, 2, 1), (1, 6, 5), (2, 4, 1), (3, …
Query OK, 5 rows affected
mysql> SELECT o.order_id, o.order_date, c.customer_name, c.city, SUM(oi.quant…
order_idorder_datecustomer_namecitytotal
12025-01-05Ananya SharmaMumbai4744
22025-01-17Rohit KumarDelhi8999
32025-02-02Ananya SharmaMumbai4298
3 rows in set
How this query works, step by step
  1. Four tables, each about one thing: customers, products, orders, order lines.
  2. Each fact is stored once: Ananya’s city lives in one row; the keyboard’s price in one row.
  3. Foreign keys keep the pieces consistent.
  4. The final query joins them back and calculates the totals, which match the original flat table.

The anomalies are gone: moving Ananya to Pune is one UPDATE of one row; a new product can be added before anyone orders it; deleting an order no longer deletes the customer. This is exactly the structure of the tutorial’s sample database.

BCNF and beyond

Boyce-Codd Normal Form (BCNF) tightens 3NF: every determinant (a column set that decides another column) must be a candidate key. Most 3NF designs are already in BCNF. 4NF and 5NF deal with multi-valued and join dependencies and are rarely needed in everyday applications.

When to denormalize

Normalized designs are best for data that changes (OLTP systems). Reporting and analytics sometimes denormalize on purpose, storing a calculated order_total or a copy of the customer’s city, to avoid joins on huge tables. Do it deliberately, document it, and keep the copies in sync with triggers or batch jobs.

NormalizedDenormalized
Each fact storedoncepossibly many times
Writessimple and safemust update every copy
Readsmore joinsfewer joins, faster reports
Typical useapplications (OLTP)data warehouses (OLAP)

Quick quiz

1. A column holds “Keyboard x1, Mouse x2”. Which normal form does this break?

2. In order_items with key (order_id, product_id), price depends only on product_id. That is…

3. customer_city depends on customer_id, which is not the key of orders. This breaks…

4. Which anomaly happens when deleting an order also loses the customer’s details?

Normalization FAQ

What is normalization in DBMS?

The process of structuring tables so each fact is stored once, removing redundancy and the insert, update and delete anomalies that redundancy causes.

What is the difference between 1NF, 2NF and 3NF?

1NF: atomic values, no repeating groups. 2NF: no non-key column depends on only part of a composite key. 3NF: no non-key column depends on another non-key column.

Is normalization always necessary?

For transactional applications, normalize to 3NF. Analytical systems often denormalize deliberately for faster reads.

What is denormalization?

Intentionally adding redundant data, such as stored totals, to reduce joins and speed up reads, at the cost of more complex writes.

SQL INTERSECT and EXCEPT

SQL Tutorial › Joins & Set Operators › INTERSECT & EXCEPT

SQL INTERSECT and EXCEPT

IntermediateLesson 39 of 698 min read6 queries run on MySQL 8.4

UNION combines two results. Its siblings compare them: INTERSECT returns the rows that appear in both results, and EXCEPT returns rows in the first result that are not in the second. MySQL added both in version 8.0.31, so MySQL 8.4 supports them; older versions need a join or EXISTS.

In this lesson
  • Find common rows with INTERSECT
  • Find missing rows with EXCEPT
  • Understand the duplicate rules
  • Rewrite them with EXISTS for older databases

The two lists

Cities of employees and of customers:

employees · 12 rows (some columns)
emp_idfirst_namecity
100MeeraMumbai
101AshaBengaluru
102RahulBengaluru
103PriyaPune
104KaranBengaluru
105VikramMumbai
106NehaMumbai
107ArjunKolkata
108KavyaPune
109RohanPune
110SnehaDelhi
111AmitBengaluru
customers · 7 rows (some columns)
customer_idcustomer_namecity
1Ananya SharmaMumbai
2Rohit KumarDelhi
3Fatima KhanHyderabad
4David FernandesGoa
5Ishita BoseKolkata
6Manoj PillaiChennai
7Sara ThomasBengaluru

INTERSECT: rows in both

Cities where we have both an employee and a customer:

SQL
SELECT city FROM employeesINTERSECTSELECT city FROM customersORDER BY city;
ResultMySQL 8.4
city
Bengaluru
Delhi
Kolkata
Mumbai
4 rows in set
How this query works, step by step
  1. The first query lists employee cities; the second, customer cities.
  2. INTERSECT keeps the cities that appear in both lists.
  3. Like UNION, it removes duplicates: Bengaluru, Delhi, Kolkata and Mumbai.

EXCEPT: rows in the first only

Cities with employees but no customers:

SQL
SELECT city FROM employeesEXCEPTSELECT city FROM customersORDER BY city;
ResultMySQL 8.4
city
Pune
1 row in set

The order matters. Cities with customers but no employees:

SQL
SELECT city FROM customersEXCEPTSELECT city FROM employeesORDER BY city;
ResultMySQL 8.4
city
Chennai
Goa
Hyderabad
3 rows in set
How this query works, step by step
  1. A EXCEPT B keeps the rows of A that do not appear in B.
  2. Swapping the queries asks the opposite question: Chennai, Goa and Hyderabad have customers but no staff.

Practical example: products never ordered

SQL
SELECT product_id FROM productsEXCEPTSELECT product_id FROM order_items;
ResultMySQL 8.4
product_id
9
1 row in set

Several columns

The comparison uses the whole row, so all selected columns must match. Customer ids that ordered in both January and April 2025:

SQL
SELECT customer_id FROM orders WHERE order_date BETWEEN '2025-01-01' AND '2025-01-31'INTERSECTSELECT customer_id FROM orders WHERE order_date BETWEEN '2025-04-01' AND '2025-04-30';
ResultMySQL 8.4
customer_id
1
2
2 rows in set

Alternatives with EXISTS

On MySQL before 8.0.31 (and many interview answers), write INTERSECT with EXISTS and EXCEPT with NOT EXISTS:

SQL
SELECT DISTINCT e.cityFROM employees eWHERE NOT EXISTS (SELECT 1 FROM customers c WHERE c.city = e.city)ORDER BY e.city;
ResultMySQL 8.4
city
Pune
1 row in set
How this query works, step by step
  1. For each employee city, the subquery checks whether any customer has the same city.
  2. NOT EXISTS keeps the cities with no such customer: the same answer as EXCEPT.
  3. DISTINCT removes repeats, which EXCEPT does automatically. See EXISTS.
Note

INTERSECT ALL and EXCEPT ALL keep duplicates (MySQL supports them too). Also note that, unlike = in WHERE, these operators treat two NULLs as equal.

Quick quiz

1. Which operator returns rows found in both queries?

2. Is A EXCEPT B the same as B EXCEPT A?

3. From which MySQL version are INTERSECT and EXCEPT supported?

INTERSECT and EXCEPT FAQ

Does MySQL support INTERSECT?

Yes, since MySQL 8.0.31. In older versions use an inner join or WHERE EXISTS.

What is the difference between EXCEPT and MINUS?

They do the same thing. Oracle calls it MINUS; standard SQL, PostgreSQL, SQL Server and MySQL use EXCEPT.

What is the difference between INTERSECT and INNER JOIN?

INTERSECT compares whole result rows and removes duplicates. An inner join combines columns of two tables on a condition and can multiply rows.

SQL Joins Explained

SQL Tutorial › Joins & Set Operators › SQL Joins Overview

SQL Joins Explained

IntermediateLesson 31 of 6914 min read7 queries run on MySQL 8.4

A join combines rows from two tables by matching a column they share, such as an employee’s dept_id and a department’s dept_id. This overview runs every type of join on the same two small tables, so you can see exactly which rows each one keeps and which it drops. Each join then has its own detailed lesson.

In this lesson
  • Why data is split across tables and joined back
  • What INNER, LEFT, RIGHT and FULL joins keep
  • How CROSS and SELF joins differ
  • How to pick the right join for a question

Why joins exist

A well-designed database stores each fact once. Department names live in departments; employees store only a dept_id. That avoids repeating “Engineering, Bengaluru” on every engineer’s row, but it means that to print an employee next to their department name you must join the two tables back together.

The two tables

To keep the comparison easy, this lesson uses a few columns of each table:

employees · 12 rows (some columns)
emp_idfirst_namedept_id
100MeeraNULL
101Asha1
102Rahul1
103Priya1
104Karan1
105Vikram2
106Neha2
107Arjun2
108Kavya3
109Rohan3
110Sneha4
111Amit1
departments · 5 rows
dept_iddept_namelocation
1EngineeringBengaluru
2SalesMumbai
3MarketingPune
4HRDelhi
5FinanceMumbai

Notice the two “unmatched” rows that make the joins differ:

  • Meera (100) has dept_id = NULL: she belongs to no department.
  • Finance (5) has no employees.

Join types at a glance

JoinKeeps
INNER JOINonly rows that match in both tables
LEFT JOINall rows of the left table + matches from the right (NULL if none)
RIGHT JOINall rows of the right table + matches from the left
FULL OUTER JOINall rows of both tables (MySQL: LEFT + RIGHT with UNION)
CROSS JOINevery combination of rows (no condition)
SELF JOINa table joined to itself

INNER JOIN

SQL
SELECT e.emp_id, e.first_name, d.dept_nameFROM employees eINNER JOIN departments d ON d.dept_id = e.dept_idORDER BY e.emp_id;
ResultMySQL 8.4
emp_idfirst_namedept_name
101AshaEngineering
102RahulEngineering
103PriyaEngineering
104KaranEngineering
105VikramSales
106NehaSales
107ArjunSales
108KavyaMarketing
109RohanMarketing
110SnehaHR
111AmitEngineering
11 rows in set
How this query works, step by step
  1. MySQL pairs each employee with the department whose dept_id is equal (ON d.dept_id = e.dept_id).
  2. Meera has no matching department (NULL never equals anything), so she is dropped.
  3. Finance has no matching employee, so it is dropped too.
  4. Result: 11 rows, only complete pairs.

Full lesson: INNER JOIN.

LEFT JOIN

SQL
SELECT e.emp_id, e.first_name, d.dept_nameFROM employees eLEFT JOIN departments d ON d.dept_id = e.dept_idORDER BY e.emp_id;
ResultMySQL 8.4
emp_idfirst_namedept_name
100MeeraNULL
101AshaEngineering
102RahulEngineering
103PriyaEngineering
104KaranEngineering
105VikramSales
106NehaSales
107ArjunSales
108KavyaMarketing
109RohanMarketing
110SnehaHR
111AmitEngineering
12 rows in set
How this query works, step by step
  1. Every row of the left table (employees, written first) is kept.
  2. Matching departments are attached as with an inner join.
  3. Meera has no match, so she stays, with NULL in dept_name.
  4. Result: 12 rows. Finance still does not appear, because it is in the right table.

Full lesson: LEFT JOIN.

SQL
SELECT e.emp_id, e.first_name, d.dept_nameFROM employees eRIGHT JOIN departments d ON d.dept_id = e.dept_idORDER BY d.dept_id, e.emp_id;
ResultMySQL 8.4
emp_idfirst_namedept_name
101AshaEngineering
102RahulEngineering
103PriyaEngineering
104KaranEngineering
111AmitEngineering
105VikramSales
106NehaSales
107ArjunSales
108KavyaMarketing
109RohanMarketing
110SnehaHR
NULLNULLFinance
12 rows in set
How this query works, step by step
  1. Every row of the right table (departments) is kept.
  2. Finance has no employee, so it appears once with NULL employee columns.
  3. Meera disappears, because she has no department to attach to.

Full lesson: RIGHT JOIN.

FULL OUTER JOIN

MySQL has no FULL OUTER JOIN keyword. Combine a left and a right join with UNION, which also removes the rows they share:

SQL
SELECT e.emp_id, e.first_name, d.dept_nameFROM employees eLEFT JOIN departments d ON d.dept_id = e.dept_idUNIONSELECT e.emp_id, e.first_name, d.dept_nameFROM employees eRIGHT JOIN departments d ON d.dept_id = e.dept_idORDER BY emp_id;
ResultMySQL 8.4
emp_idfirst_namedept_name
NULLNULLFinance
100MeeraNULL
101AshaEngineering
102RahulEngineering
103PriyaEngineering
104KaranEngineering
105VikramSales
106NehaSales
107ArjunSales
108KavyaMarketing
109RohanMarketing
110SnehaHR
111AmitEngineering
13 rows in set
How this query works, step by step
  1. The left join gives 12 rows, including Meera without a department.
  2. The right join gives 12 rows, including Finance without an employee.
  3. UNION merges them and removes the 11 duplicate matched rows: 13 rows, with both unmatched rows present.

Full lesson: FULL OUTER JOIN.

CROSS JOIN

A cross join pairs every row of one table with every row of the other. With 3 product categories and 2 sizes you get 6 combinations:

SQL
SELECT c.category, s.sizeFROM (SELECT DISTINCT category FROM products) cCROSS JOIN (SELECT 'S' AS size UNION ALL SELECT 'L') sORDER BY c.category, s.size;
ResultMySQL 8.4
categorysize
ElectronicsL
ElectronicsS
FurnitureL
FurnitureS
StationeryL
StationeryS
6 rows in set

Full lesson: CROSS JOIN.

SELF JOIN

A table joined to itself, here to put each employee next to their manager:

SQL
SELECT e.first_name AS employee, m.first_name AS managerFROM employees eLEFT JOIN employees m ON m.emp_id = e.manager_idORDER BY e.emp_id;
ResultMySQL 8.4
employeemanager
MeeraNULL
AshaMeera
RahulAsha
PriyaAsha
KaranRahul
VikramMeera
NehaVikram
ArjunVikram
KavyaMeera
RohanKavya
SnehaMeera
AmitAsha
12 rows in set

Full lesson: SELF JOIN.

Joining more than two tables

Joins chain: each JOIN adds one more table to the rows built so far. Who bought what?

SQL
SELECT o.order_id, c.customer_name, p.product_name, oi.quantityFROM orders oJOIN customers   c  ON c.customer_id = o.customer_idJOIN order_items oi ON oi.order_id   = o.order_idJOIN products    p  ON p.product_id  = oi.product_idWHERE o.order_id IN (1001, 1006)ORDER BY o.order_id, p.product_name;
ResultMySQL 8.4
order_idcustomer_nameproduct_namequantity
1001Ananya SharmaMechanical Keyboard1
1001Ananya SharmaNotebook Pack5
1006Ishita BoseDesk Lamp1
1006Ishita BoseUSB-C Hub2
4 rows in set
How this query works, step by step
  1. Start from orders.
  2. Attach the customer of each order.
  3. Attach the order’s lines: one row per line, so an order with two lines becomes two rows.
  4. Attach each line’s product name.

Which join should I use?

QuestionJoin
Employees with their departmentINNER JOIN
All employees, department if they have oneLEFT JOIN
Departments with no employeesLEFT JOIN from departments + WHERE e.emp_id IS NULL
Everything from both sidesFULL OUTER (UNION of LEFT and RIGHT)
All combinations (e.g. sizes × colours)CROSS JOIN
Rows related to other rows of the same tableSELF JOIN
Tip

Most teams write LEFT JOIN and simply swap the table order instead of using RIGHT JOIN. Queries read more naturally when the “main” table comes first.

Quick quiz

1. Which join returns only rows that have a match in both tables?

2. employees LEFT JOIN departments: what happens to an employee with no department?

3. How do you write a FULL OUTER JOIN in MySQL?

4. Table A has 4 rows and table B has 3. How many rows does A CROSS JOIN B return?

SQL joins FAQ

What are the types of joins in SQL?

INNER JOIN, LEFT (OUTER) JOIN, RIGHT (OUTER) JOIN, FULL OUTER JOIN, CROSS JOIN and SELF JOIN (a table joined to itself with any of the other types).

What is the difference between INNER JOIN and LEFT JOIN?

INNER JOIN returns only rows with a match in both tables. LEFT JOIN returns every row of the left table and fills the right table’s columns with NULL where there is no match.

Does MySQL support FULL OUTER JOIN?

No. Emulate it with LEFT JOIN ... UNION ... RIGHT JOIN.

Is JOIN the same as INNER JOIN?

Yes. In MySQL JOIN on its own means INNER JOIN.

SQL LAG and LEAD Functions

SQL Tutorial › Window Functions › LAG & LEAD

SQL LAG and LEAD Functions

AdvancedLesson 47 of 6910 min read6 queries run on MySQL 8.4

LAG reads a value from the previous row and LEAD from the next row, in an order you choose, without a self join. They answer “how much did revenue change since last month?”, “how many days passed between a customer’s orders?” and “who was hired right after this person?”.

In this lesson
  • Read the previous row with LAG and the next with LEAD
  • Calculate changes and growth between rows
  • Work per group with PARTITION BY
  • Use offsets, defaults, FIRST_VALUE and LAST_VALUE

Syntax

MySQL
LAG(expression [, offset [, default]])  OVER ([PARTITION BY ...] ORDER BY ...)LEAD(expression [, offset [, default]]) OVER ([PARTITION BY ...] ORDER BY ...)
  • offset: how many rows back or forward (default 1).
  • default: the value when there is no such row (default NULL).

Sample data

orders · 10 rows
order_idcustomer_idorder_datestatus
100112025-01-05Delivered
100222025-01-17Delivered
100312025-02-02Delivered
100432025-02-14Cancelled
100542025-03-08Delivered
100652025-03-22Shipped
100722025-04-10Delivered
100812025-04-28Pending
100932025-05-15Delivered
101052025-06-01Delivered

Previous and next order

SQL
SELECT order_id, order_date,       LAG(order_date)  OVER (ORDER BY order_date) AS previous_order,       LEAD(order_date) OVER (ORDER BY order_date) AS next_orderFROM ordersORDER BY order_date;
ResultMySQL 8.4
order_idorder_dateprevious_ordernext_order
10012025-01-05NULL2025-01-17
10022025-01-172025-01-052025-02-02
10032025-02-022025-01-172025-02-14
10042025-02-142025-02-022025-03-08
10052025-03-082025-02-142025-03-22
10062025-03-222025-03-082025-04-10
10072025-04-102025-03-222025-04-28
10082025-04-282025-04-102025-05-15
10092025-05-152025-04-282025-06-01
10102025-06-012025-05-15NULL
10 rows in set
How this query works, step by step
  1. OVER (ORDER BY order_date) puts the orders in date order.
  2. LAG(order_date) takes the date from the row just before; the first row has none, so it is NULL.
  3. LEAD(order_date) takes it from the row just after; the last row gets NULL.

Days between orders

MySQL
SELECT order_id, order_date,       DATEDIFF(order_date, LAG(order_date) OVER (ORDER BY order_date)) AS days_since_previousFROM ordersORDER BY order_date;
ResultMySQL 8.4
order_idorder_datedays_since_previous
10012025-01-05NULL
10022025-01-1712
10032025-02-0216
10042025-02-1412
10052025-03-0822
10062025-03-2214
10072025-04-1019
10082025-04-2818
10092025-05-1517
10102025-06-0117
10 rows in set

Per customer: PARTITION BY

Days between each customer’s own orders:

MySQL
SELECT customer_id, order_id, order_date,       LAG(order_date) OVER (PARTITION BY customer_id ORDER BY order_date) AS prev_order,       DATEDIFF(order_date,                LAG(order_date) OVER (PARTITION BY customer_id ORDER BY order_date)) AS days_gapFROM ordersORDER BY customer_id, order_date;
ResultMySQL 8.4
customer_idorder_idorder_dateprev_orderdays_gap
110012025-01-05NULLNULL
110032025-02-022025-01-0528
110082025-04-282025-02-0285
210022025-01-17NULLNULL
210072025-04-102025-01-1783
310042025-02-14NULLNULL
310092025-05-152025-02-1490
410052025-03-08NULLNULL
510062025-03-22NULLNULL
510102025-06-012025-03-2271
10 rows in set
How this query works, step by step
  1. PARTITION BY customer_id makes each customer’s orders a separate sequence.
  2. The first order of every customer has no previous order: NULL.
  3. Customer 1 waited 28 days between their first two orders, then 85 days.

Month-over-month revenue change

First total revenue per month in a CTE, then compare each month with the previous one:

MySQL
WITH monthly AS (  SELECT DATE_FORMAT(o.order_date, '%Y-%m') AS month,         SUM(oi.quantity * p.price) AS revenue  FROM orders o  JOIN order_items oi ON oi.order_id = o.order_id  JOIN products p     ON p.product_id = oi.product_id  WHERE o.status <> 'Cancelled'  GROUP BY month)SELECT month, revenue,       LAG(revenue) OVER (ORDER BY month) AS prev_revenue,       revenue - LAG(revenue) OVER (ORDER BY month) AS change_amt,       ROUND(100 * (revenue - LAG(revenue) OVER (ORDER BY month))             / LAG(revenue) OVER (ORDER BY month), 1) AS change_pctFROM monthlyORDER BY month;
ResultMySQL 8.4
monthrevenueprev_revenuechange_amtchange_pct
2025-0113743NULLNULLNULL
2025-02429813743-9445-68.7
2025-03878342984485104.4
2025-0421795878313012148.1
2025-05349921795-18296-83.9
2025-0611453499-2354-67.3
6 rows in set
How this query works, step by step
  1. The CTE gives one row per month with its revenue.
  2. LAG(revenue) brings the previous month’s revenue onto each row.
  3. The difference and the percentage are ordinary arithmetic on those two columns.
  4. January has no previous month, so its change is NULL.

Offsets and defaults

SQL
SELECT first_name, hire_date,       LAG(first_name, 2, '-')  OVER (ORDER BY hire_date) AS hired_two_before,       LEAD(first_name, 1, '-') OVER (ORDER BY hire_date) AS hired_nextFROM employeesORDER BY hire_date;
ResultMySQL 8.4
first_namehire_datehired_two_beforehired_next
Meera2015-01-05–Vikram
Vikram2017-11-20–Asha
Asha2018-04-12MeeraSneha
Sneha2019-02-25VikramRahul
Rahul2019-07-01AshaKavya
Kavya2020-09-09SnehaPriya
Priya2021-01-18RahulNeha
Neha2022-03-14KavyaKaran
Karan2022-08-29PriyaArjun
Arjun2023-06-05NehaRohan
Rohan2024-01-22KaranAmit
Amit2025-06-02Arjun–
12 rows in set
How this query works, step by step
  1. LAG(first_name, 2, '-') looks two rows back; for the first two rows there is no such row, so the default '-' is shown.
  2. LEAD(first_name, 1, '-') looks one row forward, with '-' for the last row.

FIRST_VALUE and LAST_VALUE

The first hire of each department next to every employee:

SQL
SELECT dept_id, first_name, hire_date,       FIRST_VALUE(first_name) OVER (PARTITION BY dept_id ORDER BY hire_date) AS first_hire,       LAST_VALUE(first_name) OVER (PARTITION BY dept_id ORDER BY hire_date                                    ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS latest_hireFROM employeesWHERE dept_id IN (1, 2)ORDER BY dept_id, hire_date;
ResultMySQL 8.4
dept_idfirst_namehire_datefirst_hirelatest_hire
1Asha2018-04-12AshaAmit
1Rahul2019-07-01AshaAmit
1Priya2021-01-18AshaAmit
1Karan2022-08-29AshaAmit
1Amit2025-06-02AshaAmit
2Vikram2017-11-20VikramArjun
2Neha2022-03-14VikramArjun
2Arjun2023-06-05VikramArjun
8 rows in set
Watch out

With ORDER BY in the window, the default frame ends at the current row, so a plain LAST_VALUE(...) just returns the current row’s value. Add ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING to see the whole partition.

Exercise: Salary step to the next person

Order employees by salary (lowest first) and show, for each, the next higher salary and the difference.

Expected resultMySQL 8.4
first_namesalarynext_salarystep
Amit250004500020000
Rohan45000480003000
Arjun48000540006000
Neha540007200018000
Priya72000720000
Karan72000780006000
Sneha780008800010000
Kavya880009800010000
Rahul980001050007000
Vikram10500012500020000
Asha125000250000125000
Meera250000NULLNULL
12 rows in set
Hint 1

LEAD(salary) OVER (ORDER BY salary).

Quick quiz

1. What does LAG(x) return for the first row of a partition?

2. Which function reads the next row?

3. Why does LAST_VALUE often return the current row’s value?

LAG and LEAD FAQ

What does the LAG function do in SQL?

LAG(column) returns the value of the column from the previous row in the window’s order, letting you compare a row with the one before it.

How do I calculate month-over-month growth in SQL?

Aggregate by month, then use LAG(revenue) OVER (ORDER BY month) to get the previous month and compute (revenue - prev) / prev * 100.

How do I replace NULL in LAG?

Pass a default as the third argument: LAG(revenue, 1, 0) OVER (...).

SQL Injection and Prepared Statements

SQL Tutorial › Security › SQL Injection & Prepared Statements

SQL Injection and Prepared Statements

AdvancedLesson 69 of 6912 min read4 queries run on MySQL 8.4

SQL injection happens when an application builds a SQL statement by pasting user input into it, and the input changes the meaning of the query. It is one of the most damaging and still most common web vulnerabilities. This lesson shows exactly why it works, on a demo table, and how prepared statements with parameters stop it completely.

In this lesson
  • See how string-built queries change meaning
  • Use prepared statements in MySQL with PREPARE and EXECUTE
  • Use parameters from application code
  • Test an application for injection as a QA engineer

A vulnerable login

A demo app_users table (passwords are stored as hashes, as they always should be):

MySQL
CREATE TABLE app_users (  user_id  INT PRIMARY KEY,  username VARCHAR(30) NOT NULL UNIQUE,  pw_hash  CHAR(64) NOT NULL,  role     VARCHAR(10) NOT NULL);INSERT INTO app_users VALUES(1, 'admin', SHA2('Adm1n#Demo', 256), 'admin'),(2, 'asha',  SHA2('Asha#Demo1', 256), 'user');SELECT user_id, username, role FROM app_users;
ResultMySQL 8.4
mysql> CREATE TABLE app_users (
Query OK, 0 rows affected
mysql> INSERT INTO app_users VALUES
Query OK, 2 rows affected
mysql> SELECT user_id, username, role FROM app_users
user_idusernamerole
1adminadmin
2ashauser
2 rows in set

An application checks a login by gluing the typed values into SQL text, like this Python code:

login.py (vulnerable)
sql = ("SELECT user_id, role FROM app_users "       f"WHERE username = '{username}' AND pw_hash = SHA2('{password}', 256)")cursor.execute(sql)

With honest input (asha / Asha#Demo1) the query finds Asha. Now an attacker types the username admin' -- and any password. The pasted SQL becomes:

MySQL
SELECT user_id, role FROM app_usersWHERE username = 'admin' -- ' AND pw_hash = SHA2('anything', 256)
ResultMySQL 8.4
user_idrole
1admin
1 row in set
How this query works, step by step
  1. The ' in the input closes the username string early.
  2. -- starts a comment, so the whole password check is ignored.
  3. The query returns the admin row: the attacker is logged in as admin without the password.

Another classic input, ' OR '1'='1, makes the condition true for every row:

MySQL
SELECT user_id, username, role FROM app_usersWHERE username = '' OR '1'='1' AND pw_hash = SHA2('x', 256) OR '1'='1';
ResultMySQL 8.4
user_idusernamerole
1adminadmin
2ashauser
2 rows in set
Watch out

The same trick can read other tables (UNION SELECT ...), change data or drop tables, depending on the account’s privileges. Only run injection tests against systems you own or are authorised to test.

The fix: prepared statements

A prepared statement sends the SQL with placeholders (?) and the values separately. The values are always treated as data, never as SQL, so quotes and comments in them have no special meaning.

In MySQL itself:

MySQL
PREPARE login FROM  'SELECT user_id, role FROM app_users WHERE username = ? AND pw_hash = SHA2(?, 256)'; SET @u = 'asha', @p = 'Asha#Demo1';EXECUTE login USING @u, @p; SET @u = 'admin'' -- ', @p = 'anything';EXECUTE login USING @u, @p; DEALLOCATE PREPARE login;
ResultMySQL 8.4
mysql> PREPARE login FROM
Query OK, 0 rows affected
mysql> SET @u = 'asha', @p = 'Asha
Query OK, 0 rows affected
mysql> EXECUTE login USING @u, @p
user_idrole
2user
1 row in set
mysql> SET @u = 'admin''
Query OK, 0 rows affected
mysql> EXECUTE login USING @u, @p
Empty set
mysql> DEALLOCATE PREPARE login
Query OK, 0 rows affected
How this query works, step by step
  1. PREPARE compiles the query once, with two ? placeholders.
  2. The honest login passes Asha’s values and finds her row.
  3. The attack passes the text admin' -- as the username. It is compared as a plain string: no user has that name, so the result is an Empty set.
  4. DEALLOCATE PREPARE frees the statement.

Parameters in application code

Every database library supports parameters. In Python with mysql-connector or PyMySQL:

login.py (safe)
sql = "SELECT user_id, role FROM app_users WHERE username = %s AND pw_hash = SHA2(%s, 256)"cursor.execute(sql, (username, password))   # values passed separately, never formatted into the string

The same idea in Java is PreparedStatement with ?, in Node.js connection.execute(sql, [values]), and in ORMs such as SQLAlchemy or Hibernate, bound parameters.

Note

Placeholders work for values only, not for table or column names. If a column name comes from user input (for example a sort option), check it against a fixed allow-list in code.

Defence in depth

  1. Parameters everywhere: no string concatenation or f-strings for SQL values.
  2. Least privilege: the application account should not have DROP, ALTER or access to other databases (GRANT).
  3. Validate input (type, length, format) as an extra layer, not as the main defence.
  4. Don’t show database errors to users; log them instead. Error messages help attackers.
  5. Store passwords hashed with a slow algorithm (bcrypt, Argon2) in the application, not reversible.

Testing for SQL injection (QA checklist)

As a tester, try these inputs in every field that reaches the database, on a test environment:

InputLooks for
'an SQL error or a 500 page: input is not escaped
' OR '1'='1login bypass or extra rows
admin' -- comment-based bypass
1 OR 1=1numeric parameters without quotes
' UNION SELECT NULL -- data from other tables

A safe application treats all of them as ordinary text: “invalid username or password”, no errors, no extra data. Automated scanners such as OWASP ZAP and sqlmap can help, but only with permission.

Quick quiz

1. What causes SQL injection?

2. Why do prepared statements prevent injection?

3. Can a placeholder be used for a table name?

4. An app account has only SELECT on its database. What does that limit?

SQL injection FAQ

What is SQL injection?

A vulnerability where an attacker supplies input that changes the structure of a SQL query, because the application builds the query by concatenating that input.

How do you prevent SQL injection?

Use prepared statements or parameterized queries for every value, give the application account the least privileges it needs, and validate input.

Does escaping quotes prevent SQL injection?

Escaping is error-prone and easy to forget or bypass with character-set tricks. Parameters are the reliable fix.

How do testers check for SQL injection?

By entering characters and payloads such as ', ' OR '1'='1 and admin' -- into inputs on a test system and checking for errors, bypassed logins or unexpected data.

SQL Indexes

SQL Tutorial › Performance & Objects › Indexes

SQL Indexes

IntermediateLesson 60 of 6913 min read7 queries run on MySQL 8.4

An index is a sorted lookup structure that lets the database find rows without reading the whole table, like the index at the back of a book. The right index turns a query that scans millions of rows into one that reads a handful. The wrong ones waste disk space and slow down every write. This lesson shows how to create indexes and, with EXPLAIN, how to prove MySQL uses them.

In this lesson
  • What an index is and how a B-tree lookup works
  • Create, list and drop indexes
  • Design composite indexes with the leftmost-prefix rule
  • Recognise queries that cannot use an index

How an index works

Without an index, WHERE city = 'Pune' makes MySQL read every row and test it: a full table scan. An index on city keeps the city values sorted, each with a pointer to its row, in a balanced tree (B-tree). MySQL walks down the tree to the first ‘Pune’ and reads only the matching entries.

Full scanIndex lookup
Rows read for 1 match in 1,000,000~1,000,000a few
Cost grows withtable sizelog of table size

InnoDB always has one index already: the primary key, which also stores the rows themselves in key order.

See the difference with EXPLAIN

EXPLAIN shows how MySQL plans to run a query (EXPLAIN lesson). Look at type, key and rows:

MySQL
EXPLAIN SELECT first_name FROM employees WHERE city = 'Pune';
ResultMySQL 8.4
idselect_typetablepartitionstypepossible_keyskeykey_lenrefrowsfilteredExtra
1SIMPLEemployeesNULLALLNULLNULLNULLNULL1210Using where
1 row in set
How this query works, step by step
  1. type = ALL means a full table scan.
  2. key = NULL: no index is used.
  3. rows = 12: MySQL expects to read every row.

CREATE INDEX

MySQL
CREATE INDEX idx_emp_city ON employees (city);EXPLAIN SELECT first_name FROM employees WHERE city = 'Pune';
ResultMySQL 8.4
mysql> CREATE INDEX idx_emp_city ON employees (city)
Query OK, 0 rows affected
mysql> EXPLAIN SELECT first_name FROM employees WHERE city = 'Pune'
idselect_typetablepartitionstypepossible_keyskeykey_lenrefrowsfilteredExtra
1SIMPLEemployeesNULLrefidx_emp_cityidx_emp_city123const3100NULL
1 row in set
How this query works, step by step
  1. CREATE INDEX idx_emp_city ON employees (city) builds a sorted index of the city column.
  2. Now type = ref and key = idx_emp_city: MySQL looks up ‘Pune’ in the index.
  3. rows = 3: only the matching rows are read.

List and drop indexes:

MySQL
CREATE INDEX idx_emp_city ON employees (city);SELECT index_name, column_name, non_uniqueFROM information_schema.statisticsWHERE table_schema = 'company' AND table_name = 'employees'ORDER BY index_name, seq_in_index;
ResultMySQL 8.4
mysql> CREATE INDEX idx_emp_city ON employees (city)
Query OK, 0 rows affected
mysql> SELECT index_name, column_name, non_unique
INDEX_NAMECOLUMN_NAMENON_UNIQUE
dept_iddept_id1
idx_emp_citycity1
PRIMARYemp_id0
3 rows in set

DROP INDEX idx_emp_city ON employees; removes it. The foreign key on dept_id created its own index automatically.

Unique indexes

A unique index also enforces that no two rows share a value (UNIQUE):

MySQL
CREATE UNIQUE INDEX uq_customer_email ON customers (email);

Composite indexes and the leftmost prefix

An index can cover several columns. Its entries are sorted by the first column, then the second, like a phone book sorted by last name, then first name:

MySQL
CREATE INDEX idx_orders_cust_date ON orders (customer_id, order_date);EXPLAIN SELECT order_id FROM orders WHERE customer_id = 1 AND order_date >= '2025-02-01';
ResultMySQL 8.4
mysql> CREATE INDEX idx_orders_cust_date ON orders (customer_id, order_date)
Query OK, 0 rows affected
mysql> EXPLAIN SELECT order_id FROM orders WHERE customer_id = 1 AND order_da…
idselect_typetablepartitionstypepossible_keyskeykey_lenrefrowsfilteredExtra
1SIMPLEordersNULLindexidx_orders_cust_dateidx_orders_cust_date7NULL1100Using where; Using index
1 row in set

The index can be used for the leftmost columns:

WHERE clauseUses (customer_id, order_date)?
customer_id = 1yes
customer_id = 1 AND order_date >= '2025-02-01'yes, both columns
order_date >= '2025-02-01'no (skips the first column)
MySQL
CREATE INDEX idx_orders_cust_date ON orders (customer_id, order_date);EXPLAIN SELECT order_id, status FROM orders WHERE order_date >= '2025-02-01';
ResultMySQL 8.4
mysql> CREATE INDEX idx_orders_cust_date ON orders (customer_id, order_date)
Query OK, 0 rows affected
mysql> EXPLAIN SELECT order_id, status FROM orders WHERE order_date >= '2025-…
idselect_typetablepartitionstypepossible_keyskeykey_lenrefrowsfilteredExtra
1SIMPLEordersNULLALLNULLNULLNULLNULL1100Using where
1 row in set
How this query works, step by step
  1. The query filters only on order_date, the second column of the index.
  2. Like finding everyone named “Rahul” in a phone book sorted by last name, the sorted order does not help to jump to the matching rows.
  3. type = ALL, key = NULL: a full table scan. (If the query asked only for columns that are in the index, MySQL could still scan the whole index instead of the table, shown as type = index, but that is a scan too, not a lookup.)
Tip

Put the column you filter with = first and the range column (>, BETWEEN, LIKE 'x%') last.

When an index cannot be used

PatternWhyBetter
WHERE YEAR(hire_date) = 2022function on the columnhire_date >= '2022-01-01' AND hire_date < '2023-01-01'
WHERE email LIKE '%@gmail.com'leading wildcardstore the domain in its own column
WHERE phone = 9845012345 (phone is text)type conversion of every rowWHERE phone = '9845012345'
WHERE a = 1 OR b = 2one index cannot serve bothtwo indexes (index merge) or UNION
MySQL
CREATE INDEX idx_emp_hire ON employees (hire_date);EXPLAIN SELECT first_name FROM employees WHERE YEAR(hire_date) = 2022;EXPLAIN SELECT first_name FROM employees WHERE hire_date >= '2022-01-01' AND hire_date < '2023-01-01';
ResultMySQL 8.4
mysql> CREATE INDEX idx_emp_hire ON employees (hire_date)
Query OK, 0 rows affected
mysql> EXPLAIN SELECT first_name FROM employees WHERE YEAR(hire_date) = 2022
idselect_typetablepartitionstypepossible_keyskeykey_lenrefrowsfilteredExtra
1SIMPLEemployeesNULLALLNULLNULLNULLNULL12100Using where
1 row in set
mysql> EXPLAIN SELECT first_name FROM employees WHERE hire_date >= '2022-01-0…
idselect_typetablepartitionstypepossible_keyskeykey_lenrefrowsfilteredExtra
1SIMPLEemployeesNULLrangeidx_emp_hireidx_emp_hire4NULL2100Using index condition
1 row in set
How this query works, step by step
  1. The first plan wraps the column in YEAR(), so the sorted index is useless: type = ALL.
  2. The second expresses the same condition as a range on the raw column: type = range using idx_emp_hire.
Note

On tiny tables like these, MySQL may choose a full scan even when an index exists, because reading 12 rows is cheaper than using the index. The plans above show the behaviour you will see on real-sized tables.

Covering indexes

If an index contains every column the query needs, MySQL answers from the index alone without touching the table. EXPLAIN then shows Using index in Extra:

MySQL
CREATE INDEX idx_orders_cust_date ON orders (customer_id, order_date);EXPLAIN SELECT customer_id, order_date FROM orders WHERE customer_id = 1;
ResultMySQL 8.4
mysql> CREATE INDEX idx_orders_cust_date ON orders (customer_id, order_date)
Query OK, 0 rows affected
mysql> EXPLAIN SELECT customer_id, order_date FROM orders WHERE customer_id = 1
idselect_typetablepartitionstypepossible_keyskeykey_lenrefrowsfilteredExtra
1SIMPLEordersNULLrefidx_orders_cust_dateidx_orders_cust_date4const3100Using index
1 row in set

The cost of indexes

  • Every INSERT, UPDATE and DELETE must also update every index on the table.
  • Indexes take disk space and memory.
  • So: index the columns used in WHERE, JOIN ... ON and ORDER BY of frequent queries, and drop indexes nobody uses (sys.schema_unused_indexes lists them).

Quick quiz

1. What does type = ALL in EXPLAIN mean?

2. An index on (customer_id, order_date). Which WHERE can use it?

3. Why can’t WHERE YEAR(hire_date) = 2022 use an index on hire_date?

4. What is a downside of having many indexes?

Index FAQ

What is an index in SQL?

A data structure (usually a B-tree) that keeps column values sorted with pointers to the rows, so the database can find matching rows without scanning the whole table.

When should I create an index?

On columns that appear often in WHERE, JOIN conditions and ORDER BY of important queries, especially on large tables. Confirm with EXPLAIN that it is used.

What is a composite index?

An index on several columns. It can be used for conditions on its leftmost column(s), so column order matters.

Do indexes slow down inserts?

Yes, a little per index, because each one must be updated. That is why you should not index every column.