SQL Fundamentals I Assessment Test

xlv
36. A view is created using the following code. Which of the following operations are permitted
on the view?
CREATE VIEW USA_STATES
AS SELECT * FROM STATE
WHERE CNT_CODE = 1
WITH READ ONLY;
A. SELECT
B. SELECT, UPDATE
C. SELECT, DELETE
D. SELECT, INSERT
37. You query the database with the following:
SELECT PRODUCT_ID FROM PRODUCTS
WHERE PRODUCT_ID LIKE ‘%S\_J\_C’ ESCAPE ‘\’;
Choose the two PRODUCT_ID strings that will satisfy the query.
A. BTS_J_C
B. SJC
C. SKJKC
D. S_J_C
38. The EMPLOYEE table is defined as follows:
EMP_NAME VARCHAR2(40)
HIRE_DATE DATE
SALARY NUMBER (14,2)
Which query is most appropriate to use if you need to find the employees who were hired
before January 1, 1998 and have a salary greater than 5,000 or less than 1,000?
A. SELECT emp_name FROM employee
WHERE hire_date > TO_DATE(‘01011998’,’MMDDYYYY’)
AND SALARY < 1000 OR > 5000;
B. SELECT emp_name FROM employee

Answers to SQL Fundamentals I
Assessment Test
1. B. In the arithmetic operators, unary operators are evaluated first, then multiplication
and division, and finally addition and subtraction. The expression is evaluated from left to
right. For more information about order of evaluation, see Chapter 1.
2. A, C. The CREATE FORCE VIEW statement can be used to create a view before its base table
is created. In versions prior to Oracle 11g, any modification to the table will invalidate
the view. In Oracle 11g, the view will be invalidated only if the columns used in the view
are modified in the base table. Use the ALTER VIEW <view name> COMPILE statement to
recompile the view. See Chapter 7 to learn more about views.
3. C. CONCAT will return a non-NULL if only one parameter is NULL. Both CONCAT parameters
would need to be NULL for CONCAT to return NULL. The NULLIF function returns NULL if the
two parameters are equal. The LENGTH of a NULL is NULL. INSTR will return NULL if NULL is
passed in and the tangent of a NULL is NULL. For more information about NULL values, see
Chapter 2.
4. C. Group functions cannot appear in the WHERE clause. To learn more about group func-
tions, see Chapter 3.
5. B. When updating multiple columns in a single UPDATE statement, the column assignments
in the SET clause must be separated by commas, not AND operators. To read more about
DML statements (INSERT, UPDATE, and DELETE), refer to Chapter 5.
6. C. Since job_id is used in the SELECT clause, it must be used in the GROUP BY clause also.
To learn more about the rules of using the GROUP BY clause and aggregate functions, read
Chapter 3.
7. B. The BFILE datatype stores only the locator to an external file in the database; the actual
data is stored as an operating system file. BLOB, NCLOB, and CLOB are the other large
object data types in Oracle 11g. UROWID is Universal ROWID datatype and EXTERNAL
is a not a valid datatype. See Chapter 6 for information about datatypes.
8. C. The statement will work without error. Option B would be correct if you used the WITH
CHECK OPTION clause in the subquery. See Chapter 4 for more information about subqueries.
9. A, E. An outer join on both tables can be achieved using the FULL OUTER JOIN syntax.

table name. To learn more about table and column names, read Chapter 6.
17. B, D. You can use the IS NULL or IS NOT NULL operator to search for NULLs or non-NULLs
in a column. Since NULLs are sorted higher, they appear at the bottom of the result set in an
ascending-order sort. See Chapter 1 for more information about sorting NULL values.
18. D. COUNT (<column_name>) does not include the NULL values, whereas COUNT (*) includes
the NULL values. No other aggregate function takes NULL into consideration. To learn more
about aggregate functions, read Chapter 3.
19. E. These statements don’t account for possible NULL values in the BONUS column. For more
information about NULL values, see Chapter 2.
20. D. Using the ALTER TABLE statement, you can add new columns, rename existing columns,
and drop existing columns. To learn more about managing tables, read Chapter 6.
21. B. Since DEPARTMENT_ID is NULL for employee 178, NULL will be sorted after the non-NULL
values when doing an ascending-order sort. Since I did not specify the sort order or the
NULLS FIRST clause, the defaults are ASC and NULLS LAST. Read Chapter 1 for more infor-
mation on SELECT and sort orders.
22. D, E, F, G. The INTERVAL DAY TO SECOND datatype is used to store an interval
between two datetime components. See Chapter 6 for more information on the INTERVAL
and TIMESTAMP datatypes.
95127flast.indd 48 2/18/09 6:16:18 AM
Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
Answers to SQL Fundamentals I Assessment Test

