MySQL Coding Interview Questions with Queries and Answers

<hr>

<h1>MySQL Coding Interview Questions with Queries</h1>

<p>Knowing MySQL theory is important, but during technical interviews you may also be asked to write SQL queries directly.</p>

<p>For PHP, Laravel, backend, and database developer interviews, common SQL coding questions include:</p>

<ul> <li>Finding duplicate records</li> <li>Finding highest and second-highest salary</li> <li>Finding users without orders</li> <li>Working with JOINs</li> <li>GROUP BY and HAVING</li> <li>Subqueries</li> <li>Window functions</li> <li>Removing duplicates</li> <li>Getting latest records</li> <li>Finding Nth highest salary</li> <li>Pagination</li> <li>Working with dates</li> <li>Aggregations</li> <li>Real-world order and inventory queries</li> </ul>

<p>This article covers practical MySQL coding interview questions with queries and explanations.</p>

<hr>

<h2>Sample Tables Used in This Article</h2>

<p>Assume we have the following tables.</p>

<h3>employees</h3>

<pre><code>id name department_id salary manager_id created_at</code></pre>

<h3>departments</h3>

<h3>users</h3>

<pre><code>id name email city status created_at</code></pre>

<h3>orders</h3>

<pre><code>id user_id total_amount status created_at</code></pre>

<h3>products</h3>

<pre><code>id name category_id price stock created_at</code></pre>

<h3>order_items</h3>

<pre><code>id order_id product_id quantity price</code></pre>

<hr>

<h2>Basic MySQL Coding Interview Questions</h2>

<h3>1. How do you retrieve all records from a table?</h3>

<pre><code>SELECT * FROM users;</code></pre>

<p>This returns all rows and all columns from the users table.</p>

<p>In real applications, it is usually better to select only required columns.</p>

<pre><code>SELECT id, name, email FROM users;</code></pre>

<hr>

<h3>2. How do you find all active users?</h3>

<pre><code>SELECT * FROM users WHERE status = 1;</code></pre>

<p>The WHERE clause filters rows before they are returned.</p>

<hr>

<h3>3. How do you find users from Bhubaneswar?</h3>

<pre><code>SELECT * FROM users WHERE city = 'Bhubaneswar';</code></pre>

<hr>

<h3>4. How do you find users whose name starts with B?</h3>

<pre><code>SELECT * FROM users WHERE name LIKE 'B%';</code></pre>

<p><code>%</code> represents any number of characters.</p>

<hr>

<h3>5. How do you find users whose name contains "ram"?</h3>

<pre><code>SELECT * FROM users WHERE name LIKE '%ram%';</code></pre>

<p>Be aware that searches beginning with <code>%</code> may not use normal B-tree indexes efficiently.</p>

<hr>

<h3>6. How do you get the latest 10 users?</h3>

<pre><code>SELECT * FROM users ORDER BY created_at DESC LIMIT 10;</code></pre>

<hr>

<h3>7. How do you get the top 5 highest-paid employees?</h3>

<pre><code>SELECT * FROM employees ORDER BY salary DESC LIMIT 5;</code></pre>

<hr>

<h3>8. How do you find employees whose salary is between 50,000 and 80,000?</h3>

<pre><code>SELECT * FROM employees WHERE salary BETWEEN 50000 AND 80000;</code></pre>

<hr>

<h3>9. How do you find users from multiple cities?</h3>

<pre><code>SELECT * FROM users WHERE city IN ( 'Bhubaneswar', 'Cuttack', 'Puri' );</code></pre>

<hr>

<h3>10. How do you find users whose phone number is NULL?</h3>

<pre><code>SELECT * FROM users WHERE phone IS NULL;</code></pre>

<pre><code>phone = NULL</code></pre>

<p>NULL must be checked using <code>IS NULL</code> or <code>IS NOT NULL</code>.</p>

<hr>

<h2>Salary-Based Interview Questions</h2>

<h3>11. How do you find the highest salary?</h3>

<pre><code>SELECT MAX(salary) AS highest_salary FROM employees;</code></pre>

<hr>

<h3>12. How do you find the second-highest salary?</h3>

<p>One common approach is:</p>

<pre><code>SELECT MAX(salary) AS second_highest_salary FROM employees WHERE salary < ( SELECT MAX(salary) FROM employees );</code></pre>

<p>This returns the second distinct highest salary.</p>

<hr>

