The Complete MySQL Guide

Core Concepts, Definitions, and 30 Multi-Table Practice Problems

with line-by-line execution-flow explanations, using one shared company + sales schema
Part 1 — Core Concepts & Definitions
Before diving into the practice questions, here is a plain-language glossary of every MySQL concept used later in
this guide.

SELECT
The statement used to read data from one or more tables. It specifies which columns to return. SELECT * FROM
employees; returns every column; SELECT emp_name FROM employees; returns only one column.

WHERE
Filters rows BEFORE any grouping happens. Only rows that satisfy the condition are passed to the next stage of
the query (GROUP BY, SELECT, ORDER BY).

DISTINCT
Removes duplicate rows from the result set, comparing the full row (or the selected columns) after the query
would otherwise have produced repeats.

ORDER BY
Sorts the final result set by one or more columns, ascending (ASC, default) or descending (DESC). Applied after
SELECT, WHERE, GROUP BY and HAVING.

LIMIT / OFFSET
LIMIT restricts how many rows are returned; OFFSET skips a number of rows before starting to return rows.
LIMIT 5 OFFSET 10 skips the first 10 rows and returns the next 5.

Aggregate Functions
Functions that collapse many rows into one value: COUNT() counts rows, SUM() adds numeric values, AVG()
averages them, MIN()/MAX() find the smallest/largest value. They ignore NULLs (except COUNT(*)).

GROUP BY
Groups rows that share the same value(s) in specified column(s) into a single summary row, so aggregate
functions can be calculated per group instead of over the whole table.

HAVING
Filters groups AFTER GROUP BY has produced them, using conditions on aggregate results. WHERE cannot filter
on an aggregate; HAVING can.

INNER JOIN
Returns only the rows where a match exists in BOTH tables based on the join condition. Non-matching rows on
either side are dropped entirely.

LEFT JOIN (LEFT OUTER JOIN)

Returns every row from the left table, plus matching rows from the right table. If there is no match, the right-
table columns are filled with NULL.

RIGHT JOIN (RIGHT OUTER JOIN)

The mirror of LEFT JOIN: returns every row from the right table, plus matching rows from the left table, with
NULLs where there is no match.

FULL OUTER JOIN

Returns all rows from both tables, matched where possible and NULL-padded where not. MySQL has no native
FULL OUTER JOIN keyword, so it is emulated with a LEFT JOIN UNION a RIGHT JOIN.

SELF JOIN
A table joined to itself, typically using two different aliases, so rows in a table can be compared against other
rows in the same table (e.g. an employee row compared to its manager's row, which also lives in the employees
table).

CROSS JOIN
Returns the Cartesian product of two tables: every row of the first table combined with every row of the second
table. No join condition is used.

Subquery
A SELECT nested inside another SQL statement. A non-correlated subquery runs once, independently, and its
result is used by the outer query. A correlated subquery references a column from the outer query, so it logically
re-runs once per outer row.

EXISTS / NOT EXISTS

A boolean test that checks whether a subquery returns at least one row. It stops as soon as one matching row is
found and doesn't care about the actual values returned, only whether rows exist.

IN / NOT IN
Tests whether a value matches any value in a list or in the result set of a subquery.

UNION / UNION ALL

Stacks the results of two or more SELECT statements on top of each other (same number/type of columns
required). UNION removes duplicate rows across the combined set; UNION ALL keeps every row, including
duplicates, and is faster because it skips de-duplication.

CASE WHEN
SQL's inline if/else. It evaluates conditions top to bottom and returns the value tied to the first true condition, or
the ELSE value if none match.

Window Functions (OVER / PARTITION BY)

Functions that calculate a value across a 'window' of rows related to the current row, without collapsing rows
the way GROUP BY does. PARTITION BY divides rows into groups for the calculation; ORDER BY inside OVER()
controls row sequence for ranking/running calculations.

ROW_NUMBER()
A window function that assigns a unique, sequential integer (1, 2, 3, ...) to each row within its partition, based on
the ORDER BY inside OVER(). Ties still get different numbers.

RANK() / DENSE_RANK()
Window functions that assign a rank based on ORDER BY. RANK() leaves gaps after ties (1,2,2,4); DENSE_RANK()
does not (1,2,2,3).

LAG() / LEAD()
Window functions that let a row see a value from a previous row (LAG) or a following row (LEAD) within the
same partition/order, useful for period-over-period comparisons.

Common Table Expression (CTE / WITH)

A named, temporary result set defined with WITH ... AS (...) that can be referenced later in the same query,
making complex queries easier to read by breaking them into named steps.

VIEW
A saved SELECT query that behaves like a virtual table. It does not store data itself (unless it is a materialized
view); it re-runs the underlying query every time it is used.

INDEX
An on-disk data structure (typically a B-Tree) that MySQL builds on one or more columns to speed up lookups, at
the cost of extra storage and slower writes. A PRIMARY KEY automatically creates a unique index.

Constraints (PK, FK, UNIQUE, NOT NULL, CHECK)

