Archive

Archive for February, 2014

Employee Id, Employee Name and Manager Id Self Join SQL Query as Interview Question

February 10, 2014 7 comments

One of the most common SQL interview question related to Self Join is that one Employee table with three columns Employee Id, Employee Name and Manager Id.

The interviewer may ask you questions like the following:

  1. Find the employees who are managers.
  2. Find the managers with the count of subordinates
  3. Find the  managers with the count of subordinates greater than 1 (or any other number)

Lets try resolve this  with the basic understanding – what is Self Join?: “A self join is a join in which a table is joined with itself (which is also called Unary relationships), specially when the table has a FOREIGN KEY which references its own PRIMARY KEY. To join a table itself means that each row of the table is combined with itself and with every other row of the table.” – W3Reaource

At first create an Employee table and insert some dummy records into it. Please refer to the  CREATE and INSERT statements below:

Create Employee Table:

CREATE TABLE employees (

emp_id int(11) NOT NULL,
emp_name varchar(45) DEFAULT NULL,
mng_id int(11) DEFAULT NULL,
PRIMARY KEY (emp_id)
)

Insert Dummy Records:

INSERT INTO employees (emp_id,emp_name,mng_id) VALUES (1,'Joe','2');
INSERT INTO employees (emp_id,emp_name,mng_id) VALUES (2,'Green',NULL);
INSERT INTO employees (emp_id,emp_name,mng_id) VALUES (3,'Jen','2');
INSERT INTO employees (emp_id,emp_name,mng_id) VALUES (4,'Brown','1');
INSERT INTO employees (emp_id,emp_name,mng_id) VALUES (5,'Harry','1');
INSERT INTO employees (emp_id,emp_name,mng_id) VALUES (6,'Jenny','4');
  1. SQL Statement for “Find the employees who are managers”:
    SELECT DISTINCT e.emp_id AS 'mng_id', e.emp_name AS 'mng_name'
    FROM employees e, employees m WHERE e.emp_id = m.mng_id
    

    In this query you can see the join condition  e.emp_id = m.mng_id where both e and m aliases are of Employees table.

  2. SQL Statement for “Find the managers with the count of subordinates”:
    SELECT COUNT(emp.emp_id) AS 'emp_count', mng.mng_name AS 'mng_name'
    FROM employees emp,
    (SELECT DISTINCT e.emp_id AS 'mng_id', e.emp_name AS 'mng_name'
    FROM employees e, employees m
    WHERE e.emp_id = m.mng_id) mng
    WHERE emp.mng_id = mng.mng_id
    GROUP BY mng.mng_id
    

    In this query we are getting all the managers’ names with their subordinates counts.

  3. SQL Statement for “Find the managers with the count of subordinates greater than 1 (or any other number)”:

    SELECT COUNT(emp.emp_id) AS 'emp_count', mng.mng_name AS 'mng_name'
    FROM employees emp,
    (SELECT DISTINCT e.emp_id AS 'mng_id', e.emp_name AS 'mng_name'
    FROM employees e, employees m
    WHERE e.emp_id = m.mng_id) mng
    WHERE emp.mng_id = mng.mng_id
    GROUP BY mng.mng_id HAVING COUNT(emp.emp_id) > 1;
    

    In this query we need to just add additional Having clause to find out all the managers who have more than specific number of employees.

I hope this post would be helpful for you. Please feel free to leave your comments below in case if you have any suggestion or if you have better optimized solution.

Advertisements
Categories: SQL