MASTERING SQL

A Complete Guide for Beginners, Analysts & Developers

Covers SQL Fundamentals • Joins & Subqueries • Window Functions

Indexes & Performance • Stored Procedures • Interview Q&A; • Practice Problems

Mastering SQL — A Complete Guide | Page 1

 CHAPTER

Table of Contents

Chapter 1 — SQL Fundamentals — Databases, Tables & Data Types

Chapter 2 — Core SQL Commands — DDL, DML, DQL & DCL
Chapter 3 — Filtering, Sorting & Aggregation
Chapter 4 — Joins — Combining Tables
Chapter 5 — Subqueries & Common Table Expressions (CTEs)
Chapter 6 — Window Functions
Chapter 7 — Indexes & Query Performance
Chapter 8 — Stored Procedures, Functions & Triggers
Chapter 9 — Transactions & Concurrency
Chapter 10 — Interview Questions & Practice Problems

Mastering SQL — A Complete Guide | Page 2

 CHAPTER 1

SQL Fundamentals
Databases, Tables, Data Types & the Relational Model

What is SQL?
SQL (Structured Query Language) is the standard language for managing and manipulating relational
databases. Originally developed at IBM in the 1970s, SQL is now supported by virtually every major
database system: MySQL, PostgreSQL, Microsoft SQL Server, Oracle, SQLite, and more. Despite minor
dialect differences between vendors, roughly 90% of SQL you write is portable across all of them.

A relational database stores data in structured tables (also called relations), where each table has named
columns (attributes) and rows (records/tuples). Tables can be linked to each other through foreign keys,
eliminating redundancy and enabling powerful cross-table queries.

Data Types — Choosing the Right Type

Choosing the correct data type is one of the most impactful decisions you make when designing a database.
It affects storage size, query speed, and data integrity.

Category Data Type Description Example

Numeric INT / INTEGER Whole numbers, 4 bytes age INT

Numeric BIGINT Large whole numbers, 8 bytes user_id BIGINT

Numeric DECIMAL(p,s) Exact precision for money price DECIMAL(10,2)

Numeric FLOAT / DOUBLE Approximate decimal (scientific use) ratio FLOAT

Text VARCHAR(n) Variable-length string up to n chars name VARCHAR(100)

Text CHAR(n) Fixed-length string, padded with spaces code CHAR(3)

Text TEXT Unlimited text (not indexable in full) description TEXT

Date/Time DATE Calendar date only (YYYY-MM-DD) birth_date DATE

created_at
Date/Time TIMESTAMP Date + time, often UTC-aware
TIMESTAMP

Boolean BOOLEAN TRUE / FALSE (stored as 1/0 in many DBs) is_active BOOLEAN

Binary BLOB Binary large object (images, files) photo BLOB

Primary Keys, Foreign Keys & Constraints

Mastering SQL — A Complete Guide | Page 3

Constraints enforce rules at the database level, preventing bad data from ever being stored. They are your
first line of defence for data quality.

• PRIMARY KEY — Uniquely identifies each row. Cannot be NULL. Every table should have one.
• FOREIGN KEY — Links a column in one table to the PRIMARY KEY of another, enforcing referential
integrity.
• UNIQUE — Ensures all values in a column (or group of columns) are distinct.
• NOT NULL — Prevents NULL values from being stored in a column.
• CHECK — Validates that a value meets a condition (e.g. age > 0).
• DEFAULT — Automatically assigns a value when one is not provided on INSERT.

CREATE TABLE employees (

employee_id INT PRIMARY KEY AUTO_INCREMENT,

full_name VARCHAR(150) NOT NULL,

email VARCHAR(200) UNIQUE NOT NULL,

department_id INT NOT NULL,

salary DECIMAL(12,2) CHECK (salary >= 0),

hired_on DATE DEFAULT (CURRENT_DATE),

FOREIGN KEY (department_id) REFERENCES departments(department_id)

);

PRO TIP: Always define a PRIMARY KEY on every table. Use BIGINT AUTO_INCREMENT (MySQL) or
SERIAL (PostgreSQL) for surrogate keys in large systems.

Mastering SQL — A Complete Guide | Page 4

 CHAPTER 2

Core SQL Commands

DDL, DML, DQL & DCL — The Four Languages Within SQL

SQL is divided into four sub-languages, each serving a distinct purpose:

Sub-langu
Stands For Purpose Key Commands
age

