If you are preparing for a PHP Developer, Backend Developer, Full Stack Developer, Software Engineer, or Database Developer interview, these 50 MySQL queries are essential for experienced-level preparation.

The list covers JOINs, subqueries, aggregation, duplicate handling, CTEs, window functions, ranking, date queries, transactions, optimization, and real-world reporting queries.

Note: Queries using ROW_NUMBER(), RANK(), DENSE_RANK(), LAG(), LEAD(), and CTEs are intended for modern MySQL versions such as MySQL 8.x.

1. Find Duplicate Emails

SELECT email, COUNT(*) AS total
FROM users
GROUP BY email
HAVING COUNT(*) > 1;

Use: Find duplicate email addresses.

2. Find Complete Duplicate User Records

SELECT *
FROM users
WHERE email IN (
    SELECT email
    FROM users
    GROUP BY email
    HAVING COUNT(*) > 1
)
ORDER BY email;

3. Find the Second Highest Salary

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

4. Find the Nth Highest Salary

SELECT salary
FROM (
    SELECT
        salary,
        DENSE_RANK() OVER (ORDER BY salary DESC) AS rnk
    FROM employees
) t
WHERE rnk = 5;

Change 5 to the required rank.

5. Find Highest Salary in Each Department

SELECT department_id, employee_id, salary
FROM (
    SELECT
        department_id,
        employee_id,
        salary,
        RANK() OVER (
            PARTITION BY department_id
            ORDER BY salary DESC
        ) AS rnk
    FROM employees
) t
WHERE rnk = 1;

6. Find Top 3 Salaries in Every Department

SELECT *
FROM (
    SELECT
        employee_id,
        department_id,
        salary,
        DENSE_RANK() OVER (
            PARTITION BY department_id
            ORDER BY salary DESC
        ) AS rnk
    FROM employees
) t
WHERE rnk <= 3;

7. Find Employees Earning More Than Department Average

SELECT e.*
FROM employees e
JOIN (
    SELECT
        department_id,
        AVG(salary) AS avg_salary
    FROM employees
    GROUP BY department_id
) d
ON e.department_id = d.department_id
WHERE e.salary > d.avg_salary;

8. Find Departments Having More Than 5 Employees

SELECT
    department_id,
    COUNT(*) AS employee_count
FROM employees
GROUP BY department_id
HAVING COUNT(*) > 5;

9. Find Employees Without a Department

SELECT e.*
FROM employees e
LEFT JOIN departments d
    ON e.department_id = d.id
WHERE d.id IS NULL;

10. Find Departments Without Employees

SELECT d.*
FROM departments d
LEFT JOIN employees e
    ON d.id = e.department_id
WHERE e.id IS NULL;

11. Get Latest Record for Each User

One of the most useful queries in real-world applications.

SELECT *
FROM (
    SELECT
        a.*,
        ROW_NUMBER() OVER (
            PARTITION BY user_id
            ORDER BY created_at DESC
        ) AS rn
    FROM user_attempts a
) t
WHERE rn = 1;

12. Get Latest Approved Record for Each User

SELECT *
FROM (
    SELECT
        a.*,
        ROW_NUMBER() OVER (
            PARTITION BY user_id
            ORDER BY approved_at DESC
        ) AS rn
    FROM applications a
    WHERE status = 'approved'
) t
WHERE rn = 1;

13. Find the First Order of Every Customer

SELECT *
FROM (
    SELECT
        o.*,
        ROW_NUMBER() OVER (
            PARTITION BY user_id
            ORDER BY created_at ASC
        ) AS rn
    FROM orders o
) t
WHERE rn = 1;

14. Find the Second Order of Every Customer

SELECT *
FROM (
    SELECT
        o.*,
        ROW_NUMBER() OVER (
            PARTITION BY user_id
            ORDER BY created_at ASC
        ) AS order_number
    FROM orders o
) t
WHERE order_number = 2;

15. Find Users Who Never Placed an Order

SELECT u.*
FROM users u
LEFT JOIN orders o
    ON u.id = o.user_id
WHERE o.id IS NULL;

16. Find Users Who Placed More Than 3 Orders

SELECT
    user_id,
    COUNT(*) AS total_orders
FROM orders
GROUP BY user_id
HAVING COUNT(*) > 3;

17. Calculate Total Purchase per Customer

SELECT
    user_id,
    SUM(amount) AS total_purchase
FROM orders
GROUP BY user_id;

18. Find Customers Spending More Than ₹50,000

SELECT
    user_id,
    SUM(amount) AS total_purchase
