Re: Errors in OCA Associate DBA certification book?

Hi Kovacs,

You've only found two errors !! Look deeper man ...

Your description of joins is correct. The (+) goes on the side of the nulls or on the opposite side to which you wish to perform a left/right join.

Cheers

Richard
"Kovacs Istvan" <kofa_at_alarmix.net> wrote in message news:3E0052F1.9050104_at_alarmix.net...
> 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
> --------------
> I found that there may be a mistake in the description of ANSI/ISO
> syntax of outer joins in the book, starting on page 112.
> It gives an example with the Oracle syntax as comments and the ISO
> syntax as code:
> ---
> -- select e.ename, e.deptno, d.name
> -- from dept d, emp e
> -- where d.deptno (+) = e.deptno;
> select e.ename, e.deptno, d.dname
> from emp e left outer join dept d
> on d.deptno = e.deptno;
> ENAME DEPTNO DNAME
> --------------------------
> [list of result rows, including]
> KING
> ---
>
> 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 (+);
> select e.ename, e.deptno, d.dname
> from emp e left outer join dept d
> on d.deptno = e.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
> ------------
> On page 139, the following question (#6) is shown:
> ---
> Which line in the following select statement, when removed from the
> statement, will correct the error?
>
> SELECT deptno, AVG(sal)
> FROM emp
> GROUP BY empno;
>
> A: SELECT deptno, AVG(sal)
> B: FROM emp
> C: GROUP BY empno;
> D: There are no errors in the statement, therefore no clauses should be
> removed.
> ---
>
> 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 - 06:45:28 CST