1. Start with a practice schema

Use these sample tables to practise the questions below. Examples use MySQL 8.0+ syntax.

CREATE TABLE departments (
  department_id INT PRIMARY KEY,
  department_name VARCHAR(100)
);

CREATE TABLE employees (
  employee_id INT PRIMARY KEY,
  employee_name VARCHAR(100),
  department_id INT,
  salary DECIMAL(10,2),
  manager_id INT,
  hire_date DATE,
  FOREIGN KEY (department_id)
    REFERENCES departments(department_id)
);

CREATE TABLE customers (
  customer_id INT PRIMARY KEY,
  customer_name VARCHAR(100),
  email VARCHAR(150),
  city VARCHAR(100)
);

CREATE TABLE orders (
  order_id INT PRIMARY KEY,
  customer_id INT,
  order_date DATETIME,
  amount DECIMAL(10,2),
  status VARCHAR(30),
  FOREIGN KEY (customer_id)
    REFERENCES customers(customer_id)
);

CREATE TABLE payments (
  payment_id INT PRIMARY KEY,
  order_id INT,
  payment_amount DECIMAL(10,2),
  payment_status VARCHAR(30),
  payment_date DATETIME
);

2. SQL fundamentals: questions 1–20

Example: Employees earning above the company average

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

Example: Find employees hired during 2025

SELECT employee_id, employee_name, hire_date
FROM employees
WHERE hire_date >= '2025-01-01'
  AND hire_date <  '2026-01-01';

Using a range can allow better use of an index on the date column than wrapping the column in YEAR().

3. JOIN scenarios: questions 21–35

Example: Customers who never placed an order

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

Example: Employee and manager names

SELECT e.employee_name AS employee,
       m.employee_name AS manager
FROM employees e
LEFT JOIN employees m
  ON e.manager_id = m.employee_id;

Example: Orders with no payment record

SELECT o.order_id
FROM orders o
WHERE NOT EXISTS (
  SELECT 1
  FROM payments p
  WHERE p.order_id = o.order_id
);

4. Duplicate records & data-quality scenarios: questions 36–55

Example: Find duplicate email addresses

SELECT email, COUNT(*) AS occurrences
FROM customers
WHERE email IS NOT NULL
GROUP BY email
HAVING COUNT(*) > 1;

Example: Return all customer rows with duplicate emails

SELECT c.*
FROM customers c
JOIN (
  SELECT email
  FROM customers
  WHERE email IS NOT NULL
  GROUP BY email
  HAVING COUNT(*) > 1
) d ON d.email = c.email;

Example: Find missing or blank emails

SELECT *
FROM customers
WHERE email IS NULL OR TRIM(email) = '';

Example: Find negative order amounts

SELECT *
FROM orders
WHERE amount < 0;

Example: Find payment amounts that differ from order amounts

This example assumes one successful payment should equal the order amount. If split or partial payments are allowed, aggregate successful payments first.

SELECT o.order_id,
       o.amount AS order_amount,
       p.payment_amount
FROM orders o
JOIN payments p ON p.order_id = o.order_id
WHERE p.payment_status = 'SUCCESS'
  AND p.payment_amount <> o.amount;

More practice questions

38. Find duplicate rows based on customer name and city.
39. Find duplicate orders for a customer on the same date—after confirming the business rule.
40. Find orphan payment records.
41. Find names with leading or trailing spaces.
42. Find duplicate emails after normalizing case and whitespace.
43. Find missing IDs in a sequence.
44. Find orders with future dates.
45. Identify malformed email addresses.
46. Find records where created date is later than updated date.
47. Validate mandatory columns across several tables.
48. Compare source and target row counts.
49. Identify repeated transactions caused by retries.
50. Find values outside an approved status list.
51. Detect inconsistent whitespace or capitalization in category fields.
52. Find duplicates without a unique identifier.
53. Design a safe deduplication process that retains the correct record.
54. Find rows that violate a business-specific data rule.
55. Identify unexpected NULL values in required fields.

5. Advanced SQL & window functions: questions 56–75

Second-highest distinct salary

SELECT MAX(salary) AS second_highest_salary
FROM employees
WHERE salary < (SELECT MAX(salary) FROM employees);

Top three distinct salary levels in each department

WITH ranked AS (
  SELECT employee_id, employee_name, department_id, salary,
         DENSE_RANK() OVER (
           PARTITION BY department_id
           ORDER BY salary DESC
         ) AS salary_rank
  FROM employees
  WHERE salary IS NOT NULL
)
SELECT *
FROM ranked
WHERE salary_rank <= 3;

Latest order for each customer

WITH ranked AS (
  SELECT o.*,
         ROW_NUMBER() OVER (
           PARTITION BY customer_id
           ORDER BY order_date DESC, order_id DESC
         ) AS rn
  FROM orders o
)
SELECT *
FROM ranked
WHERE rn = 1;

Running total of orders

SELECT order_id, order_date, amount,
       SUM(amount) OVER (
         ORDER BY order_date, order_id
         ROWS BETWEEN UNBOUNDED PRECEDING
                  AND CURRENT ROW
       ) AS running_total
