ERRATA FOR OCP INTRODUCTION TO Oracle9i: SQL Exam Guide By Jason Couchman
Revised March 3, 2003
Chapter 1
p. 6 In the section on setting up your own working tables, the examples are for unix. If you are using Windows, reverse the slashes or read the TIP on using Windows p. 21.
p. 7 E. F. Codd presented his paper on relational theory in 1970.
p. 24 The answer key for #4 should read “update, delete, or insert. Oracle does not consider SELECT to be part of DML.”
P. 40 The TIP should include using Edit|Editor|Define Editor in the SQL*Plus GUI on Windows.
p. 51 #3 The data in the PLAY_NAME column includes double quotes. They do not have to be included in the query.
p. 53 #3 Choice C provides the correct result except the column name alias.
p. 54 #6 If you omit the database name, Oracle assumes you want to connect to the local database on your machine. This may be set via the ORACLE_SID environment parameter.
Chapter 2
p.61 Answer Key #4 should be B, not C.
p. 62 Table 2-1 LIKE and BETWEEN – the ‘is’ keyword cannot be used with these two comparison operations. You will receive ORA-00908: missing NULL keyword.
SHOWN AS CHANGE TO
is like like
is between between
p. 64 First paragraph - To review how Oracle resolves operators in a where clause, consider the following three queries:
Select * from emp where not ename = ‘KING’ and ename = ‘FORD’ – would return just FORD
Select * from emp where ename = ‘KING’ and not ename = ‘FORD’ – would return just KING
Select * from emp where not ename = ‘KING’ and not ename = ‘FORD’ – would return all rows
p.69 First SELECT query should appear as follows to match the output:
select col_1 as lastname,
trim(both '_' from col_1) as trimmed
from example
p. 82 The third bullet describing the months_between date function should say (second sentence), “If date x is earlier than y, the result is negative.”
p. 82 The fifth bullet describing next_day date function should show the syntax as
NEXT_DAY (date, char)
The description of this function is returns the date of the first weekday named by char that is later than the date date.
p. 82-83 All the code blocks involving dates are using a two-digit year (YY) unless otherwise noted. This code is based on the nls_date_format = ‘DD-MON-YY’. On page 83 in the months_between code block, this function is subtracting March 15 of the year 2000 minus the June 26 of the year 1999 to determine the number of months between these two dates.
p. 84 The second code block should show sysdate-hiredate as shown below:
select ename, (sysdate-hiredate)/7 as "Weeks at Work"
from emp
where ename = 'TURNER'
p. 88 The description and sample code block on how to bypass using nls_date_format for new_time will not work. You can achieve the result given one of two ways:
1) You can set the NLS_DATE_FORMAT parameter to display 24-hour time before using the new_time function. The code and result should appear as follows:
ALTER SESSION SET NLS_DATE_FORMAT = 'DD-MON-YYYY HH24:MI:SS';
SELECT TO_CHAR(NEW_TIME(TO_DATE('15-MAR-2000 14:35:00',
'DD-MON-YYYY HH24:MI:SS'),'AST','GMT'))
FROM DUAL
TO_CHAR(NEW_TIME(TO_
------
15-MAR-2000 18:35:00
2) Add the date format to the single-row function:
select to_char(New_time(to_date('15-mar-2000 14:35:00',
'dd-mon-yyyy hh24:mi:ss'), 'AST','GMT'),
'dd-mon-yyyy hh24:mi:ss')
from dual;
Chapter 3
p. 115 Table 3-1 Oracle Outer Join syntax and ANSI/ISO equivalent
should read as follows:
Oracle Outer ANSI/ISO
Join Syntax Equivalent
from tab_a a, tab_b b from tab_b b left outer join
where a.col_1 (+) = b.col_1 tab_a a on a.col_1 = b.col_1
from tab_a a, tab_b b from tab_b b right outer join
where a.col_1 = b.col_1 (+) tab_a a on a.col_1 = b.col_1
from tab_a a, tab_b b from tab_b b left outer join
where a.col_1 (+) = b.col_1 tab_a a on a.col_1 = b.col_1
where b.col_2 = ‘VALUE’ where b.col_2 = ‘VALUE
p. 117 question #4 could be re-written as follows for clarification:
1. You issue the following command in Oracle:
SQL> select e.ename, a.street_address, a.city, a.state, a.post_code
2 from emp e, addr a
3 where e.empno = a.empno (+);
Which of the following choices shows the ANSI/ISO equivalent statement?
- select e.ename, a.street_address, a.city, a.state, a.post_code from emp e outer join addr a on e.empno = a.empno;
- select e.ename, a.street_address, a.city, a.state, a.post_code from emp e left outer join addr a on e.empno = a.empno;
- select e.ename, a.street_address, a.city, a.state, a.post_code from emp e right outer join addr a on e.empno = a.empno ;
- select e.ename, a.street_address, a.city, a.state, a.post_code from emp e right outer join addr a where e.empno = a.empno (+) ;
p. 118 Answer Key #4 the correct answer would be B.
p. 119 Second SELECT should include e.mgr to match the results.
select e.empno, e.ename, e.job, e.mgr......
p. 131 SELECT statement - JOB column heading is misaligned. It should appear three spaces to the right.
p. 132 Exercise #2 A, B and D are all correct. The order of the selected columns does not have to be in the same order as the GROUP BY nor does the group function have to appear last in the selection. The only syntax rule is that all non-grouping functions must by on the GROUP BY clause.
Chapter 4
p. 151 The TIP is listing OTHER comparison operations. The reason the equal sign (=) does not appear here is that it was discussed in the previous paragraph.
p. 152 The having Clauses and Subqueries paragraph has a single row subquery as an example yet this topic appears right after the Writing Multi-row Subqueries.
p. 154 Exercises #1 This is a Single-row Subquery as the subquery only returns one row. This same query on p. 152 should appear under the Single-Row Subquery section or be changed to have a Multi-row Subquery.
p. 156 Answer Key #2 The correct answer is A or D as the queries are exactly the same.
p. 173 Answer Key #2 The correct answer is B. Once you remove the trunc function, the query is successful.
p. 190 Additional information on the login.sql file covered right after the first NOTE:
The glogin.sql and login.sql files contain SQL statements or SQL*Plus commands that you choose to execute at the beginning of each SQL*Plus session. When you invoke SQL*Plus, glogin.sql is read first, followed by login.sql. The options in login.sql will override the options in glogin.sql.
The default location for these files is $ORACLE_HOME/sqlplus/admin. To change the default location, you can use the environment variable SQLPATH.
p. 193 Fill-in-the-Blank #3, COLUMN could also be used here.
p. 195 Exercise #8 INSERT is covered in chapter 6.
p. 196 Exercise #9 DELETE is covered in chapter 6.
Chapter 5
p. 204 Exercise #1 Make the following change to part D. of possible solutions.
D. potential primary key of the table
Just because a NOT NULL is displayed in the NULL? column of a describe command does not mean it is the primary key of the table. For various business reasons, non-primary key columns may also be NOT NULL.
p. 206 Under the Creating Temporary Tables paragraph, add the following to the CREATE TEMPORARY TABLE statement:
ON COMMIT DELETE ROWS (default) specifies that the temporary table is transaction specific and Oracle truncates the table (delete all rows) after each commit. ON COMMIT PRESERVE ROWS specifies that the temporary table is session specific and Oracle truncates the table when you terminate the session.
create global temporary table temp_emp
(empno number,
ename varchar2(10))
on commit preserve rows;
p. 220 The last sql statement is incorrect. It should read
alter table employee modify (lastname varchar2(25));
p. 224 Exercises #4 Answer B should read
alter table sales modify (product_type varchar2(10));
p. 247 Exercises #3 The last sql statement syntax is incorrect. It should read
alter table prices enable novalidate constraint pk_prices_01; or
alter table prices enable novalidate primary key;
Using either statement will cause the following error:
ERROR at line 1:
ORA-02437: cannot validate (SCOTT.PK_PRICES_01) - primary key violated
This makes none of the answers correct.
Chapter 6
p. 264 In the paragraph Another Variation, the insert could also appear as
insert into scott.employee select * from master.employee;
The select does not have to be in parenthesis.
p. 273 In the Merging Data in Oracle Tables paragraph, the insert statement is missing the values clause:
insert into movies values ((‘GONE WITH THE WIND’,’6:00 PM’);
p. 278 Exercise 1 (A) is the correct answer as this is the error that is returned:
merge into emp e1
*
ERROR at line 1:
ORA-00001: unique constraint (SCOTT.PK_EMP) violated
p. 284 The TIP first line should read:
An update statement acquires a special row-level lock called a row-exclusive lock, which means that for the period of time the update statement is executing, no other user in the database can change the data in the row but they can view it.
Chapter 7
p. 296 The view should be coded as follows so the user only sees their salary and commission and the president (KING) can see everyone’s salary and commission:
create or replace view emp_view as
( select empno, ename, job, mgr, hiredate,
decode(user, ename, sal, 'KING', sal, 0) as sal,
decode(user, ename, comm, 'KING', comm, 0) as comm,
deptno from emp);
p. 299 Exercises #1 This view is the same as the one on p. 296. However, if you leave this create view statement alone, it is correct syntax even though logically it may not be the correct business rule you are trying to enforce. The correct answer still remains C.
p. 313 Exercises #1 The question should be reworded as follows: You have just rebuilt a base table that was dropped inadvertently. The base table had one view that referred to it. Which two of the following statements can be used to update the status of the view in one step? (Choose two.)
p. 335 Exercises #1 Answer B should say the following in order for the Answer Key given to be correct. The create public synonym command is missing the name of the public synonym.
create public synonym profits for profits; (issued by WATTERSON)
p. 338 Two-minute drill items 5-9 are in the two-minute drill items for Chapter 8 on p.372-373 and should be removed from this list. Item number 10 should remain in this chapter as it discusses synonyms.
p. 340 There are two #4 Chapter questions. The Answer to Chapter Questions on p. 343 has the correct numbering format
Chapter 8
p. 373 - The last two-minute drill item should be removed from this chapter as it appears in the two-minute drill for chapter 7 on p. 338.
Chapter 9
p. 421 Practice Exam 3 Question #14
The min(hiredate) is 17-Dec-80 and the max(hiredate) is 23-May-87. So, the correct answer is 77 which is not one of the choices. If you do not get this answer, rerun the $ORACLE_HOME/rdbms/admin/utlsampl.sql script to remove any changes to the data from previous scripts and try the query again.
p. 427 Practice Exam 3 Question #33
The correct answer is A, B and C as the order of the columns selected and the order of the columns on the GROUP BY do not have to be in any particular order. But, all non-group functions must be on the GROUP BY statement.