CODEWITHSUNDEEP
sqldatabaseoracle-database
raju
raju

Reputation: 1

How to know departments without male employes

We have two tables

  1. Department
  2. Emp

Department

CREATE TABLE department
( 
    deptno int NOT NULL,
    dname  varchar2(50) NOT NULL,
    loc    varchar2(13)
);

INSERT INTO DEPT (DEPTNO, DNAME, LOC) 
VALUES (10, 'ACCOUNTING', 'NEW YORK');

INSERT INTO DEPT (DEPTNO, DNAME, LOC) 
VALUES (20, 'RESEARCH', 'DALLAS');

INSERT INTO DEPT (DEPTNO, DNAME, LOC) 
VALUES (30, 'SALES', 'CHICAGO');

INSERT INTO DEPT (DEPTNO, DNAME, LOC) 
VALUES (40, 'OPERATIONS', 'BOSTON');

CREATE TABLE emp
(   
    empno  number(4,0),   
    ename  varchar2(10),
    sex    varchar2(10),
    deptno number(2,0)
)

INSERT INTO emp   
VALUES (7839, 'KING', 'PRESIDENT', Male,  10);
INSERT INTO emp   
VALUES (7898, 'BLAKE', 'MANAGER', Male,  30);
INSERT INTO emp   
VALUES (7782, 'CLARK', 'MANAGER', Female,  10);
INSERT INTO emp   
VALUES (7566, 'JONES', 'ANALYST', Female,  20);
INSERT INTO emp   
VALUES (7788, 'SCOTT', 'ANALYST', Male,  20);
INSERT INTO emp   
VALUES (7902, 'FORD', 'ANALYST', Male,  20);
INSERT INTO emp   
VALUES (7369, 'SMITH', 'CLERK', Female,  20);
INSERT INTO emp   
VALUES (7369, 'Allen', 'Manager', Male,  40);

COMMIT;

Need a list of departments without a female employee....

Thanks Raju

I got a list of departments with female employees and removed them from the entire list.

select deptno 
from emp 
where deptno not in (select deptno 
                     from emp 
                     where sex = 'Female');

I wanted to know if there is a better way to do it

Upvotes: 0

Views: 56

Answers (2)

The Impaler
The Impaler

Reputation: 48850

You can use NOT EXISTS:

select *
from dept
where not exists (
  select 1 from emp where emp.deptno = dept.deptno and emp.sex = 'Female'
)

Or the easier to read NOT IN, though probably less performant:

select *
from dept
where deptno not in (select deptno from emp where sex = 'Female')

Upvotes: 1

Tonelock
Tonelock

Reputation: 53

Something like

SELECT 
    deptno,
    sum(CASE sex WHEN 'Female' THEN 1 ELSE 0 END) AS number_female
FROM emp
GROUP BY deptno;

might work. You would need to select those department numbers with number_females = 0 But apart from that I can not come up with a solution that is shorter than yours. The table department seems to be irrelevant in this case, unless you need department names.

Upvotes: 0

Related Questions