SQL interview questions

Why not just use files?

The usual warm-up, "SQL vs MySQL": SQL is a language, standardised by ANSI and ISO. MySQL is one product, an RDBMS, that implements SQL along with its own extensions, such as LIMIT and AUTO_INCREMENT.

Follow-ups

• What is a schema? The structure of the database: its tables, columns, types, keys and constraints, as opposed to the data in them.
• Is MongoDB a DBMS? Yes, a document-oriented DBMS. It isn't relational: it stores JSON-like documents and doesn't enforce joins and foreign keys the same way.

-- DDL: change the structure
ALTER TABLE employees ADD COLUMN phone VARCHAR(15);

-- DML: change the data
UPDATE employees SET salary = salary * 1.10 WHERE dept_id = 2;

-- DCL: change who may do what
GRANT SELECT ON company.orders TO 'report_user'@'%';

-- TCL: make changes permanent, or undo them
START TRANSACTION;
DELETE FROM orders WHERE status = 'cancelled';
ROLLBACK;                                          -- the cancelled orders are back

Why the grouping matters: in MySQL, DDL statements commit implicitly, so they end any open transaction and can't be rolled back. DML inside a transaction can be. That's the root of the DELETE vs TRUNCATE difference (question 8).

Follow-ups

• Is TRUNCATE DDL or DML? DDL: it deallocates and recreates the table's storage rather than deleting rows one by one.
• What does GRANT ALL do? Gives every privilege on the named objects. In production, grant only what each application user needs.

Mapping each relationship to tables

-- The many-to-many case: a junction table
CREATE TABLE projects (
  project_id INT AUTO_INCREMENT PRIMARY KEY,
  name       VARCHAR(100) NOT NULL
);

CREATE TABLE employee_projects (
  emp_id     INT NOT NULL,
  project_id INT NOT NULL,
  role       VARCHAR(50),                         -- attributes of the relationship itself live here
  PRIMARY KEY (emp_id, project_id),                -- a composite key: one row per pairing
  FOREIGN KEY (emp_id) REFERENCES employees (emp_id),
  FOREIGN KEY (project_id) REFERENCES projects (project_id)
);

ER notation in brief: rectangles for entities, ovals for attributes (underlined for keys), diamonds for relationships, and 1, N or M on the lines for cardinality. Crow's-foot notation draws the "many" end as a three-pronged foot.

Follow-ups

• What is a weak entity? One that can't be identified by its own attributes alone, such as an order line identified only together with its order. It takes part of its key from its owner.
• What are composite, multivalued and derived attributes? Composite: made of parts (an address of street, city, PIN). Multivalued: several values (phone numbers), which become a separate table. Derived: computed from others (age from date of birth), usually not stored.

INSERT INTO employees (emp_name, email, salary, hire_date)
VALUES ('Test User', '[email protected]', 50000, '2025-01-01');
-- ERROR 1062 (23000): Duplicate entry '[email protected]' for key 'employees.email'

NULLs in unique columns differ between databases: MySQL and PostgreSQL allow many NULLs in a unique column; SQL Server allows only one, unless you use a filtered index.

Follow-ups

• Can a primary key span several columns? Yes, a composite primary key, such as (emp_id, project_id) in a junction table (question 6).
• Natural or surrogate key? A natural key comes from the data (an email); a surrogate key is invented (an auto-increment ID). Surrogates are usually preferred as primary keys, because natural values can change.

CREATE TABLE employees_demo (
  emp_id   INT PRIMARY KEY,
  emp_name VARCHAR(100) NOT NULL,
  dept_id  INT NULL,
  FOREIGN KEY (dept_id) REFERENCES departments (dept_id)
    ON DELETE SET NULL                    -- or RESTRICT (the default), or CASCADE
    ON UPDATE CASCADE
);

Deleting the Sales department (dept_id 2), which has three employees

Choosing a rule: CASCADE suits parts that can't exist without their parent, such as an order's line items. SET NULL suits optional links. RESTRICT is the safe default when deleting the parent would be a mistake, as with a department that still has staff.

The usual opening version, "primary key vs foreign key":

Follow-ups

• Can a foreign key reference its own table? Yes: employees.manager_id references employees.emp_id, a self-referencing key.
• Does MySQL index foreign key columns? InnoDB requires an index on them and creates one automatically if none exists.

super keys      ⊇  candidate keys  ⊇  primary key
(any unique set)   (minimal ones)      (the chosen one; the rest are alternate keys)

emp_name alone isn't a key: two employees can share a name. (emp_id, emp_name) is a super key, but not a candidate key, because emp_name is unnecessary.

Follow-ups

• Why choose emp_id over email? It's short, never changes and isn't personal data. Emails change, and every foreign key pointing at them would have to change too.
• What is a surrogate key? A key with no business meaning, generated by the database, such as AUTO_INCREMENT or a UUID.

CREATE TABLE employees (
  emp_id     INT AUTO_INCREMENT,
  emp_name   VARCHAR(100)  NOT NULL,
  email      VARCHAR(100)  NOT NULL,
  salary     DECIMAL(10,2) NOT NULL,
  hire_date  DATE          NOT NULL,
  dept_id    INT           NULL,                  -- an employee may have no department yet
  manager_id INT           NULL,                  -- the top boss has no manager
  CONSTRAINT pk_employees PRIMARY KEY (emp_id),
  CONSTRAINT uq_employees_email UNIQUE (email),
  CONSTRAINT chk_employees_salary CHECK (salary > 0),
  CONSTRAINT fk_emp_dept FOREIGN KEY (dept_id) REFERENCES departments (dept_id)
    ON DELETE SET NULL,
  CONSTRAINT fk_emp_manager FOREIGN KEY (manager_id) REFERENCES employees (emp_id)
);

