Errata Sheet
OCA Oracle Database 11g: SQL Fundamentals I Exam Guide (Exam 1Z0-051)
By, John Watson, Roopesh Ramklass
ISBN 0-07-15978-67

Errata as of 11/09/2010


Please note: The phrase "OCP examination" encompasses the "OCA examination" and is used synonymously throughout the text.


Page 33: "SQQ" should read "SQL" (in "On The Job" section) 

Page 116:  In the section "Equality and Inequality" there is an error in the discussion of the algorithm used for the numeric evaluation of character strings.
           In the paragraph following the example "select last_name from employees where last_name < 'King';" which begins with:
           "The character literal 'King'..." and ends with "then the row is selected", please replace with the following sentences:
           "The character literal 'King' is converted to a numeric representation. Assuming a 
           US7ASCII database character set with AMERICAN NLS settings, the literal 'King' is 
           converted into its ordinal character values: K + i + n + g = (75+105+110+103).
           For each row in the EMPLOYEES table, the LAST_NAME column data is similarly 
           converted into numeric values for each character which are then compared in turn 
           with the numeric values of the characters in the literal 'King'. For example,
           the row with LAST_NAME='Kaufling' is compared as follows: The first character in 
           both strings is 'K' with an ordinal value of 75. So the second character (i=105) is 
           compared with (a=97). Since (97 < 105) or (a < i), 'Kaufling' < 'King' and the row
           is selected."

Page 158, Bullet 5: should read "... a term falls between (and including) given start and end..."


Page 178: result of query 3 should be all lower case. "The" ---> "the"


Page 188: "3.14 approximates pi." should be "3.14 is pi"

Page 200: Sentence "If the divisor is zero...returns zero instead." should be "If the divisor is zero, no division by zero error is returned and the MOD function returns the dividend instead."

Page 230: The mask "[R|RR|YY|YYYY]" in the sentence:
      	  "Implicit character to date conversions are possible when the character string conforms to the following date patterns: [D|DD] separator1 [MON|MONTH] separator2 [R|RR|YY|YYYY]"
      	  should be changed to:
      	  "Implicit character to date conversions are possible when the character string conforms to the following date patterns: [D|DD] separator1 [MON|MONTH] separator2 [R|RR|YYYY]"
	  The sentence "YY and YYYY represent a 2- and 4-digit year, respectively" should be changed to "YYYY represents a 4-digit year"

Page 249: The last sentence on this page:
	  "The arithmetic equation in query 2 is not implicitly evaluated, and the NULLIF function finds 1234 different from 123+1, so it returns the ifunequal parameter, which is 1234."
	  should be changed to:
	  "The arithmetic equation in query 2 is implicitly evaluated, and the NULLIF function finds 1234 different from 123+1 (124), so it returns the ifunequal parameter, which is 1234."

Page 307:  Answer 10  "...having SUM(salary)>400 clause.." should be "...having SUM(NVL(salary,100))>400..."

Page 327: The first query and the paragraph following it should be changed from:
          "Consider the following query using pure natural joins:
	    select r.region_name, c.country_name, l.city, d.department_name
	    from departments d natural join locations l
            natural join countries c natural join regions r
	   The join between DEPARTMENTS and LOCATIONS creates an interim result set consisting of 27 rows. These tables provide the DEPARTMENT_NAME and CITY columns. This set is naturally joined to the COUNTRIES table. Since the interim set does not contain the COUNTRY_ID column, a Cartesian join is performed. The 27 interim rows are joined to the 25 rows in the COUNTRIES table, yielding a new interim results set with 675 (27 × 25) rows and three columns: DEPARTMENT_NAME, CITY, and COUNTRY_NAME. This set is naturally joined to the REGIONS table. Once again, a Cartesian join occurs because the REGION_ID column is absent from the interim set. The final result set contains 2700 (675 × 4) rows and four columns. Using pure natural joins with multiple tables is error prone and not recommended."
          to:
          "Consider the following query using a mixture of natural joins and Oracle joins:
	    select r.region_name, c.country_name, l.city, d.department_name
	    from departments d natural join locations l,
            countries c, regions r
	   The natural join between DEPARTMENTS and LOCATIONS creates an interim result set consisting of 27 rows since they are implicitly joined on the LOCATION_ID column. This set is then Cartesian joined to the COUNTRIES table since a join condition is not implicitly or explicitly specified. The 27 interim rows are joined to the 25 rows in the COUNTRIES table, yielding a new interim results set with 675 (27 × 25) rows and three columns: DEPARTMENT_NAME, CITY, and COUNTRY_NAME. This set is then joined to the REGIONS table. Once again, a Cartesian join occurs because the REGION_ID column is absent from any join condition. The final result set contains 2700 (675 × 4) rows and four columns. Using pure natural joins mixed with oracle joins is error prone and not recommended since join conditions may sometimes be erroneously omitted."

Page 444, Question 12: "Choose three correct answers." should be "Choose the best answer."


Page 484, Lab Answer: "constraint tel_telno_ck check (telno between 1000000 and 9999999)" should read "constraint tel_telno_ck check (telno between 2000000 and 3999999)"



Errors in "Master Exam" from CD (Quiz A for OCA):


Question 56: "If a table has indexes defined...". The correct answer is "D," but the test engine incorrectly scores the answer as "A,B,C."


Question 61: "create view emp_sales are select ...." "Are" should be replaced with "as"  
 

Errors in "Bonus Master Exam" download (Quiz B for OCA):

Question  7: "From the following SELECT...". Right answer is "C," but test engine incorrectly scores correct answer as "D."


Question 15: "Which of following statements..." Answer "C" should be 'select last_name "&last_name" from employees;'


Question 21: Remember that year 2008 was leap year.


Question 39: First answer option is incorrectly disabled. "A" is the correct answer.


Question 40: First answer option is incorrectly disabled. "A" is the correct answer.


Question 41: First answer option is incorrectly disabled. "A" is the correct answer.


Question 43: Exhibit is missing. Using the exhibit referenced in Question 41, answer "C" is clearly correct. Without an exhibit that shows the columns in each table, "A" could technically also result in a Cartesian join.



Thanks to the following readers for submitting errata:

Jussi Jääskeläinen,
Roman Churakov