<h3>13. How do you find the second-highest salary using ORDER BY?</h3>

<pre><code>SELECT DISTINCT salary FROM employees ORDER BY salary DESC LIMIT 1 OFFSET 1;</code></pre>

<p><code>DISTINCT</code> is important if multiple employees have the same salary.</p>

<hr>

<h3>14. How do you find the third-highest salary?</h3>

<pre><code>SELECT DISTINCT salary FROM employees ORDER BY salary DESC LIMIT 1 OFFSET 2;</code></pre>

<hr>

<h3>15. How do you find the Nth highest salary?</h3>

<p>With window functions:</p>

<pre><code>SELECT salary FROM ( SELECT salary, DENSE_RANK() OVER ( ORDER BY salary DESC ) AS salary_rank FROM employees ) ranked WHERE salary_rank = 3;</code></pre>

<p>Replace <code>3</code> with the required rank.</p>

<hr>

<h3>16. How do you find employees earning more than the average salary?</h3>

<pre><code>SELECT * FROM employees WHERE salary > ( SELECT AVG(salary) FROM employees );</code></pre>

<hr>

<h3>17. How do you find employees earning below average salary?</h3>

<pre><code>SELECT * FROM employees WHERE salary < ( SELECT AVG(salary) FROM employees );</code></pre>

<hr>

<h3>18. How do you find the highest salary in each department?</h3>

<pre><code>SELECT department_id, MAX(salary) AS highest_salary FROM employees GROUP BY department_id;</code></pre>

<hr>

<h3>19. How do you find the employee with the highest salary in each department?</h3>

<p>Using a window function:</p>

<pre><code>SELECT * FROM ( SELECT e.*, DENSE_RANK() OVER ( PARTITION BY department_id ORDER BY salary DESC ) AS salary_rank FROM employees e ) ranked WHERE salary_rank = 1;</code></pre>

<p>This also handles multiple employees having the same highest salary.</p>

<hr>

<h3>20. How do you find the second-highest-paid employee in each department?</h3>

<pre><code>SELECT * FROM ( SELECT e.*, DENSE_RANK() OVER ( PARTITION BY department_id ORDER BY salary DESC ) AS salary_rank FROM employees e ) ranked WHERE salary_rank = 2;</code></pre>

<hr>

<h2>Duplicate Record Interview Questions</h2>

<h3>21. How do you find duplicate email addresses?</h3>

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

<p>The HAVING clause filters grouped results.</p>

<hr>

<h3>22. How do you find duplicate records based on both name and email?</h3>

<pre><code>SELECT name, email, COUNT(*) AS total FROM users GROUP BY name, email HAVING COUNT(*) > 1;</code></pre>

<hr>

<h3>23. How do you display all duplicate rows?</h3>

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

<hr>

<h3>24. How would you prevent duplicate emails?</h3>

<p>Add a UNIQUE constraint:</p>

<pre><code>ALTER TABLE users ADD CONSTRAINT unique_users_email UNIQUE (email);</code></pre>

<p>This protects data integrity at the database level.</p>

<hr>

<h2>JOIN Interview Questions</h2>

<h3>25. How do you get users with their orders?</h3>

<pre><code>SELECT u.id, u.name, o.id AS order_id, o.total_amount FROM users u INNER JOIN orders o ON u.id = o.user_id;</code></pre>

<p>INNER JOIN returns only users who have matching orders.</p>

<hr>

<h3>26. How do you get all users including those without orders?</h3>

<pre><code>SELECT u.id, u.name, o.id AS order_id FROM users u LEFT JOIN orders o ON u.id = o.user_id;</code></pre>

<p>LEFT JOIN preserves every user from the left table.</p>

<hr>

<h3>27. How do you find users who have never placed an order?</h3>

<pre><code>SELECT u.* FROM users u LEFT JOIN orders o ON u.id = o.user_id WHERE o.id IS NULL;</code></pre>

<p>This is one of the most common SQL interview queries.</p>

<hr>

<h3>28. How do you find users who have placed at least one order?</h3>

<p>Using EXISTS:</p>

<pre><code>SELECT * FROM users u WHERE EXISTS ( SELECT 1 FROM orders o WHERE o.user_id = u.id );</code></pre>

<hr>

<h3>29. How do you get employees with their department names?</h3>

<pre><code>SELECT e.id, e.name, e.salary, d.name AS department FROM employees e LEFT JOIN departments d ON e.department_id = d.id;</code></pre>

