Home » SQL & PL/SQL » SQL & PL/SQL » Remove the department in dept table where dept does not have any employees?
Remove the department in dept table where dept does not have any employees? [message #612183] Mon, 14 April 2014 04:03 Go to next message
kiranrathodkr916
Messages: 32
Registered: March 2014
Location: India
Member
Remove the department in dept table where dept does not have any employees?

my solution

delete dept where deptno  in(select deptno from emp where empno is null) ;


the given solution is

delete dept where deptno not in(select deptno from emp where deptno is not null) ;

is my solution correct
Re: Remove the department in dept table where dept does not have any employees? [message #612184 is a reply to message #612183] Mon, 14 April 2014 04:06 Go to previous messageGo to next message
John Watson
Messages: 4568
Registered: January 2010
Location: Global Village
Senior Member
orclz> delete dept where deptno  in(select deptno from emp where empno is null) ;

0 rows deleted.

orclz> delete dept where deptno not in(select deptno from emp where deptno is not null) ;

1 row deleted.

orclz>
Do you think your solution is correct?

You need to consider that EMPNO is a primary key. So will your subquery ever select any rows?








Re: Remove the department in dept table where dept does not have any employees? [message #612185 is a reply to message #612183] Mon, 14 April 2014 04:08 Go to previous messageGo to next message
ThomasG
Messages: 3099
Registered: April 2005
Location: Heilbronn, Germany
Senior Member
Your solution is wrong.

If a department has no employees, then there is no entry with that deptno in emp so you can't select that deptno from there.
Re: Remove the department in dept table where dept does not have any employees? [message #612189 is a reply to message #612185] Mon, 14 April 2014 05:40 Go to previous messageGo to next message
gauravgautam135
Messages: 31
Registered: December 2013
Member
Hi,

Please see below approach and let me know if this can work.

SQL> WITH emp AS
  2  (SELECT 7369 empno,'SMITH' ename, 20 deptno  FROM dual
  3   UNION ALL
  4   SELECT 7499, 'ALLEN', 30  FROM dual
  5   UNION ALL
  6   SELECT 7521, 'WARD',  30  FROM dual
  7   UNION ALL
  8   SELECT 7566, 'JONES', 20  FROM dual
  9   UNION ALL
 10   SELECT 7654, 'MARTIN', 30  FROM dual
 11   UNION ALL
 12   SELECT 7698, 'BLAKE', 30  FROM dual
 13   UNION ALL
 14   SELECT 7782, 'CLARK', 10  FROM dual
 15   UNION ALL
 16   SELECT 7788, 'SCOTT', 30  FROM dual
 17   UNION ALL
 18   SELECT 7839, 'KING', 10  FROM dual),
 19  dept AS
 20  (SELECT 10 deptno FROM dual
 21   UNION ALL 
 22   SELECT 20 FROM dual
 23   UNION ALL
 24   SELECT 30 FROM dual
 25   UNION ALL
 26   SELECT 40 FROM dual)
 27  SELECT d.deptno 
 28    FROM dept d 
 29   WHERE NOT EXISTS (SELECT 1
 30                       FROM emp e
 31                      WHERE d.deptno = e.deptno); 

    DEPTNO
----------
        40

SQL> 

[Updated on: Mon, 14 April 2014 05:41]

Report message to a moderator

Re: Remove the department in dept table where dept does not have any employees? [message #612194 is a reply to message #612189] Mon, 14 April 2014 06:26 Go to previous message
cookiemonster
Messages: 10961
Registered: September 2008
Location: Rainy Manchester
Senior Member
Yes that works too.
Previous Topic: Remove all entries except last one
Next Topic: Delete all levels of employees those who are under BLAKE?
Goto Forum:
  


Current Time: Sat Sep 20 10:00:34 CDT 2014

Total time taken to generate the page: 0.09577 seconds