Interview Q&A / SQL & DBMS

1. Write an SQL query to find the second highest salary.

   Find the highest salary that is less than the maximum, with SELECT MAX(salary) FROM employees WHERE salary < (SELECT MAX(salary) FROM employees). For the Nth highest, use DENSE_RANK() so that duplicate salaries share a rank, or ORDER BY DISTINCT salary with LIMIT and OFFSET.

   BeginnerIn-depthCoding3 min read
2. Write a query to list departments that have more than 5 employees.

   Group by department and filter groups with HAVING COUNT(*) > 5. WHERE cannot be used because the count exists only after grouping.

   BeginnerQuick answerCoding1 min read
3. How do you find rows in one table that have no matching row in another?

   Use LEFT JOIN and keep rows where the right table's key IS NULL, or use NOT EXISTS. Both are safer than NOT IN, which fails when the subquery returns a NULL.

   IntermediateQuick answerCoding1 min read
4. Write a query to calculate a running total of daily sales.

   Use SUM(amount) OVER (ORDER BY day) to get a cumulative sum. Add PARTITION BY to restart the total per group, and use ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW to be explicit.

   IntermediateQuick answerCoding1 min read
5. Write a query to find the highest-paid employee in each department.

   Compute MAX(salary) per department in a subquery and join back, or use RANK() OVER (PARTITION BY dept ORDER BY salary DESC) and keep rank 1. Both return all tied employees.

   IntermediateQuick answerCoding1 min read