What each constraint stops

Why DECIMAL for money? DECIMAL(10,2) stores exact values. FLOAT and DOUBLE are binary approximations, so sums of rupees and paise can drift by tiny amounts.

Follow-ups

• How do you add a constraint later? ALTER TABLE employees ADD CONSTRAINT chk_hire CHECK (hire_date >= '2000-01-01');
• Can a CHECK constraint refer to another table? No. Cross-table rules need a foreign key, a trigger (question 40) or application code.

START TRANSACTION;
DELETE FROM orders WHERE status = 'cancelled';   -- removes 103 and 109
ROLLBACK;                                        -- they're back

TRUNCATE TABLE orders;                           -- every order gone, and no undo in MySQL

DROP TABLE IF EXISTS orders_archive;             -- the table itself is removed

Database differences: PostgreSQL and SQL Server let you roll back TRUNCATE, and even DROP, inside a transaction. That's a common follow-up for anyone who says "TRUNCATE can never be rolled back".

Follow-ups

• Why is TRUNCATE faster? It deallocates the table's storage pages instead of deleting and logging each row.
• How do you remove all rows but keep the IDs counting up? DELETE FROM table without a WHERE: slower, but AUTO_INCREMENT continues from where it was.

Departments whose staff hired since 2018 average over ₹1 lakh

SELECT dept_id, AVG(salary) AS avg_salary
FROM employees
WHERE hire_date >= '2018-01-01'          -- row filter: before grouping
GROUP BY dept_id
HAVING AVG(salary) > 100000;             -- group filter: after grouping

HR (Meera and Sameer) averages 87,500, so HAVING drops it; Suresh's group (no department) averages 70,000. MySQL shows AVG of a DECIMAL(10,2) column with six decimal places.

The logical order a query runs in

SELECT emp_name, salary * 12 AS annual FROM employees WHERE annual > 1500000;
-- ERROR 1054 (42S22): Unknown column 'annual' in 'where clause'

Database differences: MySQL lets you use a SELECT alias in GROUP BY and HAVING, as an extension. PostgreSQL accepts aliases in GROUP BY but not in HAVING, and SQL Server accepts them in neither, so repeating the expression is the portable habit.

Follow-ups

• Can HAVING be used without GROUP BY? Yes: the whole result is then one group. SELECT COUNT(*) FROM orders HAVING COUNT(*) > 5 returns one row or none.
• Is the logical order the order the database executes in? Not necessarily. The optimiser can reorder work, but the result must be as if it ran in this order.

Every city the company touches, from customers and from department locations

SELECT city FROM customers
UNION
SELECT location FROM departments
ORDER BY city;

UNION returns 7 distinct cities; UNION ALL returns all 10 values, with Delhi three times and Pune twice. The ORDER BY applies to the combined result, and uses the first query's column name.

Related set operators: INTERSECT (rows in both) and EXCEPT (rows in the first but not the second). MySQL only supports them from 8.0.31; before that, use JOIN or NOT EXISTS instead.

Follow-ups

• UNION vs JOIN? UNION stacks rows from similar queries; JOIN combines columns from related tables side by side.
• Does UNION ALL guarantee the order of rows? No. Without ORDER BY, no SQL result has a guaranteed order.

SELECT emp_name, hire_date
FROM employees
WHERE emp_name LIKE 'S%'
ORDER BY hire_date DESC
LIMIT 5;

Only four employees match, so LIMIT 5 returns four: it's a maximum, not a guarantee.

LIKE patterns

The same in other databases

-- SQL Server
SELECT TOP 5 emp_name, hire_date FROM employees WHERE emp_name LIKE 'S%' ORDER BY hire_date DESC;

-- Standard SQL (PostgreSQL, Oracle 12c+, SQL Server)
SELECT emp_name, hire_date FROM employees WHERE emp_name LIKE 'S%'
ORDER BY hire_date DESC FETCH FIRST 5 ROWS ONLY;

Follow-ups

• Can LIKE 'S%' use an index? Yes: a fixed prefix lets the database range-scan an index on emp_name. A leading wildcard, '%S', can't (question 37).
• What if two people were hired the same day? The order between them is undefined; add a tie-breaker such as ORDER BY hire_date DESC, emp_id.

SELECT emp_name FROM employees WHERE manager_id = NULL;
-- (no rows)

SELECT emp_name FROM employees WHERE manager_id IS NULL;
-- Asha Rao

SELECT COUNT(*) FROM employees WHERE manager_id != 3;
-- 8: Arjun (manager 3) and Asha (manager NULL) are both excluded

SELECT emp_name FROM employees WHERE emp_id NOT IN (SELECT manager_id FROM employees);
-- (no rows): the list includes Asha's NULL

SELECT e.emp_name
FROM employees e
WHERE NOT EXISTS (SELECT 1 FROM employees r WHERE r.manager_id = e.emp_id);
-- Arjun Mehta, Vikram Singh, Sana Khan, Suresh Pillai, Sameer Joshi: the people who manage nobody

Three-valued logic in one table

WHERE keeps only rows where the condition is TRUE, so UNKNOWN rows silently disappear.

Including the NULLs on purpose

SELECT COUNT(*) FROM employees WHERE manager_id != 3 OR manager_id IS NULL;   -- 9

NULL vs 0 vs an empty string: NULL means "unknown or not applicable"; 0 and '' are real values. Replace NULLs for display with COALESCE(manager_id, 0) (standard) or MySQL's IFNULL. Beware: ISNULL(x) tests for NULL in MySQL (returning 1 or 0) but replaces NULL in SQL Server.

Follow-ups