<hr>

<h3>30. How do you show employees with their manager names?</h3>

<p>This requires a self join.</p>

<pre><code>SELECT e.name AS employee, m.name AS manager FROM employees e LEFT JOIN employees m ON e.manager_id = m.id;</code></pre>

<hr>

<h2>GROUP BY and HAVING Questions</h2>

<h3>31. How do you count employees in each department?</h3>

<pre><code>SELECT department_id, COUNT(*) AS total_employees FROM employees GROUP BY department_id;</code></pre>

<hr>

<h3>32. How do you find departments with more than 10 employees?</h3>

<pre><code>SELECT department_id, COUNT(*) AS total_employees FROM employees GROUP BY department_id HAVING COUNT(*) > 10;</code></pre>

<hr>

<h3>33. How do you calculate total sales for each customer?</h3>

<pre><code>SELECT user_id, SUM(total_amount) AS total_sales FROM orders WHERE status = 'completed' GROUP BY user_id;</code></pre>

<hr>

<h3>34. How do you find users who spent more than 50,000?</h3>

<pre><code>SELECT user_id, SUM(total_amount) AS total_spent FROM orders WHERE status = 'completed' GROUP BY user_id HAVING SUM(total_amount) > 50000;</code></pre>

<hr>

<h3>35. How do you calculate average salary by department?</h3>

<pre><code>SELECT department_id, AVG(salary) AS average_salary FROM employees GROUP BY department_id;</code></pre>

<hr>

<h2>Subquery Interview Questions</h2>

<h3>36. How do you find employees belonging to the department with the highest average salary?</h3>

<pre><code>SELECT * FROM employees WHERE department_id = ( SELECT department_id FROM employees GROUP BY department_id ORDER BY AVG(salary) DESC LIMIT 1 );</code></pre>

<hr>

<h3>37. How do you find products priced above the average product price?</h3>

<pre><code>SELECT * FROM products WHERE price > ( SELECT AVG(price) FROM products );</code></pre>

<hr>

<h3>38. How do you find the highest-value order?</h3>

<pre><code>SELECT * FROM orders WHERE total_amount = ( SELECT MAX(total_amount) FROM orders );</code></pre>

<p>This returns all orders tied for the highest amount.</p>

<hr>

<h2>Latest and Earliest Record Questions</h2>

<h3>39. How do you find the latest order?</h3>

<pre><code>SELECT * FROM orders ORDER BY created_at DESC LIMIT 1;</code></pre>

<hr>

<h3>40. How do you find the oldest user?</h3>

<pre><code>SELECT * FROM users ORDER BY created_at ASC LIMIT 1;</code></pre>

<hr>

<h3>41. How do you find the latest order for each user?</h3>

<p>Using ROW_NUMBER:</p>

<pre><code>SELECT * FROM ( SELECT o.*, ROW_NUMBER() OVER ( PARTITION BY user_id ORDER BY created_at DESC, id DESC ) AS row_num FROM orders o ) ranked WHERE row_num = 1;</code></pre>

<hr>

<h3>42. How do you find each user's first order?</h3>

<pre><code>SELECT * FROM ( SELECT o.*, ROW_NUMBER() OVER ( PARTITION BY user_id ORDER BY created_at ASC, id ASC ) AS row_num FROM orders o ) ranked WHERE row_num = 1;</code></pre>

<hr>

<h2>Date-Based Coding Questions</h2>

<h3>43. How do you find today's orders?</h3>

<p>A range query is often preferable:</p>

<pre><code>SELECT * FROM orders WHERE created_at >= CURRENT_DATE() AND created_at < CURRENT_DATE() + INTERVAL 1 DAY;</code></pre>

<hr>

<h3>44. How do you find yesterday's orders?</h3>

<pre><code>SELECT * FROM orders WHERE created_at >= CURRENT_DATE() - INTERVAL 1 DAY AND created_at < CURRENT_DATE();</code></pre>

<hr>

<h3>45. How do you find orders from the last 7 days?</h3>

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

<hr>

<h3>46. How do you find orders from the current month?</h3>

<pre><code>SELECT * FROM orders WHERE created_at >= DATE_FORMAT( CURRENT_DATE(), '%Y-%m-01' ) AND created_at < DATE_FORMAT( CURRENT_DATE() + INTERVAL 1 MONTH, '%Y-%m-01' );</code></pre>