FROM orders
GROUP BY user_id
HAVING SUM(amount) > 50000;

19. Monthly Sales Report

SELECT
    YEAR(created_at) AS year,
    MONTH(created_at) AS month,
    SUM(amount) AS total_sales
FROM orders
GROUP BY
    YEAR(created_at),
    MONTH(created_at)
ORDER BY year DESC, month DESC;

20. Daily Sales Report

SELECT
    DATE(created_at) AS sale_date,
    SUM(amount) AS total_sales,
    COUNT(*) AS total_orders
FROM orders
GROUP BY DATE(created_at)
ORDER BY sale_date DESC;

21. Find Records Created in Last 7 Days

SELECT *
FROM orders
WHERE created_at >= NOW() - INTERVAL 7 DAY;

22. Find Records Created in Current Month

SELECT *
FROM orders
WHERE created_at >= DATE_FORMAT(
    CURRENT_DATE,
    '%Y-%m-01'
);

This form can be preferable to wrapping the indexed created_at column in a function.

23. Find Records Between Two Dates

SELECT *
FROM orders
WHERE created_at >= '2026-09-01'
  AND created_at < '2026-10-01';

Using a half-open date range is especially useful when created_at is a DATETIME.

24. Conditional Aggregation

SELECT
    COUNT(*) AS total_users,
    SUM(status = 'active') AS active_users,
    SUM(status = 'inactive') AS inactive_users,
    SUM(status = 'blocked') AS blocked_users
FROM users;

25. Count Pass and Fail Students