CREATE, ALTER,
DDL Data Definition Language Define & modify database structure
DROP, TRUNCATE

INSERT, UPDATE,
DML Data Manipulation Language Insert, update, delete data
DELETE, MERGE

DQL Data Query Language Retrieve data SELECT

DCL Data Control Language Manage permissions & access GRANT, REVOKE

Transaction Control COMMIT, ROLLBACK,

TCL Control transactions
Language SAVEPOINT

DDL — Creating & Modifying Structures

-- Create a new table

CREATE TABLE products (

product_id INT PRIMARY KEY AUTO_INCREMENT,

product_name VARCHAR(200) NOT NULL,

category VARCHAR(100),

price DECIMAL(10,2) NOT NULL,

stock_qty INT DEFAULT 0

);

-- Add a new column

ALTER TABLE products ADD COLUMN supplier_id INT;

-- Rename a column (PostgreSQL syntax)

ALTER TABLE products RENAME COLUMN category TO product_category;

-- Delete a table permanently

DROP TABLE IF EXISTS products;

-- Remove all rows but keep the structure

TRUNCATE TABLE products;

Mastering SQL — A Complete Guide | Page 5

DML — Inserting, Updating & Deleting Data

-- INSERT single row

INSERT INTO products (product_name, price, stock_qty)

VALUES ('Wireless Keyboard', 49.99, 200);

-- INSERT multiple rows

INSERT INTO products (product_name, price, stock_qty) VALUES

('USB Hub', 24.99, 500),

('Webcam HD', 79.99, 150),

('Monitor Stand', 34.99, 300);

-- UPDATE specific rows

UPDATE products

SET price = 44.99, stock_qty = 180

WHERE product_id = 1;

-- DELETE specific rows

DELETE FROM products

WHERE stock_qty = 0;

PRO TIP: ALWAYS use a WHERE clause with UPDATE and DELETE. Without it, every row in the table is
affected. Test your WHERE condition with a SELECT first before executing the change.

Mastering SQL — A Complete Guide | Page 6

 CHAPTER 3

Filtering, Sorting & Aggregation

SELECT, WHERE, GROUP BY, HAVING & ORDER BY

The SELECT Statement — Full Anatomy

Understanding the logical execution order of SELECT is critical. SQL does not execute clauses in the order
you write them:

Execution Order Clause Purpose

1 FROM / JOIN Identify source tables and combine them

2 WHERE Filter rows before grouping

3 GROUP BY Group filtered rows into summary buckets

4 HAVING Filter groups (applied after GROUP BY)

5 SELECT Choose and compute columns to output

6 DISTINCT Remove duplicate rows from the output

7 ORDER BY Sort the final result set

8 LIMIT / OFFSET Restrict how many rows are returned

WHERE Clause — Filtering Operators

-- Comparison operators

SELECT * FROM products WHERE price > 50;

SELECT * FROM products WHERE price BETWEEN 20 AND 80;

-- Pattern matching with LIKE

SELECT * FROM products WHERE product_name LIKE 'Wire%'; -- starts with Wire

SELECT * FROM products WHERE product_name LIKE '%board'; -- ends with board

SELECT * FROM products WHERE product_name LIKE '%key%'; -- contains key

-- IN — match any value in a list

SELECT * FROM products WHERE category IN ('Electronics', 'Accessories');

-- IS NULL check

SELECT * FROM products WHERE supplier_id IS NULL;

-- Combining conditions

Mastering SQL — A Complete Guide | Page 7

 SELECT * FROM products

WHERE category = 'Electronics' AND price < 100 AND stock_qty > 0;

Aggregate Functions
Aggregate functions collapse multiple rows into a single summary value. They are almost always used with
GROUP BY to compute per-group statistics.

Function Returns Example

COUNT(*) Number of rows in the group COUNT(*) AS total_orders

COUNT(col) Non-NULL values in column COUNT(email) AS with_email

SUM(col) Total of numeric column SUM(price * qty) AS revenue

AVG(col) Arithmetic mean (NULLs excluded) AVG(salary) AS avg_salary

MIN(col) Smallest value MIN(price) AS cheapest

MAX(col) Largest value MAX(created_at) AS latest

GROUP_CONCA
Concatenates values (MySQL) GROUP_CONCAT(tag) AS tags
T

STRING_AGG Concatenates values (PostgreSQL) STRING_AGG(tag, ', ')