FROM orders;

Compare each order with the customer’s previous order

SELECT customer_id, order_id, order_date, amount,
       LAG(amount) OVER (
         PARTITION BY customer_id
         ORDER BY order_date, order_id
       ) AS previous_amount
FROM orders;

Other advanced challenges

69. Find employees whose salary equals their department’s maximum.
70. Find the median salary.
71. Calculate month-over-month sales percentage change.
72. Find customers who ordered in every month of a quarter.
73. Find products contributing to the first 80% of cumulative sales.
74. Find employees earning more than every employee in another department.
75. Find customers who ordered in January but not February.

6. MySQL internals & performance: questions 76–90

Use EXPLAIN to inspect a query plan

EXPLAIN
SELECT *
FROM orders
WHERE customer_id = 1001;

Review the access type, selected key, estimated rows, join order, and extra operations. On supported MySQL versions, EXPLAIN ANALYZE executes the query and reports actual timing and row counts; use it cautiously for expensive queries.

DELETE vs TRUNCATE vs DROP

• DELETE: removes selected rows and supports WHERE. Transactional rollback depends on the storage engine and transaction context.
• TRUNCATE TABLE: removes all rows; in MySQL it is DDL and causes an implicit commit.
• DROP TABLE: removes the table definition and its data.

Why can NOT IN fail with NULL?

SELECT customer_id
FROM customers
WHERE customer_id NOT IN (
  SELECT customer_id FROM orders
);

If the subquery contains NULL, comparisons can evaluate to UNKNOWN and exclude rows unexpectedly. NOT EXISTS is often a safer alternative.

7. SQL for QA & SDET interviews: questions 91–120

Example: Find orders without a successful payment

SELECT o.order_id
FROM orders o
WHERE NOT EXISTS (
  SELECT 1
  FROM payments p
  WHERE p.order_id = o.order_id
    AND p.payment_status = 'SUCCESS'
);

Example: Safe inventory decrement

UPDATE inventory
SET stock_quantity = stock_quantity - 1
WHERE product_id = 101
  AND stock_quantity > 0;

Check the affected-row count. A complete checkout should use a transaction for the inventory change and related order records. Also account for the case where the product ID does not exist.

8. Transactions, constraints & schema design: questions 121–135

Example: transaction pattern

START TRANSACTION;

UPDATE accounts
SET balance = balance - 500
WHERE account_id = 101
  AND balance >= 500;

-- Application must check affected rows.
-- Apply related updates only when valid.

COMMIT;
-- On a handled failure, use ROLLBACK instead.

For a transfer, debit and credit should happen in one transaction. Use InnoDB, validate affected-row counts, and design locking and retry behavior deliberately.

9. Tricky SQL output questions: 136–150

136. Why does COUNT(*) differ from COUNT(email)?
137. How do WHERE and HAVING differ?
138. What happens when a scalar subquery returns multiple rows?
139. Why can joining two one-to-many tables inflate totals?
140. How do UNION and UNION ALL differ?
141. When is EXISTS preferable to a JOIN?
142. Why is salary = NULL incorrect?
143. Why can GROUP BY queries fail with unrelated selected columns?
144. How do ROW_NUMBER, RANK and DENSE_RANK differ?
145. Why can a date filter miss records on the final day?
146. Find the third occurrence of an event for each user.
147. Find gaps in a sequence of dates.
148. Why can OR conditions affect query performance?
149. How can implicit type conversion affect indexes?
150. Return the latest row per group when timestamps can tie.

COUNT(*) vs COUNT(email)

SELECT COUNT(*) AS total_rows,
       COUNT(email) AS rows_with_email
FROM customers;

COUNT(*) counts all rows. COUNT(email) excludes rows where email is NULL.

UNION vs UNION ALL

SELECT city FROM customers
UNION
SELECT city FROM suppliers;

UNION removes duplicate result rows; UNION ALL preserves them and is generally cheaper when duplicate removal is unnecessary.

10. Interactive practice tracker

Check off each challenge after solving it without help. Your progress is tracked in this page while it remains open.

11. Quick knowledge-check quiz

Choose an answer for each question, then select Check my answers to see your score and explanations.

12. Seven-day SQL interview study plan

13. How to answer SQL interview questions confidently

1. Clarify the rule: What does the business mean by “duplicate”, “latest”, or “successful”?
2. Design the query: Identify tables, joins, filters, grouping and ordering.
3. Cover edge cases: Check NULLs, ties, empty results, duplicates and date boundaries.
4. Consider performance: Discuss indexes and validate the execution plan with EXPLAIN.
5. Connect it to automation: Explain how your test reads data and asserts the result.
6. Protect data: Use isolated test data, least-privilege access and safe cleanup.

Compatibility note: The interactive tracker and quiz use JavaScript. Some WordPress editors or security plugins strip inline scripts from post content. If that happens, keep the article markup in the post and add the script through an approved snippets plugin or your theme’s enqueued JavaScript. Avoid pasting untrusted scripts into production.