SELECT
    COUNT(*) AS total_students,
    SUM(CASE
        WHEN marks >= 60 THEN 1
        ELSE 0
    END) AS passed,
    SUM(CASE
        WHEN marks < 60>

26. Calculate Running Total

SELECT
    id,
    order_date,
    amount,
    SUM(amount) OVER (
        ORDER BY order_date, id
    ) AS running_total
FROM orders;

Window functions calculate values across related rows while retaining individual result rows.

27. Calculate Running Total per Customer

SELECT
    user_id,
    order_date,
    amount,
    SUM(amount) OVER (
        PARTITION BY user_id
        ORDER BY order_date, id
    ) AS running_total
FROM orders;

28. Compare Current Value with Previous Value

SELECT
    employee_id,
    effective_date,
    salary,
    LAG(salary) OVER (
        PARTITION BY employee_id
        ORDER BY effective_date
    ) AS previous_salary
FROM employee_salary_history;

29. Find Salary Changes

SELECT *
FROM (
    SELECT
        employee_id,
        effective_date,
        salary,
        LAG(salary) OVER (
            PARTITION BY employee_id
            ORDER BY effective_date
        ) AS previous_salary
    FROM employee_salary_history
) t
WHERE previous_salary IS NOT NULL
  AND salary <> previous_salary;

30. Calculate Salary Increase Percentage

SELECT
    employee_id,
    salary,
    previous_salary,
    ROUND(
        ((salary - previous_salary)
        / previous_salary) * 100,
        2
    ) AS increase_percentage
FROM (
    SELECT
        employee_id,
        salary,
        LAG(salary) OVER (
            PARTITION BY employee_id
            ORDER BY effective_date
        ) AS previous_salary
    FROM employee_salary_history
) t
WHERE previous_salary IS NOT NULL
  AND previous_salary <> 0;

31. Find Status Changes

SELECT *
FROM (
    SELECT
        user_id,
        status,
        updated_at,
        LAG(status) OVER (
            PARTITION BY user_id
            ORDER BY updated_at
        ) AS previous_status
    FROM user_status
) t
WHERE previous_status IS NOT NULL
  AND status <> previous_status;

32. Find Duplicate Records Using ROW_NUMBER()

SELECT *
FROM (
    SELECT
        u.*,
        ROW_NUMBER() OVER (
            PARTITION BY email
            ORDER BY id
        ) AS rn
    FROM users u
) t
WHERE rn > 1;

33. Delete Duplicate Records

Keep the smallest ID.

DELETE u1
FROM users u1
JOIN users u2
    ON u1.email = u2.email
   AND u1.id > u2.id;

⚠️ Always take a backup and test the corresponding SELECT before running a DELETE in production.

34. Find Missing IDs

SELECT t1.id + 1 AS missing_id
FROM numbers t1
LEFT JOIN numbers t2
    ON t2.id = t1.id + 1
WHERE t2.id IS NULL;

35. Use CTE for Better Query Structure

WITH department_avg AS (
    SELECT
        department_id,
        AVG(salary) AS avg_salary
    FROM employees
    GROUP BY department_id
)
SELECT
    e.employee_id,
    e.department_id,
    e.salary,
    d.avg_salary
FROM employees e
JOIN department_avg d
    ON e.department_id = d.department_id
WHERE e.salary > d.avg_salary;

A CTE is a named temporary result set available within the scope of a single statement.

36. Recursive CTE for Number Generation

WITH RECURSIVE numbers AS (
    SELECT 1 AS n

    UNION ALL

    SELECT n + 1
    FROM numbers
    WHERE n < 10>

Recursive CTEs can be useful for generating sequences and traversing hierarchical data.

37. Recursive Employee Hierarchy

WITH RECURSIVE employee_tree AS (

    SELECT
        id,
        name,
        manager_id,
        1 AS level
    FROM employees
    WHERE manager_id IS NULL

    UNION ALL

    SELECT
        e.id,
        e.name,
        e.manager_id,
        et.level + 1
    FROM employees e
    JOIN employee_tree et
        ON e.manager_id = et.id
)
SELECT *
FROM employee_tree
ORDER BY level, id;

38. Find Employees with Same Salary

SELECT salary, COUNT(*) AS total
FROM employees
GROUP BY salary
HAVING COUNT(*) > 1;

39. Find Employees with Same Name

SELECT
    name,
    COUNT(*) AS total
FROM employees
GROUP BY name
HAVING COUNT(*) > 1;

40. Find Maximum Salary Without MAX()

SELECT salary
FROM employees
ORDER BY salary DESC
LIMIT 1;

41. Find Second Highest Salary Using LIMIT

SELECT DISTINCT salary
FROM employees
ORDER BY salary DESC
LIMIT 1 OFFSET 1;

42. Find Employees Whose Salary Is Between Two Values

SELECT *
FROM employees
WHERE salary BETWEEN 50000 AND 100000;

43. Find Employees Joined in the Current Year

SELECT *
FROM employees
WHERE joined_at >= MAKEDATE(YEAR(CURRENT_DATE), 1)
  AND joined_at < MAKEDATE>

44. Find the Percentage of Active Users

SELECT
    ROUND(
        100 * SUM(status = 'active') / COUNT(*),
        2
    ) AS active_percentage
FROM users;

45. Find Products Never Ordered

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

46. Find Best-Selling Products

SELECT
    product_id,
    SUM(quantity) AS total_quantity
FROM order_items
GROUP BY product_id
ORDER BY total_quantity DESC
LIMIT 10;

47. Find Customers with More Than One Order

SELECT
    user_id,
    COUNT(*) AS total_orders
FROM orders
GROUP BY user_id
HAVING COUNT(*) > 1;

48. Find Records Existing in One Table but Not Another

Using NOT EXISTS:

SELECT u.*
FROM users u
WHERE NOT EXISTS (
    SELECT 1
    FROM payments p
    WHERE p.user_id = u.id
);

This is useful when checking missing related records.

49. Use EXPLAIN to Analyze a Query

EXPLAIN
SELECT *
FROM orders
WHERE user_id = 1001
  AND status = 'completed';

For performance troubleshooting, experienced developers should understand execution plans, indexes, joins, row estimates, and access methods. MySQL's documentation has dedicated sections for index usage, multiple-column indexes, verifying index usage, and query optimization.

50. Create a Composite Index

CREATE INDEX idx_orders_user_status
ON orders (user_id, status);

Then analyze the query:

EXPLAIN
SELECT *
FROM orders
WHERE user_id = 1001
  AND status = 'completed';

The right index depends on the actual query workload and data distribution—don't add indexes blindly.

???? Top MySQL Concepts for Experienced Developers

???? Interview Preparation Strategy

For an experienced MySQL developer, don't simply memorize these queries.

Understand:

1. What problem does the query solve?

2. Why is JOIN used instead of a subquery?

3. When should EXISTS be preferred?

4. How does ROW_NUMBER() differ from RANK() and DENSE_RANK()?

5. Which columns should be indexed?

6. How do you identify a slow query using EXPLAIN?

7. How do transactions and COMMIT/ROLLBACK work?

8. How does the query behave when the table contains millions of records?

Quick Revision Formula

SELECT → JOIN → GROUP BY → HAVING → Subquery → CTE → Window Functions → Index → EXPLAIN → Optimization → Transactions

Master these concepts and you'll be much better prepared for experienced-level MySQL interviews and real-world backend development.

Important

Some queries above use MySQL 8.x features, particularly CTEs and window functions. Check your production MySQL version before using them. MySQL's current 8.4 reference documents these features and the associated syntax.