ORACLE SQL BY EXAMPLE BY ALICE RISCHERT - Home



Practice Q & A

(Chapter 14 - 18)

Chapter 14

|[pic] |

|1 . |     |The USER_OBJECTS view shows information about synonyms, tables, views, and packages. |

| | | |

| | |[pic] True |

| | |[pic] False |

|[pic] |

|2 . |     |You can determine if a column has a default value by querying the data dictionary view USER_CONSTRAINTS.   |

| | | |

| | |[pic] True |

| | |[pic] False |

|[pic] |

|3 . |     |Unique indexes created by the primary key constraint are not stored in the USER_INDEXES view. |

| | |[pic] True |

| | |[pic] False |

|[pic] |

|4 . |     |To find out how much space is allocated to a table or index, you can query the USER_SEGMENTS table. |

| | |[pic] True |

| | |[pic] False |

|[pic] |

|5 . |     |The following represents a multiline comment: |

| | |/* This is a comment |

| | |that spawns several lines. |

| | |*/ |

| | | [pic] True |

| | | [pic] False |

|[pic] |

|6 . |     |The SET DEFINE OFF command turns the use of the substitution parameter, typically the ampersand (&) symbol, off. |

| | | |

| | |[pic] True |

| | |[pic] False |

|[pic] |

|7 . |     |The SPOOL command is a SQL*Plus command. |

| | |[pic] True |

| | |[pic] False |

|[pic] |

|8 . |     |The SET FEEDBACK 0 command shows the result of the SQL statement on the screen. |

| | | |

| | |[pic] True |

| | |[pic] False |

|[pic] |

|9 . |     |The v$ tables represent the static data dictionary. They can only be updated by Oracle. |

| | | |

| | |[pic] True |

| | |[pic] False |

|[pic] |

|10. |     |The following query specifies a data dictionary view. |

| | |SELECT * |

| | |FROM tabs |

| | | |

| | |[pic] True |

| | |[pic] False |

Answers for "Chapter 14"

|[pic] |

|1. |True |The USER_OBJECTS view shows information about synonyms, tables, views, and packages. |

| |[pic] | |

|[pic] |

|2. |False |You can determine if a column has a default value by querying the data dictionary view |

| |[pic] |USER_CONSTRAINTS. |

| | |The view USER_TAB_COLUMNS shows this information. |

|[pic] |

|3. |False |Unique indexes created by the primary key constraint are not stored in the USER_INDEXES view. |

| |[pic] | |

|[pic] |

|4. |True |To find out how much space is allocated to a table or index, you can query the USER_SEGMENTS |

| |[pic] |table. |

|[pic] |

|5. |True [pic] |The following represents a multiline comment: |

| | |/* This is a comment |

| | |that spawns several lines. |

| | |*/ |

|[pic] |

|6. |True [pic] |The SET DEFINE OFF command turns the use of the substitution parameter, typically the ampersand |

| | |(&) symbol, off. |

|[pic] |

|7. |True [pic] |The SPOOL command is a SQL*Plus command. |

|[pic] |

|8. |False |The SET FEEDBACK 0 command shows the result of the SQL statement on the screen. |

| |[pic] | |

| | |It shows the number of rows returned. |

|[pic] |

|9. |False |The v$ tables represent the static data dictionary. They can only be updated by Oracle. |

| |[pic] | |

| | |The v$ tables are referred to as the dynamic data dictionary views. They are only updated by |

| | |Oracle. |

|[pic] |

|10. |True [pic] |The following query specifies a data dictionary view. |

| | |SELECT * |

| | |FROM tabs |

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

|[pic] |

Chapter 15

|1. |     |Privileges, roles, and synonyms are all used to implement security in an Oracle database. |

| | |[pic]  |

| | |False |

| | | |

| | |[pic]  |

| | |True |

| | | |

|[pic] |

|2. |     |Any user can grant or revoke any type of system privilege to or from another user. |

| | |[pic]  |

| | |True |

| | | |

| | |[pic]  |

| | |False |

| | | |

|[pic] |

|3. |     |The data dictionary view USER_TAB_PRIVS_MADE lists details of table privilege grants performed by the current user. |

| | |[pic]  |

| | |True |

| | | |

| | |[pic]  |

| | |False |

| | | |

|[pic] |

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

| | |[pic]  |

| | |True |

| | | |

| | |[pic]  |

| | |False |

| | | |

|[pic] |

|5. |     |Both object and system privileges can be granted through a single role to a user. |

| | |[pic]  |

| | |True |

| | | |

| | |[pic]  |

| | |False |

| | | |

Answers for "Chapter 15"

|[pic] |

|1. |True |Privileges, roles, and synonyms are all used to implement security in an Oracle database. |

| |[pic] | |

|[pic] |

|2. |False |Any user can grant or revoke any type of system privilege to or from another user. |

| |[pic] | |

|[pic] |

|3. |True |The data dictionary view USER_TAB_PRIVS_MADE lists details of table privilege grants performed by |

| |[pic] |the current user. |

|[pic] |

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

| |[pic] | |

| | |Note: A user and its objects can be dropped simultaneously with the DROP USER CASCADE command. |

|[pic] |

|5. |True [pic] |Both object and system privileges can be granted through a single role to a user |

|[pic] |

Chapter 16

|[pic] |

|1. |     |You identify the relationship between the parent and the child by placing the PRIOR operator before the parent column. |

| | | |

| | |[pic] True |

| | |[pic] False |

