Interview questions on emp and dept table (part 3)

  Frequently asked questions about the emp (employee) and dept (department) tables, along with examples of SQL queries for each:

Click the link to find the Emp and dept table records

Find the difference between the highest and lowest salary in each department.

SELECT d.dname, MAX(e.sal) - MIN(e.sal) AS salary_range
FROM emp e
JOIN dept d ON e.deptno = d.deptno
GROUP BY d.dname;

Find employees who have a higher salary than their manager

SELECT e1.ename, e1.sal, e2.ename AS manager_name
FROM emp e1
JOIN emp e2 ON e1.mgr = e2.empno
WHERE e1.sal > e2.sal;

List all employees who have the same job title as a specific employee (e.g., 'SCOTT').

SELECT e1.ename, e1.job
FROM emp e1
JOIN emp e2 ON e1.job = e2.job
WHERE e2.ename = 'SCOTT';

Find the total commission for each department.

SELECT d.dname, SUM(e.comm) AS total_commission
FROM emp e
JOIN dept d ON e.deptno = d.deptno
GROUP BY d.dname;

List all employees and their department names who earn more than a certain amount (e.g., 1500).

SELECT e.ename, d.dname, e.sal
FROM emp e
JOIN dept d ON e.deptno = d.deptno
WHERE e.sal > 1500;

Find the department with the lowest number of employees

SELECT TOP 1 dept.dname, COUNT(emp.empno) AS num_employees
FROM dept
LEFT JOIN emp ON dept.deptno = emp.deptno
GROUP BY dept.dname
ORDER BY num_employees ASC;

List departments with no employees.

SELECT d.dname
FROM dept d
LEFT JOIN emp e ON d.deptno = e.deptno
WHERE e.deptno IS NULL;

List departments that have more than a specific number of employees (e.g., 2).

SELECT dept.dname, COUNT(emp.empno) AS num_employees
FROM dept
LEFT JOIN emp ON dept.deptno = emp.deptno
GROUP BY dept.dname
HAVING COUNT(emp.empno) > 2;

Click below links for more examples on Emp and dept table.
 Interview questions on emp and dept table (part 1)
Interview questions on emp and dept table (part 2)
Interview questions on emp and dept table (part 3)
Interview questions on emp and dept table (part 4)
Tricky interview questions employee and manager column in emp table
Tricky interview questions employee and manager column in emp table