Re: Oracle OCP Book - Is This Correct?

> I bought a copy of Oracle OCP Introduction to Oracle 9i: SQL Exam Guide
> from Oracle Press over the weekend and my first thought, other than the
> fact that no one ran a spell checker on it, is that there are many
> factual errors.

Daniel,

    I'm reading the same book and have already reported errata to the publisher. It's very frustrating because I don't know if these questions will reappear on the actual test or not. I don't know if they got these questions when using their 'close relationship with Oracle' or not. Here are some answers --

> Pg. 163: "Other types of views that you will learn about in Chapter 7 do
> not support the use of the order by clause".

At one time I believe Oracle wouldn't accept an ORDER BY clause in a view. I'm not sure if it was 7.3 or 8 though. I think they are pulling a lot of information from a 7.3 book.

> Pg. 203: "A listing of not NULL for the table column usually (although
> not always) indicates that the table column is the primary key for
> identifying unique rows in the table.

Yes, once you start doing some heavy architecture, NOT NULLs are quite common for other columns than the primary key. I think this is a 'tip' for newbies.

> Pg. 207: "However, if the select statement includes a specific list of
> columns named in the column clause, your crate table clause must list
> the columns you want the table to include, enclosed in parentheses. Here
> us ab example of what I mean:
>
> SQL> create table emp_copy_2
> (empno, sal) as
> select empno, sal from emp
> where deptno = 10;
>
> I've never had a problem yet with
>
> SQL> create table emp_copy_2 as
> select empno, sal from emp
> where deptno = 10;

Both are appropriate. That's one of the errata I sent to the editor. In my experience, when relaying information (teaching, writing) people usually get things stuck in their heads, i.e. its "easier to learn than to unlearn." This would probably be the case for the author. He learned these issues in an older version of Oracle, 7.3 or before, and assumed they are still there.

Here are some others I found --

-----------------------BEGIN PASTE----------------------

    When you use a group by clause in your query, all the nongroup expressions in the column clause of the query must appear before the grouped expression in the column clause. Put another way, no nongroup expression can appear after the group expression in the column clause.

Note: This is assuming you can't do the following

  SELECT deptno, avg(sal), job
  FROM emp
  GROUP BY deptno, job

    This is not true; I have done it plenty of times in my consulting experience and ran it on a 9i database to make sure. I believe I've been doing this since Oracle 7.3, but not sure on that one.

---

    When you use a group by clause in your query, all the nongroup
expressions in the column clause of the query must appear before the grouped
expression in the column clause. Put another way, no nongroup expression can
appear after the group expression in the column clause.Note: This is
assuming you can't do the following

  SELECT deptno, avg(sal), job
  FROM emp
  GROUP BY deptno, job

    This is not true; I have done it plenty of times in my consulting
experience and ran it on a 9i database to make sure. I believe I've been
doing this since Oracle 7.3, but not sure on that one.

---

TIP: The order by clause can be used in a query that uses subqueries, but
this clause must appear in the outermost query only. The subquery cannot
have the order by clause defined for it.

Note: This was resolved in Oracle 8i. One of my commercial applications
requires Oracle 8i or greater because it allows order by clauses in
subqueries.

-----------------------END PASTE----------------------

Oh well, what can we do?

Tom
www.oraclepower.com