|[pic] |

|2. |     |To eliminate rows from the hierarchy tree, you use the WHERE clause. |

| | | |

| | |[pic] True |

| | |[pic] False |

|[pic] |

|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 |

| | |[pic] 02/05/04 |

| | |[pic] 4:23 pm |

| | |[pic] 04:23 |

| | |[pic] Invalid query |

|[pic] |

|4. |     |What is the result of this query? |

| | |SELECT REGEXP_SUBSTR('This is a sentence.','.+[[:space:]]') |

| | |FROM dual |

| | | |

| | |[pic] This is a |

| | |[pic] This is a sentence. |

| | |[pic] This |

| | |[pic] Invalid query |

|[pic] |

Answers for "Chapter 16"

|[pic] |

|1. |True [pic] |You identify the relationship between the parent and the child by placing the PRIOR operator |

| | |before the parent column. |

|[pic] |

|2. |True [pic] |To eliminate rows from the hierarchy tree, you use the WHERE clause. |

|[pic] |

|3. |04:23 [pic] |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. |

|[pic] |

|4. |This is a [pic] |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. |

|[pic] |

Chapter 17

|[pic] |

|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. |

| | |[pic] True |

| | |[pic] False |

|[pic] |

|2 . |     |The ranking function DENSE_RANK assigns duplicate values a different rank. |

| | | |

| | |[pic] True |

| | |[pic] False |

|[pic] |

|3 . |     |The ORDER BY clause is required for all ranking functions. |

| | | |

| | |[pic] True |

| | |[pic] False |

|[pic] |

|4 . |     |Then WINDOWING clause allows you to compute cumulative, moving, and centered aggregates. |

| | | |

| | |[pic] True |

| | |[pic] False |

|[pic] |

|5 . |     |The RANGE keyword indicates a physical window. |

| | | |

| | |[pic] True |

| | |[pic] False |

|[pic] |

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

| | |[pic] True |

| | |[pic] False |

|[pic] |

Answers for "Chapter 17"

|[pic] |

|1. |True |The OVER keyword as part of the analytical function syntax indicates that the function operates |

| |[pic] |after the results of the FROM, WHERE, GROUP BY, and HAVING clauses have been formed. |

|[pic] |

|2. |False |The ranking function DENSE_RANK assigns duplicate values a different rank. |

| |[pic] | |

| | |The exception is COUNT(*), which does take NULL values into account. |

|[pic] |

|3. |True |The ORDER BY clause is required for all ranking functions. |

| |[pic] | |

|[pic] |

|4. |True |Then WINDOWING clause allows you to compute cumulative, moving, and centered aggregates. |

| |[pic] | |

|[pic] |

|5. |False [pic] |The RANGE keyword indicates a physical window. |

| | |Note: The RANGE keyword indicates a logical window. A physical window is defined with the ROWS |

| | |keyword. |

|[pic] |

|6. |True [pic] |The GROUPING function helps determine if a row is a summary row or not. |

|[pic] |

Chapter 18

|[pic] |

|1 . |     |One of the tasks performed when a SQL statement is parsed, is checking for valid syntax and user access rights. |

| | | |

| | |[pic] True |

| | |[pic] False |

|[pic] |

|2 . |     |A hint is enclosed by either a multiline comment with a plus sign (/*+ */) or prefixed by a single line comment with a |

| | |plus (--+).  |

| | | |

| | |[pic] True |

| | |[pic] False |

|[pic] |

|3 . |     |The ORDERED hint causes the tables to be joined in the order listed in the FROM clause of the query. |

| | | |

| | |[pic] True |

| | |[pic] False |

|[pic] |

|4 . |     |The nested loop join is typically the fastest join type if you want to join the majority of data from two tables. |

| | | |

| | |[pic] True |

| | |[pic] False |

|[pic] |

|5 . |     |If your statements use bind variables, you can eliminate the parsing step. |

| | | |

| | |[pic] True |

| | |[pic] False |

|[pic] |

|6 . |     |If your data is uniformly distributed, histograms are useful to help determine the best execution plan. |

| | | |

| | |[pic] True |

| | |[pic] False |

|[pic] |

|7 . |     |When joining tables make sure to choose the table that returns the largest number of rows as the driving table. |

| | |[pic] True |

| | |[pic] False |

Answers for "Chapter 18"

|[pic] |

|1. |True |One of the tasks performed when a SQL statement is parsed, is checking for valid syntax and user |

| |[pic] |access rights. |

|[pic] |

|2. |True [pic] |A hint is enclosed by either a multiline comment with a plus sign (/*+ */) or prefixed by a single|

| | |line comment with a plus (--+). |

|[pic] |

|3. |True [pic] |The ORDERED hint causes the tables to be joined in the order listed in the FROM clause of the |

| | |query. |

|[pic] |

|4. |False [pic] |The nested loop join is typically the fastest join type if you want to join the majority of data |

| | |from two tables. |

|[pic] |

|5. |True [pic] |If your statements use bind variables, you can eliminate the parsing step. |

|[pic] |

|6. |False [pic] |If your data is uniformly distributed, histograms are useful to help determine the best execution |

| | |plan. |

| | |Histograms are useful if your data is skewed. |

|[pic] |

|7. |False [pic] |When joining tables make sure to choose the table that returns the largest number of rows as the |

| | |driving table. |

|[pic] |

................
................

In order to avoid copyright disputes, this page is only a partial summary.

Google Online Preview   Download