• Is NOT IN ever safe? Yes, when the subquery's column can't contain NULL, for example a NOT NULL column, or with WHERE manager_id IS NOT NULL added inside the subquery.
• How do aggregates treat NULL? They skip it: COUNT(manager_id) is 9, not 10 (question 19).

Employees and departments, joined on dept_id

SELECT e.emp_name, d.dept_name
FROM employees e
LEFT JOIN departments d ON d.dept_id = e.dept_id;     -- every employee, Suresh with dept_name NULL

FULL OUTER JOIN in MySQL

SELECT e.emp_name, d.dept_name
FROM employees e LEFT JOIN departments d ON d.dept_id = e.dept_id
UNION ALL
SELECT e.emp_name, d.dept_name
FROM employees e RIGHT JOIN departments d ON d.dept_id = e.dept_id
WHERE e.emp_id IS NULL;                                 -- only the right-side rows with no match

That returns the same 11 rows a real FULL OUTER JOIN would. Many tutorials use UNION instead of UNION ALL without the WHERE, which works only when the rows contain no duplicates (question 16 shows how it breaks).

INNER:  [ A ∩ B ]        LEFT:  [ A ] + matches        RIGHT: matches + [ B ]
FULL:   [ A ∪ B ]        CROSS: every A paired with every B

Follow-ups

• What is a self join? A table joined to itself, with two aliases, such as employees and their managers (question 14).
• What is a NATURAL JOIN? A join on every column with the same name in both tables. Avoid it: adding a column later can silently change the join.
• Does the order of tables matter? For INNER and CROSS joins, no; for LEFT and RIGHT, yes: it decides which side keeps all its rows.

SELECT e.emp_name, e.salary,
       m.emp_name AS manager_name, m.salary AS manager_salary
FROM employees e
JOIN employees m ON e.manager_id = m.emp_id     -- m is the manager's row
WHERE e.salary > m.salary;

Arjun earns exactly what his manager Priya earns, so the strict > leaves him out.

The warm-up: everyone with their manager

SELECT e.emp_name, m.emp_name AS manager
FROM employees e
LEFT JOIN employees m ON e.manager_id = m.emp_id
ORDER BY e.emp_id;

With an INNER JOIN, Asha would vanish, because she has no manager row to match.

What is a self join? Not a special keyword: an ordinary join where both sides are the same table, distinguished by aliases. It's how you query any hierarchy stored in one table: employees and managers, categories and parent categories, replies and the comments they reply to.

Follow-ups

• How would you count each manager's direct reports? SELECT m.emp_name, COUNT(*) FROM employees e JOIN employees m ON e.manager_id = m.emp_id GROUP BY m.emp_id, m.emp_name;
• How do you get everyone below a manager, at every level? A recursive CTE (question 26).

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

-- NOT EXISTS: often the clearest, and NULL-safe
SELECT c.customer_id, c.customer_name
FROM customers c
WHERE NOT EXISTS (SELECT 1 FROM orders o WHERE o.customer_id = c.customer_id);

Why check o.order_id, a primary key, for NULL? A primary key is never NULL in a real row, so a NULL there can only mean "no matching row". Checking a nullable column such as o.emp_id would also catch real orders that happen to have no sales rep.

Follow-ups

• Customers whose last order was over 90 days ago? Group orders by customer, take MAX(order_date), and keep those before CURDATE() - INTERVAL 90 DAY, plus the never-ordered ones from this query.
• Which is faster, NOT EXISTS or LEFT JOIN … IS NULL? Usually the same plan in modern optimisers; check with EXPLAIN rather than guessing.

CREATE TABLE a (v INT);
CREATE TABLE b (v INT);
INSERT INTO a VALUES (1), (1), (2), (NULL);
INSERT INTO b VALUES (1), (1), (3), (NULL);

SELECT COUNT(*) FROM a JOIN b ON a.v = b.v;          -- 4
SELECT COUNT(*) FROM a LEFT JOIN b ON a.v = b.v;     -- 6
SELECT COUNT(*) FROM a RIGHT JOIN b ON a.v = b.v;    -- 6
SELECT COUNT(*) FROM a CROSS JOIN b;                 -- 16

The MySQL FULL JOIN trap. The common workaround, LEFT JOIN … UNION … RIGHT JOIN, returns only 4 rows here, not 8. UNION removes duplicates, so the four identical (1, 1) rows collapse into one, and the two (NULL, NULL) rows into one. The safe version uses UNION ALL and keeps only the unmatched rows from the second half (question 13).

Follow-ups

• How would you join NULLs to NULLs on purpose? MySQL's null-safe equality operator <=> treats two NULLs as equal; the standard spelling is IS NOT DISTINCT FROM.
• Why does this matter in practice? Joining on a non-unique column multiplies rows, which silently inflates sums and counts. Check row counts before and after a join.

-- Condition in ON: still a real LEFT JOIN
SELECT c.customer_name, o.order_id
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.customer_id AND o.status = 'delivered';
-- 11 rows: Rahul V, Fatima and Deepak appear with order_id NULL

-- Condition in WHERE: the LEFT JOIN collapses into an inner join
SELECT c.customer_name, o.order_id
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.customer_id
WHERE o.status = 'delivered';
-- 8 rows: only Kiran, Lakshmi and Rahul Verma

The rule: for an outer join, conditions on the optional (right-hand) table belong in ON if you want to keep the unmatched rows. Conditions on the preserved (left) table, such as c.city = 'Delhi', belong in WHERE.

Follow-ups

• Does it matter for an INNER JOIN? No: for inner joins, ON and WHERE conditions give the same result.
• How would you count delivered orders per customer, zeros included? Put the status test in ON and count o.order_id: COUNT(o.order_id) gives 0 for customers with no match.

