Tricky interview questions employee and manager column in emp table

 Frequently asked Tricky interview questions employee and manager column in emp table, along with examples of SQL queries for each:

Click the link to find the Emp and dept table records

Find employees who manage themselves (i.e., their mgr ID is their own empno).

SELECT ename, empno
FROM emp
WHERE empno = mgr;

Find employees who have the same manager as another employee with the same job title

SELECT e1.ename AS emp1, e2.ename AS emp2, e1.job
FROM emp e1
JOIN emp e2 ON e1.mgr = e2.mgr AND e1.job = e2.job
WHERE e1.empno != e2.empno;

List employees who have a higher salary than their manager.

SELECT e1.ename AS emp_name, e1.sal AS emp_salary, e2.ename AS mgr_name, e2.sal AS mgr_salary
FROM emp e1
JOIN emp e2 ON e1.mgr = e2.empno
WHERE e1.sal > e2.sal;

Identify employees who report to the same manager as an employee with a specific job title (e.g., 'CLERK').

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

List employees who report to a manager who has no subordinates.

SELECT e1.ename, e1.mgr
FROM emp e1
LEFT JOIN emp e2 ON e1.mgr = e2.empno
WHERE e2.empno IS NULL;

Find the second highest salary among employees who report to the same manager.

SELECT e1.mgr, MAX(e1.sal) AS second_highest_salary
FROM emp e1
WHERE e1.sal < (
    SELECT MAX(e2.sal)
    FROM emp e2
    WHERE e1.mgr = e2.mgr
)
GROUP BY e1.mgr;

Find the average salary of employees who report to each manager and list the manager names.

SELECT e2.ename AS mgr_name, AVG(e1.sal) AS avg_salary
FROM emp e1
JOIN emp e2 ON e1.mgr = e2.empno
GROUP BY e2.ename;

List all employees who have a higher salary than all their direct subordinates.

SELECT e1.ename
FROM emp e1
WHERE NOT EXISTS (
    SELECT 1
    FROM emp e2
    WHERE e2.mgr = e1.empno AND e2.sal >= e1.sal
);

Find employees who have the same job title as their manager.

SELECT e1.ename AS emp_name, e1.job AS emp_job, e2.ename AS mgr_name, e2.job AS mgr_job
FROM emp e1
JOIN emp e2 ON e1.mgr = e2.empno
WHERE e1.job = e2.job;

Identify managers who manage employees in more than one department.

SELECT e1.ename AS mgr_name
FROM emp e1
JOIN emp e2 ON e1.empno = e2.mgr
JOIN dept d1 ON e2.deptno = d1.deptno
GROUP BY e1.ename
HAVING COUNT(DISTINCT d1.deptno) > 1;

List employees who were hired in the same year as their manager.

SELECT e1.ename AS emp_name, e2.ename AS mgr_name, e1.hiredate AS emp_hiredate, e2.hiredate AS mgr_hiredate
FROM emp e1
JOIN emp e2 ON e1.mgr = e2.empno
WHERE YEAR(e1.hiredate) = YEAR(e2.hiredate);

Find managers who have employees with salaries both below and above a specific threshold (e.g., 2000).

SELECT DISTINCT e1.ename AS mgr_name
FROM emp e1
JOIN emp e2 ON e1.empno = e2.mgr
WHERE e2.sal < 2000
AND e2.mgr IN (
    SELECT e2.mgr
    FROM emp e2
    WHERE e2.sal >= 2000
);

Find the highest salary of employees managed by each manager, and list those managers.

SELECT e1.ename AS mgr_name, MAX(e2.sal) AS highest_salary
FROM emp e1
JOIN emp e2 ON e1.empno = e2.mgr
GROUP BY e1.ename;

List all managers and the number of employees they manage, including managers with zero subordinates.

SELECT e1.ename AS mgr_name, COUNT(e2.empno) AS num_subordinates
FROM emp e1
LEFT JOIN emp e2 ON e1.empno = e2.mgr
GROUP BY e1.ename;

Find employees who have the same hire date as their manager but are paid more.

SELECT e1.ename AS emp_name, e2.ename AS mgr_name, e1.sal AS emp_salary, e2.sal AS mgr_salary
FROM emp e1
JOIN emp e2 ON e1.mgr = e2.empno
WHERE e1.hiredate = e2.hiredate AND e1.sal > e2.sal;

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