SQL Challenges-1: Find departments with 6 or more employees

SQL Challenges-1: Exercise-61 with Solution

From the following table write a SQL query to find the departments where 6 or more employees are working. Return employees ID,name,designation and department ID.

Input:

Table: employees

Structure:

Data:

Sample Solution:

SQL Code(MySQL):

create table employees (
employee_id integer(4) not null unique,
emp_name varchar(25),
hire_date date,
job_id varchar(25),
salary decimal(10,2),
manager_id integer(4),
department_id integer(4)
);



insert into employees values( 100,'Steven     ','1987-06-17','AD_PRES   ',24000.00,         0,   90);
insert into employees values( 101,'Neena      ','1987-06-18','AD_VP     ',17000.00,       100,   90);
insert into employees values( 102,'Lex        ','1987-06-19','AD_VP     ',17000.00,       100,   90);
insert into employees values( 103,'Alexander  ','1987-06-20','IT_PROG   ', 9000.00,       102,   60);
insert into employees values( 104,'Bruce      ','1987-06-21','IT_PROG   ', 6000.00,       103,   60);
insert into employees values( 105,'David      ','1987-06-22','IT_PROG   ', 4800.00,       103,   60);
insert into employees values( 106,'Valli      ','1987-06-23','IT_PROG   ', 4800.00,       103,   60);
insert into employees values( 107,'Diana      ','1987-06-24','IT_PROG   ', 4200.00,       103,   60);
insert into employees values( 114,'Den        ','1987-07-01','PU_MAN    ',11000.00,       100,   30);
insert into employees values( 115,'Alexander  ','1987-07-02','PU_CLERK  ', 3100.00,       114,   30);
insert into employees values( 116,'Shelli     ','1987-07-03','PU_CLERK  ', 2900.00,       114,   30);
insert into employees values( 117,'Sigal      ','1987-07-04','PU_CLERK  ', 2800.00,       114,   30);
insert into employees values( 108,'Nancy      ','1987-06-25','FI_MGR    ',12000.00,       101,  100);
insert into employees values( 109,'Daniel     ','1987-06-26','FI_ACCOUNT', 9000.00,       108,  100);
insert into employees values( 110,'John       ','1987-06-27','FI_ACCOUNT', 8200.00,       108,  100);
insert into employees values( 111,'Ismael     ','1987-06-28','FI_ACCOUNT', 7700.00,       108,  100);
insert into employees values( 112,'Jose Manuel','1987-06-29','FI_ACCOUNT', 7800.00,       108,  100);
insert into employees values( 113,'Luis       ','1987-06-30','FI_ACCOUNT', 6900.00,       108,  100);
insert into employees values( 133,'Jason      ','1987-07-20','ST_CLERK  ', 3300.00,       122,   50);
insert into employees values( 134,'Michael    ','1987-07-21','ST_CLERK  ', 2900.00,       122,   50);
insert into employees values( 135,'Ki         ','1987-07-22','ST_CLERK  ', 2400.00,       122,   50);
insert into employees values( 136,'Hazel      ','1987-07-23','ST_CLERK  ', 2200.00,       122,   50);
insert into employees values( 137,'Renske     ','1987-07-24','ST_CLERK  ', 3600.00,       123,   50);
insert into employees values( 138,'Stephen    ','1987-07-25','ST_CLERK  ', 3200.00,       123,   50);
insert into employees values( 139,'John       ','1987-07-26','ST_CLERK  ', 2700.00,       123,   50);



SELECT employee_id,emp_name,job_id "Designation",department_id
FROM employees 
WHERE department_id IN (
SELECT department_id  
FROM employees 
GROUP BY department_id 
HAVING COUNT(DISTINCT employee_id) >= 6);

Sample Output:

employee_id|emp_name   |Designation|department_id|
-----------+-----------+-----------+-------------+
        108|Nancy      |FI_MGR     |          100|
        109|Daniel     |FI_ACCOUNT |          100|
        110|John       |FI_ACCOUNT |          100|
        111|Ismael     |FI_ACCOUNT |          100|
        112|Jose Manuel|FI_ACCOUNT |          100|
        113|Luis       |FI_ACCOUNT |          100|
        133|Jason      |ST_CLERK   |           50|
        134|Michael    |ST_CLERK   |           50|
        135|Ki         |ST_CLERK   |           50|
        136|Hazel      |ST_CLERK   |           50|
        137|Renske     |ST_CLERK   |           50|
        138|Stephen    |ST_CLERK   |           50|
        139|John       |ST_CLERK   |           50|

SQL Code Editor:

Contribute your code and comments through Disqus.

Previous: Find customers booked orders more than 3 times.
Next: Find highest sale amount that appears distinctly.