OCP sample test question -- I think the answer provided is incorrect
Date: Fri, 14 Feb 2003 17:40:32 -0600
Message-ID: <4rednTjOwMXz49CjXTWcqA_at_comcast.com>
SQL>
SQL> /* I came across a sample test question that I do not agree with the
supplied
DOC>answer. The question is: DOC> DOC>You issue the following command in Oracle: DOC> DOC>select e.ename, a.street_address, a.city, a.state, a.post_code DOC>from emp e, addr a DOC>where e.empno = a.empno(+) DOC>and state = 'TEXAS'; DOC> DOC> DOC>There are two 'close' answers: DOC> DOC>1) select e.ename, a.street_address, a.city, a.state, a.post_code DOC>from emp e left outer join addr a on e.empno = a.empno DOC>where a.state = 'TEXAS'; DOC> DOC>2) select e.ename, a.street_address, a.city, a.state, a.post_code DOC>from emp e right outer join addr a on e.empno = a.empno DOC>where a.state = 'TEXAS'; DOC> DOC>The answer key say 2 is correct, but I believe it is 1. To prove it tomyself
DOC>I made some scripts. If I am missing something let me know. DOC> DOC>Note the emp and dept tables are built from DOC> $ORACLE_HOME\rdbms\admin\utlsampl.sql DOC>*/ SQL> SQL> set echo on SQL> set feedback on SQL> SQL> /* just so we are on the same page */ SQL> select * from v$version;
BANNER
Oracle9i Enterprise Edition Release 9.2.0.1.0 - Production PL/SQL Release 9.2.0.1.0 - Production
CORE 9.2.0.1.0 Production
TNS for 32-bit Windows: Version 9.2.0.1.0 - Production NLSRTL Version 9.2.0.1.0 - Production
5 rows selected.
SQL>
SQL> drop table addr;
Table dropped.
SQL> SQL> /* this is for my testing of the outter join as you will see */ SQL> delete from emp where empno = 1001;
1 row deleted.
SQL>
SQL> commit;
Commit complete.
SQL>
SQL> create table addr
2 (
3 empno number(4),
4 street_address varchar2(30),
5 city varchar2(30),
6 state varchar2(30),
7 post_code varchar2(11),
8 constraint addr_fk foreign key (empno) references emp (empno) on delete
cascade
9 );
Table created.
SQL>
SQL> merge into addr a
2 using (select e.empno, CASE d.loc
3 WHEN 'NEW YORK' THEN 'NEW YORK' 4 WHEN 'DALLAS' THEN 'TEXAS' 5 WHEN 'CHICAGO' THEN 'ILLINOIS' 6 ELSE 'UNKNOWN' END as loc
7 from emp e left outer join dept d on e.DEPTNO = d.deptno) subq 8 on (subq.empno = a.empno)
9 when matched then update set a.state = subq.loc 10 when not matched then insert (empno,state) values (subq.empno,subq.loc);
14 rows merged.
SQL>
SQL> commit;
Commit complete.
SQL> SQL> /* Note I cut out the unneeded columns DOC> and I put a order by for easyer viewing */ SQL> SQL> /* here is the test question */ SQL> select e.ename, a.state
2 from emp e, addr a
3 where e.EMPNO = a.EMPNO (+)
4 and a.state = 'TEXAS'
5 order by 1;
ENAME STATE
---------- ------------------------------
ADAMS TEXAS FORD TEXAS JONES TEXAS SCOTT TEXAS SMITH TEXAS
5 rows selected.
SQL> SQL> /* here is (1) the answer I says is correct */ SQL> select e.ename, a.state
2 from emp e left outer join addr a on e.EMPNO = a.EMPNO 3 where a.state = 'TEXAS'
4 order by 1;
ENAME STATE
---------- ------------------------------
ADAMS TEXAS FORD TEXAS JONES TEXAS SCOTT TEXAS SMITH TEXAS
5 rows selected.
SQL> SQL> /* here is (2) the answer the test says is correct */ SQL> select e.ename, a.state
2 from emp e right outer join addr a on e.EMPNO = a.EMPNO 3 where a.state = 'TEXAS'
4 order by 1;
ENAME STATE
---------- ------------------------------
ADAMS TEXAS FORD TEXAS JONES TEXAS SCOTT TEXAS SMITH TEXAS
5 rows selected.
SQL> SQL> /* hum they all look the same so lets loosen it up a bit */ SQL> SQL> /* here is the test question */ SQL> select e.ename, a.state
2 from emp e, addr a
3 where e.EMPNO = a.EMPNO (+)
4 order by 1;
ENAME STATE
---------- ------------------------------
ADAMS TEXAS ALLEN ILLINOIS BLAKE ILLINOIS CLARK NEW YORK FORD TEXAS JAMES ILLINOIS JONES TEXAS KING NEW YORK MARTIN ILLINOIS MILLER NEW YORK SCOTT TEXAS ENAME STATE
---------- ------------------------------
SMITH TEXAS TURNER ILLINOIS WARD ILLINOIS
14 rows selected.
SQL> SQL> /* here is (1) the answer I says is correct */ SQL> select e.ename, a.state
2 from emp e left outer join addr a on e.EMPNO = a.EMPNO 3 order by 1;
ENAME STATE
---------- ------------------------------
ADAMS TEXAS ALLEN ILLINOIS BLAKE ILLINOIS CLARK NEW YORK FORD TEXAS JAMES ILLINOIS JONES TEXAS KING NEW YORK MARTIN ILLINOIS MILLER NEW YORK SCOTT TEXAS ENAME STATE
---------- ------------------------------
SMITH TEXAS TURNER ILLINOIS WARD ILLINOIS
14 rows selected.
SQL> SQL> /* here is (2) the answer the test says is correct */ SQL> select e.ename, a.state
2 from emp e right outer join addr a on e.EMPNO = a.EMPNO 3 order by 1;
ENAME STATE
---------- ------------------------------
ADAMS TEXAS ALLEN ILLINOIS BLAKE ILLINOIS CLARK NEW YORK FORD TEXAS JAMES ILLINOIS JONES TEXAS KING NEW YORK MARTIN ILLINOIS MILLER NEW YORK SCOTT TEXAS ENAME STATE
---------- ------------------------------
SMITH TEXAS TURNER ILLINOIS WARD ILLINOIS
14 rows selected.
SQL>
SQL> /* But the test question is dealing with an outter join so I added a
row
DOC>in emp that would have a NULL in addr. So my outter join would show DOC>something */ SQL> SQL> insert into emp (empno,ename) values (1001,'PETE');
1 row created.
SQL>
SQL> commit;
Commit complete.
SQL> SQL> /* here is the test question */ SQL> select e.ename, a.state
2 from emp e, addr a
3 where e.EMPNO = a.EMPNO (+)
4 order by 1;
ENAME STATE
---------- ------------------------------
ADAMS TEXAS ALLEN ILLINOIS BLAKE ILLINOIS CLARK NEW YORK FORD TEXAS JAMES ILLINOIS JONES TEXAS KING NEW YORK MARTIN ILLINOIS MILLER NEW YORK
PETE ENAME STATE
---------- ------------------------------
SCOTT TEXAS SMITH TEXAS TURNER ILLINOIS WARD ILLINOIS
15 rows selected.
SQL> SQL> /* here is (1) the answer I says is correct */ SQL> select e.ename, a.state
2 from emp e left outer join addr a on e.EMPNO = a.EMPNO 3 order by 1;
ENAME STATE
---------- ------------------------------
ADAMS TEXAS ALLEN ILLINOIS BLAKE ILLINOIS CLARK NEW YORK FORD TEXAS JAMES ILLINOIS JONES TEXAS KING NEW YORK MARTIN ILLINOIS MILLER NEW YORK
PETE ENAME STATE
---------- ------------------------------
SCOTT TEXAS SMITH TEXAS TURNER ILLINOIS WARD ILLINOIS
15 rows selected.
SQL> SQL> /* here is (2) the answer the test says is correct */ SQL> select e.ename, a.state
2 from emp e right outer join addr a on e.EMPNO = a.EMPNO 3 order by 1;
ENAME STATE
---------- ------------------------------
ADAMS TEXAS ALLEN ILLINOIS BLAKE ILLINOIS CLARK NEW YORK FORD TEXAS JAMES ILLINOIS JONES TEXAS KING NEW YORK MARTIN ILLINOIS MILLER NEW YORK SCOTT TEXAS ENAME STATE
---------- ------------------------------
SMITH TEXAS TURNER ILLINOIS WARD ILLINOIS
14 rows selected.
SQL> SQL> SQL> /* as you can see #2 does not return the row containing 'PETE'. DOC>So I think I am right and this is why. Am I missing something??? */ SQL> SQL> SQL> spool offReceived on Sat Feb 15 2003 - 00:40:32 CET