Re: Sub-query issue on pre-test

Exactly! You have it right on the head - this is what is confusing me.

On Thu, 23 Oct 2003 12:35:14 -0500, Turkbear <john.g_at_dot.spamfree.com> wrote:

>
>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:47:28 CDT