Oracle 1z0-071 Exam Made Easy: Step-by-Step Preparation Guide

Object privileges applicable to tables, views, and sequences in Oracle are:

B. REFERENCES can be granted only on tables. It allows the grantee to create a foreign key that refers to the table.

D. SELECT can be granted on tables and views. This allows the grantee to perform a SELECT on the table or view.

A, C, and E are incorrect. Specifically:

A is incorrect because DELETE cannot be granted on sequences; it is a privilege applicable to tables and views.

C is incorrect because INSERT cannot be granted on sequences; INSERT is applicable only to tables and views.

E is incorrect because ALTER is a privilege applicable to tables and indexes, not sequences.

References:

Oracle Database SQL Language Reference, 12c Release 1 (12.1): "GRANT"

Regarding INTERVAL data types in Oracle Database 12c:

A. INTERVAL YEAR TO MONTH columns only support monthly intervals within a range of years. This is true. The INTERVAL YEAR TO MONTH data type stores a period of time using years and months.

E. INTERVAL DAY TO SECOND columns support fractions of seconds. This is true. The INTERVAL DAY TO SECOND data type can store days, hours, minutes, seconds, and fractional seconds.

Options B, C, D, and F are incorrect:

B is incorrect because data types between INTERVAL DAY TO SECOND and INTERVAL YEAR TO MONTH are not compatible.

C is incorrect as it incorrectly limits INTERVAL YEAR TO MONTH to a single year.

D is incorrect; the YEAR field can be negative to represent a past interval.

F is incorrect as INTERVAL YEAR TO MONTH supports intervals that can span multiple years, not just annual increments.

In SQL, the GROUP BY clause is used in conjunction with aggregate functions to group the result-set by one or more columns.

A. This statement will execute successfully. MAX() is an aggregate function that can be used to return the highest value of the selected column, and SUM() is an aggregate function used to sum the values. Both are valid in the SELECT statement with a GROUP BY clause grouping the results by dept_id.

B. This statement will not execute successfully when used with GROUP BY because LENGTH(last_name) is not an aggregate function and it doesn't appear in the GROUP BY clause. All selected columns that are not under an aggregate function must be included in the GROUP BY clause.

C. This statement will not execute successfully because STDDEV() is an aggregate function that calculates the standard deviation of a set of numbers, and it can only be used on numeric data types. last_name is not a numeric column.

D. This statement will not execute successfully for the same reason as B: INSTR(last_name, 'A') is not an aggregate function and must appear in the GROUP BY clause if it's in the SELECT clause.

In SQL, the ORDER BY clause can refer to columns by their alias defined in the SELECT clause or by their positional number in the SELECT list. It's important to understand that after a UNION, the column names in the ORDER BY clause refer to the first SELECT statement's column names and aliases:

Option A: ORDER BY 2, 1This is correct because it refers to the second and first columns in the first SELECT clause, which correspond to the aliases "Last Name" and cust_id, respectively.

Option E: ORDER BY "Last Name"This is correct because "Last Name" is a valid alias defined in the first SELECT clause.

The other options fail for the following reasons:

Option B: "CUST_NO" is not recognized in the ORDER BY clause because it's not an alias used in the first SELECT statement.

Option C: Incorrect because 2, cust_id mixes positional reference with a column name that doesn't apply to both SELECT statements consistently.

Option D: ORDER BY CUST_NO fails because CUST_NO is not an alias in the first SELECT clause.

B. True. Oracle databases guarantee read consistency at the transaction level, meaning that each query can only see data that has been committed before the query began executing.

C. True. When an UPDATE statement is executed, Oracle locks each row as it is updated to prevent other transactions from modifying it until the transaction is committed or rolled back.

A is incorrect because updates made by one user cannot be rolled back by another user. D is incorrect because a query can access tables in other schemas if proper permissions are granted. E is incorrect because a user can own only one schema, which has the same name as the user in an Oracle database.

A: Insert statements can be done through a view only if all NOT NULL constraints without default values of the base table are included in the view. Therefore, statement A is incorrect.

B: The WITH CHECK OPTION ensures that all DML operations performed through the view result in data that conforms to the view’s defining query. It affects DELETE as well as other DML operations, making statement B incorrect.

C: Similar to inserts, DELETE operations can be done through a view unless the view contains constructs that inherently do not support it, such as certain joins or set operations. Statement C is generally incorrect.

D: If a view does not include all NOT NULL columns without defaults of the underlying table, it cannot be used to add rows because the missing columns will lack values. This makes statement D correct.

E: This statement is incorrect as primary keys do not affect querying through views; they affect insert and update operations where uniqueness and non-nullability are enforced.

F: If the defining query of a view includes DISTINCT, the view generally cannot be used to perform update or insert operations as it may not be able to uniquely determine rows. This makes statement F correct.

When you use the INTERVAL literal for months, the result is displayed in years and months if the total number of months exceeds 12.

In the query given:

SELECT INTERVAL '100' MONTH DURATION FROM DUAL;

the output will display the interval of 100 months converted to years and the remaining months. 100 months is equal to 8 years and 4 months. Hence, the correct answer is:

DURATION +08-04

For the *TABLES views in Oracle:

Option C: USER_TABLES displays all tables owned by the current user.

USER_TABLES is an Oracle data dictionary view that shows all tables owned by the user issuing the query.

Option F: All users can query DBA_TABLES successfully.

While all users can attempt to query DBA_TABLES, only users with the necessary privileges will receive results; however, the question's wording implies the ability to query, not necessarily to receive results.

Options A, B, D, and E are incorrect:

Option A and Option E are incorrect because ALL_TABLES and USER_TABLES show tables accessible to or owned by the current user, respectively, without requiring individual SELECT privileges.

Option B is incorrect because DBA_TABLES requires users to have the SELECT ANY TABLE privilege or equivalent, not SELECT privileges on each table.

Option D is incorrect because ALL_TABLES displays all tables that the current user has access to, not just those owned by them.

When using the TRUNCATE TABLE command in Oracle, several aspects of the table's structure and associated database objects are impacted. Here's an explanation of each option:

A: Incorrect. TRUNCATE TABLE does not drop triggers associated with the table; they remain defined.

B: Correct. Indexes on the table are retained and not dropped when you truncate a table. However, if the index is a domain index, it may be dropped depending on its type.

C: Correct. Integrity constraints such as primary keys, foreign keys, etc., are retained unless they are on a disabled state where truncation can lead to constraint being dropped.

D: Incorrect. A TRUNCATE TABLE operation cannot be rolled back. It is a DDL (Data Definition Language) operation and commits automatically.

E: Incorrect. The TRUNCATE TABLE operation deallocates the space used by the data unless the REUSE STORAGE clause is specified.

F: Incorrect. TRUNCATE TABLE operation removes all the rows in a table and does not log individual row deletions, thus FLASHBACK TABLE cannot be used to retrieve the data.

In the SQL statement provided, the error is caused by the GROUP BY clause. In Oracle SQL, when using GROUP BY, the expression in the GROUP BY clause must match exactly the expression in the SELECT list. The GROUP BY clause should reference the column name dept_no as mentioned in the SELECT list, not the alias department_id which is defined within the same SELECT statement.

The corrected SQL statement should be:

SELECT dept_no AS department_id, MAX(salary) As max_sal FROM employees WHERE salary > 10000 GROUP BY dept_no -- Changed from 'department_id' to 'dept_no' ORDER BY max_sal;

References:

Oracle Documentation on GROUP BY clause: GROUP BY Clause