Re: Sub-query issue on pre-test

Hi,
The 2 where conditions can be confusing ( at least they confused me):

!1st criteria:WHERE (salary, commission) IN (SELECT salary, commission FROM p_emp  WHERE deptno = 30
 this returns salary and commission from those who ARE in dept 30.

2und criteria : AND deptno <> 30
This results in a selection of those employees NOT in dept 30 who have the SAME salary and commission as those who ARE IN dept 30.

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.
>
>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
>>

----== Posted via Newsfeed.Com - Unlimited-Uncensored-Secure Usenet News==---- http://www.newsfeed.com The #1 Newsgroup Service in the World! >100,000 Newsgroups ---= 19 East/West-Coast Specialized Servers - Total Privacy via Encryption =--- Received on Thu Oct 23 2003 - 12:35:14 CDT