Practice Q & A
(Chapter 14 - 18)
Chapter 14
1.
The USER_OBJECTS view shows information about synonyms, tables,
views, and packages.
True
False
2.
You can determine if a column has a default value by querying the data
dictionary view USER_CONSTRAINTS.
True
False
3.
Unique indexes created by the primary key constraint are not stored in the
USER_INDEXES view.
True
False
4.
To find out how much space is allocated to a table or index, you can query
the USER_SEGMENTS table.
True
False
5.
The following represents a multiline comment:
/* This is a comment
that spawns several lines.
*/
True
False
Additional Q&A for Chapters 14-18; Oracle SQL by Example by Alice Rischert; Prentice Hall (2009)
Page 1 of 11
Version Date: 5/4/2009
6.
The SET DEFINE OFF command turns the use of the substitution
parameter, typically the ampersand (&) symbol, off.
True
False
7.
The SPOOL command is a SQL*Plus command.
True
False
8.
The SET FEEDBACK 0 command shows the result of the SQL statement
on the screen.
True
False
9.
The v$ tables represent the static data dictionary. They can only be
updated by Oracle.
True
False
10.
The following query specifies a data dictionary view.
SELECT *
FROM tabs
True
False
Answers for "Chapter 14"
1.
True
The USER_OBJECTS view shows information about synonyms, tables,
views, and packages.
Additional Q&A for Chapters 14-18; Oracle SQL by Example by Alice Rischert; Prentice Hall (2009)
Page 2 of 11
Version Date: 5/4/2009
2.
False
You can determine if a column has a default value by querying the
data dictionary view USER_CONSTRAINTS.
The view USER_TAB_COLUMNS shows this information.
3.
False
Unique indexes created by the primary key constraint are not stored in
the USER_INDEXES view.
4.
True
To find out how much space is allocated to a table or index, you can
query the USER_SEGMENTS table.
5.
True
The following represents a multiline comment:
/* This is a comment
that spawns several lines.
*/
6.
True
The SET DEFINE OFF command turns the use of the substitution
parameter, typically the ampersand (&) symbol, off.
7.
True
The SPOOL command is a SQL*Plus command.
8.
False
The SET FEEDBACK 0 command shows the result of the SQL
statement on the screen.
It shows the number of rows returned.
9.
False
The v$ tables represent the static data dictionary. They can only be
updated by Oracle.
The v$ tables are referred to as the dynamic data dictionary views. They are
only updated by Oracle.
10.
True
The following query specifies a data dictionary view.
SELECT *
FROM tabs
Additional Q&A for Chapters 14-18; Oracle SQL by Example by Alice Rischert; Prentice Hall (2009)
Page 3 of 11
Version Date: 5/4/2009
Note: The TABS view is the synonym of the USER_TABLES view.
Chapter 15
1.
Privileges, roles, and synonyms are all used to implement security in an
Oracle database.
False
True
2.
Any user can grant or revoke any type of system privilege to or from another
user.
True
False
3.
The data dictionary view USER_TAB_PRIVS_MADE lists details of table
privilege grants performed by the current user.
True
False
4.
You cannot drop a user if objects exist in the user's schema.
True
False
5.
Both object and system privileges can be granted through a single role to a
user.
True
False
Answers for "Chapter 15"
1.
True
Privileges, roles, and synonyms are all used to implement security in
an Oracle database.
2.
False
Any user can grant or revoke any type of system privilege to or from
another user.
Additional Q&A for Chapters 14-18; Oracle SQL by Example by Alice Rischert; Prentice Hall (2009)
Page 4 of 11
Version Date: 5/4/2009
3.
True
The data dictionary view USER_TAB_PRIVS_MADE lists details of table
privilege grants performed by the current user.
4.
False
You cannot drop a user if objects exist in the user's schema.
Note: A user and its objects can be dropped simultaneously with the DROP
USER CASCADE command.
5.
True
Both object and system privileges can be granted through a single role
to a user
Additional Q&A for Chapters 14-18; Oracle SQL by Example by Alice Rischert; Prentice Hall (2009)
Page 5 of 11
Version Date: 5/4/2009
Chapter 16
1.
You identify the relationship between the parent and the child by placing the
PRIOR operator before the parent column.
True
False
2.
To eliminate rows from the hierarchy tree, you use the WHERE clause.
True
False
3.
Choose the result that the following statement will return.
SELECT REGEXP_SUBSTR('The plane leaves 02/05/04 at 04:23 pm.',
'([0-1][0-9]|2[0-3]):[0-5][0-9]')
FROM dual
02/05/04
4:23 pm
04:23
Invalid query
4.
What is the result of this query?
SELECT REGEXP_SUBSTR('This is a sentence.','.+[[:space:]]')
FROM dual
This is a
This is a sentence.
This
Invalid query
Answers for "Chapter 16"
1.
True
You identify the relationship between the parent and the child by
Additional Q&A for Chapters 14-18; Oracle SQL by Example by Alice Rischert; Prentice Hall (2009)
Page 6 of 11
Version Date: 5/4/2009
placing the PRIOR operator before the parent column .
2.
True
3.
04:23
To eliminate rows from the hierarchy tree, you use the WHERE clause.
Choose the result that the following statement will return.
SELECT REGEXP_SUBSTR('The plane leaves 02/05/04 at 04:23 pm.',
'([0-1][0-9]|2[0-3]):[0-5][0-9]')
FROM dual
Note: The regular expression checks for the time, not the date. It
requires that the string begins with a 0, 1 or 2. It validates that the
hour is of a format between 00:00 and 23:59.
4.
This is a
What is the result of this query?
SELECT REGEXP_SUBSTR('This is a sentence.','.+[[:space:]]')
FROM dual
Note: Oracle's regular expression implementation is greedy and
attempts to match as much as possible. The second answer choice is
incorrect because the regular expression requires that the string end
with a space at the end.
Chapter 17
1.
The OVER keyword as part of the analytical function syntax indicates that
the function operates after the results of the FROM, WHERE, GROUP BY,
and HAVING clauses have been formed.
True
False
2.
The ranking function DENSE_RANK assigns duplicate values a different
rank.
True
False
3.
The ORDER BY clause is required for all ranking functions.
Additional Q&A for Chapters 14-18; Oracle SQL by Example by Alice Rischert; Prentice Hall (2009)
Page 7 of 11
Version Date: 5/4/2009
True
False
4.
Then WINDOWING clause allows you to compute cumulative, moving, and
centered aggregates.
True
False
5.
The RANGE keyword indicates a physical window.
True
False
6.
The GROUPING function helps determine if a row is a summary row or not.
True
False
Additional Q&A for Chapters 14-18; Oracle SQL by Example by Alice Rischert; Prentice Hall (2009)
Page 8 of 11
Version Date: 5/4/2009
Answers for "Chapter 17"
1.
True
The OVER keyword as part of the analytical function syntax indicates
that the function operates after the results of the FROM, WHERE,
GROUP BY, and HAVING clauses have been formed.
2.
False
The ranking function DENSE_RANK assigns duplicate values a different
rank.
The exception is COUNT(*), which does take NULL values into account.
3.
True
The ORDER BY clause is required for all ranking functions.
4.
True
Then WINDOWING clause allows you to compute cumulative, moving,
and centered aggregates.
5.
False
The RANGE keyword indicates a physical window.
Note: The RANGE keyword indicates a logical window. A physical window is
defined with the ROWS keyword.
6.
True
The GROUPING function helps determine if a row is a summary row or
not.
Chapter 18
1.
One of the tasks performed when a SQL statement is parsed, is checking
for valid syntax and user access rights.
True
False
2.
A hint is enclosed by either a multiline comment with a plus sign (/*+ */) or
prefixed by a single line comment with a plus (--+).
Additional Q&A for Chapters 14-18; Oracle SQL by Example by Alice Rischert; Prentice Hall (2009)
Page 9 of 11
Version Date: 5/4/2009
True
False
3.
The ORDERED hint causes the tables to be joined in the order listed in the
FROM clause of the query.
True
False
4.
The nested loop join is typically the fastest join type if you want to join the
majority of data from two tables.
True
False
5.
If your statements use bind variables, you can eliminate the parsing step.
True
False
6.
If your data is uniformly distributed, histograms are useful to help determine
the best execution plan.
True
False
7.
When joining tables make sure to choose the table that returns the largest
number of rows as the driving table.
True
False
Additional Q&A for Chapters 14-18; Oracle SQL by Example by Alice Rischert; Prentice Hall (2009)
Page 10 of 11
Version Date: 5/4/2009
Answers for "Chapter 18"
1.
True
One of the tasks performed when a SQL statement is parsed, is
checking for valid syntax and user access rights.
2.
True
A hint is enclosed by either a multiline comment with a plus sign (/*+
*/) or prefixed by a single line comment with a plus (--+).
3.
True
The ORDERED hint causes the tables to be joined in the order listed in
the FROM clause of the query.
4.
False
The nested loop join is typically the fastest join type if you want to
join the majority of data from two tables.
5.
True
If your statements use bind variables, you can eliminate the parsing
step.
6.
False
If your data is uniformly distributed, histograms are useful to help
determine the best execution plan.
Histograms are useful if your data is skewed.
7.
False
When joining tables make sure to choose the table that returns the
largest number of rows as the driving table.
Additional Q&A for Chapters 14-18; Oracle SQL by Example by Alice Rischert; Prentice Hall (2009)
Page 11 of 11
Version Date: 5/4/2009
