Interview questions on emp and dept table (part 2)

 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

List employees who have the same hire date as a specific employee (e.g., 'ALLEN')

SELECT e1.ename, e1.hiredate
FROM emp e1
JOIN emp e2 ON e1.hiredate = e2.hiredate
WHERE e2.ename = 'ALLEN';

Find the average commission across all employees.

SELECT AVG(comm) AS avg_commission
FROM emp
WHERE comm IS NOT NULL;

Find all employees who are earning less than the average salary in their department and have a commission.

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
) AND e.comm IS NOT NULL;

List departments along with the highest paid employee in each department.

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

Find the employee who was hired most recently in each department.

SELECT e.deptno, e.ename, e.hiredate
FROM emp e
WHERE e.hiredate = (
    SELECT MAX(hiredate)
    FROM emp
    WHERE deptno = e.deptno
);

Find the department with the highest average salary.

SELECT d.dname, AVG(e.sal) AS avg_salary
FROM emp e
JOIN dept d ON e.deptno = d.deptno
GROUP BY d.dname
ORDER BY avg_salary DESC
LIMIT 1;

List all employees and their departments who were hired before a specific date (e.g., '1982-01-01').

SELECT e.ename, d.dname
FROM emp e
JOIN dept d ON e.deptno = d.deptno
WHERE e.hiredate < '1982-01-01';

Find the average salary of employees reporting to a specific manager (e.g., 7839).

SELECT AVG(e.sal) AS avg_salary
FROM emp e
WHERE e.mgr = 7839;

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