<hr>

<h3>47. How do you calculate monthly sales?</h3>

<pre><code>SELECT YEAR(created_at) AS sales_year, MONTH(created_at) AS sales_month, SUM(total_amount) AS total_sales FROM orders WHERE status = 'completed' GROUP BY YEAR(created_at), MONTH(created_at) ORDER BY sales_year, sales_month;</code></pre>

<hr>

<h3>48. How do you calculate daily sales?</h3>

<pre><code>SELECT DATE(created_at) AS order_date, SUM(total_amount) AS total_sales FROM orders WHERE status = 'completed' GROUP BY DATE(created_at) ORDER BY order_date;</code></pre>

<hr>

<h2>Product and Order Queries</h2>

<h3>49. How do you find products that were never ordered?</h3>

<pre><code>SELECT p.* FROM products p LEFT JOIN order_items oi ON p.id = oi.product_id WHERE oi.product_id IS NULL;</code></pre>

<hr>

<h3>50. How do you find the most sold product?</h3>

<pre><code>SELECT oi.product_id, SUM(oi.quantity) AS total_quantity FROM order_items oi GROUP BY oi.product_id ORDER BY total_quantity DESC LIMIT 1;</code></pre>

<p>To include the product name:</p>

<pre><code>SELECT p.id, p.name, SUM(oi.quantity) AS total_quantity FROM order_items oi JOIN products p ON p.id = oi.product_id GROUP BY p.id, p.name ORDER BY total_quantity DESC LIMIT 1;</code></pre>

<hr>

<h3>51. How do you find the top 5 best-selling products?</h3>

<pre><code>SELECT p.id, p.name, SUM(oi.quantity) AS total_sold FROM products p JOIN order_items oi ON p.id = oi.product_id GROUP BY p.id, p.name ORDER BY total_sold DESC LIMIT 5;</code></pre>

<hr>

<h3>52. How do you find products with stock below 10?</h3>

<pre><code>SELECT * FROM products WHERE stock < 10 ORDER BY stock ASC;</code></pre>

<hr>

<h3>53. How do you find out-of-stock products?</h3>

<pre><code>SELECT * FROM products WHERE stock = 0;</code></pre>

<hr>

<h3>54. How do you calculate the value of current inventory?</h3>

<pre><code>SELECT SUM(price * stock) AS inventory_value FROM products;</code></pre>

<hr>

<h2>Window Function Interview Questions</h2>

<h3>55. What is ROW_NUMBER()?</h3>

<p>ROW_NUMBER assigns a unique sequence number to each row.</p>

<pre><code>SELECT id, name, salary, ROW_NUMBER() OVER ( ORDER BY salary DESC ) AS row_number FROM employees;</code></pre>

<hr>

<p>RANK gives the same rank to equal values but leaves gaps.</p>

<pre><code>SELECT name, salary, RANK() OVER ( ORDER BY salary DESC ) AS salary_rank FROM employees;</code></pre>

<hr>

<h3>57. What is DENSE_RANK()?</h3>

<p>DENSE_RANK gives the same rank to duplicate values but does not leave gaps.</p>

<pre><code>SELECT name, salary, DENSE_RANK() OVER ( ORDER BY salary DESC ) AS salary_rank FROM employees;</code></pre>

<hr>

<h3>58. What is the difference between ROW_NUMBER, RANK and DENSE_RANK?</h3>

<p>Suppose salaries are:</p>

<p>ROW_NUMBER:</p>

<p>DENSE_RANK:</p>

<hr>

<h3>59. How do you calculate running total sales?</h3>

<pre><code>SELECT id, created_at, total_amount, SUM(total_amount) OVER ( ORDER BY created_at, id ) AS running_total FROM orders WHERE status = 'completed';</code></pre>

<hr>

<h3>60. How do you compare an employee's salary with the previous employee?</h3>

<p>Using LAG:</p>

<pre><code>SELECT name, salary, LAG(salary) OVER ( ORDER BY salary ) AS previous_salary FROM employees;</code></pre>

<hr>

<h2>Pagination Interview Questions</h2>

<h3>61. How do you implement basic SQL pagination?</h3>

<pre><code>SELECT * FROM users ORDER BY id LIMIT 20 OFFSET 40;</code></pre>

<p>This returns 20 records after skipping 40.</p>

<hr>

<h3>62. Why can OFFSET pagination become slow?</h3>

