| Remove least paid employee who are reporting to BLAKE ? [message #611654] |
Sun, 06 April 2014 07:32  |
 |
kiranrathodkr916
Messages: 36
Registered: March 2014
Location: India
|
Member |
|
|
Remove least paid employee who are reporting to BLAKE ?
my solution
delete emp where sal = (select min(sal) from emp where mgr =
(select empno from emp where ename = 'BLAKE')
the correct solution is
delete emp where sal = (select min(sal) from emp where mgr =
(select empno from emp where ename = 'BLAKE')) and
ename in(select ename from emp where mgr =
(select empno from emp where ename = 'BLAKE'))
here the question why my solution is wrong.
1)find out who is the least paid employee who are reporting to BLAKE
(select min(sal) from emp where mgr =
(select empno from emp where ename = 'BLAKE')
2)Remove least paid employee who are reporting to BLAKE ?
my solution
so ,
delete emp where sal = (select min(sal) from emp where mgr =
(select empno from emp where ename = 'BLAKE')
--1 row deleted
delete emp where sal = (select min(sal) from emp where mgr =
(select empno from emp where ename = 'BLAKE')) and
ename in(select ename from emp where mgr =
(select empno from emp where ename = 'BLAKE'))
----1 row deleted |
|
|
|
| Re: Remove least paid employee who are reporting to BLAKE ? [message #611655 is a reply to message #611654] |
Sun, 06 April 2014 07:48  |
John Watson
Messages: 8931
Registered: January 2010
Location: Global Village
|
Senior Member |
|
|
What if two employees have the same SAL? Which will you delete?
Furthermore, if you want to get top marks for this question, you need to consider that ENAME is not a unique column. What will happen if there are two employees named BLAKE? I'll tell you: the queries will fail. How should you handle this situation?
|
|
|
|
|
|
| Re: Remove least paid employee who are reporting to BLAKE ? [message #611657 is a reply to message #611654] |
Sun, 06 April 2014 08:25 |
Solomon Yakobson
Messages: 3273
Registered: January 2010
Location: Connecticut, USA
|
Senior Member |
|
|
Neither solution is correct. First of all, as John already noted, multiple employees can have same lowest salary and since both solutions use = Oracle will raise ORA-01427: single-row subquery returns more than one row. So you need to adjust solution depending if we want to delete all or one (any) such employee. Now why is your solution incorrect?
select min(sal) from emp where mgr = (select empno from emp where ename = 'BLAKE')
will select lowest salary among employees reporting to BLAKE. Assume it is S1000. Then you delete all employees who have that $1000 salary regardless who that employee reports to. So if some employee reporting to JONES earns $1000 your code will delete that employee too.
SY.
|
|
|
|
| Re: Remove least paid employee who are reporting to BLAKE ? [message #611658 is a reply to message #611656] |
Sun, 06 April 2014 08:38 |
John Watson
Messages: 8931
Registered: January 2010
Location: Global Village
|
Senior Member |
|
|
kiranrathodkr916 wrote on Sun, 06 April 2014 14:19just wanted to know a what would be a better solution,as you said ename is not an ideal choice It is more than "not an ideal choice". Your solution is an excellent example of bugs that can have the most horrific effect in production code. In test, your solution works. But then it goes live. And some time in the future, either it fails with an error because there are two BLAKEs (annoying, but it can be debugged) or (much worse) it deletes many rows that are nothing to do with BLAKE, because the employees just happen to have the same salary. This sort of error will be undetected and disastrous.
|
|
|
|
|
|
| Re: Remove least paid employee who are reporting to BLAKE ? [message #611664 is a reply to message #611659] |
Sun, 06 April 2014 13:27 |
 |
EdStevens
Messages: 1376
Registered: September 2013
|
Senior Member |
|
|
kiranrathodkr916 wrote on Sun, 06 April 2014 08:53how to avoid this?how the code can be rewritten assuming that there are two BLAKE's
You obviously have to have a method of destinquishing between multiple Blakes and deciding which applies. What does the nature of your data .. the definition of the table being selected ... suggest.
Since this is obviously a homework question, you shouldn't expect anyone to provide a complete, debugged solution. You should take what has been pointed out and asked of you to try to get a solution yourself. At the very least, show an attempt to do so.
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
