Re: Sub-query issue on pre-test

Thank you for your quick reply Chris, but I do not see HOW you got the answer - which is my issue.

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:19:32 CDT