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. 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
4. 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