SELECT dept_id, COUNT(*) AS staff, MAX(salary) AS top_salary
FROM employees
GROUP BY dept_id;

NULL forms its own group: Suresh, who has no department.

The error

SELECT dept_id, emp_name, MAX(salary)
FROM employees
GROUP BY dept_id;
-- ERROR 1055 (42000): Expression #2 of SELECT list is not in GROUP BY clause and contains
-- nonaggregated column 'company.employees.emp_name' which is not functionally dependent on
-- columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by

Older MySQL versions (before 5.7) accepted this and returned an arbitrary emp_name from each group, often not the person with the top salary, which is why some old tutorials show it "working".

The fix depends on what you meant: to show the top earner's name, join back to the maximum or use a window function (questions 21 and 29); to show any one name, say so explicitly with ANY_VALUE(emp_name).

Follow-ups

• Can you group by several columns? Yes: GROUP BY dept_id, YEAR(hire_date) gives one row per department per hiring year.
• What does "functionally dependent" mean in that error? If you group by a primary key, every other column of that row has one value per group, so MySQL allows selecting them.

SELECT COUNT(*), COUNT(manager_id), COUNT(DISTINCT dept_id)
FROM employees;

AVG skips NULLs too, which changes the answer

If a bonus column held 100, 200 and NULL, AVG(bonus) is 150 (300 ÷ 2), not 100 (300 ÷ 3). If NULL should count as zero, say so: AVG(COALESCE(bonus, 0)).

Follow-ups

• Why does COUNT(*) give 1 for an empty department in a LEFT JOIN? Because the NULL-extended row is still a row; count a column from the right-hand table instead (question 22).
• What does SUM return for a group with only NULLs, or no rows? NULL, not 0; wrap it in COALESCE(SUM(x), 0) when you need a number.

SELECT email, COUNT(*) AS times
FROM customers
GROUP BY email
HAVING COUNT(*) > 1;

Showing the duplicate customers themselves

SELECT c.customer_id, c.customer_name, c.email, c.signup_date
FROM customers c
JOIN (
  SELECT email FROM customers GROUP BY email HAVING COUNT(*) > 1
) dup ON dup.email = c.email
ORDER BY c.email, c.signup_date;

