Practice Q's 14-18

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.
Page 2 of 11
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
Page 3 of 11
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
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.
Page 4 of 11
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
Page 5 of 11
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
1.
True
You identify the relationship between the parent and the child by
Page 6 of 11
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.
Page 7 of 11
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
Page 8 of 11
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 (--+).
Page 9 of 11
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
Page 10 of 11
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.
Page 11 of 11

Practice Q's 14-18

Uploaded by

Copyright:

Available Formats

Practice Q's 14-18

Uploaded by

Document Information

Copyright

Available Formats

Share this document

Share or Embed Document

Sharing Options

Did you find this document useful?

Is this content inappropriate?

Copyright:

Available Formats

Practice Q's 14-18

Uploaded by

Copyright:

Available Formats

What are the main data dictionary views in Oracle?

What are the main data dictionary views in Oracle?

What are some ways to implement security in an Oracle database?

What are some ways to implement security in an Oracle database?

Practice Q & A

The USER_OBJECTS view shows information about synonyms, tables,

The following represents a multiline comment:

The SPOOL command is a SQL*Plus command.

The following query specifies a data dictionary view.

Answers for "Chapter 14"

The USER_OBJECTS view shows information about synonyms, tables,

You can determine if a column has a default value by querying the

The following represents a multiline comment:

The SPOOL command is a SQL*Plus command.

The SET FEEDBACK 0 command shows the result of the SQL

The following query specifies a data dictionary view.

Note: The TABS view is the synonym of the USER_TABLES view.

Privileges, roles, and synonyms are all used to implement security in an

The data dictionary view USER_TAB_PRIVS_MADE lists details of table

You cannot drop a user if objects exist in the user's schema.

Answers for "Chapter 15"

Privileges, roles, and synonyms are all used to implement security in

The data dictionary view USER_TAB_PRIVS_MADE lists details of table

You cannot drop a user if objects exist in the user's schema.

Choose the result that the following statement will return.

What is the result of this query?

Answers for "Chapter 16"

placing the PRIOR operator before the parent column .

Choose the result that the following statement will return.

What is the result of this query?

The ranking function DENSE_RANK assigns duplicate values a different

The ORDER BY clause is required for all ranking functions.

Then WINDOWING clause allows you to compute cumulative, moving, and

The RANGE keyword indicates a physical window.

The GROUPING function helps determine if a row is a summary row or not.

Answers for "Chapter 17"

The OVER keyword as part of the analytical function syntax indicates

The ranking function DENSE_RANK assigns duplicate values a different

The ORDER BY clause is required for all ranking functions.

Then WINDOWING clause allows you to compute cumulative, moving,

The RANGE keyword indicates a physical window.

The GROUPING function helps determine if a row is a summary row or

One of the tasks performed when a SQL statement is parsed, is checking

If your data is uniformly distributed, histograms are useful to help determine

Answers for "Chapter 18"

One of the tasks performed when a SQL statement is parsed, is

A hint is enclosed by either a multiline comment with a plus sign (/*+

If your data is uniformly distributed, histograms are useful to help

You might also like