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

Tricky Interview questions: Employee and manager hierarchy within an emp table

 To analyze the employee (emp) and manager (mgr) hierarchy within an emp table, you typically want to understand the reporting structure and how employees are organized under their managers. Here are some SQL queries and techniques to explore the hierarchical relationships:

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

Display the hierarchy of employees and their managers.

This query shows each employee and their direct manager:

SELECT e1.ename AS employee_name, e2.ename AS manager_name
FROM emp e1
LEFT JOIN emp e2 ON e1.mgr = e2.empno;

List employees with their level in the hierarchy.

To calculate hierarchy levels, use a recursive common table expression (CTE):

WITH Hierarchy AS (
    SELECT empno, ename, mgr, 1 AS level
    FROM emp
    WHERE mgr IS NULL
    
    UNION ALL
    
    SELECT e.empno, e.ename, e.mgr, h.level + 1
    FROM emp e
    INNER JOIN Hierarchy h ON e.mgr = h.empno
)
SELECT empno, ename, mgr, level
FROM Hierarchy;

Find the top-level managers (those without managers).

SELECT ename
FROM emp
WHERE mgr IS NULL;

Find the chain of command for a specific employee.

To see the full chain of command for an employee (e.g., employee with empno 7566), use a recursive CTE:

WITH ChainOfCommand AS (
    SELECT empno, ename, mgr
    FROM emp
    WHERE empno = 7566
    
    UNION ALL
    
    SELECT e.empno, e.ename, e.mgr
    FROM emp e
    INNER JOIN ChainOfCommand c ON e.empno = c.mgr
)
SELECT empno, ename, mgr
FROM ChainOfCommand;

Find employees who have no subordinates.

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

Determine the number of direct and indirect reports for each manager.

This query counts all direct and indirect reports for each manager:

WITH EmployeeHierarchy AS (
    SELECT empno, mgr
    FROM emp
    
    UNION ALL
    
    SELECT e.empno, eh.mgr
    FROM emp e
    JOIN EmployeeHierarchy eh ON e.mgr = eh.empno
)
SELECT mgr, COUNT(DISTINCT empno) AS total_reports
FROM EmployeeHierarchy
GROUP BY mgr;

Find the longest chain of command in the organization.

To determine the maximum depth of the reporting structure:

WITH Hierarchy AS (
    SELECT empno, mgr, 1 AS depth
    FROM emp
    WHERE mgr IS NULL
    
    UNION ALL
    
    SELECT e.empno, e.mgr, h.depth + 1
    FROM emp e
    INNER JOIN Hierarchy h ON e.mgr = h.empno
)
SELECT MAX(depth) AS longest_chain
FROM Hierarchy;

Identify managers who have more than one level of subordinates.

WITH Hierarchy AS (
    SELECT empno, mgr, 1 AS level
    FROM emp
    WHERE mgr IS NULL
    
    UNION ALL
    
    SELECT e.empno, e.mgr, h.level + 1
    FROM emp e
    INNER JOIN Hierarchy h ON e.mgr = h.empno
)
SELECT mgr
FROM Hierarchy
GROUP BY mgr
HAVING MAX(level) > 1;

List all employees with their total number of direct reports (excluding indirect reports).

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

Recursive CTEs: These are essential for traversing hierarchical data where each record points to a parent or manager.
Handling Cyclic Hierarchies: Ensure your data does not have cyclic relationships, or use specific database features to handle them.

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