Rules enforced by the database on column values. PRIMARY KEY uniquely identifies each row; FOREIGN KEY
enforces that a value must exist in a referenced table; UNIQUE forbids duplicate values; NOT NULL forbids
empty values; CHECK enforces a custom condition.

Transactions (COMMIT / ROLLBACK / ACID)

A transaction groups multiple statements so they succeed or fail together. COMMIT saves the changes
permanently; ROLLBACK undoes everything since the transaction began. ACID stands for Atomicity, Consistency,
Isolation, Durability -- the guarantees a transaction provides.

Stored Procedure
A named, precompiled block of SQL statements stored in the database that can be called with CALL
procedure_name(...), optionally taking parameters.

Trigger
A block of SQL that MySQL runs automatically BEFORE or AFTER an INSERT, UPDATE, or DELETE on a specific
table.

String Functions
Functions that manipulate text, e.g. CONCAT() joins strings, SUBSTRING() extracts part of a string,
UPPER()/LOWER() change case, TRIM() removes whitespace, LENGTH() returns character count.

Date Functions
Functions that operate on dates, e.g. NOW()/CURDATE() return the current date/time, DATEDIFF() returns the
difference in days between two dates, DATE_FORMAT() reformats a date for display, YEAR()/MONTH() extract
parts of a date.

NULL Handling
NULL means 'unknown/absent', not zero or empty string. It is never equal to anything, even another NULL, so =
NULL never matches -- use IS NULL / IS NOT NULL. COALESCE() and IFNULL() return a fallback value when an
expression is NULL.
Part 2 — The Shared Database Schema
All 30 questions below use the SAME single schema so the concepts build on each other. It models a small
company that also runs a sales/ordering system: employees belong to departments, work on projects, and take
orders from customers for products.

departments
One row per department.

Column Type / Key

dept_id INT, PRIMARY KEY

dept_name VARCHAR(50)

location VARCHAR(50)

employees
One row per employee. manager_id points back to another row in this same table (self-referencing foreign key)
so we can build an org chart.

Column Type / Key

emp_id INT, PRIMARY KEY

emp_name VARCHAR(50)

dept_id INT, FOREIGN KEY -> departments.dept_id

manager_id INT, FOREIGN KEY -> employees.emp_id (NULL for the

top manager)

salary DECIMAL(10,2)

hire_date DATE

job_title VARCHAR(50)

projects
One row per project run by a department.

Column Type / Key

project_id INT, PRIMARY KEY

project_name VARCHAR(50)

dept_id INT, FOREIGN KEY -> departments.dept_id

budget DECIMAL(10,2)

start_date DATE
 Column Type / Key

end_date DATE

employee_projects
Bridge/junction table for the many-to-many relationship between employees and projects.

Column Type / Key

emp_id INT, FOREIGN KEY -> employees.emp_id

project_id INT, FOREIGN KEY -> projects.project_id

hours_worked INT

role VARCHAR(50)

categories
One row per product category.

Column Type / Key

category_id INT, PRIMARY KEY

category_name VARCHAR(50)

products
One row per product for sale.

Column Type / Key

product_id INT, PRIMARY KEY

product_name VARCHAR(50)

category_id INT, FOREIGN KEY -> categories.category_id

price DECIMAL(10,2)

stock_quantity INT

customers
One row per customer.

Column Type / Key

customer_id INT, PRIMARY KEY

customer_name VARCHAR(50)
 Column Type / Key

city VARCHAR(50)

country VARCHAR(50)

orders
One row per order placed. emp_id records which employee (salesperson) handled it.

Column Type / Key

order_id INT, PRIMARY KEY

customer_id INT, FOREIGN KEY -> customers.customer_id

emp_id INT, FOREIGN KEY -> employees.emp_id

order_date DATE

status VARCHAR(20)

order_items
One row per product line inside an order (an order can have many items).

Column Type / Key

order_item_id INT, PRIMARY KEY

order_id INT, FOREIGN KEY -> orders.order_id

product_id INT, FOREIGN KEY -> products.product_id

quantity INT

unit_price DECIMAL(10,2)

Full CREATE TABLE Statements

The DDL below creates every table shown above, with primary keys and foreign keys wired together exactly as
described.

CREATE TABLE departments (

dept_id INT PRIMARY KEY,
dept_name VARCHAR(50),
location VARCHAR(50)
);

CREATE TABLE employees (

emp_id INT PRIMARY KEY,
emp_name VARCHAR(50),
dept_id INT,
manager_id INT,
salary DECIMAL(10,2),
 hire_date DATE,
job_title VARCHAR(50),
FOREIGN KEY (dept_id) REFERENCES departments(dept_id),
FOREIGN KEY (manager_id) REFERENCES employees(emp_id)
);

CREATE TABLE projects (

project_id INT PRIMARY KEY,
project_name VARCHAR(50),
dept_id INT,
budget DECIMAL(10,2),
start_date DATE,
end_date DATE,
FOREIGN KEY (dept_id) REFERENCES departments(dept_id)
);

