Sub-query issue on pre-test

From: <spamintoreceptacle_at_yahoo.com>
Date: Thu, 23 Oct 2003 12:45:37 -0400
Message-ID: <c91gpvccmcolq80deh9qkq9nir8mebo8e3@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:45:37 CDT