Re: Sub-query issue on pre-test

From: cf <news_at_tolede.com>
Date: Thu, 23 Oct 2003 18:57:11 +0200
Message-ID: <3f980868$0$243$4d4eb98e@read.news.fr.uu.net>

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 - 11:57:11 CDT