1. Write a SQL query to find the second-highest salary.
SELECT MAX(salary) AS SecondHighestSalary
FROM employees
WHERE salary < (SELECT MAX(salary) FROM employees);
2. How would you optimize a slow SQL query?
• Use appropriate indexes.
• Avoid
SELECT * and fetch only needed columns.• Rewrite correlated subqueries as joins.
• Use
EXPLAIN to analyze query plan.• Break complex queries into CTEs.
3. What is the difference between INNER JOIN and OUTER JOIN?
•
INNER JOIN: Returns only matching rows between tables.•
OUTER JOIN: Includes matching rows and non-matching rows with NULLs (LEFT, RIGHT, FULL).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;
5. How do you handle duplicate rows in a SQL query?
Use
DISTINCT, or use ROW_NUMBER() in a CTE and filter rows:WITH cte AS (
SELECT *, ROW_NUMBER() OVER (PARTITION BY name, department_id ORDER BY id) AS rn
FROM employees
)
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
FROM employees
GROUP BY name, department_id
HAVING COUNT(*) > 1;
7. What is the difference between UNION and UNION ALL?
•
UNION: Removes duplicates.•
UNION ALL: Keeps all records including duplicates.8. Write a SQL query to find the departments with no employees.
SELECT d.department_id, d.department_name
FROM departments d
LEFT JOIN employees e ON d.department_id = e.department_id
WHERE e.employee_id IS NULL;
9. How do you use indexing to improve SQL query performance?
• Indexes reduce the data scanned by filtering or joining.
• Create indexes on frequently used
WHERE, JOIN, and ORDER BY columns.10. Write a SQL query to find the employees who have worked for more than 5 years.
SELECT *
FROM employees
WHERE DATEDIFF(CURDATE(), hire_date) > 5 * 365;
11. What is the difference between SUBQUERY and JOIN?
•
Subquery: Query inside another query, can be nested.•
Join: Combines rows from multiple tables based on related columns.12. Write a SQL query to find the top 2 products with the highest sales.
SELECT product_id, SUM(sales) AS total_sales
FROM sales_table
GROUP BY product_id
ORDER BY total_sales DESC
LIMIT 2;
13. How do you use stored procedures to improve SQL query performance?
• Pre-compiled and cached.
• Reduces network traffic with multiple operations in one call.
• Enhances modularity and reusability.
14. Write a SQL query to find the customers who have placed an order but have not made a payment.
SELECT o.customer_id
FROM orders o
LEFT JOIN payments p ON o.order_id = p.order_id
WHERE p.payment_id IS NULL;
15. What is the difference between GROUP BY and HAVING?
•
GROUP BY: Groups rows based on one or more columns.•
HAVING: Filters groups, like WHERE but for aggregated data.16. Write a SQL query to find the employees who work in the same department as their manager.
SELECT e.employee_id, e.name
FROM employees e
JOIN employees m ON e.department_id = m.department_id AND e.manager_id = m.employee_id;
17. How do you use window functions to solve complex queries?
• Use
ROW_NUMBER(), RANK(), SUM() OVER(), LAG() etc.• They perform calculations across rows without collapsing them.
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;
19. What is the difference between TRUNCATE and DELETE?
•
TRUNCATE: Removes all rows, faster, can’t rollback in most DBs.•
DELETE: Removes selected rows, supports WHERE, and rollback.20. Write a SQL query to find the employees who have not taken any leave in the last 6 months.
SELECT e.*
FROM employees e
LEFT JOIN leaves l ON e.employee_id = l.employee_id AND l.leave_date >= DATE_SUB(CURDATE(), INTERVAL 6 MONTH)
WHERE l.leave_id IS NULL;