<p>For very large offsets, MySQL may need to scan and skip many rows before returning the requested results.</p>

<p>For example:</p>

<pre><code>SELECT * FROM users ORDER BY id LIMIT 20 OFFSET 500000;</code></pre>

<p>This can become expensive.</p>

<hr>

<h3>63. How do you implement cursor or keyset pagination?</h3>

<p>If the last displayed ID was 500000:</p>

<pre><code>SELECT * FROM users WHERE id > 500000 ORDER BY id LIMIT 20;</code></pre>

<p>This can be much more efficient for large datasets.</p>

<hr>

<h2>Update and Delete Coding Questions</h2>

<h3>64. How do you increase all product prices by 10%?</h3>

<pre><code>UPDATE products SET price = price * 1.10;</code></pre>

<p>Always verify the intended rows before running large UPDATE queries.</p>

<hr>

<h3>65. How do you increase salaries by 10% for one department?</h3>

<pre><code>UPDATE employees SET salary = salary * 1.10 WHERE department_id = 2;</code></pre>

<hr>

<h3>66. How do you deactivate users who have not logged in for one year?</h3>

<p>Assuming there is a <code>last_login_at</code> column:</p>

<pre><code>UPDATE users SET status = 0 WHERE last_login_at < NOW() - INTERVAL 1 YEAR;</code></pre>

<hr>

<h3>67. How do you delete records older than 5 years?</h3>

<pre><code>DELETE FROM logs WHERE created_at < NOW() - INTERVAL 5 YEAR;</code></pre>

<p>For very large tables, deleting millions of records in a single transaction can be risky. Production cleanup may need batching.</p>

<hr>

<h2>Real-World MySQL Coding Questions</h2>

<h3>68. How do you find the total number of orders for each user?</h3>

<pre><code>SELECT u.id, u.name, COUNT(o.id) AS total_orders FROM users u LEFT JOIN orders o ON u.id = o.user_id GROUP BY u.id, u.name;</code></pre>

<p>LEFT JOIN also shows users who have zero orders.</p>

<hr>

<h3>69. How do you find customers with more than 5 completed orders?</h3>

<pre><code>SELECT user_id, COUNT(*) AS total_orders FROM orders WHERE status = 'completed' GROUP BY user_id HAVING COUNT(*) > 5;</code></pre>

<hr>

<h3>70. How do you find the customer who spent the most money?</h3>

<pre><code>SELECT user_id, SUM(total_amount) AS total_spent FROM orders WHERE status = 'completed' GROUP BY user_id ORDER BY total_spent DESC LIMIT 1;</code></pre>

<hr>

<h3>71. How do you find top 10 customers by revenue?</h3>

<pre><code>SELECT u.id, u.name, SUM(o.total_amount) AS total_spent FROM users u JOIN orders o ON u.id = o.user_id WHERE o.status = 'completed' GROUP BY u.id, u.name ORDER BY total_spent DESC LIMIT 10;</code></pre>

<hr>

<h3>72. How do you calculate average order value?</h3>

<pre><code>SELECT AVG(total_amount) AS average_order_value FROM orders WHERE status = 'completed';</code></pre>

<hr>

<h3>73. How do you find orders whose amount is above average?</h3>

<pre><code>SELECT * FROM orders WHERE total_amount > ( SELECT AVG(total_amount) FROM orders );</code></pre>

<hr>

<h3>74. How do you find categories with no products?</h3>

<pre><code>SELECT c.* FROM categories c LEFT JOIN products p ON c.id = p.category_id WHERE p.id IS NULL;</code></pre>

<hr>

<h3>75. How do you find products ordered more than 100 times?</h3>

<pre><code>SELECT product_id, SUM(quantity) AS total_quantity FROM order_items GROUP BY product_id HAVING SUM(quantity) > 100;</code></pre>

<hr>

<h2>Transaction and Concurrency Coding Questions</h2>

<h3>76. How would you transfer money between two accounts?</h3>

<p>Use a transaction:</p>

<pre><code>START TRANSACTION; UPDATE accounts SET balance = balance - 1000 WHERE id = 1; UPDATE accounts SET balance = balance + 1000 WHERE id = 2; COMMIT;</code></pre>

<p>If an error occurs:</p>

<pre><code>ROLLBACK;</code></pre>

<p>Both operations should succeed together.</p>

<hr>

<h3>77. How would you prevent overselling the last product?</h3>

