Top MySQL Interview SQL Queries and Solutions
Master SQL coding interview rounds with top MySQL queries, Nth highest salaries, duplicate removals, window functions, and expert solutions.
Q1. Find the Nth highest salary from an Employee table
SELECT DISTINCT salary
FROM employee
ORDER BY salary DESC
LIMIT 1 OFFSET N-1;Sorts salaries in descending order, eliminates duplicates using DISTINCT, and uses LIMIT with OFFSET to pick the exact Nth position (e.g., OFFSET 1 for 2nd highest).
Q2. Find duplicate rows in a table
SELECT email, COUNT(*)
FROM users
GROUP BY email
HAVING COUNT(*) > 1;Groups records by the duplicate-prone column and filters groups having a count greater than 1 using HAVING.
Q3. Delete duplicate rows while keeping the one with the lowest ID
DELETE t1 FROM users t1
JOIN users t2
ON t1.email = t2.email
AND t1.id > t2.id;Performs a self-join to delete rows where the email matches but the ID is higher than another record.
Q4. Find employees who earn more than their managers
SELECT e.name AS Employee
FROM employee e
JOIN employee m
ON e.manager_id = m.id
WHERE e.salary > m.salary;Joins the employee table with itself on the manager ID key and compares salary attributes.
Q5. Find the second highest salary without using LIMIT
SELECT MAX(salary)
FROM employee
WHERE salary < (SELECT MAX(salary) FROM employee);Excludes the absolute maximum salary using a subquery and then finds the maximum of the remaining set.
Q6. Find departments with highest number of employees
SELECT department, COUNT(*) AS emp_count
FROM employees
GROUP BY department
ORDER BY emp_count DESC
LIMIT 1;Groups by department, tallies counts descending, and limits output to the top record.
Q7. Find employees whose names start with 'A'
SELECT *
FROM employees
WHERE name LIKE 'A%';Uses the LIKE operator with the wildcard '%' to match prefix character patterns.
Q8. Fetch alternating odd/even rows from a table
-- Odd rows
SELECT * FROM (SELECT *, ROW_NUMBER() OVER(ORDER BY id) as rn FROM users) t WHERE rn % 2 = 1;
-- Even rows
SELECT * FROM (SELECT *, ROW_NUMBER() OVER(ORDER BY id) as rn FROM users) t WHERE rn % 2 = 0;Utilizes window functions (ROW_NUMBER) to partition row sequences into modulo remainders.
Q9. Find employees not assigned to any department
SELECT e.*
FROM employees e
LEFT JOIN departments d
ON e.dept_id = d.id
WHERE d.id IS NULL;Uses a LEFT JOIN and filters where right table keys evaluate to NULL.
Q10. Calculate cumulative sum / running total
SELECT date, amount,
SUM(amount) OVER(ORDER BY date) AS running_total
FROM transactions;Applies window function aggregation (SUM OVER ORDER BY) to calculate continuous running totals.
Q11. Find max salary per department
SELECT department, MAX(salary) AS max_salary
FROM employee
GROUP BY department;Groups records by department and calculates aggregate maximums.
Q12. Swap gender column values (M to F, F to M) in a single update
UPDATE salary
SET sex = CASE sex
WHEN 'm' THEN 'f'
ELSE 'm'
END;Uses conditional CASE branching logic inside an UPDATE statement.
Q13. Find top 3 salaries in each department (Dense Rank)
SELECT department, salary
FROM (
SELECT department, salary,
DENSE_RANK() OVER(PARTITION BY department ORDER BY salary DESC) as rk
FROM employee
) t
WHERE rk <= 3;Partitions window ranks by department and filters top 3 positions.
Q14. Get current date and time in MySQL
SELECT NOW(), CURRENT_DATE(), CURRENT_TIMESTAMP();Retrieves current system timestamp configurations.
Q15. Find records that exist in Table A but not in Table B (EXCEPT alternative)
SELECT id FROM table_a
NOT IN (SELECT id FROM table_b);Filters records using subquery exclusion lists.
Q16. Find employees hired in the last 30 days
SELECT *
FROM employees
WHERE hire_date >= CURDATE() - INTERVAL 30 DAY;Uses interval arithmetic on current system dates to filter recent records.
Q17. Find customers who have placed orders every month
SELECT customer_id
FROM orders
GROUP BY customer_id
HAVING COUNT(DISTINCT DATE_FORMAT(order_date, '%Y-%m')) = 12;Groups by customer and checks distinct active order months count.
Q18. Find employees with names ending in vowel
SELECT *
FROM employees
WHERE name REGEXP '[aeiou]$';Uses regular expression pattern matching for ending characters.
Q19. Find median salary of employees
SELECT AVG(salary)
FROM (
SELECT salary, ROW_NUMBER() OVER(ORDER BY salary) as row_num, COUNT(*) OVER() as total_count
FROM employee
) x
WHERE row_num BETWEEN total_count / 2.0 AND total_count / 2.0 + 1;Computes median values using row number partitions and count boundaries.
Q20. Find departments with zero employees
SELECT d.department_name
FROM departments d
LEFT JOIN employees e ON d.id = e.dept_id
WHERE e.id IS NULL;Identifies departments without matching child employee rows via LEFT JOIN.
Q21. Find total sales per month with year breakdown
SELECT YEAR(order_date) as yr, MONTH(order_date) as mth, SUM(amount)
FROM orders
GROUP BY YEAR(order_date), MONTH(order_date);Extracts year and month parts from dates to group transactional aggregates.
Q22. Find top customer by total purchase amount
SELECT customer_id, SUM(total_amount) as total
FROM orders
GROUP BY customer_id
ORDER BY total DESC
LIMIT 1;Aggregates customer order totals, sorts descending, and limits to top row.
Q23. Find customers who placed orders greater than average order value
SELECT DISTINCT customer_id
FROM orders
WHERE total_amount > (SELECT AVG(total_amount) FROM orders);Compares individual orders against global average thresholds using subqueries.
Q24. Fetch first row from each group in a table
SELECT *
FROM (
SELECT *, ROW_NUMBER() OVER(PARTITION BY category ORDER BY id) as rn
FROM products
) t
WHERE rn = 1;Uses window partitioning to isolate the primary row per category group.
Q25. Find consecutive available seats in a cinema hall
SELECT c1.seat_id
FROM cinema c1, cinema c2
WHERE abs(c1.seat_id - c2.seat_id) = 1
AND c1.free = 1 AND c2.free = 1;Self-joins seating tables to evaluate adjacent free seat availability.
Q26. Find employees with same salary
SELECT salary, COUNT(*)
FROM employee
GROUP BY salary
HAVING COUNT(*) > 1;Groups by salary values and filters frequency counts greater than 1.
Q27. Find users who logged in on consecutive days
SELECT DISTINCT l1.user_id
FROM logins l1
JOIN logins l2
ON l1.user_id = l2.user_id
AND DATEDIFF(l2.login_date, l1.login_date) = 1;Performs self-join checking date difference equality of 1 day.
Q28. Find percentage of users who made a purchase on their first day
SELECT ROUND(COUNT(DISTINCT t2.user_id) * 100.0 / COUNT(DISTINCT t1.user_id), 2) AS percentage
FROM (SELECT user_id, MIN(order_date) as first_date FROM orders GROUP BY user_id) t1
LEFT JOIN orders t2
ON t1.user_id = t2.user_id AND t1.first_date = t2.order_date;Calculates proportion ratios between first-day buyers and total user base.
Q29. Pivot table rows into columns (Conditional Aggregation)
SELECT department,
SUM(CASE WHEN month = 'Jan' THEN revenue ELSE 0 END) AS Jan_Rev,
SUM(CASE WHEN month = 'Feb' THEN revenue ELSE 0 END) AS Feb_Rev
FROM sales
GROUP BY department;Pivots vertical row data into horizontal columns using CASE conditional aggregations.
Q30. Unpivot table columns into rows using UNION ALL
SELECT department, 'Jan' AS month, Jan_Rev AS revenue FROM sales
UNION ALL
SELECT department, 'Feb' AS month, Feb_Rev AS revenue FROM sales;Transforms horizontal column attributes into vertical row records.
Q31. Find moving average of sales over 3 days
SELECT date, amount,
AVG(amount) OVER(ORDER BY date ROWS BETWEEN 2 PRECEDING AND CURRENT ROW) as moving_avg
FROM sales;Calculates rolling window averages across preceding row frames.
Q32. Find products that have never been ordered
SELECT p.product_name
FROM products p
LEFT JOIN orders o ON p.id = o.product_id
WHERE o.product_id IS NULL;Filters unmatched products via LEFT JOIN null checks.
Q33. Find tree node types (Root, Leaf, Inner)
SELECT id,
CASE
WHEN p_id IS NULL THEN 'Root'
WHEN id NOT IN (SELECT DISTINCT p_id FROM tree WHERE p_id IS NOT NULL) THEN 'Leaf'
ELSE 'Inner'
END AS type
FROM tree;Classifies tree node structures based on parent-child existence rules.
Q34. Find customers who bought all products
SELECT customer_id
FROM customer_purchases
GROUP BY customer_id
HAVING COUNT(DISTINCT product_id) = (SELECT COUNT(*) FROM products);Validates customers whose distinct purchase counts match total products count.
Q35. Find most frequent item purchased by each customer
SELECT customer_id, product_id
FROM (
SELECT customer_id, product_id, COUNT(*) as cnt,
RANK() OVER(PARTITION BY customer_id ORDER BY COUNT(*) DESC) as rk
FROM purchases
GROUP BY customer_id, product_id
) t
WHERE rk = 1;Groups purchase frequencies per customer and filters top ranks.
Q36. Find 3 consecutive empty seats in stadium
SELECT DISTINCT s1.id
FROM stadium s1, stadium s2, stadium s3
WHERE s1.people >= 100 AND s2.people >= 100 AND s3.people >= 100
AND ((s2.id = s1.id + 1 AND s3.id = s2.id + 1));Joins 3 table instances to verify consecutive condition matches.
Q37. Calculate difference between max and min salary per department
SELECT department, MAX(salary) - MIN(salary) AS salary_diff
FROM employee
GROUP BY department;Calculates spread ranges by subtracting minimums from maximums.
Q38. Find employees hired on same date
SELECT hire_date, GROUP_CONCAT(name) AS employees
FROM employees
GROUP BY hire_date
HAVING COUNT(*) > 1;Groups employees by hire date and aggregates names using GROUP_CONCAT.
Q39. Find top 2 highest paid employees per department
SELECT department, name, salary
FROM (
SELECT department, name, salary,
DENSE_RANK() OVER(PARTITION BY department ORDER BY salary DESC) as rk
FROM employee
) t
WHERE rk <= 2;Partitions departments by salary ranking descending and filters top 2.
Q40. Find students who scored above average in their class
SELECT s.name, s.score, s.class_id
FROM students s
JOIN (SELECT class_id, AVG(score) as avg_score FROM students GROUP BY class_id) c
ON s.class_id = c.class_id
WHERE s.score > c.avg_score;Compares student scores against class average subquery tables.
Q41. Find managers with at least 5 direct reports
SELECT m.name
FROM employee e
JOIN employee m ON e.manager_id = m.id
GROUP BY m.id
HAVING COUNT(e.id) >= 5;Joins employee table to manager records and filters groups with 5 or more reports.
Q42. Find sales representatives with zero sales
SELECT e.name
FROM employees e
LEFT JOIN sales s ON e.id = s.rep_id
WHERE s.id IS NULL AND e.role = 'Sales';Filters sales staff with no corresponding sales records using LEFT JOIN.
Q43. Find transactions exceeding user's average spending by 50%
SELECT t.*
FROM transactions t
JOIN (SELECT user_id, AVG(amount) as avg_amt FROM transactions GROUP BY user_id) u
ON t.user_id = u.user_id
WHERE t.amount > u.avg_amt * 1.5;Compares transaction amounts against user-specific computed average baselines.
Q44. Find active subscriptions expiring in next 7 days
SELECT *
FROM subscriptions
WHERE status = 'active'
AND expiry_date BETWEEN CURDATE() AND CURDATE() + INTERVAL 7 DAY;Filters subscription date ranges using date interval additions.
Q45. Find countries with more than 3 active data centers
SELECT country, COUNT(*) as dc_count
FROM datacenters
WHERE status = 'active'
GROUP BY country
HAVING COUNT(*) > 3;Groups active locations by country and filters groups exceeding threshold counts.
Q46. Find cumulative revenue per store over time
SELECT store_id, date, revenue,
SUM(revenue) OVER(PARTITION BY store_id ORDER BY date) as cumulative_revenue
FROM daily_sales;Applies partitioned window summation over ordered store dates.
Q47. Find rank of each student based on total score
SELECT student_id, total_score,
RANK() OVER(ORDER BY total_score DESC) as student_rank
FROM exam_results;Assigns performance ranking scores using global ranking window functions.
Q48. Find users who changed password within 24 hours of registration
SELECT u.user_id
FROM users u
JOIN password_changes p ON u.user_id = p.user_id
WHERE p.change_time <= u.registration_time + INTERVAL 24 HOUR;Evaluates timestamp proximity between user creation and credential updates.
Q49. Find top 3 categories by total product inventory value
SELECT category, SUM(price * stock) as total_value
FROM products
GROUP BY category
ORDER BY total_value DESC
LIMIT 3;Computes total inventory worth per category and limits output to top 3.
Q50. Find users who have both read and unread notifications
SELECT user_id
FROM notifications
GROUP BY user_id
HAVING SUM(status = 'read') > 0 AND SUM(status = 'unread') > 0;Evaluates conditional aggregate sums to confirm dual status existence.
Q51. Find employees whose salary is equal to department average
SELECT e.name, e.salary
FROM employee e
JOIN (SELECT department, AVG(salary) as avg_sal FROM employee GROUP BY department) d
ON e.department = d.department
WHERE e.salary = d.avg_sal;Joins employee records with departmental average subqueries.
Q52. Find the highest revenue-generating day of the week
SELECT DAYNAME(order_date) as day_name, SUM(amount) as total_rev
FROM orders
GROUP BY day_name
ORDER BY total_rev DESC
LIMIT 1;Extracts day names from dates and aggregates total revenue sums.
Q53. Find projects taking longer than average project duration
SELECT project_id, DATEDIFF(end_date, start_date) as duration
FROM projects
WHERE DATEDIFF(end_date, start_date) > (SELECT AVG(DATEDIFF(end_date, start_date)) FROM projects);Compares individual project day intervals against global duration averages.
Q54. Find users who have never posted a comment
SELECT u.user_id
FROM users u
LEFT JOIN comments c ON u.user_id = c.user_id
WHERE c.comment_id IS NULL;Filters unmatched user accounts using LEFT JOIN null evaluations.
Q55. Find top 5 oldest active users
SELECT *
FROM users
WHERE status = 'active'
ORDER BY birth_date ASC
LIMIT 5;Sorts user birth dates ascending to capture senior accounts.
Q56. Find orders where shipping cost exceeds 20% of order value
SELECT order_id
FROM orders
WHERE shipping_cost > (order_total * 0.20);Calculates ratio boundaries between shipping costs and total order values.
Q57. Find items sold in every region
SELECT item_id
FROM sales_records
GROUP BY item_id
HAVING COUNT(DISTINCT region_id) = (SELECT COUNT(*) FROM regions);Confirms item sales span across total available geographic regions.
Q58. Find employees with missing phone numbers or emails
SELECT *
FROM employees
WHERE phone IS NULL OR email IS NULL OR phone = '' OR email = '';Checks for NULL or empty string contact details.
Q59. Find percent change in monthly sales compared to previous month
SELECT month, sales,
LAG(sales, 1) OVER(ORDER BY month) as prev_sales,
(sales - LAG(sales, 1) OVER(ORDER BY month)) / LAG(sales, 1) OVER(ORDER BY month) * 100 as pct_change
FROM monthly_sales;Uses LAG window function to compare current values against preceding months.
Q60. Find customers whose email domains match corporate domain
SELECT *
FROM customers
WHERE email LIKE '%@company.com';Filters corporate email suffixes using wildcard pattern matching.
Q61. Find rooms booked continuously for more than 5 days
SELECT room_id
FROM bookings
WHERE DATEDIFF(checkout_date, checkin_date) > 5
GROUP BY room_id;Groups rooms exceeding continuous booking day limits.
Q62. Find maximum concurrent active sessions per user
SELECT user_id, MAX(concurrent_count)
FROM session_logs
GROUP BY user_id;Aggregates peak concurrency session counts per user account.
Q63. Find categories having products in all price ranges
SELECT category_id
FROM products
GROUP BY category_id
HAVING MIN(price) < 50 AND MAX(price) > 500;Filters categories spanning both low-end and high-end pricing tiers.
Q64. Find drivers with rating higher than company average
SELECT driver_id, rating
FROM drivers
WHERE rating > (SELECT AVG(rating) FROM drivers);Compares individual ratings against fleet-wide average benchmarks.
Q65. Find top 3 most expensive products per category
SELECT category, product_name, price
FROM (
SELECT category, product_name, price,
RANK() OVER(PARTITION BY category ORDER BY price DESC) as rk
FROM products
) t
WHERE rk <= 3;Partitions category item pricing ranks descending and filters top 3.
Q66. Find employees sharing the same first name
SELECT first_name, COUNT(*)
FROM employees
GROUP BY first_name
HAVING COUNT(*) > 1;Groups records by first name attributes and filters duplicate counts.
Q67. Find total active duration of user sessions in minutes
SELECT user_id, SUM(TIMESTAMPDIFF(MINUTE, login_time, logout_time)) as total_mins
FROM sessions
GROUP BY user_id;Calculates minute intervals between session login and logout timestamps.
Q68. Find orders shipped within 24 hours of order placement
SELECT order_id
FROM orders
WHERE TIMESTAMPDIFF(HOUR, order_date, ship_date) <= 24;Filters orders meeting rapid fulfillment speed thresholds.
Q69. Find products with inventory count below reorder threshold
SELECT product_id, stock_qty, reorder_level
FROM inventory
WHERE stock_qty < reorder_level;Flags items requiring inventory restock alerts.
Q70. Find top referrer source driving highest user signups
SELECT referrer, COUNT(*) as signup_count
FROM users
GROUP BY referrer
ORDER BY signup_count DESC
LIMIT 1;Groups user registrations by referrer channels and sorts descending.
Q71. Find cities with population greater than country average
SELECT c.name, c.population
FROM cities c
JOIN (SELECT country_code, AVG(population) as avg_pop FROM cities GROUP BY country_code) x
ON c.country_code = x.country_code
WHERE c.population > x.avg_pop;Compares city populations against country-specific averages.
Q72. Find authors who have published books across multiple genres
SELECT author_id
FROM books
GROUP BY author_id
HAVING COUNT(DISTINCT genre_id) > 1;Filters authors with multiple distinct genre category listings.
Q73. Find average delivery delay in days for logistics orders
SELECT AVG(DATEDIFF(actual_delivery_date, estimated_delivery_date)) as avg_delay
FROM shipments
WHERE actual_delivery_date > estimated_delivery_date;Calculates day variance averages for late delivery shipments.
Q74. Find items that were returned more than 3 times
SELECT item_id
FROM returns
GROUP BY item_id
HAVING COUNT(*) > 3;Identifies problematic products with high return frequencies.
Q75. Find students who scored highest in Mathematics
SELECT student_id, score
FROM exam_scores
WHERE subject = 'Math'
ORDER BY score DESC
LIMIT 1;Filters subject records for Math and retrieves top score.
Q76. Find users whose password has not been updated in over a year
SELECT user_id
FROM users
WHERE last_password_update < CURDATE() - INTERVAL 1 YEAR;Flags stale account passwords exceeding annual update intervals.
Q77. Find total revenue generated by discount coupon codes
SELECT coupon_code, SUM(order_total) as revenue
FROM orders
WHERE coupon_code IS NOT NULL
GROUP BY coupon_code;Aggregates promotional performance metrics per coupon code.
Q78. Find flights with maximum passenger capacity utilization
SELECT flight_id, (booked_seats / total_seats) * 100 as utilization_pct
FROM flights
ORDER BY utilization_pct DESC
LIMIT 1;Calculates seat utilization percentages and sorts descending.
Q79. Find invoices pending payment past due date
SELECT invoice_id
FROM invoices
WHERE status = 'unpaid' AND due_date < CURDATE();Filters overdue unpaid invoice records.
Q80. Find hotels with average guest review rating above 4.5
SELECT hotel_id, AVG(rating) as avg_rating
FROM reviews
GROUP BY hotel_id
HAVING AVG(rating) > 4.5;Calculates average ratings and filters top-tier hospitality venues.
Q81. Find user accounts created via mobile app platform
SELECT *
FROM users
WHERE signup_source = 'mobile_app';Filters registrations originating from mobile app channels.
Q82. Find top 3 most popular programming languages in developer survey
SELECT language, COUNT(*) as votes
FROM developer_survey
GROUP BY language
ORDER BY votes DESC
LIMIT 3;Tallies vote frequencies and limits results to top 3 items.
Q83. Find employees who report directly to CEO (manager_id is NULL)
SELECT name
FROM employees
WHERE manager_id IS NULL;Isolates executive top-level personnel records.
Q84. Find vehicles driven past speed limit threshold
SELECT DISTINCT vehicle_id
FROM telemetry
WHERE speed > speed_limit;Filters telemetric speed violation records.
Q85. Find total downloads for each mobile application version
SELECT app_version, COUNT(*) as downloads
FROM app_downloads
GROUP BY app_version;Groups download logs by software release versions.
Q86. Find customers with credit limit exceeding 50,000
SELECT customer_name
FROM bank_accounts
WHERE credit_limit > 50000;Filters high-limit banking account profiles.
Q87. Find movies released between 2010 and 2020
SELECT title
FROM movies
WHERE release_year BETWEEN 2010 AND 2020;Filters records within a specific decade release range.
Q88. Find total penalty fines collected per traffic violation type
SELECT violation_type, SUM(fine_amount) as total_fines
FROM tickets
GROUP BY violation_type;Aggregates fine amounts grouped by infraction types.
Q89. Find hospitals with emergency room wait times under 30 minutes
SELECT hospital_name
FROM emergency_rooms
WHERE avg_wait_time_mins < 30;Filters medical facilities meeting rapid ER wait time criteria.
Q90. Find artists with highest album sales volume
SELECT artist_name, SUM(sales_qty) as total_sales
FROM album_sales
GROUP BY artist_name
ORDER BY total_sales DESC
LIMIT 1;Computes total music sales aggregate quantities per artist.
Q91. Find blog posts with zero user comments
SELECT p.post_id
FROM posts p
LEFT JOIN comments c ON p.post_id = c.post_id
WHERE c.comment_id IS NULL;Isolates unengaged content articles using LEFT JOIN null checks.
Q92. Find cities with highest air pollution AQI index
SELECT city_name, aqi_index
FROM air_quality
ORDER BY aqi_index DESC
LIMIT 5;Sorts AQI pollution indexes descending to capture top polluted cities.
Q93. Find user profiles missing profile picture uploads
SELECT user_id
FROM profiles
WHERE avatar_url IS NULL OR avatar_url = '';Flags incomplete user profile image setups.
Q94. Find total energy consumption per household meter
SELECT meter_id, SUM(kwh_consumed) as total_kwh
FROM power_readings
GROUP BY meter_id;Aggregates electricity consumption totals per smart meter ID.
Q95. Find flights delayed by more than 60 minutes
SELECT flight_no
FROM flight_delays
WHERE delay_minutes > 60;Filters commercial aviation delays exceeding 1 hour.
Q96. Find student clubs with active membership exceeding 50 students
SELECT club_name
FROM memberships
GROUP BY club_name
HAVING COUNT(student_id) > 50;Groups student rosters per club and filters high-capacity organizations.
Q97. Find stock tickers with daily trading volume spike over 1 million
SELECT ticker_symbol
FROM stock_trades
WHERE trade_date = CURDATE() AND volume > 1000000;Filters active daily equities experiencing heavy trading volume surges.
Q98. Find recipe ingredients containing allergen tags
SELECT recipe_id, ingredient_name
FROM ingredients
WHERE allergen_flag = 1;Filters recipe components carrying allergen warning flags.
Q99. Find top 10 highest-grossing box office movies
SELECT title, box_office_gross
FROM box_office
ORDER BY box_office_gross DESC
LIMIT 10;Sorts financial earnings descending to capture top 10 cinematic blockbusters.
Q100. Find employees working remotely based on location flags
SELECT employee_id, name
FROM employee_directory
WHERE work_mode = 'remote';Filters corporate staff directory records assigned to remote working configurations.