| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Usenet -> c.d.o.server -> Errors in OCA Associate DBA certification book?
Hi,
I think I've discovered two problems in the OCA Oracle9i Associate DBA Certification Exam Guide. Or maybe the problems are with me, not with the book. I've tried writing to one of the authors, Sudheer Marisetti, but have received no reply. Please help me make sure I understand what's written in the book. This mail is going to be rather long, sorry about that.
I have some experience with SQL (mostly DB2 and some MySQL, and also had a bit of very old Oracle on VMS at university).
#1: Outer join
-- select e.ename, e.deptno, d.name -- from dept d, emp e -- where d.deptno (+) = e.deptno;
This is OK: as described previously, 'd.deptno (+)' means that the deptno column in d may be null (may not exist), and the engine is to return all rows from e, even those for which there is no matching row in d. KING comes from such a row.
Then it goes on to say (p. 113): 'Earlier, we placed the (+) symbol next to D.DEPTNO on the left side of the join comparison. [...] ANSI/ISO replaces the outer join operator with the left outer join tablename clause.' I feel this is not quite true: on the left hand side of the LEFT OUTER JOIN we have e, not d! A LEFT OUTER JOIN (quoting the DB2 Certification Guide) 'produces an answer set that includes the matching values of both joined tables and those values only present in the left joined table' (i.e. NULL values may appear in data coming from the table on the right-hand side of the operator).
The same is then repeated for RIGHT OUTER JOIN.
On page 115, the book includes a summary table (Table 3-1), which says:
Oracle syntax ISO syntax from tab_a a, tab_b b from tab_a a left outer join where a.col_1 (+) = b.col_1 tab_b on a.col_1 = b.col_1
I feel this is in error. The example quoted at the beginning of the
e-mail could be re-written as:
-- select e.ename, e.deptno, d.name -- from dept d, emp e -- where e.deptno = d.deptno (+);
Notice the third comment line, which has been changed. It clearly shows that if tables are listed in the same order with the Oracle syntax (i.e. the (+) operator) and the ISO syntax (LEFT OUTER JOIN), then the (+) operator has to go on the RIGHT side of the comparison.
Question 4 on page 117 is related to this. According to the solution
key,
---
from emp e, addr a
where e.empno = a.empno (+)
---
has to be re-written as option C:
---
from emp e right outer join addr a
on e.empno = a.empno
---
I feel this is not right. I created a short script which has an
analogous setup, and uses the sample database which has no addr table.
The only difference is that I used the dept table and modified the join
field (as deptno has no empno column):
---
select e.ename, a.deptno
from emp e, dept a
where e.deptno = a.deptno (+)
order by ename, deptno;
select e.ename, a.deptno
from emp e left outer join dept a
on e.deptno = a.deptno
order by ename, deptno;
select e.ename, a.deptno
from dept a right outer join emp e
on e.deptno = a.deptno
order by ename, deptno;
---
All of those commands produce the same result:
---
ENAME DEPTNO
ADAMS 20 ALLEN 30 BLAKE 30 CLARK 10 FORD 20 JAMES 30 JONES 20 KING MARTIN 30 MILLER 10 SCOTT 20 SMITH 20 TURNER 30 WARD 30
However, the (+) sign goes with table dept (alias 'a'), so dept ('a') has to appear on the right-hand side of a LEFT OUTER JOIN operator or the left-hand-side of a RIGHT OUTER JOIN operator.
Therefore, I think table 3-1 has to be corrected as follows:
Oracle syntax ISO syntax
from tab_a a, tab_b b from tab_a a right outer join
where a.col_1 (+) = b.col_1 tab_b on a.col_1 = b.col_1
OR
from tab_b b left outer join
tab_a on a.col_1 = b.col_1
from tab_a a, tab_b b from tab_a a left outer join
where a.col_1 = b.col_1 (+) tab_b on a.col_1 = b.col_1
OR
from tab_b b right outer join
tab_a on a.col_1 = b.col_1
The third row of the table, which contains an attribute match as well as the join should also be corrected. The solution key (on page 118) of question 4 (page 117) should be changed to B, LEFT OUTER JOIN.
#2: GROUP BY
SELECT deptno, AVG(sal)
FROM emp
GROUP BY empno;
I think deptno should be included in the group by clause, as AVG() is an group function, and thus will return a single value.
The official key says:
---
C: GROUP BY empno;
Explanation: Because the empno column does not appear in the original
list of columns to be displayed by the query, it cannot be used in a
GROUP BY statement. [...] if you removed the GROUP BY expression, the
query would execute correctly [...]
---
This contradicts (twice) what's written on page 127 as a tip:
---
All columns in the column clause of your SELECT statement that are not
in a group function must be listed in the GROUP BY clause [1st
contradiction: deptno is not in a group function, and thus should be in
the GROUP BY clause]. However, a column listed in the GROUP BY clause
needn't appear in the column clause [2nd contradiction: 'Because the
empno column does not appear in the original list of columns to be
displayed by the query, it cannot be used in a GROUP BY statement'].
Also, it makes no sense to group by empno, as it's a unique property (primary key).
Of course, I tried the book's version in Oracle, and it failed just as I expected, with error code ORA-00937.
The following works OK:
SELECT deptno, AVG(sal)
FROM emp
GROUP BY deptno;
Please advise. If I'm right in any of the points, please let me know how I can get the info to the publisher (I've been unable to find the right address on their website). If I'm wrong, then please correct me.
TIA,
Kofa
Received on Wed Dec 18 2002 - 04:50:25 CST
 |
 |