-- Sales report: revenue by category, only categories with > 5 products

SELECT

category,

COUNT(*) AS total_products,

SUM(price * stock_qty) AS inventory_value,

ROUND(AVG(price), 2) AS avg_price,

MAX(price) AS most_expensive

FROM products

WHERE stock_qty > 0 -- filter rows BEFORE grouping

GROUP BY category

HAVING COUNT(*) > 5 -- filter AFTER grouping

ORDER BY inventory_value DESC

LIMIT 10;

Mastering SQL — A Complete Guide | Page 8

 CHAPTER 4

Joins — Combining Tables

INNER, LEFT, RIGHT, FULL OUTER, CROSS & SELF Joins

Joins are the heart of relational databases. They let you query data spread across multiple tables as if it were
a single result set. Mastering joins is the single most important SQL skill for analysts and developers alike.

Join Type Returns Use When

You want records that exist in

INNER JOIN Only rows with matching values in BOTH tables
both

All rows from LEFT table + matching rows from right Keep all left records, even
LEFT JOIN
(NULLs where no match) unmatched

Keep all right records (rare —

RIGHT JOIN All rows from RIGHT table + matching rows from left
use LEFT JOIN instead)

Full reconciliation, finding

FULL OUTER JOIN All rows from BOTH tables (NULLs where no match)
mismatches

Cartesian product — every row paired with every

CROSS JOIN Generating combinations
other

Hierarchies, manager-employee,
SELF JOIN Join a table to itself using aliases
adjacency lists

INNER JOIN — The Most Common Join

-- Get all orders with customer and product details

SELECT

o.order_id,

c.full_name AS customer,

p.product_name,

[Link],

oi.unit_price,

([Link] * oi.unit_price) AS line_total

FROM orders o

INNER JOIN customers c ON o.customer_id = c.customer_id

INNER JOIN order_items oi ON o.order_id = oi.order_id

INNER JOIN products p ON oi.product_id = p.product_id

WHERE o.order_date >= '2024-01-01'

ORDER BY o.order_date DESC;

Mastering SQL — A Complete Guide | Page 9

LEFT JOIN — Finding Missing Records

-- Find customers who have NEVER placed an order

SELECT

c.customer_id,

c.full_name,

[Link],

o.order_id -- will be NULL for customers with no orders

FROM customers c

LEFT JOIN orders o ON c.customer_id = o.customer_id

WHERE o.order_id IS NULL; -- filter for unmatched rows only

SELF JOIN — Hierarchical Data

-- Show each employee with their manager's name

SELECT

e.employee_id,

e.full_name AS employee,

m.full_name AS manager

FROM employees e

LEFT JOIN employees m ON e.manager_id = m.employee_id

ORDER BY m.full_name, e.full_name;

PRO TIP: When joining more than 2 tables, always alias your tables with short, meaningful names (o =
orders, c = customers). This makes complex queries readable and reduces mistakes.

Mastering SQL — A Complete Guide | Page 10

 CHAPTER 5

Subqueries & CTEs

Scalar, Correlated, IN/EXISTS Subqueries & Common Table Expressions

Subqueries — Queries Within Queries

A subquery is a SELECT statement nested inside another SQL statement. They can appear in the SELECT
list, FROM clause, WHERE clause, or HAVING clause.

-- Scalar subquery (returns a single value)

SELECT product_name, price,

price - (SELECT AVG(price) FROM products) AS diff_from_avg

FROM products

ORDER BY diff_from_avg DESC;

-- Subquery in WHERE with IN

SELECT full_name, email

FROM customers

WHERE customer_id IN (

SELECT DISTINCT customer_id

FROM orders

WHERE order_date >= '2024-06-01'

);

-- Correlated subquery (executes once per outer row)

SELECT product_name, price, category

FROM products p

WHERE price = (

SELECT MAX(price)

FROM products

WHERE category = [Link] -- references outer query

);

EXISTS vs IN — Performance Matters

EXISTS stops scanning as soon as it finds one matching row, making it faster than IN for large datasets
where subquery results are big.

-- Using EXISTS (often faster for large tables)

Mastering SQL — A Complete Guide | Page 11

 SELECT c.full_name

FROM customers c

WHERE EXISTS (

SELECT 1 FROM orders o

WHERE o.customer_id = c.customer_id

AND o.total_amount > 1000

);

Common Table Expressions (CTEs)

