20 Medium-Level SQL Interview Questions and Answers

Round 1

Questions:

1. Write a SQL query to find the second-highest salary. Query:

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

   Explanation: This query finds the maximum salary that is less than the highest salary.

2. How would you optimize a slow SQL query? Answer:

   • Use indexing to speed up searches.
   • Avoid SELECT *, instead select only required columns.
   • Use JOINs wisely instead of subqueries.
   • Analyze execution plans to identify bottlenecks.
   • Normalize or denormalize data as per query requirements.

3. What is the difference between INNER JOIN and OUTER JOIN? Answer:

   • INNER JOIN: Returns only matching rows between tables.
   • OUTER JOIN: Returns matching rows and includes unmatched rows (LEFT, RIGHT, FULL JOIN).

4. Write a SQL query to find the top 3 departments with the highest average salary. Query:

   SELECT department_id, AVG(salary) AS avg_salary
   FROM employees
   GROUP BY department_id
   ORDER BY avg_salary DESC
   LIMIT 3;

   Explanation: This query groups employees by department and sorts them by average salary in descending order, limiting results to 3.

5. How do you handle duplicate rows in a SQL query? Answer:

   • Use DISTINCT to remove duplicates:

   SELECT DISTINCT column1, column2 FROM table_name;

   • Use ROW_NUMBER() to identify duplicates and remove them.

6. Write a SQL query to find employees who have the same name and work in the same department. Query:

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

   Explanation: Groups employees by name and department, filters those with counts greater than 1.

7. What is the difference between UNION and UNION ALL? Answer:

   • UNION: Combines results and removes duplicates.
   • UNION ALL: Combines results without removing duplicates (faster).

8. Write a SQL query to find departments with no employees. Query:

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

   Explanation: Uses LEFT JOIN to find unmatched rows in the departments table.

9. How do you use indexing to improve SQL query performance? Answer:

   • Index columns frequently used in WHERE, JOIN, or ORDER BY clauses.
   • Avoid indexing columns with low cardinality (few unique values).
   • Use composite indexes for queries involving multiple columns.

10. Write a SQL query to find employees who have worked for more than 5 years. Query:

    SELECT *
    FROM employees
    WHERE DATEDIFF(CURDATE(), hire_date) > 365 * 5;

    Explanation: Calculates the difference between the current date and hire date.

11. What is the difference between SUBQUERY and JOIN? Answer:

    • SUBQUERY: Nested query executed first, result used in outer query.
    • JOIN: Combines data from multiple tables in a single query.

12. Write a SQL query to find the top 2 products with the highest sales. Query:

    SELECT product_id, SUM(sales) AS total_sales
    FROM orders
    GROUP BY product_id
    ORDER BY total_sales DESC
    LIMIT 2;

    Explanation: Groups products by sales and sorts them in descending order.

13. How do you use stored procedures to improve SQL query performance? Answer:

    • Pre-compiled code improves execution speed.
    • Reduces network traffic by processing logic on the server.
    • Allows reuse and modular design for complex operations.

14. Write a SQL query to find customers who placed an order but have not made a payment. Query:

    SELECT c.customer_id
    FROM customers c
    LEFT JOIN payments p ON c.customer_id = p.customer_id
    WHERE p.payment_id IS NULL;

    Explanation: Finds unmatched rows using LEFT JOIN.

15. What is the difference between GROUP BY and HAVING? Answer:

    • GROUP BY: Groups rows.
    • HAVING: Filters groups based on conditions (used after GROUP BY).

16. Write a SQL query to find employees who work in the same department as their manager. Query:

    SELECT e.employee_id, e.name
    FROM employees e
    JOIN employees m ON e.department_id = m.department_id
    WHERE e.manager_id = m.employee_id;

    Explanation: Self-join to match employees and managers by department.

17. How do you use window functions to solve complex queries? Answer:

    • Window functions like RANK(), ROW_NUMBER(), and SUM() operate over partitions without grouping rows. Example:

    SELECT name, salary, RANK() OVER (PARTITION BY department_id ORDER BY salary DESC) AS rank
    FROM employees;

18. Write a SQL query to find the top 3 products with the highest average price. Query:

    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? Answer:

    • TRUNCATE: Removes all rows, faster, cannot be rolled back.
    • DELETE: Removes specific rows, slower, can be rolled back.

20. Write a SQL query to find employees who have not taken any leave in the last 6 months. Query:

    SELECT e.employee_id
    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;

    Explanation: Uses LEFT JOIN to find employees without recent leave records.

These questions cover a variety of SQL concepts to help you prepare for your interview confidently. Practice these queries and understand their logic, and you’ll be ready to tackle most SQL challenges during an interview!