Re: Sub-query issue on pre-test

You have analysed the inner query (inline view correctly). This gives you

Salary Commission
1600 300
1250 400

The outer query then says ignore departments who are 30 but have salary, commision combination of 1600, 300 or 1250, 400.

Empno 4 and 5 are both not in dept 30 and match these criteria.

To understand this properly you might create a view for

SELECT salary, commission
 FROM p_emp
 WHERE deptno = 30

and use it instead of the explicit sql. That way , you can interrogate the view, and hopefully understand what is happening

<spamintoreceptacle_at_yahoo.com> wrote in message news:qb3gpvs1g2a3u97b1rmjf0jgh50glopqo8_at_4ax.com...
> 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:29:19 CDT