Watch out for: case and spaces. [email protected] and [email protected] are different strings; group by LOWER(TRIM(email)) to catch them. (MySQL 8's default collation already ignores case, but it treats trailing spaces as significant, so normalising is the portable habit.)

Follow-ups

• How would you delete the duplicates? Keep one row per email and delete the rest, as question 27 does for orders. For customers, merge their orders first.
• Duplicates across several columns? Group by all of them: GROUP BY customer_id, order_date, amount HAVING COUNT(*) > 1.

SELECT d.dept_name, MAX(e.salary) AS highest_salary
FROM employees e
JOIN departments d ON d.dept_id = e.dept_id
GROUP BY d.dept_id, d.dept_name
ORDER BY highest_salary DESC;

The follow-up: who earns it?

Adding e.emp_name to the query above fails (question 18). Join back to the per-department maximum instead:

SELECT d.dept_name, e.emp_name, e.salary
FROM employees e
JOIN departments d ON d.dept_id = e.dept_id
JOIN (
  SELECT dept_id, MAX(salary) AS max_salary
  FROM employees
  GROUP BY dept_id
) top ON top.dept_id = e.dept_id AND top.max_salary = e.salary;

If two people tie for the top salary, both appear, which is usually what's wanted. The window-function version is in question 29.

Follow-ups

• Highest salary per department, but only departments with at least three people? Add HAVING COUNT(*) >= 3.
• And the lowest, average and total, too? Add MIN(e.salary), ROUND(AVG(e.salary), 2) and SUM(e.salary) to the same SELECT.

SELECT d.dept_name, COUNT(e.emp_id) AS employees
FROM departments d
LEFT JOIN employees e ON e.dept_id = d.dept_id
GROUP BY d.dept_id, d.dept_name
ORDER BY employees DESC;

With COUNT(*), Finance would show 1: the LEFT JOIN creates one row for it, with every employee column NULL, and COUNT(*) counts rows.

The common variant: departments with more than three people

SELECT d.dept_name, COUNT(e.emp_id) AS employees
FROM departments d
LEFT JOIN employees e ON e.dept_id = d.dept_id
GROUP BY d.dept_id, d.dept_name
HAVING COUNT(e.emp_id) > 3;          -- Engineering, with 4

Follow-ups

• What about the employee with no department? This query starts from departments, so Suresh doesn't appear. Counting "unassigned" staff needs a separate query, or a FULL OUTER JOIN.
• How is this the same as the pandas question? It's the SQL form of a left merge followed by counting non-null values (ML kit, question 28).

SELECT c.customer_name,
       SUM(CASE WHEN o.status = 'delivered' THEN 1 ELSE 0 END) AS delivered,
       SUM(CASE WHEN o.status = 'cancelled' THEN 1 ELSE 0 END) AS cancelled
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.customer_id
GROUP BY c.customer_id, c.customer_name
ORDER BY c.customer_id;

For customers without orders, o.status is NULL, so every CASE falls through to ELSE 0.

What CASE does, the usual fresher warm-up: it returns a value based on conditions, like if-else inside a query:

SELECT emp_name, salary,
       CASE
         WHEN salary >= 150000 THEN 'High'
         WHEN salary >= 90000 THEN 'Medium'
         ELSE 'Low'
       END AS band
FROM employees;

The conditions are checked top to bottom and the first match wins, so order them from the most specific.

A MySQL shortcut that doesn't travel: in MySQL, SUM(o.status = 'delivered') works, because a comparison evaluates to 1 or 0. PostgreSQL and SQL Server reject it. COUNT(CASE WHEN o.status = 'delivered' THEN 1 END) is another portable form, since COUNT skips the NULLs from unmatched CASEs.

Follow-ups

• How would you add each customer's delivered revenue? SUM(CASE WHEN o.status = 'delivered' THEN o.amount ELSE 0 END).
• What if the statuses aren't known in advance? Static SQL needs fixed columns. Dynamic pivots need prepared statements, or are better done in the reporting tool.

-- 1. The max below the max
SELECT MAX(salary) AS second_highest
FROM employees
WHERE salary < (SELECT MAX(salary) FROM employees);

-- 2. Distinct salaries, skip the first
SELECT DISTINCT salary AS second_highest
FROM employees
ORDER BY salary DESC
LIMIT 1 OFFSET 1;

Why DISTINCT matters: without it, a tie for the top salary would make the "second" row repeat the highest value. That's also why the first method works: salary < MAX naturally skips every copy of the top salary.

The Nth highest: DENSE_RANK

SELECT DISTINCT salary
FROM (
  SELECT salary, DENSE_RANK() OVER (ORDER BY salary DESC) AS salary_rank
  FROM employees
) ranked
WHERE salary_rank = 5;          -- 120000.00: shared by Priya and Arjun

DENSE_RANK gives tied salaries the same rank and doesn't skip numbers afterwards, so "the 5th highest salary" means the 5th distinct value (question 28).

Follow-ups

• The second-highest salary in each department? DENSE_RANK() OVER (PARTITION BY dept_id ORDER BY salary DESC), then filter rank 2 (question 29).
• Why not LIMIT 1, 1? It means the same as LIMIT 1 OFFSET 1 in MySQL, but the explicit OFFSET form is clearer and more portable.

SELECT e.emp_name, e.dept_id, e.salary
FROM employees e
WHERE e.salary > (
  SELECT AVG(e2.salary)
  FROM employees e2
  WHERE e2.dept_id = e.dept_id          -- refers to the outer row: correlated
)
ORDER BY e.dept_id, e.salary DESC;

The department averages are 167,500 (Engineering), 135,000 (Sales) and 87,500 (HR). Suresh has no department: e2.dept_id = NULL matches nothing, the average is NULL, and the comparison drops him.

The same with a join to the averages

SELECT e.emp_name, e.dept_id, e.salary, ROUND(a.avg_salary) AS dept_avg
FROM employees e
JOIN (
  SELECT dept_id, AVG(salary) AS avg_salary
  FROM employees
  GROUP BY dept_id
) a ON a.dept_id = e.dept_id
WHERE e.salary > a.avg_salary;

The warm-up: above the company average, a plain (non-correlated) subquery that runs once:

SELECT emp_name, salary
FROM employees
WHERE salary > (SELECT AVG(salary) FROM employees);   -- average 132000.00: Asha, Ravi, Sana, Sneha

Follow-ups

• What's a correlated subquery, in one line? A subquery that depends on the current row of the outer query, so its result changes row by row.
• And the window-function version? AVG(salary) OVER (PARTITION BY dept_id) puts each department's average on every row without grouping, then an outer query filters.

WITH dept_stats AS (
  SELECT dept_id, AVG(salary) AS avg_salary, COUNT(*) AS staff
  FROM employees
  WHERE dept_id IS NOT NULL
  GROUP BY dept_id
),
big_depts AS (
  SELECT dept_id, avg_salary FROM dept_stats WHERE staff >= 3
)
SELECT d.dept_name, ROUND(b.avg_salary) AS avg_salary
FROM big_depts b
JOIN departments d ON d.dept_id = b.dept_id;

Recursive CTEs: everyone in the org chart, with their level

WITH RECURSIVE org AS (
  SELECT emp_id, emp_name, manager_id, 1 AS level
  FROM employees
  WHERE manager_id IS NULL                 -- the anchor: the top boss
  UNION ALL
  SELECT e.emp_id, e.emp_name, e.manager_id, o.level + 1
  FROM employees e
  JOIN org o ON e.manager_id = o.emp_id    -- the recursive step: reports of the previous level
)
SELECT emp_name, level FROM org ORDER BY level, emp_id;

Change the anchor to WHERE emp_id = 5 and the query returns Sneha and everyone under her: Vikram and Sana.

Follow-ups

• Is a CTE faster than a subquery? Usually the same: MySQL can merge simple CTEs into the main query, much like a derived table. Choose it for readability.
• What stops a recursive CTE looping forever? The recursion ends when a step returns no rows. MySQL also stops at cte_max_recursion_depth (1,000 by default), which catches cycles in bad data.

Before: orders 104 and 105 are one duplicate pair; 107, 108 and 111 are a triple.

-- 1. Self-join DELETE (MySQL)
DELETE later
FROM orders later
JOIN orders earlier
  ON  earlier.customer_id = later.customer_id
  AND earlier.order_date  = later.order_date
  AND earlier.amount      = later.amount
  AND earlier.order_id    < later.order_id;     -- "later" has an earlier twin, so it goes

SELECT order_id FROM orders ORDER BY order_id;
-- 101, 102, 103, 104, 106, 107, 109, 110: 105, 108 and 111 are gone

With ROW_NUMBER

DELETE FROM orders
WHERE order_id IN (
  SELECT order_id FROM (
    SELECT order_id,
           ROW_NUMBER() OVER (PARTITION BY customer_id, order_date, amount ORDER BY order_id) AS copy_no
    FROM orders
  ) numbered                              -- the extra derived table is what keeps MySQL happy
  WHERE copy_no > 1
);

Without the outer SELECT … FROM (…) numbered wrapper, MySQL refuses with ERROR 1093 (HY000): You can't specify target table 'orders' for update in FROM clause. The wrapper makes MySQL materialise the subquery's result first.

Before deleting anything in a real database

1. Run the SELECT version first and check exactly which rows would go.
2. Do it inside a transaction, so a mistake can be rolled back.
3. Check for child rows: delivery records or payments pointing at an order you're about to delete.
4. Prevent it recurring: a unique constraint, or an idempotency key on the API that creates orders (Backend kit, question 8).

Follow-ups

• What if "duplicate" should ignore the amount? Change the columns in the join or the PARTITION BY; that list is the definition of a duplicate, so agree it first.
• How would you keep the latest copy instead? Flip the comparison to earlier.order_id > later.order_id, or order the ROW_NUMBER by order_id DESC.

SELECT emp_name, salary,
       ROW_NUMBER() OVER (ORDER BY salary DESC) AS row_num,
       RANK()       OVER (ORDER BY salary DESC) AS rnk,
       DENSE_RANK() OVER (ORDER BY salary DESC) AS dense_rnk
FROM employees;

Priya and Arjun tie at 120,000. ROW_NUMBER splits them 5 and 6, in an order you shouldn't rely on unless you add a tie-breaker such as ORDER BY salary DESC, emp_id. RANK gives both 5 and then jumps to 7; DENSE_RANK gives both 5 and continues at 6.

Window functions vs GROUP BY

SELECT emp_name, dept_id, salary,
       SUM(salary) OVER (PARTITION BY dept_id) AS dept_total    -- every row keeps its detail
FROM employees;

Follow-ups

• What does PARTITION BY do? It restarts the numbering, or the calculation, for each group (question 29).
• Other window functions worth naming? LAG and LEAD (previous and next row), SUM and AVG over windows (running totals, question 30), NTILE (split rows into buckets), FIRST_VALUE.

WITH ranked AS (
  SELECT d.dept_name, e.emp_name, e.salary,
         DENSE_RANK() OVER (PARTITION BY e.dept_id ORDER BY e.salary DESC) AS pay_rank
  FROM employees e
  JOIN departments d ON d.dept_id = e.dept_id
)
SELECT dept_name, emp_name, salary, pay_rank
FROM ranked
WHERE pay_rank <= 3                      -- allowed here: the rank exists in the CTE's output
ORDER BY dept_name, pay_rank, emp_name;

HR has only two people, so it returns two. The inner join leaves out Suresh; without it, PARTITION BY dept_id would put him in a NULL "department" of his own, ranked 1.

Choosing the ranking function changes the answer

The common variant, "the top earner in each department": the same query with WHERE pay_rank = 1.

Follow-ups

• Why not WHERE DENSE_RANK() OVER (...) <= 3 directly? WHERE runs before window functions are computed, so MySQL rejects window functions in WHERE. Some databases add QUALIFY for exactly this; MySQL doesn't have it.
• What if the interviewer wants exactly three rows per department? Use ROW_NUMBER with a deterministic tie-breaker, such as ORDER BY salary DESC, hire_date.

SELECT DATE_FORMAT(order_date, '%Y-%m') AS month,
       SUM(amount) AS month_total,
       SUM(SUM(amount)) OVER (ORDER BY DATE_FORMAT(order_date, '%Y-%m')) AS running_total
FROM orders
WHERE status <> 'cancelled'
GROUP BY DATE_FORMAT(order_date, '%Y-%m')
ORDER BY month;

(These totals still include the duplicate orders from question 27; delete those first for true revenue.)

The same in two clear steps, with a CTE

WITH monthly AS (
  SELECT DATE_FORMAT(order_date, '%Y-%m') AS month, SUM(amount) AS month_total
  FROM orders
  WHERE status <> 'cancelled'
  GROUP BY month
)
SELECT month, month_total,
       SUM(month_total) OVER (ORDER BY month) AS running_total,
       month_total - LAG(month_total) OVER (ORDER BY month) AS change_vs_last_month
FROM monthly
ORDER BY month;

LAG returns the previous row's value, so change_vs_last_month is NULL for January, then 148.00, 5202.00 and -1400.00.

Frames, briefly: with ORDER BY and no frame, a running SUM uses RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW. A moving three-month average spells the frame out: AVG(month_total) OVER (ORDER BY month ROWS BETWEEN 2 PRECEDING AND CURRENT ROW).

Follow-ups

• What if a month has no orders at all? It won't appear. Generate the full list of months, with a calendar table or a recursive CTE, and LEFT JOIN the totals to it.
• A running total per customer? Add PARTITION BY customer_id to the window.

One wide table, three anomalies

The normalized version stores the department's facts once, in departments, and each employee just points at it with dept_id. That's exactly the shared schema on this page: Finance can exist with no employees, and moving Engineering is a one-row update.

Denormalization, the usual follow-up: deliberately adding redundancy back for faster reads, such as storing order_total on an order instead of summing its lines every time, or copying the customer's name into a reporting table. You trade write complexity and the risk of drift for fewer joins; it's common in analytics and read-heavy systems.

Follow-ups

• Does normalization make queries slower? Reads that need joins can be, slightly; writes are simpler and safer. Normalize first, then denormalize specific spots where measurements show it's needed.
• What is redundancy, precisely? The same fact stored in more than one place, which is what lets copies disagree.

Start: one unnormalized table of employee project assignments

1NF: make values atomic. projects holds a list, so split it into one row per employee per project:

The key is now (emp_id, project_id).

2NF: remove partial dependencies. emp_name, dept_id and dept_name depend on emp_id alone, which is only part of the key. Only hours depends on the whole key. Split:

• employees (emp_id, emp_name, dept_id, dept_name)
• assignments (emp_id, project_id, hours)

3NF: remove transitive dependencies. In employees, emp_id → dept_id and dept_id → dept_name, so dept_name depends on the key only through dept_id. Split again:

• employees (emp_id, emp_name, dept_id)
• departments (dept_id, dept_name)
• assignments (emp_id, project_id, hours)

Now each fact lives once: a name in employees, a department name in departments, hours in assignments.

BCNF: every determinant is a candidate key. 3NF still allows a dependency X → Y where Y is part of a key and X isn't a candidate key. Example: each topic in a training programme is taught by exactly one trainer, and each trainer teaches one topic; students attend topics. In (student, topic, trainer), the key is (student, topic), but trainer → topic and trainer isn't a candidate key. That breaks BCNF; splitting into (trainer, topic) and (student, trainer) fixes it.

A memory aid often quoted for 3NF: every non-key column depends on "the key, the whole key, and nothing but the key".

Follow-ups

• Can a table with a single-column key violate 2NF? No: partial dependency needs a composite key. It can still violate 3NF.
• Do 4NF and 5NF matter? They deal with multi-valued and join dependencies, and rarely come up at this level.

CREATE TABLE accounts (
  account_id INT PRIMARY KEY,
  holder     VARCHAR(50),
  balance    DECIMAL(10,2) NOT NULL CHECK (balance >= 0)
);
INSERT INTO accounts VALUES (1, 'Asha', 5000.00), (2, 'Ravi', 1000.00);

START TRANSACTION;
UPDATE accounts SET balance = balance - 2000 WHERE account_id = 1;   -- debit Asha
UPDATE accounts SET balance = balance + 2000 WHERE account_id = 2;   -- credit Ravi
COMMIT;                                       -- both changes become permanent together
-- Asha 3000.00, Ravi 3000.00

If the server crashed between the two updates, or the second one failed, ROLLBACK (or crash recovery) undoes the first, so money is never debited without being credited.

SAVEPOINT: undo part of a transaction

START TRANSACTION;
UPDATE accounts SET balance = balance - 500 WHERE account_id = 1;
SAVEPOINT after_fee;
UPDATE accounts SET balance = balance - 9999 WHERE account_id = 1;   -- fails the CHECK, or a mistake
ROLLBACK TO SAVEPOINT after_fee;                                     -- undo only the second update
COMMIT;                                                              -- the 500 fee stays

A MySQL-specific note: ACID applies to the InnoDB engine, MySQL 8's default. The older MyISAM engine has no transactions at all, which is the usual follow-up: "InnoDB vs MyISAM?" InnoDB adds transactions, row-level locking, foreign keys and crash recovery.

Follow-ups

• What is autocommit? MySQL's default mode: each statement outside an explicit transaction is committed on its own.
• Does every statement in MySQL obey transactions? No: DDL such as CREATE, ALTER, DROP and TRUNCATE commits implicitly (question 8).

Each problem as a timeline

SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;
SELECT @@transaction_isolation;          -- READ-COMMITTED

Defaults differ: InnoDB defaults to REPEATABLE READ, using MVCC snapshots plus next-key locks for locking reads. PostgreSQL, SQL Server and Oracle default to READ COMMITTED.

The trade-off: stronger isolation means more locking or more aborted transactions, so less concurrency. Most applications run at the default and lock explicitly where it matters, for example with SELECT … FOR UPDATE when booking the last seat.

Follow-ups

• What is MVCC? Multi-version concurrency control: readers see a consistent snapshot of committed versions, so reads don't block writes and writes don't block reads.
• What is a lost update? Two transactions read a value and both write back changes based on it, so one overwrites the other. SELECT … FOR UPDATE, or an atomic UPDATE … SET stock = stock - 1, prevents it.

Shared vs exclusive locks, the usual lead-in: a shared (S) lock lets many transactions read a row at once; an exclusive (X) lock, taken for writes and SELECT … FOR UPDATE, blocks everyone else from locking that row.

Preventing deadlocks

• Access rows and tables in the same order everywhere: for example, always lock the lower account ID first in a transfer.
• Keep transactions short, and don't wait for user input inside one.
• Use indexes on the columns in WHERE, so updates lock few rows instead of scanning and locking many.
• Accept that some will still happen, and retry on error 1213.

Follow-ups

• How do you investigate one? SHOW ENGINE INNODB STATUS shows the latest deadlock: both transactions, the locks held and the locks wanted.
• Deadlock vs lock wait timeout? A deadlock is detected immediately; a plain wait gives up after innodb_lock_wait_timeout (50 seconds by default) with error 1205.
• Do deadlocks happen in application code too? Yes, with threads and locks; the Java kit's question 38 covers them.

CREATE INDEX idx_employees_dept_salary ON employees (dept_id, salary);

EXPLAIN SELECT emp_name FROM employees WHERE dept_id = 2 AND salary > 100000;
-- on a table of realistic size: type: range, key: idx_employees_dept_salary
-- (on 10 rows the optimiser may simply scan the table: it's cheaper)

A covering index contains every column a query needs, so the database never visits the table rows: SELECT dept_id, salary FROM employees WHERE dept_id = 2 can be answered from idx_employees_dept_salary alone, shown as "Using index" in EXPLAIN.

How the two kinds fit together in InnoDB: a lookup by email searches the email index, finds the emp_id stored there, then searches the clustered primary-key index for the row. That's why a long primary key makes every secondary index bigger.

SQL Server lets you choose which index is clustered (it defaults to the primary key); InnoDB always clusters on the primary key, or a hidden row ID if a table has none. Why an index speeds up lookups at all, and what it costs on writes, is covered in the Backend kit's question 30.

Follow-ups

• Why are B+ trees used rather than binary trees? Each node holds hundreds of keys, so the tree is only three or four levels deep even for millions of rows, which means few disk reads per lookup. Leaves are linked, which makes range scans fast.
• What is a hash index? A structure for exact-match lookups only: no ranges, no sorting. InnoDB uses an adaptive hash index internally; MEMORY tables support them explicitly.

The leftmost-prefix rule, with INDEX (dept_id, salary)

Think of a phone book sorted by surname, then first name: it finds "Sharma, Priya" instantly, and every Sharma, but it's no help for "everyone called Priya".

Choosing the column order: put columns tested with equality first and range columns last, and favour the column that appears in the most queries.

Checking with EXPLAIN

EXPLAIN SELECT emp_name FROM employees WHERE YEAR(hire_date) = 2024;
-- type: ALL (a full scan), even with an index on hire_date

In EXPLAIN, type shows the access method: ALL is a full scan; range, ref and const use an index, from broad to precise. key names the index chosen, and rows estimates how many rows will be examined.

Follow-ups

• Can MySQL index an expression? Yes, since 8.0.13 a functional index works: CREATE INDEX idx_hire_year ON employees ((YEAR(hire_date)));.
• Does a low-selectivity column deserve an index? Rarely. On a column like status with three values, the optimiser often prefers a scan anyway.

CREATE VIEW employee_directory AS
SELECT e.emp_id, e.emp_name, e.email, d.dept_name
FROM employees e
LEFT JOIN departments d ON d.dept_id = e.dept_id;       -- no salaries in here

SELECT * FROM employee_directory WHERE dept_name = 'Sales';

GRANT SELECT ON company.employee_directory TO 'intranet'@'%';   -- access to the view, not the table

Updatable or not

CREATE VIEW sales_team AS
SELECT emp_id, emp_name, salary, dept_id FROM employees WHERE dept_id = 2
WITH CHECK OPTION;                    -- rejects changes that would move a row out of the view

UPDATE sales_team SET salary = 165000 WHERE emp_id = 7;   -- allowed: updates employees
UPDATE sales_team SET dept_id = 3 WHERE emp_id = 7;       -- rejected by WITH CHECK OPTION

CREATE VIEW dept_payroll AS
SELECT dept_id, SUM(salary) AS total FROM employees GROUP BY dept_id;
-- read-only: an UPDATE fails, because a total maps to no single row

Materialized views store the query's result and refresh it on demand, which suits expensive reports. MySQL has no materialized views; you emulate them with a summary table refreshed by a scheduled event. PostgreSQL has CREATE MATERIALIZED VIEW, and SQL Server uses indexed views.

Follow-ups

• Does a view make a query faster? Not by itself: it's expanded into the underlying query each time. A materialized view can.
• What happens if the base table drops a column the view uses? The view becomes invalid and errors when queried until it's recreated.

DELIMITER //

CREATE FUNCTION annual_ctc(monthly DECIMAL(10,2))
RETURNS DECIMAL(12,2)
DETERMINISTIC
BEGIN
  RETURN monthly * 12;
END //

CREATE PROCEDURE give_department_raise(IN p_dept_id INT, IN p_percent DECIMAL(5,2), OUT p_updated INT)
BEGIN
  START TRANSACTION;
  UPDATE employees
  SET salary = ROUND(salary * (1 + p_percent / 100), 2)
  WHERE dept_id = p_dept_id;
  SET p_updated = ROW_COUNT();
  COMMIT;
END //

DELIMITER ;

SELECT emp_name, annual_ctc(salary) AS yearly FROM employees WHERE dept_id = 3;   -- a function, inside SELECT
CALL give_department_raise(3, 10, @updated);                                      -- a procedure, with CALL
SELECT @updated;                                                                  -- 2 rows updated

DELIMITER // changes the statement terminator in the MySQL client, so the semicolons inside the body don't end the CREATE statement early.

Database or application?

Follow-ups

• Are stored procedures faster? Sometimes, by saving round trips for multi-step work, but most performance comes from good queries and indexes, wherever they're sent from.
• Do procedures prevent SQL injection? Only if they use parameters internally; building dynamic SQL from strings inside a procedure is just as injectable.

CREATE TABLE salary_audit (
  audit_id   INT AUTO_INCREMENT PRIMARY KEY,
  emp_id     INT,
  old_salary DECIMAL(10,2),
  new_salary DECIMAL(10,2),
  changed_at DATETIME DEFAULT CURRENT_TIMESTAMP
);

DELIMITER //
CREATE TRIGGER trg_salary_audit
AFTER UPDATE ON employees
FOR EACH ROW
BEGIN
  IF OLD.salary <> NEW.salary THEN           -- only log real salary changes
    INSERT INTO salary_audit (emp_id, old_salary, new_salary)
    VALUES (OLD.emp_id, OLD.salary, NEW.salary);
  END IF;
END //
DELIMITER ;

UPDATE employees SET salary = 130000 WHERE emp_id = 3;
SELECT emp_id, old_salary, new_salary FROM salary_audit;
-- 3 | 120000.00 | 130000.00

OLD and NEW: in an UPDATE trigger, OLD holds the row before the change and NEW after it. INSERT triggers have only NEW; DELETE triggers only OLD. A BEFORE trigger can modify NEW to adjust the values being written.

Database differences: MySQL triggers are row-level only (FOR EACH ROW). SQL Server triggers fire once per statement, with all affected rows in the inserted and deleted tables, so code written for one row breaks on multi-row updates. PostgreSQL supports both.

Follow-ups

• Can a trigger stop the change? Yes: a BEFORE trigger can SIGNAL SQLSTATE '45000' with a message, which aborts the statement.
• How many triggers can a table have? MySQL allows several per event and timing, ordered with FOLLOWS and PRECEDES.