CODEWITHSUNDEEP
sqloracle-databasesubquery
Pugzly
Pugzly

Reputation: 934

Find departments with no employees

We were given an assignment in class to provide at the minimum two different solutions to find departments, which don't have any employees. As you can see from below I completed the task successfully.

An additional solution is extra credit, which I like to get. Unfortunately, I can't think of a third solution and was hoping someone can point me in the right direction. Would something with a MINUS function work?

Below is my setup and 2 working test cases. Any suggestions and help would be greatly appreciated.

CREATE TABLE departments
(
    department_id, 
    department_name
) AS
SELECT 1, 'IT' FROM DUAL UNION ALL
SELECT 3, 'Sales' FROM DUAL UNION ALL
SELECT 2, 'DBA' FROM DUAL;


CREATE TABLE employees 
(
    employee_id, 
    first_name, last_name, 
    hire_date, 
    salary,  
    department_id
) AS
SELECT 1, 'Lisa', 'Saladino', DATE '2001-04-03', 100000, 1 FROM DUAL UNION ALL
SELECT 2, 'Abby', 'Abbott', DATE '2001-04-04', 50000, 1 FROM DUAL UNION ALL
SELECT 3, 'Beth', 'Cooper', DATE '2001-04-05', 60000, 1 FROM DUAL UNION ALL
SELECT 4, 'Carol', 'Orr', DATE '2001-04-06', 70000,1 FROM DUAL UNION ALL
SELECT 5, 'Vicky', 'Palazzo', DATE '2001-04-07', 88000,2 FROM DUAL UNION ALL
SELECT 6, 'Cheryl', 'Ford', DATE '2001-04-08', 110000,1 FROM DUAL UNION ALL
SELECT 7, 'Leslee', 'Altman', DATE '2001-04-10', 66666, 1 FROM DUAL UNION ALL
SELECT 8, 'Jill', 'Coralnick', DATE '2001-04-11', 190000, 2 FROM DUAL UNION ALL
SELECT 9, 'Faith', 'Aaron', DATE '2001-04-17', 122000,2 FROM DUAL;

/* departments with no employees */

select 
    d.department_id,
    d.department_name
from 
    employees e 
right join 
    departments d on e.department_id = d.department_id
group by 
    d.department_id, d.department_name
having 
    count(e.employee_id) = 0;

Output:

DEPARTMENT_ID    DEPARTMENT_NAME
--------------------------------
      3          Sales

SELECT 
    d.department_id,
    d.department_name 
FROM 
    departments d
WHERE
    NOT EXISTS (SELECT * FROM employees e 
                WHERE d.department_id = e.department_id)

Output:

DEPARTMENT_ID    DEPARTMENT_NAME
--------------------------------
      3          Sales

Upvotes: 0

Views: 2094

Answers (2)

Ankit Bajpai
Ankit Bajpai

Reputation: 13527

You can try below query-

SELECT * 
  FROM departments
 WHERE department_id in (SELECT department_id
                           FROM departments
                          minus 
                         SELECT DISTINCT department_id
                           FROM employees
                        );

Demo.

Upvotes: 1

ahmed
ahmed

Reputation: 9201

You may also use left join as the following:

select 
     d.department_id,
     d.department_name
from departments d left join employees e 
on e.department_id=d.department_id
where e.employee_id is null

Also, you may use a sub query as the following:

select department_id, department_name
from departments
where department_id not in (select department_id from employees)

And with minus you may try:

select department_id, department_name
from departments
minus
select 
     d.department_id,
     d.department_name
from departments d join employees e 
on e.department_id=d.department_id

See a demo.

Upvotes: 2

Related Questions