<p>You can lock the product row inside a transaction:</p>

<pre><code>START TRANSACTION; SELECT stock FROM products WHERE id = 10 FOR UPDATE;</code></pre>

<p>After checking that stock is available:</p>

<pre><code>UPDATE products SET stock = stock - 1 WHERE id = 10; COMMIT;</code></pre>

<p>This helps protect against concurrent purchases.</p>

<hr>

<h3>78. How can you decrease stock atomically?</h3>

<pre><code>UPDATE products SET stock = stock - 1 WHERE id = 10 AND stock > 0;</code></pre>

<p>Then check the affected row count.</p>

<p>This is often safer than reading the stock first and updating it later without locking.</p>

<hr>

<h2>Index and Performance Coding Questions</h2>

<h3>79. How do you create an index?</h3>

<pre><code>CREATE INDEX idx_users_email ON users(email);</code></pre>

<hr>

<h3>80. How do you create a composite index?</h3>

<pre><code>CREATE INDEX idx_orders_user_status ON orders(user_id, status);</code></pre>

<p>This may help queries such as:</p>

<pre><code>SELECT * FROM orders WHERE user_id = 10 AND status = 'completed';</code></pre>

<hr>

<h3>81. How do you check query execution strategy?</h3>

<pre><code>EXPLAIN SELECT * FROM orders WHERE user_id = 100;</code></pre>

<p>EXPLAIN helps you understand how MySQL plans to execute the query.</p>

<hr>

<h3>82. How would you improve this query?</h3>

<pre><code>SELECT * FROM users WHERE email = 'user@example.com';</code></pre>

<p>If email is frequently searched and should be unique:</p>

<pre><code>ALTER TABLE users ADD UNIQUE INDEX idx_users_email (email);</code></pre>

<p>Also, if only specific fields are required:</p>

<pre><code>SELECT id, name, email FROM users WHERE email = 'user@example.com';</code></pre>

<hr>

<h3>83. Why may this query be less index-friendly?</h3>

<pre><code>SELECT * FROM orders WHERE DATE(created_at) = '2026-09-30';</code></pre>

<p>Applying a function to the indexed column can reduce index efficiency.</p>

<p>A range query can be better:</p>

<pre><code>SELECT * FROM orders WHERE created_at >= '2026-09-30 00:00:00' AND created_at < '2026-10-01 00:00:00';</code></pre>

<hr>

<h2>Advanced SQL Coding Questions</h2>

<h3>84. How do you find employees who earn more than their manager?</h3>

<pre><code>SELECT e.name AS employee, e.salary AS employee_salary, m.name AS manager, m.salary AS manager_salary FROM employees e JOIN employees m ON e.manager_id = m.id WHERE e.salary > m.salary;</code></pre>

<hr>

<h3>85. How do you find employees with the same salary?</h3>

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

<p>To get full employee details:</p>

<pre><code>SELECT * FROM employees WHERE salary IN ( SELECT salary FROM employees GROUP BY salary HAVING COUNT(*) > 1 );</code></pre>

<hr>

<h3>86. How do you find the department with the most employees?</h3>

<pre><code>SELECT department_id, COUNT(*) AS total_employees FROM employees GROUP BY department_id ORDER BY total_employees DESC LIMIT 1;</code></pre>

<hr>

<h3>87. How do you find departments with average salary above 60,000?</h3>

<pre><code>SELECT department_id, AVG(salary) AS average_salary FROM employees GROUP BY department_id HAVING AVG(salary) > 60000;</code></pre>

<hr>

<h3>88. How do you find the percentage contribution of each employee's salary to the total salary?</h3>

<pre><code>SELECT name, salary, ROUND( salary * 100.0 / SUM(salary) OVER (), 2 ) AS salary_percentage FROM employees;</code></pre>

<hr>

<h3>89. How do you get the previous order amount for every order?</h3>

<pre><code>SELECT id, total_amount, LAG(total_amount) OVER ( ORDER BY created_at, id ) AS previous_order_amount FROM orders;</code></pre>

<hr>

<h3>90. How do you get the next order amount?</h3>

<pre><code>SELECT id, total_amount, LEAD(total_amount) OVER ( ORDER BY created_at, id ) AS next_order_amount FROM orders;</code></pre>

<hr>

<h2>Common Interview Trap Questions</h2>

<h3>91. What is wrong with this query?</h3>

<pre><code>SELECT * FROM users WHERE phone = NULL;</code></pre>

