Interview questions on emp and dept table (part 4)

  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 department with the maximum 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 DESC;

List employees who earn the highest salary in their department.

SELECT e.ename, e.sal, d.dname
FROM emp e
JOIN dept d ON e.deptno = d.deptno
WHERE e.sal = (
    SELECT MAX(sal)
    FROM emp
    WHERE deptno = e.deptno
);

Find the number of employees in each department with salary greater than a specific amount (e.g., 1000).

SELECT dept.dname, COUNT(emp.empno) AS num_employees
FROM emp
JOIN dept ON emp.deptno = dept.deptno
WHERE emp.sal > 1000
GROUP BY dept.dname;

Find the total salary paid by each department.

SELECT dept.dname, SUM(emp.sal) AS total_salary
FROM emp
JOIN dept ON emp.deptno = dept.deptno
GROUP BY dept.dname;

Find the average salary of employees in each department.

SELECT dept.dname, AVG(emp.sal) AS avg_salary
FROM emp
JOIN dept ON emp.deptno = dept.deptno
GROUP BY dept.dname;

Find employees who earn more than the average salary in their department.

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

Find employees who earn more than the average salary of all employees.

SELECT ename, sal
FROM emp
WHERE sal > (SELECT AVG(sal) FROM emp);

Find employees who do not have a manager.

SELECT ename, job
FROM emp
WHERE mgr IS NULL;

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