xlix
23. D, E. In the join view, CITY is the key-preserved table. You can update the columns of
the CITY table, except STATE_CD, because STATE_CD is not part of the view definition (the
STATE_CD column in the view is from the STATE table). Since I did not include the STATE_CD
column from the CITY table, no INSERT operations are permitted (STATE_CD is part of the
primary key). If the view were defined as follows, all the columns of the CITY table would
have been updatable, and new records could be inserted into the CITY table.
CREATE OR REPLACE VIEW state_city AS

95127flast.indd 49 2/18/09 6:16:18 AM
Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
l

Answers to SQL Fundamentals I Assessment Test
33. C. Since you are finding the aggregate of the aggregate, you should not use nonaggregate
columns in the SELECT clause. To read more about nesting of aggregate functions, see
Chapter 3.
34. B. You can create primary key, foreign key, and unique key constraints on a view. The con-
straints on views are not enforced by Oracle. To enforce a constraint, it must be defined on
a table. Views can be created with the WITH CHECK OPTION and READ ONLY attributes dur-
ing view creation. Read Chapter 7 to learn more.
35. A, C. The maximum lengths of CHAR and VARCHAR2 columns can be defined in charac-
ters or bytes. BYTE is the default. To learn more about CHAR and VARCHAR2 datatypes,
read Chapter 6.
36. A. When the view is created with the READ ONLY option, only reads are allowed from the
view. See Chapter 7 to learn more about creating views as read-only.
37. A, D. The substitution character % can be substituted for zero or many characters. The
substitution character _ does not have any effect in this query because an escape character
precedes it, so it is treated as a literal. Read Chapter 1 to learn more about substitution
characters.
38. C. You have two main conditions in the question: one on the hire date and the other on
the salary. So, you should use an AND operator. In the second part, you have two options:
the salary can be either more than 5,000 or less than 1,000, so the second part should be
enclosed in parentheses and should use an OR operator. Option B is similar to option C
except for the parentheses, but the difference changes the meaning completely. Option B
would select the employees who were hired before January 1, 1998 or have a salary greater
than 5,000 or less than 1,000. Read Chapter 1 to learn more about writing queries using
filtering conditions.
39. A, B. The TRUNCATE command is used to remove all the rows from a table or cluster. By

4. Choose two utilities that can be used to apply CPU patches on an Oracle 11g database.
A. Oracle Universal Installer
B. OPatch
C. EM Database Control
D. DBCA
5. The loss of a data file in which two tablespaces requires an instance shutdown to recover
the tablespace?
A. TEMP
B. SYSTEM
C. UNDO
D. SYSAUX
95127flast.indd 51 2/18/09 6:16:18 AM
Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
lii

Administration I Assessment Test
6. Which of the following statements is not always true? (Choose two.)
A. Every database should have at least two tablespaces.
B. Every database should have at least two data files.
C. Every database should have at least three multiplexed redo logs.
D. Every database should have at least three control files.
7. Which statement about the initialization-parameter files is true?
A. The pfile and spfile can be modified using the ALTER SYSTEM statement.
B. You cannot have both an spfile and a pfile under the $ORACLE_HOME/dbs directory.
C. The pfile is used only to read by the Oracle instance, whereas the spfile is used to read
and write to.
D. On Windows systems, pfile and spfiles are not used because parameters are modified
using the system registry.
8. Which initialization parameter determines the location of the alert log file?
A. DIAGNOSTIC_DEST

12. You have just made changes to the listener.ora file for the listener called listener1
using Oracle Net Manager. Which of the following commands or combinations of commands
would you use to put the changes into effect with the least amount of client disruption?
A. lsnrctl stop listener1 followed by lsnrctl start listener1
B. lsrnctl restart listener1
C. lsnrctl reload listener1
D. lsnrctl reload
13. What is the prefix for dynamic performance views?
A. X$
B. V$
C. ALL_
D. DBA_
14. If you are updating one row in a table using the ROWID in the WHERE clause (assume that the
row is not already in the buffer cache), what will be the minimum amount of information
copied to the database buffer cache?
A. The entire table is copied to the database buffer cache.
B. The extent is copied to the database buffer cache.
C. The block is copied to the database buffer cache.
D. The row is copied to the database buffer cache.
15. When you are configuring Shared Server, which initialization parameter would you likely
need to modify?
A. DB_CACHE_SIZE
B. DB_BLOCK_BUFFERS
C. LARGE_POOL_SIZE
D. BUFFER_SIZE
E. None of the above
95127flast.indd 53 2/18/09 6:16:19 AM
Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
liv


A. MINEXTENTS
B. INITIAL
C. MINIMUM EXTENT
D. MAXEXTENTS
95127flast.indd 54 2/18/09 6:16:19 AM
Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
Administration I Assessment Test