<p>NULL cannot be compared using <code>=</code>.</p>

<p>Correct:</p>

<pre><code>SELECT * FROM users WHERE phone IS NULL;</code></pre>

<hr>

<h3>92. What is the difference between COUNT(*) and COUNT(column)?</h3>

<pre><code>COUNT(*)</code></pre>

<p>counts rows.</p>

<pre><code>COUNT(phone)</code></pre>

<p>counts non-NULL values in the phone column.</p>

<hr>

<h3>93. What is wrong with using WHERE after GROUP BY for aggregate filtering?</h3>

<p>This is incorrect:</p>

<pre><code>SELECT user_id, COUNT(*) FROM orders GROUP BY user_id WHERE COUNT(*) > 5;</code></pre>

<p>The aggregate condition should use HAVING:</p>

<pre><code>SELECT user_id, COUNT(*) FROM orders GROUP BY user_id HAVING COUNT(*) > 5;</code></pre>

<hr>

<h3>94. What happens if you omit WHERE in UPDATE?</h3>

<pre><code>UPDATE users SET status = 0;</code></pre>

<p>Every row can be updated.</p>

<p>Always verify the condition before executing production UPDATE or DELETE queries.</p>

<hr>

<h3>95. What happens if multiple employees share the highest salary?</h3>

<p>This query:</p>

<pre><code>SELECT * FROM employees ORDER BY salary DESC LIMIT 1;</code></pre>

<p>returns only one employee.</p>

<p>If all employees with the highest salary are required:</p>

<pre><code>SELECT * FROM employees WHERE salary = ( SELECT MAX(salary) FROM employees );</code></pre>

<hr>

<h2>Scenario-Based Coding Questions</h2>

<h3>96. Your users table contains 50 lakh rows. How would you fetch records efficiently?</h3>

<p>I would avoid:</p>

<pre><code>SELECT * FROM users;</code></pre>

<p>For a normal listing:</p>

<pre><code>SELECT id, name, email FROM users WHERE status = 1 ORDER BY id LIMIT 50;</code></pre>

<p>I would also check indexes using:</p>

<pre><code>EXPLAIN SELECT id, name, email FROM users WHERE status = 1 ORDER BY id LIMIT 50;</code></pre>

<p>For later pages, keyset pagination may be more efficient.</p>

<hr>

<h3>97. A query suddenly becomes slow after the table grows. What should you check?</h3>

<p>I would check:</p>

<ul> <li>EXPLAIN output</li> <li>Indexes</li> <li>Rows examined</li> <li>JOIN conditions</li> <li>ORDER BY</li> <li>GROUP BY</li> <li>Selected columns</li> <li>Large OFFSET values</li> <li>Data distribution</li> </ul>

<p>Never optimize only by guessing.</p>

<hr>

<h3>98. How would you find inactive users with no orders in the last 6 months?</h3>

<pre><code>SELECT u.* FROM users u WHERE NOT EXISTS ( SELECT 1 FROM orders o WHERE o.user_id = u.id AND o.created_at >= NOW() - INTERVAL 6 MONTH );</code></pre>

<hr>

<h3>99. How would you find customers who placed orders in every month of a year?</h3>

<pre><code>SELECT user_id FROM orders WHERE created_at >= '2026-01-01' AND created_at < '2027-01-01' GROUP BY user_id HAVING COUNT( DISTINCT MONTH(created_at) ) = 12;</code></pre>

<hr>

<h3>100. How would you find the highest sale of each month?</h3>

<pre><code>SELECT * FROM ( SELECT o.*, ROW_NUMBER() OVER ( PARTITION BY YEAR(created_at), MONTH(created_at) ORDER BY total_amount DESC ) AS row_num FROM orders o ) ranked WHERE row_num = 1;</code></pre>

<hr>

<h2>Important MySQL Coding Topics to Practice</h2>

<p>For SQL coding interviews, make sure you can write queries involving:</p>

