SQL Coding Questions

Hands-on SQL problems asked in interviews and written tests. Try each one yourself before reading the solution.

Problem 1: second highest salary

• Find the second highest distinct salary in the EMPLOYEE table:-
  SELECT MAX(SALARY) FROM EMPLOYEE WHERE SALARY < (SELECT MAX(SALARY) FROM EMPLOYEE);
• The inner query finds the top salary; the outer query finds the max of everything below it.

Problem 2: duplicate employee numbers

• Find EMPNO values that appear more than once:-
  SELECT EMPNO, COUNT(*) FROM EMPLOYEE GROUP BY EMPNO HAVING COUNT(*) > 1;
• GROUP BY collects identical values and HAVING filters the groups. Remove the HAVING to see all counts.

Problem 3: employees earning more than their manager

• List employees whose salary is greater than their manager's salary (self join):-
  SELECT E.EMPNO, E.FIRSTNME, E.SALARY FROM EMPLOYEE E, EMPLOYEE M WHERE E.MGRNO = M.EMPNO AND E.SALARY > M.SALARY;
• The table is joined to itself: E is the employee, M is the manager.

Problem 4: departments with no employees

• List departments that have no employees, using NOT EXISTS:-
  SELECT DEPTNO, DEPTNAME FROM DEPARTMENT D WHERE NOT EXISTS (SELECT 1 FROM EMPLOYEE E WHERE E.WORKDEPT = D.DEPTNO);
• NOT EXISTS is usually faster than NOT IN because it stops at the first match and handles NULLs safely.

Problem 5: top 3 earners per department

• Show the top 3 salaries in each department with a window function:-
  SELECT WORKDEPT, FIRSTNME, SALARY FROM (SELECT WORKDEPT, FIRSTNME, SALARY, ROW_NUMBER() OVER (PARTITION BY WORKDEPT ORDER BY SALARY DESC) AS RN FROM EMPLOYEE) T WHERE RN <= 3;
• PARTITION BY restarts the numbering for each department; change ROW_NUMBER to RANK to allow ties.