w3resource

SQL Challenges-1: All People Report to the Given Manager

SQL Challenges-1: Exercise-45 with Solution

From the following table write a SQL query to find all employees that directly or indirectly report to the head of the company. Return employee_id, name, and manager_id.

Input:

Table: emp_test_table

Structure:

FieldTypeNullKeyDefaultExtra
employee_idint(11)NOPRI
first_namevarchar(25)YES
manager_idint(11)YES

Data:

employee_idfirst_namemanager_id
100Steven100
101Neena100
102Lex100
103Alexander102
104Bruce103
105David103
106Valli103
107Diana103
108Nancy 101
109Daniel108
110John108

Sample Solution:

SQL Code(MySQL):

CREATE TABLE emp_test_table (
employee_id integer NOT NULL UNIQUE,
first_name varchar(25),
manager_id integer);


insert into emp_test_table values(100,'Steven     ',100); 
insert into emp_test_table values(101,'Neena      ',100); 
insert into emp_test_table values(102,'Lex        ',100); 
insert into emp_test_table values(103,'Alexander  ',102); 
insert into emp_test_table values(104,'Bruce      ',103); 
insert into emp_test_table values(105,'David      ',103); 
insert into emp_test_table values(106,'Valli      ',103); 
insert into emp_test_table values(107,'Diana      ',103); 
insert into emp_test_table values(108,'Nancy      ',101); 
insert into emp_test_table values(109,'Daniel     ',108); 
insert into emp_test_table values(110,'John       ',108); 



SELECT employee_id,first_name as Name, manager_id
FROM emp_test_table
WHERE manager_id in (SELECT employee_id from emp_test_table
WHERE manager_id in (SELECT employee_id from emp_test_table WHERE manager_id = 100))
AND employee_id != 100;

Sample Output:

employee_id|Name       |manager_id|
-----------|-----------|----------|
        101|Neena      |       100|
        102|Lex        |       100|
        103|Alexander  |       102|
        104|Bruce      |       103|
        105|David      |       103|
        106|Valli      |       103|
        107|Diana      |       103|
        108|Nancy      |       101|
        109|Daniel     |       108|
        110|John       |       108|

SQL Code Editor:


Contribute your code and comments through Disqus.

Previous: Average Selling Price.
Next: Students and Examinations.



SQL: Tips of the Day

How to select the nth row in a SQL database table?

Basically, PostgreSQL and MySQL supports the non-standard:

SELECT...
LIMIT y OFFSET x 

Oracle, DB2 and MSSQL supports the standard windowing functions:

SELECT * FROM (
  SELECT
    ROW_NUMBER() OVER (ORDER BY key ASC) AS rownumber,
    columns
  FROM tablename
) AS foo
WHERE rownumber <= n

Database: SQL

Ref: https://bit.ly/3zPxcD8