lv
21. Which of the following is the utility that you can use to test the network connections
across TCP/IP?
A. trcasst
B. lsnrctl
C. namesctl
D. ping
E. None of the above
22. What is the difference between a unique key constraint and a primary key constraint?
A. A unique key constraint requires a unique index to enforce the constraint, whereas a
primary key constraint can enforce uniqueness using a unique or nonunique index.
B. A primary key column can be NULL, but a unique key column cannot be NULL.
C. A primary key constraint can use an existing index, but a unique constraint always
creates an index.
D. A unique constraint column can be NULL, but the primary key column(s) cannot
be NULL.
23. Which of the following conditions prevents you from being able to insert into a view?
A. A TO_NUMBER function on one of the base table columns
B. A CONNECT BY clause in the view definition
C. A column of type RAW
D. All of the above
24. Which parameter is used to enable the Automatic Memory Management feature of the

B. SYSMAN
C. SYS
D. The user who creates the directory
29. Which of the following types of statements can use a temporary tablespace?
A. An index creation
B. SQL statements with a GROUP BY clause
C. A hash join operation
D. All of the above
30. Which of the following is false about shared servers?
A. Shared servers can process requests from many users.
B. Shared servers receive their requests directly from dispatchers.
C. Shared servers place completed requests on a dispatcher response queue.
D. The SHARED_SERVERS parameter configures the number of shared servers to start at
instance startup.
31. What is accomplished when you issue the following statement?
ALTER USER JOHN DEFAULT ROLE ALL;
A. John is assigned all roles created in the database.
B. Existing roles remain the same, but future roles created will be enabled.
C. All of John’s roles are enabled except the roles with passwords.
D. All of John’s roles are enabled, including the roles with passwords.
95127flast.indd 56 2/18/09 6:16:19 AM
Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
Administration I Assessment Test

lvii
32. Which initialization parameter determines the location of the alert log file?
A. LOG_ARCHIVE_DEST
B USER_DUMP_DEST
C. BACKGROUND_DUMP_DEST
D. DIAGNOSTIC_DEST

95127flast.indd 57 2/18/09 6:16:19 AM
Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
lviii

Administration I Assessment Test
38. You need to copy the GL schema from production to qa_test, changing the tablespace
for indexes from gl_index to fin_indx. What is the best way to satisfy these
requirements?
A. First, use Data Pump to copy the schema without indexes. Then, change the default
tablespace for user GL in qa_test to fin_indx. Next, use Data Pump to copy the
indexes. Finally, change the default tablespace for user GL back to gl_data.
B. Use the dbms_metadata package to extract table and index DDL. Then, use Notepad
(or sed) to edit this DDL, changing the tablespace for the indexes. Finally, run the
DDL in the qa_test database.
C. Use Data Pump import, specifying a remap_datafile parameter to change the data
file location for indexes.
D. Use Data Pump import, specifying a remap_tablespace parameter to change the
tablespace location for indexes.
39. Identify the statement that is not true about checkpoints.
A. Instance recovery is complete when the data from the last checkpoint up to the latest
SCN in the control file has been written to the data files.
B. A checkpoint keeps track of what has already been written to the data files.
C. The redo log group writes must occur before a Commit complete is returned to
the user.
D. The distance between the checkpoint position in the redo log file and the end of the redo
log group can never be more than 90 percent of the size of the largest redo log group.
E. How much the checkpoint lags behind the SCN is controlled by both the size of the
redo log groups and by setting the parameter FAST_START_MTTR_TARGET.
40. The STATUS column of the dynamic performance view V$LOG contains what value if the
redo log file group has just been added?