CREATE TABLE employee_projects (

emp_id INT,
project_id INT,
hours_worked INT,
role VARCHAR(50),
PRIMARY KEY (emp_id, project_id),
FOREIGN KEY (emp_id) REFERENCES employees(emp_id),
FOREIGN KEY (project_id) REFERENCES projects(project_id)
);

CREATE TABLE categories (

category_id INT PRIMARY KEY,
category_name VARCHAR(50)
);

CREATE TABLE products (

product_id INT PRIMARY KEY,
product_name VARCHAR(50),
category_id INT,
price DECIMAL(10,2),
stock_quantity INT,
FOREIGN KEY (category_id) REFERENCES categories(category_id)
);

CREATE TABLE customers (

customer_id INT PRIMARY KEY,
customer_name VARCHAR(50),
city VARCHAR(50),
country VARCHAR(50)
);

CREATE TABLE orders (

order_id INT PRIMARY KEY,
customer_id INT,
emp_id INT,
order_date DATE,
status VARCHAR(20),
FOREIGN KEY (customer_id) REFERENCES customers(customer_id),
FOREIGN KEY (emp_id) REFERENCES employees(emp_id)
);

CREATE TABLE order_items (

order_item_id INT PRIMARY KEY,
order_id INT,
 product_id INT,
quantity INT,
unit_price DECIMAL(10,2),
FOREIGN KEY (order_id) REFERENCES orders(order_id),
FOREIGN KEY (product_id) REFERENCES products(product_id)
);
Part 3 — 30 Complex Practice Questions
Every question below uses the same schema from Part 2. Each solution is written in the simplest, most literal
way possible — not the most optimized way — so the logic is easy to trace. Each solution is followed by a step-
by-step explanation of exactly how MySQL executes it, line by line.

Question 1 [Concept: INNER JOIN]

List every employee's name together with the name of the department they belong to.

SELECT e.emp_name, d.dept_name

FROM employees e
INNER JOIN departments d ON e.dept_id = d.dept_id;

Execution Flow:
1. MySQL starts from the FROM clause: it opens the employees table and gives it the alias e.
2. For the INNER JOIN, MySQL takes each row of e and looks for department rows d where d.dept_id equals e.dept_id.
3. Only pairs of rows that actually match on dept_id survive; an employee whose dept_id has no matching department
row (or is NULL) is dropped completely.
4. Once the matched employee-department pairs are built, the SELECT list picks just emp_name and dept_name from
each pair.
5. No WHERE, GROUP BY or ORDER BY exists here, so the rows are returned in whatever order the join happened to
produce them.

Question 2 [Concept: LEFT JOIN]

List every employee's name and their department name, but still show employees who are not yet assigned to
any department.

SELECT e.emp_name, d.dept_name

FROM employees e
LEFT JOIN departments d ON e.dept_id = d.dept_id;