<ul> <li>SELECT</li> <li>WHERE</li> <li>AND / OR</li> <li>IN</li> <li>BETWEEN</li> <li>LIKE</li> <li>IS NULL</li> <li>ORDER BY</li> <li>LIMIT</li> <li>OFFSET</li> <li>COUNT</li> <li>SUM</li> <li>AVG</li> <li>MIN</li> <li>MAX</li> <li>GROUP BY</li> <li>HAVING</li> <li>INNER JOIN</li> <li>LEFT JOIN</li> <li>SELF JOIN</li> <li>Subqueries</li> <li>EXISTS</li> <li>UNION</li> <li>Window functions</li> <li>ROW_NUMBER</li> <li>RANK</li> <li>DENSE_RANK</li> <li>LAG</li> <li>LEAD</li> <li>Transactions</li> <li>Indexes</li> <li>EXPLAIN</li> <li>Pagination</li> </ul>

<hr>

<h2>How to Answer SQL Coding Questions in an Interview</h2>

<p>When an interviewer gives you a SQL problem, avoid immediately writing a query without understanding the requirement.</p>

<p>A good process is:</p>

<pre><code>Understand the tables ↓ Identify relationships ↓ Understand expected output ↓ Decide JOIN / Subquery / Aggregation ↓ Write the query ↓ Check duplicates ↓ Check NULL values ↓ Consider performance ↓ Explain your solution</code></pre>

<p>For example, if the interviewer asks:</p>

<p><strong>"Find the second-highest salary."</strong></p>

<p>Do not only write:</p>

<pre><code>SELECT DISTINCT salary FROM employees ORDER BY salary DESC LIMIT 1 OFFSET 1;</code></pre>

<p>Also explain why <code>DISTINCT</code> is used.</p>

<p>If two employees have the same highest salary, without DISTINCT, OFFSET 1 may still return the same salary.</p>

<p>That explanation shows that you understand the problem instead of only memorizing a query.</p>

<hr>

<h2>Interview Tip: Always Ask About Duplicate Values</h2>

<p>Salary questions are a good example.</p>

<p>If an interviewer asks:</p>

<p><strong>"Find the second-highest salary."</strong></p>

<p>You should clarify whether they mean:</p>

<ul> <li>The second row after sorting, or</li> <li>The second distinct highest salary</li> </ul>

<p>Most interviews mean the second distinct highest salary.</p>

<p>Understanding such edge cases makes your answer stronger.</p>

<hr>

<h2>Interview Tip: Think About NULL Values</h2>

<p>NULL behaves differently from normal values.</p>

<p>For example:</p>

<pre><code>WHERE value = NULL</code></pre>

<p>is incorrect.</p>

<pre><code>WHERE value IS NULL</code></pre>

<p>Also remember that:</p>

<pre><code>COUNT(column)</code></pre>

<p>does not count NULL values.</p>

<pre><code>COUNT(*)</code></pre>

<p>counts rows.</p>

<hr>

<h2>Interview Tip: Do Not Ignore Performance</h2>

<p>For experienced developers, writing a logically correct SQL query may not be enough.</p>

<p>You may also be asked:</p>

<p><strong>"Will this query perform well with 50 lakh records?"</strong></p>

<p>You should be ready to discuss:</p>

<ul> <li>Indexes</li> <li>Composite indexes</li> <li>EXPLAIN</li> <li>Pagination</li> <li>Rows examined</li> <li>Query selectivity</li> <li>Large OFFSET values</li> <li>JOIN performance</li> </ul>

<p>For example:</p>

<pre><code>SELECT * FROM orders WHERE user_id = 10 AND status = 'completed';</code></pre>

<p>If this query is frequently executed, a suitable composite index might be:</p>

<pre><code>CREATE INDEX idx_orders_user_status ON orders(user_id, status);</code></pre>

<p>But index decisions should always be based on actual query patterns.</p>

<hr>

<h2>Final Thoughts</h2>

<p>MySQL coding interviews are about more than memorizing SQL syntax.</p>

<p>You should understand how to convert a business requirement into a correct query and how that query behaves with real data.</p>

<p>Practice queries involving users, employees, departments, products, orders, order items, payments, and inventory because these tables closely resemble real backend projects.</p>

<p>Pay special attention to:</p>

<ul> <li>Joins</li> <li>Subqueries</li> <li>GROUP BY</li> <li>HAVING</li> <li>Duplicate records</li> <li>Highest salary problems</li> <li>Window functions</li> <li>Date queries</li> <li>Transactions</li> <li>Indexes</li> <li>Large datasets</li> </ul>

<p>Most importantly, during an interview explain why you chose a particular SQL approach instead of simply writing the query.</p>

<p>That is what demonstrates real database understanding and practical experience.</p>

MySQL Coding Interview Questions with Queries and Answers

Discussion

Share your experience