OCA/OCP: Introduction to Oracle9i SQL Study Guide: Exam 1Z0-007

Page 0 CD

Edgetest, Chapter 1, Question #14 should read: "...that begin with DI_from the ORDERS Table?"

Page 0CD

Bonus exam 2, question 41, the questions asks which of the following statements is not true. Option D says 'You cannot insert new rows...'. Should be answer B not D.

Flash Cards - True or False: The NATURAL and CROSS join types cannot be outer joins. Correct Answer is FALSE

Page 3

Note: Seed Database -
The Oracle9i sample software can be downloaded from Oracle Technology
website (http://technet.oracle.com). The membership is free. When you
install the software, Oracle has option to create a new database, which can
be created with the sample schemas. If the database is already created and
you just want to add the sample schemas, it can be done too.

Please refer to documentation found on OTN
http://technet.oracle.com/docs/products/oracle9i/doc_library/release2/server
.920/a96539/toc.htm

You need membership to access this documentation.

Here is an extract from Chapter 1 of Oracle9i Sample Schemas Documentation:
========================================================
Using the Database Configuration Assistant
Using DBCA is by far the most intuitive and simple way to install the Sample
Schemas. Step 4 of the database creation process lets you configure the
Sample Schemas you wish to use in your database. The following dependencies
are enforced by the Database Configuration Assistant:

a.. The checkbox "Example Schemas" needs to be checked for any Sample
Schema to be created.
b.. "Oracle Spatial" needs to be selected to allow the Order Entry schema
to be created.
a.. "Oracle Intermedia" needs to be selected to allow for the creation of
the Product Media schema. You can select this option by clicking on the
"Additional database configurations ..." button.
b.. "Oracle JVM" needs to be selected for the evaluation of materialized
views and dimensions. If you intend to use these features, select this
option by clicking on the "Additional database configurations ..." button.
c.. The Order Entry schema option requires the Human Resources option to
be selected.
d.. The Product Media schema option requires the Order Entry option to be
selected.
e.. The Shipping schema option requires the Order Entry option to be
selected.
f.. Selecting "Oracle OLAP Services" with the Sales History option
selected will add OLAP server metadata to the Sales History schema.
Two of the three predefined database templates shipped with the Database
Configuration Assistant contain the Sample Schemas:

a.. OLTP database
b.. DSS database
============================================================
Thanks,
Biju

Page 12

Third sentence under heading "Text",
"Oracle treats all text literals as though they were CHAR datatypes." Change to:
"Oracle treats all text literals as though they were CHAR datatypes for comparison (blank padded)."

Page 41

Question #2,
Reader's inquiry:
Question 2 of the review questions for Chapter one, compares
two SQL statements and asks if the result will be the same, different
or in error.

Author's Response:

Yes, the question is somewhat misleading...

This chapter was on basic SELECT statements, did not consider the query tool
being used. In SQL*Plus, you get different column heading for both queries,
but the results are the same.

Page 75

The explanation should read "Imagine that you have been asked to ... .. on the employee ID, name, job ID, salary, manager ID from the employees table of HR schema."

Page 76

Formatting Columns last line :"COLUMN sal FORMAT "$9,999.99"" should read "COLUMN salary FORMAT "$9,999.99"

Page 122

In the Syntax of the LTRIM function it should be "LTRIM( [, ])".

The paragraph under LPAD should begin with "LPAD(, [, ])"

Page 123

The comma that separates the first and the second argument of the RPAD function is missing. Should read "RPAD(,[, ])"

Page 126

SUBSTRB function, The syntax notation SUBSTRB( [,]) should be changed to SUBSTRB(, [,])

Page 127

The output from the code for the TRANSLATE function, "MISSISSIPPI" shoud read "mISSISSIppI"

Page 137

SQRT function, The wording takes a single argument, where n is a number

Page 139

In the fourth line of that page it should be NLS_DATE_FORMAT.

Page 140

Table 3.3, function" TRUNCATE" should read "TRUNC"

Page 141

Top of page, the results of the ADD_MONTHS example is wrong. The PLUS_3 column should return '28-Feb-2003', since the year 2003 is not a leap year.

Page 143

Under EXTRACT, 3rd line, "MIN" should be "MINUTE"

Page 144

Function LOCALTIMESTAMP see detailed description
Function LOCALTIMESTAMP, LOCALTIMESTAMP() should be changed to LOCALTIMESTAMP(

)

Page 145

Top of page, the results of the MONTHS_BETWEEN example is incorrect. The 21 (for test2) should be 9.

Page 148

Bottom of page, V$TIMEZONE_NAME should be V$TIMEZONE_NAMES

Page 150

Table 3.5, 5th function from the top, "NUMTOTMINTERVAL" should be changed to 'NUMTOYMINTERVAL'

Page 151

Under BIN_TO_NUM, last line, BIT_AND should be BITAND

Page 152

Authors note

Page 156

The ROWIDTOCHAR function takes a ROWID as the input. The sentence "ROWIDTOCHAR() takes a single argument, where x is a character string in the format of a ROWID" should read "ROWIDTOCHAR() takes a single argument, where x is of datatype ROWID"

Page 158

Table 3.7, for date code Q should read:
the exmaple should be changed from
01-Jan-2002
to
1,2,3,4

Page 176

Seventh bullet, "TERMINAL returns a VARCHAR2 string containing information corresponding to the option opt" should read "TERMINAL returns a VARCHAR2 string containing information on operating system identifier for your current session's terminal"

Page 177

Last line, 4th paragraph under Exam Essentials should read LOCALTIMESTAMP.

Page 187

Answer #11, line 1 "ASC" should read "ASCII". Line 3, "There is no ASCII function." should read "There is no ASC function."

Page 188

Last line of Ques. 19, 'I like SUN' should be changed to 'S like SUN'

Page 194

Table 4.1: 'VARIANCE: ... or 0 for sample size 1' instead of 'VARIANCE: ... or 1 for sample size 1'

Page 201

Select statement: order of the colums is not the same displayed - COUNT(*) should be the third column in the query.

Page 203

1st paragraph, line #4, (30-Jun-1996) should be (30-Jan-1996)

Page 207

The first sentence of 2nd paragraph, SAL values: 1200, should be 1300

Page 209

Under RANK, SQL statement at the bottom of the page is incorrect. The 4th line should read:
,DENSE_RANK(10000) WITHIN GROUP should read:

,RANK (10000) WITHIN GROUP

Page 214

Line 6: 'STDDEV will return 0 when there is only one row of input' instead of 'STDDEV will return 1 when there is only one row of input'

Page 218

Code example at the bottom of the page,
The trailing semicolon should be removed from the GROUP BY line

GROUP BY cust_state_province;

should be changed to

GROUP BY cust_state_province

Page 219

Code example at the top of the page,
The trailing semicolon should be removed from the GROUP BY line

GROUP BY cust_state_province;

should be changed to

GROUP BY cust_state_province

Page 227

2nd row from bottom of page, the sentence should read "Group functions can be used in SELECT, ORDER BY and HAVING clauses".

Page 230

Ques. # 4, The question should be changed to read: Which of the following group functions can return a NULL when there is one row in the aggregation.

Page 233

Question #10, The state_cd that appears in the SQL statement should be state_code.

Page 244

Line 4 from the bottom: 'you can use the table name or table name and schema owner' instead of 'you can use the table owner or table owner and schema owner'

Page 246

Should say - If the column name is not part of the join criteria columns, it can be qualified.

Page 249

Top of page, the NOTE should read :
"Remember that you cannot use alias or table names to qualify the column names used in the join operation anywhere in the query when using NATURAL JOIN or JOIN USING syntax"

Page 255

1st para., beneath the code, "We placed the ........", delete the para. in its entirety.

Page 257

Under Right Outer Joins, para.& code just before the Note,
"In pre-9i... (the order of tables in the FROM clause does not matter):"

should be changed to

"In pre-9i... (the order of tables in the FROM clause does matter, if you change the order, it becomes a left outer join):"

Code should read:

SELECT c.country_name, l.city
FROM locations l, countries c
WHERE c.country_id = l.country_id (+);

Paragraph - "right outer join," just before the note:
i... (the order of tables in the FROM clause does not matter):"

Should be changed to

"In pre-9i... (note the order of tables in the FROM clause). If tables A and
B are joined (FROM A, B), and you need all rows from B, the outer join
operator is placed beside all columns of A. This is a right outer join,
because we are retrieving all rows from the table on the right side (table
B). In outer join syntax using the (+) operator, the placement of the outer
join operator (+) is what determines the table from where all the rows are
retrieved, not the order of tables - order of tables determines if it is a
left or right outer join "

SELECT c.country_name, l.city
FROM locations l, countries c
WHERE c.country_id = l.country_id (+);

Regards,
Biju

Page 281

Ques.11, change CNT_CODE ST CTY_CODE CTY_NAME to CNT_CODE ST_CODE CTY_CODE CTY_NAME

Page 293

Example after the syntax of multiple-table Insert: the table is 'SALES_DETAIL' instead of 'SALES_DATA' (2 occurences of this word).

Page 305

2nd to last paragraph - the description of Row Exclusive lock. Row Exclusive (SX) A row exclusive (SX) should be changed to Row Exclusive (RX) A row exclusive (RX)

Page 306

2nd para. - the description of the Shared Rwo Exclusive lock, The last sentence should be changed. Update (SX) the rows selected should be changed to update (RX) the rows selected.

TABLE 6.3, All 5 occurrences of SX should be changed to RX

Table 6.3 - Allows column for Lock RX , SS should be changed to SS, RX

Page 307

In Molly's session, time point 201 SX locks acquired should be changed to RX locks acquired

In Alan's session, time point 202, DDL is blocked by the SX lock should be changed to DDL is blocked by the RX lock.

Page 311

The code in the exception block should be - see detailed description
EXCEPTION
WHEN OTHERS THEN
ROLLBACK;

Page 323

Under session A, 2nd line, "READ CONSISTENT" should be "READ COMMITTED"

Page 328

Question #19, option D. should read: "INSERT ALL..."

Page 331

Ques. # 15 Answer should be "B", 12,000. .
Question 15 on page 326 is not correct.
The correct answer should be B; 12,000 rows will be inserted into the new_channel_sales table.
I'm surprised that I did not catch this error and also that our technical reviewer did not catch this.

This multi-table insert statement would be evaluated as follows:

Since no rows have a channel_id of 'C', no rows would be inserted into the catalog_sales table.

24,000 rows have channel_id of 'I', so control would pass to the second WHEN clause 24,000 times and the internet_sales table would get 24,000 rows. Since the second WHEN clause evaluates to TRUE and the INSERT FIRST option is specified, these rows would not make it to the third WHEN clause and would not be inserted into the new_channel_sales table. Had the INSERT ALL
option been used these 24,000 rows would also get inserted into the new_channel_sales table.

12,000 rows have a channel_id of 'T', so control would pass all the way to the third WHEN clause for these rows, and 12,000 rows would get inserted
into new_channel_sales.

Chip

Page 337

The sentence "Unicode uses either 16-bit encoding (UTF-16) or 8-bit encoding (UTF-8)." would clear it is changed to ""Unicode characterset supported by Oracle is either 16-bit encoding (UTF-16) or 8-bit encoding (UTF-8)."

Page 350

Last line should read:
"The ON COMMIT clause can be included to specify if the data in the temporary table is session-specific (ON COMMIT PRESERVE ROWS) or
transaction-specific (ON COMMIT DELETE ROWS)."

Page 364

The product_id column is missing a datatype. Line two should read "product_id VARCHAR2 (8))"

Page 368

In the Explanation of ?Disabled Constraints?the keyword in the paragraph should be DISABLE instead of DISABLED. The example is right.

Page 372

line 4 from the bottom: 'and its capital' instead of 'and its capitol'

Page 384

The question should read "which datatype shows" for clarity.

Page 387

Question #12, the correct answers are C and D.

Page 416

Question # 16, Please change the "SALARY NUMBER (5,2)" to "SALARY NUMBER (6,2)"

Page 423

Keyword "START WITH", should read "The default is MAXVALUE for descending sequences, which is -1 and MINVALUE for acsending ones, which is 1.

Page 424

1st para. on page after NOCACHE description, delete complete last sentence, starting with "The negative ............."

Page 425

POLICY_SEQ appears twice on the page, which should be replaced with EMP_SEQ.

Page 428

at the middle of the page: 'SALE_SEQ' instead of 'SALES_SEQ'

Page 441

The procedure name should read DBMS_UTILITY.ANALYZE_SCHEMA.

Page 446

Ques. # 3, answers(A, B, C, D),dbms_application_info should be dbms_application_info.set_module

Page 452

Ques. # 1 Explanation, default MAXVALUE is 0 should be changed to default MAXVALUE is -1

Page 488

Under heading FAILED_LOGIN_ATTEMPTS see detailed description
The SQL in the example should read:

CREATE PROFILE power_users LIMIT FAILED_LOGIN_ATTEMPTS UNLIMITED;
ALTER PROFILE problem_children LIMIT FAILED_LOGIN_ATTEMPTS 5;

Page 509

outer join definition, last line: 'FULL|LEFT|RIGHT OUTER JOIN'instead of 'FULL OUTER JOIN'.

Page xlvii

Answer to Question # 17 is D,E,F, G and H

Page

The parameter name should be "DB_CREATE_ONLINE_LOG_DEST_n" not "DB_CREATE_ONLINE_DEST_n"