read Chapter 8.
2. C, A, B, D. To rename a data file, you need to make the tablespace offline so that Oracle
does not try to update the data file while you are renaming. Using OS commands, copy
the data file to the new location, and using the ALTER DATABASE RENAME FILE command
or the ALTER TABLESPACE RENAME FILE command, rename the file in the database’s con-
trol file. To rename the file in the database, the new file should exist. Bring the tablespace
online for normal database operation. See Chapter 10 for more information.
3. C. Heterogeneous Services is the correct answer because these services provide cross-
platform connectivity to non-Oracle databases. Oracle Advanced Security would not solve
this application problem because it addresses security and is not accessibility to non-Oracle
databases. Oracle Net would be part of the solution, but another Oracle Network com-
ponent is necessary. Connection Manager would also not be able to accommodate this
requirement on its own. Read Chapter 11 to learn more.
4. B, C. CPU patches and interim patches can be applied using the OPatch utility or using EM
Database Control. EM Database Control also includes patch search and download options.
See Chapter 17 for more information.
5. B, C. Only the SYSTEM and UNDO tablespaces require the instance to be shut down when
their data files need recovery. Read Chapter 16 to learn about database recovery.
6. C, D. Every database must have at least two redo log files, which may or may not be multi-
plexed. Every database must have one control file. It is a good idea to have more than one con-
trol file for redundancy. Since SYSTEM and SYSAUX are mandatory tablespaces in Oracle 11g,
there will be at least two data files. See Chapter 8 for more information.
7. C. A pfile is a read-only file, and no database changes are written to the pfile. There is no
harm in having both an spfile and a pfile in the $ORACLE_HOME/dbs directory; Oracle will
only read the spfile when starting the database. On Windows systems also, you will need
a parameter-initialization file; the registry is not used. Read more about parameter files in
Chapter 9.
8. A. Oracle 11g uses the Automatic Diagnostic Repository to maintain the alert log and other
diagnostic information. In pre–Oracle 11g databases, the BACKGROUND_DUMP_DEST parameter
determined the alert log location; in Oracle 11g, this parameter value is ignored. To learn more

large pool is configured and is responsible for most of the SGA space allocation. Option C
is the correct answer. The cache size and block buffers settings do not affect Shared Server.
Read Chapter 11 for more information.
16. D. PUBLIC is the group or class of database users where all existing and future database
users belong. See Chapter 12 for more information.
17. A, C. You cannot dynamically change the parameter UNDO_MANAGEMENT after the instance
has started. You can, however, change the UNDO_TABLESPACE parameter to switch to another
undo tablespace while the instance is up and running. Read Chapter 13 to learn more.
18. C. The Manageability Monitor (MMON) process gathers performance statistics from
the system global area (SGA) and stores them in the AWR. Manageability Monitor Light
(MMNL) also does some AWR-related statistics gathering, but not to the extent that
MMON does. QMN1 is the process that monitors Oracle advanced queuing features.
Memory Manager (MMAN) is the process that dynamically manages the sizes of each SGA
component when directed to make changes by the Automatic Database Diagnostic Monitor
(ADDM). For more information, see Chapter 14.
19. B. Oracle automatically performs instance recovery after a database crash or SHUTDOWN
ABORT. All uncommitted changes are rolled back, and committed changes are written to
data files during instance recovery. Read Chapter 9 for more information.
95127flast.indd 61 2/18/09 6:16:19 AM
Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
lxii

Answers to Administration I Assessment Test
20. C. The MINIMUM EXTENT parameter is used to make sure each extent is a multiple of the
value specified on dictionary-managed tablespaces. This parameter is useful to reduce frag-
mentation in the tablespace. Oracle discourages the use of dictionary-managed tablespaces.
You should use locally managed tablespaces. Read Chapter 10 for more information.
21. D. Protocols come with tools that allow you to test network connectivity. One such utility
for TCP/IP is ping. The user supplies either an IP address or a hostname to the ping util-
ity. It then searches the network for this address. If it finds one, it displays information on

file and trace files. Read Chapter 14 to learn more about alert log and trace file locations.
95127flast.indd 62 2/18/09 6:16:19 AM
Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
Answers to Administration I Assessment Test

lxiii
33. B. The highest level at which a user can request a lock is the table level; the only other lock
level available to a user is a row-level lock. Users cannot lock at the block or schema level.
Read Chapter 13 to learn more.
34. C. There are no standard password-complexity settings in either the initialization param-
eters or the profiles. A password-verify function can validate new passwords against any
rules that you can code in PL/SQL, including regular-expression comparisons. See Chapter
12 for more information.
35. A. You can use the Undo Management Advisor to monitor and manage the undo segments
to ensure maximum levels of read consistency and minimize occurrences of “ORA-01555:
Snapshot Too Old” error messages. For more information, see Chapter 14.
36. B. Redo log files record all the changes made to Oracle Database, whether the changes are
committed or not. To learn more about redo log files and database recovery, read Chapters
15 and 16.
37. B. Settings such as the control file autobackup filename format and the snapshot-control
file destination filename must be configured using the RMAN command-line interface. To
learn more, read Chapter 15.
38. D. Options A and B are a lot of work. The remap_datafile parameter applies only
to CREATE TABLESPACE and CREATE DIRECTORY statements, not indexes. The remap_
tablespace parameter tells Data Pump import to change the tablespace that objects are
stored in between the source and the target database. See Chapter 17 for more information.
39. D. The distance between the checkpoint position in the redo log file and the end of the
redo log group can never be more than 90 percent of the size of the smallest redo log group.
Read Chapter 16 to learn more about checkpoints and instance recovery.
40. C. If the redo log file group has never been used, the value of STATUS is UNUSED until the