Execution Flow:
1. MySQL again starts with the full employees table (the 'left' table), because LEFT JOIN guarantees every left-side row
appears in the output.
2. For each employee row, MySQL tries to find a department row with a matching dept_id.
3. If a match is found, dept_name is filled in from that matching row.
4. If no match is found (employee has no dept_id, or that dept_id doesn't exist in departments), MySQL still keeps the
employee row but fills dept_name with NULL instead of dropping the row.
5. The SELECT then returns emp_name and dept_name for every employee, matched or not.

Question 3 [Concept: LEFT JOIN + IS NULL]

Find all departments that currently have zero employees.

SELECT d.dept_name
FROM departments d
LEFT JOIN employees e ON d.dept_id = e.dept_id
WHERE e.emp_id IS NULL;
Execution Flow:
1. This time departments is the left table, so every department is guaranteed to appear at least once in the joined result.
2. For each department, MySQL looks for employees with a matching dept_id.
3. A department with employees produces one joined row per matching employee, with e.emp_id filled in.
4. A department with no employees produces exactly one joined row where every employees-side column, including
e.emp_id, is NULL.
5. WHERE e.emp_id IS NULL then filters the joined rows down to only those NULL-padded rows -- i.e. the departments
that found no employee match at all.
6. SELECT finally returns just dept_name for these empty departments.

Question 4 [Concept: SELF JOIN]

Show each employee's name next to the name of their manager.

SELECT emp.emp_name AS employee, mgr.emp_name AS manager

FROM employees emp
LEFT JOIN employees mgr ON emp.manager_id = mgr.emp_id;

Execution Flow:
1. The employees table is opened twice under two different aliases, emp (representing 'the employee') and mgr
(representing 'the employee who happens to be the manager').
2. MySQL treats emp and mgr as if they were two separate tables even though they read from the same underlying data.
3. For each row in emp, MySQL looks in mgr for the row whose emp_id equals emp.manager_id.
4. LEFT JOIN is used so that the top-level employee, whose manager_id is NULL, is still kept in the output with manager
shown as NULL instead of being removed.
5. SELECT then labels emp.emp_name as employee and mgr.emp_name as manager, giving a simple two-column
reporting-line list.

Question 5 [Concept: SELF JOIN + WHERE]

Find employees who earn more than their own manager.

SELECT emp.emp_name AS employee, [Link] AS employee_salary,

mgr.emp_name AS manager, [Link] AS manager_salary
FROM employees emp
INNER JOIN employees mgr ON emp.manager_id = mgr.emp_id
WHERE [Link] > [Link];

Execution Flow:
1. The employees table is again aliased twice: emp is the subordinate row, mgr is the row belonging to that
subordinate's manager.
2. INNER JOIN connects emp.manager_id to mgr.emp_id, so only employees who actually have a manager present in the
table are considered (the top manager, with NULL manager_id, is automatically excluded here).
3. After the join, each row represents one (employee, their manager) pair with both salaries available side by side.
4. WHERE [Link] > [Link] keeps only the pairs where the subordinate's salary number is strictly larger than the
manager's salary number.
5. SELECT then prints both names and both salaries for every such pair.

Question 6 [Concept: GROUP BY + COUNT]

Count how many employees work in each department.
 SELECT d.dept_name, COUNT(e.emp_id) AS employee_count
FROM departments d
LEFT JOIN employees e ON d.dept_id = e.dept_id
GROUP BY d.dept_name;

Execution Flow:
1. departments LEFT JOIN employees builds one row per (department, employee) pair, keeping departments with no
employees as a single NULL-padded row.
2. GROUP BY d.dept_name then collects all the joined rows that share the same dept_name into one bucket per
department.
3. Inside each bucket, COUNT(e.emp_id) counts how many non-NULL emp_id values are present -- since COUNT() on a
specific column ignores NULLs, a department with no employees correctly counts as 0.
4. MySQL produces one output row per department, showing dept_name and the count for that bucket.

Question 7 [Concept: GROUP BY + HAVING]

List only the departments that have more than 3 employees.

SELECT d.dept_name, COUNT(e.emp_id) AS employee_count

FROM departments d
JOIN employees e ON d.dept_id = e.dept_id
GROUP BY d.dept_name
HAVING COUNT(e.emp_id) > 3;

Execution Flow:
1. departments is joined to employees, producing one row per employee together with their department name.
2. GROUP BY d.dept_name groups these rows into one bucket per department, and COUNT(e.emp_id) is computed for
every bucket, giving each department's headcount.
3. HAVING COUNT(e.emp_id) > 3 runs AFTER the grouping and aggregation are done, so it can test the already-
computed count -- something WHERE could not do, since WHERE only sees individual rows, not aggregated group
results.
4. Only the department buckets whose count passes the HAVING test are kept.
5. SELECT then returns dept_name and employee_count for just those surviving departments.

Question 8 [Concept: GROUP BY + HAVING on AVG]

Find the departments whose average employee salary is greater than 60000.

SELECT d.dept_name, AVG([Link]) AS avg_salary

FROM departments d
JOIN employees e ON d.dept_id = e.dept_id
GROUP BY d.dept_name
HAVING AVG([Link]) > 60000;

Execution Flow:
1. The join attaches each employee row to its department's name.
2. GROUP BY d.dept_name gathers every employee belonging to the same department into one group.
3. For each group, AVG([Link]) adds up all the salaries in that group and divides by the number of employees in that
group, giving one average salary value per department.
4. HAVING AVG([Link]) > 60000 then discards any department group whose computed average does not exceed
60000.
5. The final SELECT outputs dept_name and avg_salary only for the departments that survived the HAVING filter.
Question 9 [Concept: Subquery for Nth value]
Find the employee with the second highest salary in the company, using a subquery (no LIMIT).

SELECT emp_name, salary

FROM employees
WHERE salary = (
SELECT MAX(salary)
FROM employees
WHERE salary < (SELECT MAX(salary) FROM employees)
);

Execution Flow:
1. MySQL evaluates the innermost subquery first: SELECT MAX(salary) FROM employees scans the whole table and
returns the single highest salary value in the company.
2. The middle subquery then reruns: SELECT MAX(salary) FROM employees WHERE salary < (that highest value) -- this
scans employees again, throws away the row(s) with the very top salary, and finds the maximum of what's left, which is
the second-highest distinct salary.
3. That second-highest value is now a single number, so the outer WHERE salary = (...) can compare it directly against
every employee's salary.
4. The outer SELECT then scans employees one more time and returns the name and salary of any employee whose
salary equals that second-highest number (there could be ties).

Question 10 [Concept: ORDER BY + LIMIT/OFFSET]

Find the employee with the third highest salary using LIMIT and OFFSET.

SELECT emp_name, salary

FROM employees
ORDER BY salary DESC
LIMIT 1 OFFSET 2;

Execution Flow:
1. MySQL first reads all rows from employees.
2. ORDER BY salary DESC sorts every row from the highest salary down to the lowest.
3. OFFSET 2 then skips the first 2 rows of that sorted list -- i.e. it skips the 1st and 2nd highest salaries.
4. LIMIT 1 takes just 1 row starting right after the skipped rows, which is exactly the 3rd row in the sorted order.
5. The result is the single employee sitting at the 3rd highest salary position. Note this approach does not automatically
handle salary ties the same way DENSE_RANK would, since it counts rows, not distinct salary values.

Question 11 [Concept: Window Function DENSE_RANK]

Find the employee(s) with the Nth (say 2nd) highest DISTINCT salary, correctly handling ties, using a window
function.

SELECT emp_name, salary

FROM (
SELECT emp_name, salary,
DENSE_RANK() OVER (ORDER BY salary DESC) AS salary_rank
FROM employees
) ranked
WHERE salary_rank = 2;

Execution Flow:
 1. The inner query reads every row of employees and computes a window function on top of it: DENSE_RANK() OVER
(ORDER BY salary DESC).
2. Conceptually MySQL sorts the rows by salary descending, then walks down the sorted list assigning rank 1 to the top
salary; every subsequent row gets the same rank as the previous row if its salary is equal, and the next integer (with no
gap) if its salary is strictly lower.
3. This means two employees tied for the highest salary both get salary_rank = 1, and the next distinct (lower) salary
value gets salary_rank = 2, not 3 -- that is the key difference from ROW_NUMBER or plain OFFSET.
4. The inner query's result, now carrying a salary_rank column, is wrapped as a derived table named ranked.
5. The outer query then simply filters WHERE salary_rank = 2 and returns emp_name and salary for everyone sitting at
that second distinct salary level, even if more than one employee shares it.

Question 12 [Concept: Subquery vs company average]

List employees who earn more than the average salary of the entire company.

SELECT emp_name, salary

FROM employees
WHERE salary > (SELECT AVG(salary) FROM employees);

Execution Flow:
1. The subquery SELECT AVG(salary) FROM employees is non-correlated: it does not reference anything from the outer
query, so MySQL can compute it once, independently, before looking at the outer query row by row.
2. That single AVG value becomes a constant number substituted into the outer WHERE clause.
3. MySQL then scans the employees table row by row and keeps only the rows whose salary is greater than that fixed
average number.
4. SELECT returns emp_name and salary for every employee who passed that comparison.

Question 13 [Concept: Correlated Subquery]

List employees who earn more than the average salary of their OWN department.

SELECT e1.emp_name, [Link], e1.dept_id

FROM employees e1
WHERE [Link] > (
SELECT AVG([Link])
FROM employees e2
WHERE e2.dept_id = e1.dept_id
);

Execution Flow:
1. This subquery is correlated because it references e1.dept_id, a column from the outer query, inside its own WHERE
clause.
2. Conceptually, MySQL goes through employees one outer row (e1) at a time.
3. For every single outer row, it (logically) re-runs the inner query: scan employees as e2, keep only the e2 rows whose
dept_id matches this particular e1's dept_id, and average their salaries -- this gives the average salary of just that
employee's own department.
4. That department average is then compared against [Link] for that same outer row.
5. If [Link] is bigger than their own department's average, the outer row is kept; this repeats independently for every
employee, so two employees in different departments are compared against two different averages.

Question 14 [Concept: GROUP BY + HAVING for duplicates]

Find employee names that appear more than once in the employees table (possible duplicate entries).

SELECT emp_name, COUNT(*) AS occurrences

FROM employees
GROUP BY emp_name
HAVING COUNT(*) > 1;

Execution Flow:
1. MySQL scans every row of employees.
2. GROUP BY emp_name groups all rows sharing the exact same emp_name value into a single bucket, so if 'John Smith'
appears 3 times, all 3 rows become one bucket.
3. COUNT(*) is computed per bucket, counting every row in that bucket regardless of NULLs.
4. HAVING COUNT(*) > 1 keeps only the buckets (names) that had more than one row behind them.
5. SELECT then outputs each duplicated name along with exactly how many times it occurred.

Question 15 [Concept: LEFT JOIN + IS NULL (anti-join)]

Find customers who have never placed an order.

SELECT c.customer_name
FROM customers c
LEFT JOIN orders o ON c.customer_id = o.customer_id
WHERE o.order_id IS NULL;

Execution Flow:
1. customers is the left table, so LEFT JOIN guarantees every customer appears in the joined result at least once.
2. For each customer, MySQL looks for orders rows with a matching customer_id.
3. A customer who placed orders produces one joined row per order, with o.order_id filled in.
4. A customer who never placed any order produces exactly one joined row where every orders-side column, including
o.order_id, comes back NULL, because there was nothing to match.
5. WHERE o.order_id IS NULL keeps only those NULL-padded rows -- customers for whom absolutely no matching order
was found.
6. SELECT returns just the customer_name for these order-less customers. This pattern is often called a LEFT JOIN anti-
join.

Question 16 [Concept: LEFT JOIN across order_items]

Find products that have never appeared in any order.

SELECT p.product_name
FROM products p
LEFT JOIN order_items oi ON p.product_id = oi.product_id
WHERE oi.order_item_id IS NULL;

Execution Flow:
1. products is the left table, so every product is preserved in the output regardless of whether it was ever sold.
2. For each product, MySQL searches order_items for rows whose product_id matches.
3. If the product was sold, at least one joined row appears with oi.order_item_id filled in.
4. If the product was never sold, one NULL-padded row is produced for it, with oi.order_item_id being NULL.
5. WHERE oi.order_item_id IS NULL isolates exactly those never-sold products.
6. SELECT then lists their product_name.
Question 17 [Concept: JOIN + Aggregate expression]
Show the total monetary value of each order (quantity * unit_price summed across all items in that order).

SELECT o.order_id, SUM([Link] * oi.unit_price) AS order_total

FROM orders o
JOIN order_items oi ON o.order_id = oi.order_id
GROUP BY o.order_id;

Execution Flow:
1. orders is joined to order_items on order_id, so each row of the joined result represents one line item, tagged with the
order it belongs to.
2. Before any grouping, MySQL evaluates [Link] * oi.unit_price for every individual line item row, producing that
item's line total.
3. GROUP BY o.order_id then collects all line-item rows that belong to the same order into one bucket per order.
4. SUM() adds up all the per-line totals inside each bucket, giving the full monetary value of that order.
5. The result is one row per order_id with its computed order_total.

Question 18 [Concept: JOIN + GROUP BY + ORDER BY + LIMIT]

Find the top 5 customers by total amount spent.

SELECT c.customer_name, SUM([Link] * oi.unit_price) AS total_spent

FROM customers c
JOIN orders o ON c.customer_id = o.customer_id
JOIN order_items oi ON o.order_id = oi.order_id
GROUP BY c.customer_name
ORDER BY total_spent DESC
LIMIT 5;

Execution Flow:
1. customers is joined to orders on customer_id, and that result is joined again to order_items on order_id, so each final
row is one line item tagged with which customer bought it.
2. quantity * unit_price is computed for each line item, exactly as before.
3. GROUP BY c.customer_name buckets all line items belonging to the same customer together, across all of that
customer's orders.
4. SUM() adds up every line item's value inside each customer's bucket, producing that customer's grand total spend.
5. ORDER BY total_spent DESC sorts the resulting customer totals from highest spender to lowest.
6. LIMIT 5 keeps only the first 5 rows of that sorted list -- the 5 biggest spenders.

Question 19 [Concept: Date functions + GROUP BY]

Show the total sales value for each calendar month.

SELECT DATE_FORMAT(o.order_date, '%Y-%m') AS sales_month,

SUM([Link] * oi.unit_price) AS monthly_total
FROM orders o
JOIN order_items oi ON o.order_id = oi.order_id
GROUP BY DATE_FORMAT(o.order_date, '%Y-%m')
ORDER BY sales_month;

Execution Flow:
1. orders is joined to order_items so each line item is tagged with the order_date of the order it belongs to.
 2. DATE_FORMAT(o.order_date, '%Y-%m') converts each full date, such as 2024-03-17, into just its year-month label,
2024-03, for every line item row.
3. GROUP BY groups all line items that produced the same year-month label into one bucket, effectively one bucket per
calendar month.
4. SUM(quantity * unit_price) totals up the value of every line item inside each month's bucket.
5. ORDER BY sales_month then arranges the resulting monthly totals chronologically.

Question 20 [Concept: Window Function RANK PARTITION BY]

Rank employees by salary within their own department (highest salary = rank 1 in each department).

SELECT emp_name, dept_id, salary,

RANK() OVER (PARTITION BY dept_id ORDER BY salary DESC) AS
dept_salary_rank
FROM employees;

Execution Flow:
1. MySQL reads every row of employees, keeping dept_id and salary available for each one.
2. PARTITION BY dept_id splits all the rows into separate groups, one group per distinct dept_id -- this is like a GROUP
BY, except the individual rows are NOT collapsed; every employee row still appears in the final output.
3. Within each department's partition, ORDER BY salary DESC arranges just that department's employees from highest to
lowest salary.
4. RANK() then walks down each partition's sorted rows and assigns 1 to the top salary in that department, continuing
with the next integer for each subsequent distinct salary, and repeating the same rank for tied salaries (leaving a gap
afterwards, e.g. 1,2,2,4).
5. The result is every employee row, unchanged, with an extra dept_salary_rank column showing their standing inside
their own department only.

Question 21 [Concept: Window Function running total]

For each customer, show a running (cumulative) total of their order values over time.

SELECT c.customer_name, o.order_id, o.order_date,

SUM([Link] * oi.unit_price) AS order_total,
SUM(SUM([Link] * oi.unit_price)) OVER (
PARTITION BY c.customer_id ORDER BY o.order_date
) AS running_total
FROM customers c
JOIN orders o ON c.customer_id = o.customer_id
JOIN order_items oi ON o.order_id = oi.order_id
GROUP BY c.customer_id, c.customer_name, o.order_id, o.order_date
ORDER BY c.customer_name, o.order_date;

Execution Flow:
1. The three tables are joined so every line item is tagged with its customer and its order's date.
2. GROUP BY c.customer_id, o.order_id (plus the other selected columns) collapses the line items down to one row per
order, and the inner SUM([Link] * oi.unit_price) computes that single order's total value -- this is the normal
aggregate step, executed first.
3. Once one row per order with its order_total exists, the window function takes over: SUM(...) OVER (PARTITION BY
c.customer_id ORDER BY o.order_date) looks at the order_total values.
4. PARTITION BY c.customer_id restricts the running calculation to only that customer's own orders.
 5. ORDER BY o.order_date inside OVER() tells MySQL to walk through that customer's orders from earliest to latest, and
for each order add its order_total to the sum of every order_total that came before it in that customer's timeline,
producing a cumulative running_total.
6. Unlike the inner GROUP BY, this window step does not collapse rows further -- one row per order is still shown, now
carrying both its own total and the running total up to that point.

Question 22 [Concept: JOIN + GROUP BY + HAVING (many-to-many)]

Find employees who are working on more than one project.

SELECT e.emp_name, COUNT(ep.project_id) AS project_count

FROM employees e
JOIN employee_projects ep ON e.emp_id = ep.emp_id
GROUP BY e.emp_name
HAVING COUNT(ep.project_id) > 1;

Execution Flow:
1. employees is joined to the bridge table employee_projects on emp_id, so an employee assigned to 3 projects now
appears as 3 separate joined rows, one per project assignment.
2. GROUP BY e.emp_name collects all the assignment rows belonging to the same employee into a single bucket.
3. COUNT(ep.project_id) counts how many project assignment rows are in that employee's bucket -- i.e. how many
projects they're on.
4. HAVING COUNT(ep.project_id) > 1 discards any employee bucket with only 1 (or 0) project assignments.
5. SELECT returns the name and count for employees working on 2 or more projects.

Question 23 [Concept: JOIN + Aggregate comparison]

For each project, show its budget alongside the total hours worked on it by all employees combined.

SELECT pr.project_name, [Link], SUM(ep.hours_worked) AS total_hours

FROM projects pr
JOIN employee_projects ep ON pr.project_id = ep.project_id
GROUP BY pr.project_name, [Link];

Execution Flow:
1. projects is joined to employee_projects on project_id, producing one row per (project, employee-assigned-to-it)
combination, each carrying that assignment's hours_worked.
2. GROUP BY pr.project_name, [Link] groups all the assignment rows that belong to the same project into one
bucket -- budget is included in the GROUP BY because it is a single fixed value per project, not itself an aggregate.
3. SUM(ep.hours_worked) adds up the hours_worked figures from every employee assignment inside that project's
bucket, giving the project's combined effort in hours.
4. The result is one row per project showing its name, its fixed budget, and the summed total_hours contributed by
everyone who worked on it.

Question 24 [Concept: Subquery on same table]

Find all customers who live in the same city as the customer named 'Alice Johnson'.

SELECT customer_name, city

FROM customers
WHERE city = (
SELECT city FROM customers WHERE customer_name = 'Alice Johnson'
)
 AND customer_name != 'Alice Johnson';

Execution Flow:
1. The subquery SELECT city FROM customers WHERE customer_name = 'Alice Johnson' runs first and independently,
scanning the customers table to find the single city value tied to that specific name.
2. That city value is substituted into the outer query as a fixed constant.
3. The outer query then scans the whole customers table and keeps rows whose city column matches that constant.
4. AND customer_name != 'Alice Johnson' additionally excludes Alice Johnson's own row from the result, since we only
want other customers who share her city.
5. SELECT returns customer_name and city for everyone who passed both conditions.

Question 25 [Concept: CASE WHEN]

Label each employee's salary as 'Low' (under 40000), 'Medium' (40000 to 80000), or 'High' (above 80000).

SELECT emp_name, salary,

CASE
WHEN salary < 40000 THEN 'Low'
WHEN salary BETWEEN 40000 AND 80000 THEN 'Medium'
ELSE 'High'
END AS salary_band
FROM employees;

Execution Flow:
1. MySQL scans employees row by row.
2. For every row, the CASE expression is evaluated top to bottom like an if/else-if chain: it first checks WHEN salary <
40000; if that is true, salary_band becomes 'Low' and the rest of the CASE is skipped for this row.
3. If the first condition was false, it checks the next WHEN, salary BETWEEN 40000 AND 80000 (inclusive on both ends); if
true, salary_band becomes 'Medium'.
4. If neither WHEN condition matched, the ELSE branch applies, so salary_band becomes 'High'.
5. SELECT then returns emp_name, salary, and this newly computed salary_band label for every employee,
independently for each row.

Question 26 [Concept: UNION]

Produce a single distinct list of every city that either an employee's department is located in, or that a
customer lives in.

SELECT location AS city FROM departments

UNION
SELECT city FROM customers;

Execution Flow:
1. MySQL first runs SELECT location AS city FROM departments, producing a list of department locations.
2. It then runs SELECT city FROM customers, producing a list of customer cities.
3. Because both SELECTs return exactly one column with a compatible type, UNION can stack the second result set
directly beneath the first one.
4. UNION (as opposed to UNION ALL) then removes duplicate values from the combined stack, so a city that appears in
both lists, or multiple times within one list, shows up only once in the final output.
5. The result is one clean, deduplicated column of city names.
Question 27 [Concept: Subquery with AND condition]
Find products whose price is above the average price of ALL products, and which are still in stock.

SELECT product_name, price, stock_quantity

FROM products
WHERE price > (SELECT AVG(price) FROM products)
AND stock_quantity > 0;

Execution Flow:
1. The subquery SELECT AVG(price) FROM products is independent of the outer query, so MySQL computes it once: it
scans every row of products and returns a single average price number.
2. That number is substituted as a constant into the outer WHERE clause.
3. MySQL then scans the products table row by row, checking two conditions on each row: price > (the average
constant), and stock_quantity > 0.
4. Only rows that satisfy BOTH conditions at once (AND requires both to be true) are kept.
5. SELECT returns product_name, price and stock_quantity for each product that passed both checks.

Question 28 [Concept: CTE (WITH clause)]

Using a CTE, find the department with the highest total salary payout.

WITH dept_totals AS (
SELECT d.dept_name, SUM([Link]) AS total_salary
FROM departments d
JOIN employees e ON d.dept_id = e.dept_id
GROUP BY d.dept_name
)
SELECT dept_name, total_salary
FROM dept_totals
ORDER BY total_salary DESC
LIMIT 1;

Execution Flow:
1. MySQL first fully evaluates the CTE block named dept_totals: it joins departments to employees, groups the joined
rows by dept_name, and computes SUM([Link]) for each department -- producing one row per department with its
total payroll.
2. This named result, dept_totals, now behaves like a temporary table that only exists for the rest of this statement.
3. The outer query then simply selects dept_name and total_salary from dept_totals.
4. ORDER BY total_salary DESC sorts these department totals from largest payroll to smallest.
5. LIMIT 1 keeps only the very first row of that sorted list -- the single department with the highest total salary payout.
The CTE's only purpose here is readability: the same result could be written as a subquery in FROM, but naming it as
dept_totals makes the two logical steps (aggregate, then pick the max) easy to follow.

Question 29 [Concept: EXISTS]

Find departments that have at least one project with a budget greater than 100000.

SELECT d.dept_name
FROM departments d
WHERE EXISTS (
SELECT 1
FROM projects p
 WHERE p.dept_id = d.dept_id
AND [Link] > 100000
);

Execution Flow:
1. MySQL scans departments one row at a time.
2. For each department row, it runs the correlated subquery: look inside projects for any row where p.dept_id matches
this department's dept_id AND [Link] is greater than 100000.
3. EXISTS does not care what SELECT 1 actually returns -- it only asks 'did the subquery find at least one matching row,
yes or no?'. As soon as one qualifying project row is found for this department, MySQL can stop searching and mark the
answer as true.
4. If the answer is true, this department row is kept in the outer result; if no such project exists for that department, the
row is dropped.
5. This repeats independently for every department, and SELECT returns dept_name for every department that passed
the EXISTS test.

Question 30 [Concept: LAG window function]

For each customer, compare each order's total value with that same customer's previous order value.

WITH order_totals AS (
SELECT o.customer_id, o.order_id, o.order_date,
SUM([Link] * oi.unit_price) AS order_total
FROM orders o
JOIN order_items oi ON o.order_id = oi.order_id
GROUP BY o.customer_id, o.order_id, o.order_date
)
SELECT customer_id, order_id, order_date, order_total,
LAG(order_total) OVER (
PARTITION BY customer_id ORDER BY order_date
) AS previous_order_total
FROM order_totals;

Execution Flow:
1. The CTE order_totals is evaluated first: orders is joined to order_items, grouped by customer_id, order_id and
order_date, and SUM(quantity * unit_price) computes each individual order's total value -- so this step produces one
row per order with its own order_total.
2. The outer query reads from this order_totals result as if it were a normal table.
3. LAG(order_total) OVER (PARTITION BY customer_id ORDER BY order_date) is a window function: PARTITION BY
customer_id groups the rows so the calculation only looks within each customer's own orders, never mixing in another
customer's data.
4. ORDER BY order_date inside OVER() arranges each customer's orders from earliest to latest.
5. LAG(order_total) then, for each order row, reaches back to the immediately preceding row in that customer's ordered
sequence and pulls its order_total value forward into the current row as previous_order_total; for a customer's very first
order, there is no preceding row, so previous_order_total comes back as NULL.
6. The final output shows every order alongside its own total and the total of that same customer's order right before it,
letting you compare order-over-order changes.