Mastering Oracle SQL

Errata for Mastering Oracle SQL

Submit your own errata for this product.


The errata list is a list of errors and their corrections that were found after the product was released. If the error was corrected in a later version or reprint the date of the correction will be displayed in the column titled "Date Corrected".

The following errata were submitted by our customers and approved as valid errors by the author or editor.

Color Key: Serious Technical Mistake Minor Technical Mistake Language or formatting error Typo Question Note Update



Version Location Description Submitted By Date Submitted Date Corrected
Printed
Page 8
First Paragraph

Hello, In section 1.3 A Simple Database Figure 1-1. The parts distributor model. There is an one-to-many relationshp between PART and CUSTOMER. What is the relationship used for? Ps., there is no foreign key in CUSTOMER table. Is this relation necessary? There is no relation between CUST_ORDER and LINE_ITEM, should we add one-to-many relation form CUST_ORDER to LINE_ITEM to stands for a customer order can have 0 or more Line_items. And A line item must belong to a CUST_ORDER. Author's response: there shouldn't be a relationship between the Part and Customer table in the ER diagram. The diagram in the second edition is correct.

Anonymous   
Printed
Page 20
2nd paragraph

The text says that NOT is an operator being applied to the IN operator to perform a NOT IN operation. Strictly speaking, the NOT operator applies to expressions, not to other operators. NOT IN is an operator, distinct from IN, just like <> and != are distinct operators that perform the opposite operation of =. (It's just one that happens to be composed of two keywords.) The IN and NOT operators are actually being used if you say NOT A IN (X, Y, Z). Saying A NOT IN (X, Y, Z) is using a different but complementary operator that happens to flow better to English speakers If the NOT operator also operated on other operators, then you could say WHERE A NOT = B, but you can't--you'll get the error "ORA-00920: invalid relational operator" if you try that. The same discussion applies to BETWEEN vs. NOT BETWEEN at the top of page 21, first paragraph. It also holds for LIKE vs. NOT LIKE on page 21, 7th paragraph, beginning "For this pattern". It also holds for IS NULL vs. IS NOT NULL on page 22, paragraph 4. In a similar vein, on page 22, paragraph 2, it claims the existence of an IS operator. IS NULL is the entire operator--and IS NOT NULL is a distinct but complementary operator, one comprised of three keywords.

Anonymous   
Printed
Page 28
The first result set

The result in the text does not reflect the published data. The query returns 182 rows instead of 56 shown.

Anonymous   
Printed
Page 29
The last example query, first line of code

NOW READS: SELECT P.NAME PART_NAME, C.INV_CLASS INV_CLASS

Anonymous    Nov 01, 2003
Printed
Page 32
The last example (DESC EMPLOYEE)

The description of the employee table as shown in the text is slightly different from the actual table definition. The actual table definition is: Name Null? Type ----------------------------------------- -------- -------------- EMP_ID NOT NULL NUMBER(5) FNAME VARCHAR2(20) LNAME VARCHAR2(20) DEPT_ID NOT NULL NUMBER(5) MANAGER_EMP_ID NUMBER(5) SALARY NUMBER(5) HIRE_DATE DATE JOB_ID NUMBER(3) However, this difference doesn't affect the subsequent discussion and the examples.

Anonymous   
Printed
Page 33
DESC DEPARTMENT

The description of the department table as shown in the text is slightly different from the actual table definition. The actual table definition is: Name Null? Type ----------------------------------------- -------- ------------- DEPT_ID NOT NULL NUMBER(5) NAME VARCHAR2(20) LOCATION_ID NUMBER(3) However, this difference doesn't affect the subsequent discussion and the examples.

Anonymous   
Printed
Page 35
DESC DEPARTMENT

The description of the department table as shown in the text is slightly different from the actual table definition. The actual table definition is: Name Null? Type ----------------------------------------- -------- ------------- DEPT_ID NOT NULL NUMBER(5) NAME VARCHAR2(20) LOCATION_ID NUMBER(3) However, this difference doesn't affect the subsequent discussion and the examples.

Anonymous   
Printed
Page 38
The first example query, first line of first query

NOW READS: SELECT E.LNAME EMPLOYEE, M.NAME MANAGER

Anonymous    Nov 01, 2003
Printed
Page 39
Pages 39 through 42, Section: Self Non-Equi-Joins

All the queries in this section should refer to the DEPT view instead of the DEPARTMENT table.

Anonymous   
Printed
Page 44
The result of the last example

The result in the text does not reflect the published data. The actual result is: RTLR_NBR NAME CITY EMP_ID SALES_REP ---------- --------------------------------------------- ------------------------------ ---------- - 100 JOCKSPORTS BELMONT 7844 TURNER 101 TKB SPORT SHOP REDWOOD CITY 7521 WARD 102 VOLLYRITE BURLINGAME 7654 MARTIN 103 JUST TENNIS BURLINGAME 7521 WARD 104 EVERY MOUNTAIN CUPERTINO 7499 ALLEN 105 K + T SPORTS SANTA CLARA 7844 TURNER 106 SHAPE UP PALO ALTO 7521 WARD 107 WOMENS SPORTS SUNNYVALE 7499 ALLEN 201 STADIUM SPORTS NEW YORK 7499 ALLEN 202 HOOPS LEICESTER 7499 ALLEN 203 REBOUND SPORTS NEW YORK 7499 ALLEN 204 THE POWER FORWARD DALLAS 7654 MARTIN 205 POINT GUARD YONKERS 7499 ALLEN 206 THE COLISEUM SCARSDALE 7499 ALLEN 207 FAST BREAK CONCORD 7499 ALLEN 208 AL AND BOB'S SPORTS AUSTIN 7654 MARTIN 211 AT BAT BROOKLINE 7499 ALLEN 212 ALL SPORT BROOKLYN 7844 TURNER 213 GOOD SPORT SUNNYSIDE 7844 TURNER 214 AL'S PRO SHOP SPRING 7654 MARTIN 215 BOB'S FAMILY SPORTS HOUSTON 7654 MARTIN 216 THE ALL AMERICAN CHELSEA 7499 ALLEN 217 HIT, THROW, AND RUN GRAPEVINE 7654 MARTIN 218 THE OUTFIELD FLUSHING 7499 ALLEN 221 WHEELS AND DEALS HOUSTON 7844 TURNER 222 JUST BIKES DALLAS 7844 TURNER 223 VELO SPORTS MALDEN 7499 ALLEN 224 JOE'S BIKE SHOP GRAND PRARIE 7844 TURNER 225 BOB'S SWIM, CYCLE, AND RUN IRVING 7844 TURNER 226 CENTURY SHOP HUNTINGTON 7521 WARD 227 THE TOUR SOMERVILLE 7499 ALLEN 228 FITNESS FIRST JACKSON HEIGHTS 7521 WARD 32 rows selected. However, this difference doesn't affect the subsequent discussion and the examples.

Anonymous   
Printed
Page 44
IN PRINT: Second paragraph (create view statement)'

CREATE OR REPLACE VIEW V_RTLR_EMP AS SELECT C.RTLR_NBR, C.NAME, C.CITY, E.EMP_ID, E.LNAME SALES_REP FROM RETAILER C, EMPLOYEE E WHERE C.SALESPERSON_ID = E.EMP_ID; NOW READS: CREATE OR REPLACE VIEW V_RTLR_EMP AS SELECT C.RTLR_NBR, C.NAME, C.CITY, E.EMP_ID, C.SALESPERSON_ID, E.LNAME SALES_REP FROM RETAILER C, EMPLOYEE E WHERE C.SALESPERSON_ID = E.EMP_ID;

Anonymous    Nov 01, 2003
Printed
Page 66
The last two example queries

In the last two example queries, HAVING COUNT(ORDER_NBR) > 1 NOW READS: HAVING COUNT(ORDER_NBR) > 2

Anonymous    Nov 01, 2003
Printed
Page 77
The result set of the example.

The result sets in the text did not reflect the published data. Result set NOW READS: DEPT_ID NAME TOT ---------- -------------------- ---------- 1 Human Resources 1 2 Accounting 1 3 Domestic Sales 19 4 International Sales 5

Anonymous    Nov 01, 2003
Printed
Page 79
The result set of the first example.

The result sets in the text did not reflect the published data. Result set NOW READS: DEPT_ID DEPT_NAME TOT_ORDERS ---------- -------------------- ---------- 3 Domestic Sales 666 4 International Sales 175

Anonymous    Nov 01, 2003
Printed
Page 79
The result set of the second example.

The result sets in the text did not reflect the published data. Result set NOW READS: SALES_EMP_ID TOT_ORDERS ------------ ---------- 11 35 12 35 13 35 14 35 15 35 16 35 17 35 18 35 19 35 20 35 21 35 22 35 23 35 24 35 25 35 26 35 27 35 28 35 29 35 30 36 31 35 32 35 33 35 34 35

Anonymous    Nov 01, 2003
Printed
Page 80
The first result set.

The result sets in the text did not reflect the published data. The first result NOW READS: DEPT_ID TOT_ORDERS ---------- ---------- 3 666 4 175

Anonymous    Nov 01, 2003
Printed
Page 80
The second result set.

The result sets in the text did not reflect the published data. The second result set NOW READS: DEPT_ID DEPT_NAME TOT_ORDERS ---------- -------------------- ---------- 3 Domestic Sales 666 4 International Sales 175

Anonymous    Nov 01, 2003
Printed
Page 152
IN PRINT: First paragraph, first sentence

"...in the second component qery." SHOULD BE: "...in the second component query."

Anonymous   
Printed
Page 158
Figure 8-2

Figure 8-2 shows the wrong columns names for the EMPLOYEE table. The correct column names are: EMP_ID, FNAME, LNAME, DEPT_ID, MANAGER_EMP_ID, SALARY, HIRE_DATE, and JOB_ID. The figure does, however, correctly illustrate the concept that the EMPLOYEE table refers back to itself (via the MANAGER_EMP_ID column).

Anonymous   
Printed
Page 172
The two result sets

The result sets in the text did not reflect the published data. In the two result sets, The number 2975 NOW READS 2000 The date 01-MAY-81 NOW READS 01-MAY-80

Anonymous    Nov 01, 2003
Printed
Page 173
The two result sets

The result sets in the text did not reflect the published data. In the two examples, The number 10875 NOW READS 9900 The number 29025 NOW READS 28050

Anonymous    Nov 01, 2003
Printed
Page 185
desc mtd_orders

The description of the mtd_orders table as shown in the text is slightly different from the actual table definition. The actual table definition is: Name Null? Type ------------------------------------ -------- ------------- TOT_ORDERS NOT NULL NUMBER(7) TOT_SALE_PRICE NOT NULL NUMBER(11,2) MAX_SALE_PRICE NOT NULL NUMBER(9,2) EUROPE_TOT_ORDERS NOT NULL NUMBER(7) EUROPE_TOT_SALE_PRICE NOT NULL NUMBER(11,2) EUROPE_MAX_SALE_PRICE NOT NULL NUMBER(9,2) NORTHAMERICA_TOT_ORDERS NOT NULL NUMBER(7) NORTHAMERICA_TOT_SALE_PRICE NOT NULL NUMBER(11,2) NORTHAMERICA_MAX_SALE_PRICE NOT NULL NUMBER(9,2)

Anonymous   
Printed
Page 228
The result sets in this chapter

Oracle8i vs Oracle9i Differences 1. Sequence of returned rows: When the same query is executed in Oracle8i and Oracle9i, the sequence of rows in the result set could be different. There could be differences in the sequence of rows returned from a Oracle9i Release 1 database and Oracle9i Release 2 database. 2. Slight syntax difference: The following syntax works in 8i, but not in 9i: SELECT O.YEAR, TO_CHAR(TO_DATE(O.MONTH, 'MM'), 'Month') MONTH, R.NAME REGION, SUM(O.TOT_SALES) FROM ORDERS O, REGION R WHERE R.REGION_ID = O.REGION_ID AND O.MONTH BETWEEN 1 AND 3 GROUP BY O.YEAR, O.MONTH ROLLUP (R.NAME); Oracle9i expects a comma(,) between O.MONTH and ROLLUP in the last line. Oracle8i is fine with or without the comma. The following syntax works well in both 8i and 9i: SELECT O.YEAR, TO_CHAR(TO_DATE(O.MONTH, 'MM'), 'Month') MONTH, R.NAME REGION, SUM(O.TOT_SALES) FROM ORDERS O, REGION R WHERE R.REGION_ID = O.REGION_ID AND O.MONTH BETWEEN 1 AND 3 GROUP BY O.YEAR, O.MONTH, ROLLUP (R.NAME);

Anonymous   
Printed
Page 228
The example SQL

The WHERE clause NOW READS: WHERE R.REGION_ID = O.REGION_ID AND YEAR = 2001

Anonymous    Nov 01, 2003
Printed
Page 230
The example SQL

All the WHERE clauses NOW READ: WHERE R.REGION_ID = O.REGION_ID AND YEAR = 2001

Anonymous    Nov 01, 2003
Printed
Page 232
The example SQL

The WHERE clause NOW READS: WHERE R.REGION_ID = O.REGION_ID AND YEAR = 2001

Anonymous    Nov 01, 2003
Printed
Page 234
The example SQL

The WHERE clause NOW READS: WHERE R.REGION_ID = O.REGION_ID AND YEAR = 2001

Anonymous    Nov 01, 2003
Printed
Page 282
1st. paragraph

the ranges for each bucket will be 1 to 1,000,000, 1000,0001 to 2,000,000, and 2,000,0001 to 3,000,000. NOW READS: the ranges for each bucket will be 1 to 1,000,000, 1000,001 to 2,000,000, and 2,000,001 to 3,000,000.

Anonymous    Nov 01, 2003