CTEs (introduced with the WITH keyword) create named temporary result sets that exist only for the duration
of the query. They dramatically improve readability by breaking complex queries into logical, named steps —
like functions for SQL.

-- Multi-step CTE: monthly revenue trend with running total

WITH monthly_sales AS (

SELECT

DATE_FORMAT(order_date, '%Y-%m') AS month,

SUM(total_amount) AS revenue

FROM orders

WHERE order_date >= '2024-01-01'

GROUP BY DATE_FORMAT(order_date, '%Y-%m')

),

ranked_months AS (

SELECT

month,

revenue,

SUM(revenue) OVER (ORDER BY month) AS running_total,

RANK() OVER (ORDER BY revenue DESC) AS revenue_rank

FROM monthly_sales

SELECT * FROM ranked_months

ORDER BY month;

PRO TIP: Recursive CTEs can traverse hierarchical data (org charts, file systems, bill of materials). Use
WITH RECURSIVE in PostgreSQL/MySQL 8+ for tree traversal queries.

Mastering SQL — A Complete Guide | Page 12

 CHAPTER 6

Window Functions
OVER(), PARTITION BY, ROW_NUMBER, RANK, LAG, LEAD & More

Window functions perform calculations across a set of rows related to the current row WITHOUT collapsing
them into a single result (unlike GROUP BY). They are one of the most powerful and interview-tested
features in modern SQL.

Function Category What It Does

ROW_NUMBER() Ranking Sequential integer — no ties

RANK() Ranking Rank with gaps on ties (1,1,3,4)

DENSE_RANK() Ranking Rank without gaps on ties (1,1,2,3)

NTILE(n) Ranking Divides rows into n equal buckets

LAG(col, n) Navigation Value from n rows BEFORE current row

LEAD(col, n) Navigation Value from n rows AFTER current row

FIRST_VALUE(col) Navigation First value in the window frame

LAST_VALUE(col) Navigation Last value in the window frame

SUM() OVER() Aggregation Running or partitioned sum

AVG() OVER() Aggregation Running or partitioned average

COUNT() OVER() Aggregation Running count within partition

-- Ranking employees by salary within each department

SELECT

full_name,

department,

salary,

RANK() OVER (PARTITION BY department ORDER BY salary DESC) AS dept_rank,

DENSE_RANK() OVER (PARTITION BY department ORDER BY salary DESC) AS dept_dense_rank,

ROW_NUMBER() OVER (PARTITION BY department ORDER BY salary DESC) AS row_num

FROM employees;

-- Month-over-month revenue change using LAG

SELECT

month,

revenue,

Mastering SQL — A Complete Guide | Page 13

LAG(revenue, 1) OVER (ORDER BY month) AS prev_month_revenue,

revenue - LAG(revenue, 1) OVER (ORDER BY month) AS mom_change,

ROUND(

(revenue - LAG(revenue,1) OVER (ORDER BY month))

/ LAG(revenue,1) OVER (ORDER BY month) * 100, 2

) AS pct_change

FROM monthly_sales;

PRO TIP: To get the TOP N rows per group, use ROW_NUMBER() inside a CTE and filter WHERE rn <= N
in the outer query. This is one of the most common interview patterns.

Mastering SQL — A Complete Guide | Page 14

 CHAPTER 7

Indexes & Query Performance

B-Tree Indexes, Composite Indexes, EXPLAIN & Query Optimisation

What is an Index?
An index is a separate data structure (usually a B-Tree) that the database maintains alongside your table to
enable fast lookups. Without an index, the database performs a full table scan — reading every row. With
an index, it can jump directly to the relevant rows. Indexes speed up reads but slow down writes
(INSERT/UPDATE/DELETE).

Index Type Best For Notes

B-Tree (default) Range queries, equality, ORDER BY Default type in all major databases

Hash Index Exact equality lookups only Faster for = but useless for ranges

Composite Index Queries filtering on multiple columns Column order matters — leftmost prefix rule

Unique Index Enforcing uniqueness + fast lookups Created automatically for UNIQUE constraints

Full-Text Index LIKE '%word%' text searches Use MATCH() ... AGAINST() with this type

Partial Index Indexing a subset of rows e.g. WHERE is_active = TRUE (PostgreSQL)

All columns in SELECT + WHERE are in the

Covering Index Query satisfied entirely from the index
index

-- Single column index

CREATE INDEX idx_orders_customer ON orders(customer_id);

-- Composite index (order matters: filter by status first, then date)

CREATE INDEX idx_orders_status_date ON orders(status, order_date);

-- Unique index

CREATE UNIQUE INDEX idx_users_email ON users(email);

-- Drop an index

DROP INDEX idx_orders_customer ON orders; -- MySQL

DROP INDEX idx_orders_customer; -- PostgreSQL

-- Analyse query execution plan

EXPLAIN SELECT * FROM orders WHERE customer_id = 42 AND status = 'shipped';

Query Optimisation — Key Principles

Mastering SQL — A Complete Guide | Page 15

• Use indexes on WHERE, JOIN ON, and ORDER BY columns — these are the most common
bottlenecks.
• Avoid functions on indexed columns in WHERE — WHERE YEAR(order_date) = 2024 prevents
index use. Use WHERE order_date BETWEEN '2024-01-01' AND '2024-12-31' instead.
• SELECT only what you need — avoid SELECT *. It transfers unnecessary data and prevents covering
index optimisations.
• Avoid correlated subqueries in large tables — they re-execute for every row. Rewrite with JOINs or
CTEs.
• Use LIMIT — when you need only a few rows, LIMIT 10 lets the engine stop early.
• Check EXPLAIN output — look for 'Using filesort', 'Using temporary', and 'type: ALL' — these signal
performance problems.

Mastering SQL — A Complete Guide | Page 16

 CHAPTER 8

Stored Procedures, Functions & Triggers

Reusable SQL Logic, Automation & Event-Driven Actions

Stored Procedures
A stored procedure is a named, precompiled block of SQL stored in the database. It can accept parameters,
contain conditional logic, loops, and error handling — essentially a program written in SQL's procedural
extension (PL/SQL, T-SQL, PL/pgSQL).

-- MySQL: Stored procedure to transfer funds between accounts

DELIMITER $$

CREATE PROCEDURE transfer_funds(

IN from_account INT,

IN to_account INT,

IN amount DECIMAL(12,2),

OUT result VARCHAR(50)

BEGIN

DECLARE current_balance DECIMAL(12,2);

SELECT balance INTO current_balance FROM accounts WHERE id = from_account;

IF current_balance < amount THEN

SET result = 'INSUFFICIENT_FUNDS';

ELSE

UPDATE accounts SET balance = balance - amount WHERE id = from_account;

UPDATE accounts SET balance = balance + amount WHERE id = to_account;

SET result = 'SUCCESS';

END IF;

END $$

DELIMITER ;

-- Call the procedure

CALL transfer_funds(101, 202, 500.00, @result);

SELECT @result;

Triggers — Automatic Actions on Data Events

Mastering SQL — A Complete Guide | Page 17

A trigger fires automatically BEFORE or AFTER an INSERT, UPDATE, or DELETE on a table. They are
used for audit logging, enforcing complex business rules, and maintaining derived data.

-- Audit trigger: log every salary change

CREATE TRIGGER trg_salary_audit

AFTER UPDATE ON employees

FOR EACH ROW

BEGIN

IF [Link] <> [Link] THEN

INSERT INTO salary_audit_log (employee_id, old_salary, new_salary, changed_at)

VALUES (OLD.employee_id, [Link], [Link], NOW());

END IF;

END;

Mastering SQL — A Complete Guide | Page 18

 CHAPTER 9

Transactions & Concurrency

ACID Properties, Isolation Levels & Locking

ACID Properties
Every database transaction must satisfy four properties to guarantee data reliability:

Property Meaning Example

All operations in a transaction succeed or all Bank transfer: debit AND credit must both
Atomicity
fail — no partial commits succeed

A transaction brings the database from one Account balance cannot go negative if
Consistency
valid state to another constrained

Concurrent transactions do not interfere with Two users booking the last seat see each
Isolation
each other other's changes correctly

Once committed, changes survive crashes Committed orders persist even after server
Durability
and power failures restart

-- Transaction example: safe order placement

START TRANSACTION;

-- Step 1: Insert order

INSERT INTO orders (customer_id, total_amount) VALUES (42, 299.99);

SET @new_order_id = LAST_INSERT_ID();

-- Step 2: Reduce stock

UPDATE products SET stock_qty = stock_qty - 1 WHERE product_id = 7;

-- Step 3: Check stock didn't go negative

IF (SELECT stock_qty FROM products WHERE product_id = 7) < 0 THEN

ROLLBACK; -- undo everything

ELSE

COMMIT; -- persist everything

END IF;

Transaction Isolation Levels

Isolation Level Dirty Read Non-repeatable Read Phantom Read

READ UNCOMMITTED Possible Possible Possible

Mastering SQL — A Complete Guide | Page 19

READ COMMITTED Prevented Possible Possible

REPEATABLE READ Prevented Prevented Possible

SERIALIZABLE Prevented Prevented Prevented

Mastering SQL — A Complete Guide | Page 20

 CHAPTER 10

Interview Questions & Practice Problems

Frequently Asked SQL Questions + Hands-On Practice Exercises

Top Interview Questions

Q1: What is the difference between WHERE and HAVING?

WHERE filters individual rows BEFORE grouping; HAVING filters groups AFTER GROUP BY. WHERE
cannot reference aggregate functions (SUM, COUNT etc.) but HAVING can.

Q2: What is the difference between DELETE, TRUNCATE and DROP?

DELETE removes specific rows and can be rolled back (DML). TRUNCATE removes ALL rows faster and
cannot always be rolled back (DDL in MySQL). DROP removes the entire table structure and data
permanently.

Q3: What is a correlated subquery?

A subquery that references columns from the outer query. It re-executes for every row processed by the
outer query, making it potentially slow on large tables. Can often be rewritten as a JOIN for better
performance.

Q4: Explain the difference between RANK(), DENSE_RANK() and ROW_NUMBER().

All three assign a position to rows. ROW_NUMBER gives a unique sequential number (no ties). RANK
gives the same number to ties but leaves gaps (1,1,3). DENSE_RANK gives the same number to ties
without gaps (1,1,2).

Q5: What is a covering index?

An index that contains all columns needed by a query (in SELECT, WHERE, and ORDER BY). The
database can satisfy the query entirely from the index without touching the main table, making it extremely
fast.

Q6: How do you find duplicate rows in a table?

Use GROUP BY on the duplicate key columns and HAVING COUNT(*) > 1. Example: SELECT email,
COUNT(*) FROM users GROUP BY email HAVING COUNT(*) > 1.

Q7: What is normalisation? Name the normal forms.

Mastering SQL — A Complete Guide | Page 21

 Normalisation is the process of structuring tables to reduce redundancy and dependency. 1NF: atomic
values, no repeating groups. 2NF: no partial dependency on composite key. 3NF: no transitive
dependency. BCNF: every determinant is a candidate key.

Q8: What is the N+1 query problem?

In application code, fetching a list of N records and then running 1 additional query per record = N+1 total
queries. Solved by using JOINs or eager loading to fetch all needed data in a single query.

Practice Problems — Beginner

Use the following schema for all practice problems: customers(id, name, email, city) | orders(id,
customer_id, amount, status, order_date) | products(id, name, category, price, stock) |
order_items(order_id, product_id, quantity, unit_price)

Q List all customers from Mumbai, sorted alphabetically by name.

1.
Hint: Use WHERE city = 'Mumbai' and ORDER BY name.

Answer: SELECT name, email FROM customers WHERE city = 'Mumbai' ORDER BY name;

Q Find all products with price between Rs.500 and Rs.2000.

2.
Hint: Use BETWEEN operator.

Answer: SELECT name, price FROM products WHERE price BETWEEN 500 AND 2000;

Q Count the total number of orders placed in 2024.

3.
Hint: Use COUNT(*) with a WHERE on order_date.

Answer: SELECT COUNT(*) FROM orders WHERE YEAR(order_date) = 2024;

Q Find the top 5 most expensive products.

4.
Hint: Use ORDER BY price DESC LIMIT 5.

Answer: SELECT name, price FROM products ORDER BY price DESC LIMIT 5;

Q Find all orders with status 'pending' or 'processing'.

5.
Hint: Use IN operator.

Answer: SELECT * FROM orders WHERE status IN ('pending', 'processing');

Practice Problems — Intermediate

Q For each customer, show their name and total amount spent across all orders. Only include customers who
1. have spent more than Rs.10,000.
Hint: JOIN customers to orders, GROUP BY customer, use HAVING.

Answer: SELECT [Link], SUM([Link]) AS total FROM customers c JOIN orders o ON [Link] = o.customer_id
GROUP BY [Link], [Link] HAVING SUM([Link]) > 10000 ORDER BY total DESC;

Mastering SQL — A Complete Guide | Page 22

Q Find customers who have placed orders but never bought a product from the 'Electronics' category.
2.
Hint: Use NOT EXISTS or NOT IN with a subquery joining order_items and products.

Answer: SELECT DISTINCT [Link] FROM customers c WHERE EXISTS (SELECT 1 FROM orders o WHERE
o.customer_id = [Link]) AND [Link] NOT IN (SELECT o.customer_id FROM orders o JOIN order_items oi ON [Link] =
oi.order_id JOIN products p ON oi.product_id = [Link] WHERE [Link] = 'Electronics');

Q Show each product's name, its category, its price, and the average price of all products in the same
3. category.
Hint: Use AVG() as a window function with PARTITION BY category.

Answer: SELECT name, category, price, ROUND(AVG(price) OVER (PARTITION BY category), 2) AS

category_avg FROM products;

Q List the top 3 best-selling products by total quantity sold.

4.
Hint: JOIN order_items to products, GROUP BY product, ORDER BY SUM(quantity) DESC LIMIT 3.

Answer: SELECT [Link], SUM([Link]) AS qty_sold FROM products p JOIN order_items oi ON [Link] =
oi.product_id GROUP BY [Link], [Link] ORDER BY qty_sold DESC LIMIT 3;

Practice Problems — Advanced

Q For each month in 2024, show total revenue, the previous month's revenue, and the percentage change
1. month-over-month.
Hint: Use a CTE for monthly revenue, then LAG() window function for previous month.

Answer: WITH m AS (SELECT DATE_FORMAT(order_date,'%Y-%m') mo, SUM(amount) rev FROM orders

WHERE YEAR(order_date)=2024 GROUP BY mo) SELECT mo, rev, LAG(rev) OVER (ORDER BY mo) prev,
ROUND((rev - LAG(rev) OVER (ORDER BY mo)) / LAG(rev) OVER (ORDER BY mo) * 100, 2) pct FROM m
ORDER BY mo;

Q Find all customers who placed an order every single month in 2024 (12 consecutive months).
2.
Hint: COUNT(DISTINCT month) = 12 per customer.

Answer: SELECT customer_id FROM orders WHERE YEAR(order_date) = 2024 GROUP BY customer_id
HAVING COUNT(DISTINCT MONTH(order_date)) = 12;

Q For each product, show the running total of units sold over time (cumulative sum ordered by order date).
3.
Hint: Use SUM() as a window function with ORDER BY inside OVER().

Answer: SELECT [Link], o.order_date, [Link], SUM([Link]) OVER (PARTITION BY oi.product_id

ORDER BY o.order_date) AS running_total FROM order_items oi JOIN orders o ON oi.order_id = [Link] JOIN
products p ON oi.product_id = [Link] ORDER BY [Link], o.order_date;

Mastering SQL — A Complete Guide | Page 23

 CHAPTER

SQL Quick Reference Cheatsheet

SELECT Skeleton
SELECT col1, col2, AGG_FUNC(col3)

FROM table_name

JOIN other ON table_name.id = [Link]

WHERE condition

GROUP BY col1, col2

HAVING AGG_FUNC(col3) > value

ORDER BY col1 ASC, col2 DESC

LIMIT n OFFSET m;

Window Function Skeleton

SELECT col,

FUNCTION() OVER (

PARTITION BY partition_col

ORDER BY order_col

ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW

) AS alias

FROM table;

CTE Skeleton
WITH cte_name AS (

SELECT ... FROM ... WHERE ...

),

cte_two AS (

SELECT ... FROM cte_name WHERE ...

SELECT * FROM cte_two;

Find Duplicates
SELECT col, COUNT(*)

FROM table

GROUP BY col

Mastering SQL — A Complete Guide | Page 24

HAVING COUNT(*) > 1;

Delete Duplicates (keep lowest id)

DELETE FROM table

WHERE id NOT IN (

SELECT MIN(id)

FROM table

GROUP BY duplicate_col

);

Top N Per Group

WITH ranked AS (

SELECT *, ROW_NUMBER() OVER

(PARTITION BY group_col ORDER BY rank_col DESC) rn

FROM table

SELECT * FROM ranked WHERE rn <= N;

Keep practising. SQL mastery is built query by query. Every complex problem you
solve makes the next one easier.

Mastering SQL — A Complete Guide | Page 25