﻿ SQL Challenges-1: All People Report to the Given Manager - 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:

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