Here, we will discuss SQL Query Interview Questions and Answers, which are asked by interviewers in most company interviews.
Table of Contents
1. What is MySQL?
MySQL is an open-source relational database management system (RDBMS) that uses SQL (Structured Query Language) to manage data. It’s known for its speed, reliability, and ease of use, powering applications.
2. Top MySQL Interview Topics
Storage engines (InnoDB vs MyISAM), indexes (B-Tree, covering), ACID properties, normalization (1NF-3NF), JOIN types, transactions/isolation levels, SQL optimization (EXPLAIN, indexing), replication, partitioning, stored procedures, triggers, deadlocks, constraints (PK, FK), SQL injection prevention, TRUNCATE vs DELETE, CHAR vs VARCHAR, GROUP BY/HAVING, query tuning, NULL handling, and data types.
SQL Query Interview Questions and Answers
1. Write a SQL Query to Find the Second-Highest Salary.
To find the second-highest salary, use a subquery to sort salaries in descending order, skip the first row, and return the next.
SELECT DISTINCT salary FROM employees ORDER BY salary DESC LIMIT 1 OFFSET 1;
This method ensures clarity and efficiency. For databases without LIMIT/OFFSET, use DENSE_RANK().
Always handle ties (duplicate salaries) appropriately.
2. How to Optimize a Slow SQL Query?
Optimize by analyzing execution plans to identify bottlenecks. Use indexes on filtered/joined columns, avoid SELECT *, and normalize tables.
Replace subqueries with joins, update statistics, and partition large tables.
Example: Indexing foreign keys in joins reduces scan time.
3. What is the difference between INNER JOIN and OUTER JOIN?
INNER JOIN returns rows where both tables match. OUTER JOIN (LEFT, RIGHT, FULL) includes non-matching rows.
Example: Employees LEFT JOIN Departments show all employees, including those without departments.
Use based on data inclusivity needs.
4. Write a SQL query to find the top 3 departments with the highest average salary.
SELECT department_id, AVG(salary) AS avg_salary FROM employees GROUP BY department_id ORDER BY avg_salary DESC LIMIT 3;
Group salaries by department, compute averages, sort, and limit results.
Handle NULL departments and ties using DENSE_RANK if needed.
5. How do you handle duplicate rows in a SQL query?
Use DISTINCT or GROUP BY to filter duplicates. To delete, use ROW_NUMBER() in a CTE:
WITH CTE AS ( SELECT *, ROW_NUMBER() OVER (PARTITION BY col1, col2 ORDER BY id) AS rn FROM table ) DELETE FROM CTE WHERE rn > 1;
6. Write a SQL query to find the employees who have the same name and work in the same department.
SELECT name, department_id, COUNT(*) FROM employees GROUP BY name, department_id HAVING COUNT(*) > 1;
Group by name and department, filter groups with counts >1. Ensure case sensitivity if applicable.
7. What is the difference between UNION and UNION ALL?
UNION combines results and removes duplicates; UNION ALL retains duplicates.
Use UNION ALL for faster performance when duplicates are acceptable.
8. Write a SQL query to find the departments with no employees.
SELECT d.department_id FROM departments d LEFT JOIN employees e ON d.department_id = e.department_id WHERE e.employee_id IS NULL;
A LEFT JOIN filters departments without matching employees.
Alternatively, use NOT EXISTS.
9. How do you use indexing to improve SQL query performance?
Indexes speed up data retrieval on filtered, joined, or sorted columns.
Use covering indexes (including all query columns) to avoid table scans.
Avoid over-indexing to prevent write slowdowns.
10. Write a SQL query to find the employees who have worked for more than 5 years.
SELECT employee_id FROM employees WHERE hire_date <= DATE_SUB(CURRENT_DATE, INTERVAL 5 YEAR);
Calculate tenure using date functions. Ensure accurate date handling for leap years/time zones.
11. What is the difference between SUBQUERY and JOIN?
Subqueries nest queries and may execute row-by-row (correlated). Joins merge tables, often faster with indexes.
Use joins for readability and performance in large datasets.
12. Write a SQL query to find the top 2 products with the highest sales.
SELECT product_id, SUM(sales) AS total_sales FROM orders GROUP BY product_id ORDER BY total_sales DESC LIMIT 2;
Aggregate sales per product, sort, and limit. Handle ties with RANK() if needed.
13. How do you use stored procedures to improve SQL query performance?
Stored procedures reduce network traffic by executing precompiled SQL on the server. They parameterize queries and enhance security.
Optimize by avoiding dynamic SQL inside procedures.
14. Write a SQL query to find the customers who have placed an order but have not made a payment.
SELECT c.customer_id FROM customers c JOIN orders o ON c.customer_id = o.customer_id LEFT JOIN payments p ON o.order_id = p.order_id WHERE p.payment_id IS NULL;
LEFT JOIN orders with payments and filter unmatched entries.
15. What is the difference between GROUP BY and HAVING?
GROUP BY aggregates data into groups. HAVING filters these groups post-aggregation.
Example: Filter departments with an average salary > 5000 after grouping.
16. Write a SQL query to find the employees who work in the same department as their manager.
SELECT e.employee_id FROM employees e JOIN employees m ON e.manager_id = m.employee_id WHERE e.department_id = m.department_id;
Self-join employees with managers and compare departments.
17. How do you use window functions to solve complex queries?
Window functions (e.g., RANK(), SUM() OVER) perform calculations across rows without collapsing results.
Example: Calculate running totals or rank salaries within departments.
18. Write a SQL query to find the top 3 products with the highest average price.
SELECT product_id, AVG(price) AS avg_price FROM products GROUP BY product_id ORDER BY avg_price DESC LIMIT 3;
Compute the average price per product, sort, and limit.
19. What is the difference between TRUNCATE and DELETE?
TRUNCATE removes all rows (faster, resets auto-increment).
DELETE removes specific rows (logged, triggers fired).
Use TRUNCATE for full table clearance.
20. Write a SQL query to find the employees who have not taken any leave in the last 6 months.
SELECT employee_id FROM employees e WHERE NOT EXISTS ( SELECT 1 FROM leaves l WHERE e.employee_id = l.employee_id AND l.leave_date >= DATE_SUB(CURRENT_DATE, INTERVAL 6 MONTH) );
Check for absence in leave records within the timeframe using NOT EXISTS.