spamintoreceptacle_at_yahoo.com wrote:
> Thank you for your quick reply Chris, but I do not see HOW you got the
> answer - which is my issue.
In words it wants you to find the employees who have the same salary
and commission as employees in a different department.
Run the subselect and you get the salary and commision of employees
in dept 30
Run the main select and you get the empno of employees NOT in dept
30 but with the same salary and commission as any employee in dept 30.
>
> Thank you for your time.
>
> On Thu, 23 Oct 2003 18:57:11 +0200, "cf" <news_at_tolede.com> wrote:
>
>
>>Your request in correct.
>>
>>create table p_emp (EMPNO number, EMPNAME varchar2(20), DEPTNO number,
>>SALARY number, COMMISSION number)
>>/
>>insert into p_emp values (1,'Howe', 30, 1600, 300);
>>insert into p_emp values (2,'Wilkins', 10 ,1250 ,300);
>>insert into p_emp values (3,'Burton', 30 ,1250 ,400);
>>insert into p_emp values (4,'Mitchell', 10 ,1600 ,300 );
>>insert into p_emp values (5,'Wallace', 20,1250 ,400 );
>>insert into p_emp values (6,'Floyd', 20 ,1600 ,400 );
>>commit
>>/
>>SQL> SELECT empno
>
>>FROM p_emp
>
>>WHERE (salary, commission) IN
>> (SELECT salary, commission
>> FROM p_emp
>> WHERE deptno = 30)
>>AND deptno <> 30
>>/
>>
>>EMPNO
>>----------
>> 5
>> 4
>>
>>christophe.
>>
>><spamintoreceptacle_at_yahoo.com> a écrit dans le message de
>>news:c91gpvccmcolq80deh9qkq9nir8mebo8e3_at_4ax.com...
>>
>>>I'm hoping that the group will help me get my head on strainght here
>>>... I'm taking sample tests for my first Oracle 9i certification exam,
>>>and I'm pretty confident with the exception of a few examples that I
>>>can't seem to understand correctly.
>>>
>>>Here is the sample table p_emp:
>>>
>>>EMPNO EMPNAME DEPTNO SALARY COMMISSION
>>>1 Howe 30 1600 300
>>>2 Wilkins 10 1250 300
>>>3 Burton 30 1250 400
>>>4 Mitchell 10 1600
>>>300
>>>5 Wallace 20 1250 400
>>>6 Floyd 20 1600 400
>>>
>>>Here is the SQL code:
>>>
>>>SELECT empno
>>>FROM p_emp
>>>WHERE (salary, commission) IN
>>>(SELECT salary, commission
>>>FROM p_emp
>>>WHERE deptno = 30)
>>>AND deptno <> 30;
>>>
>>>The correct answer is EMPNO 4 and 5. However, I do not see that - I
>>>see that the answer is 'No rows are selected' which is one of the
>>>choices.
>>>
>>>Please help to correct my logic:
>>>
>>>1 - I look in the subquery FIRST and pick EMPNO's 1 and 3 because it
>>>is asking me to select those who are in department 30 (WHERE deptno =
>>>30)
>>>
>>>2 - I see that the main query is asking me to look in the list
>>>returned from the subquery and choose those from the list. Again, in
>>>the list I am bringing back those in department 30 - EMPNO's 1 and 3.
>>>
>>>3 - I look at the AND key word and determine that it does NOT want
>>>those within department 30 (AND deptno <> 30)
>>>
>>>So, my answer becomes 'No rows selected'
>>>
>>>Would someone be kind enough to pick apart my logic to tell me at
>>>which point I am deviating from arriving at the correct answer?
>>>
>>>I thank all for your help and patience.
>>>
>>>Kevin
>>
>
Received on Thu Oct 23 2003 - 12:31:02 CDT
