1z0-071 Exam Dumps - Oracle PL Questions and Answers

The actions possible using the ALTER TABLE command:

E. Rename a table: The ALTER TABLE command can be used to rename a table, although the syntax typically involves RENAME TO as part of table management.

F. Enable or disable constraints on a table: This is a standard use of the ALTER TABLE command, allowing for constraints to be enabled or disabled, which is critical for managing table integrity during data loads or maintenance.

Incorrect options:

A: You cannot drop pseudo columns from a table as they are not explicitly defined or managed like regular columns.

B: The ALTER TABLE command does not directly restrict DML statements; this would typically be managed through security roles or other database mechanisms.

C: Dropping all columns from a table simultaneously is not possible; a table must retain at least one column.

D: Locking a set of rows is performed with SQL commands like SELECT FOR UPDATE, not with ALTER TABLE.

Given the statements, the failure to execute would be due to improper SQL syntax or logical structuring of the JOIN clause:

A. SELECT * FROM employees e JOIN department d WHERE e.department_id=d.department_id AND d.department_id=90: This statement will fail because it incorrectly uses the JOIN syntax without an ON clause, which is necessary to specify the condition on which the tables are joined. This statement mistakenly places the join condition in the WHERE clause without using ON.

Correct options (executes successfully):

B: Correctly formulated JOIN with ON and an additional condition in the WHERE clause.

C: Incorporates the filtering directly in the ON clause, which is a valid method to restrict the rows returned by the JOIN based on conditions related to both tables.

D: Although it appears awkward due to the order of conditions in the ON clause, it is syntactically correct.

These answers reflect typical use and constraints in SQL queries and are aligned with Oracle Database 12c standards for SQL syntax and operations.

Roles and privileges in Oracle manage access and capabilities within the database:

Option A: False. Roles are not "owned" in the traditional sense by the user who created them. They exist independently within the Oracle database and are assigned to users.

Option B: False. System privileges can be very granular, affecting specific types of operations or database objects, not always the entire database.

Option C: False. Roles are not owned by the SYS schema but are managed by database security and can be created by any user with sufficient privileges.

Option D: True. A role can indeed contain a combination of several privileges, including other roles, allowing for flexible and layered security configurations.

Option E: True. By default, a user has all object privileges for objects they own (i.e., objects in their schema).

Option F: False. PUBLIC is a special designation that applies to all users; individual privileges granted to PUBLIC cannot be revoked from a single user without revoking them from all users.

Option G: True. PUBLIC is a role granted by default to every user in an Oracle database, providing basic privileges necessary for general usability of the database.

A. False - The CREATE TABLE command cannot include a CREATE INDEX statement within it. Indexes to enforce constraints like primary keys are generally created automatically when the constraint is defined, or they must be created separately using the CREATE INDEX command.

B. True - The owner of the table needs to have enough space quota on the tablespace where the table is going to be created, unless they have the UNLIMITED TABLESPACE privilege. This ensures that the database can allocate the necessary space for the table. Reference: Oracle Database SQL Language Reference, 12c Release 1 (12.1).

C. True - The CREATE TABLE command implicitly commits the current transaction before it executes. This behavior ensures that table creation does not interfere with transactional consistency. Reference: Oracle Database SQL Language Reference, 12c Release 1 (12.1).

D. False - It does not implicitly roll back any pending transactions; rather, it commits them.

E. True - A user must have the CREATE ANY TABLE privilege to create tables in any schema other than their own. To create tables in their own schema, they need the CREATE TABLE privilege. Reference: Oracle Database Security Guide, 12c Release 1 (12.1).

F. False - While the UNLIMITED TABLESPACE privilege allows storing data without quota restrictions on any tablespace, it is not a mandatory requirement for a table owner. Owners can create tables as long as they have sufficient quotas on the specific tablespaces.

In Oracle Database 12c SQL, regarding date/time functions and considering a session where NLS_DATE_FORMAT is set to DD-MON-YYYY SH24:MI:SS:

C. CURRENT_DATE returns the current date and time as per the session time zone. This is correct as CURRENT_DATE returns the current date and time in the time zone of the current SQL session, as set by the ALTER SESSION command.

D. SYSDATE and CURRENT_DATE return the current date and time set for the operating system of the database server. This is partially correct. SYSDATE returns the current date and time from the operating system of the database server. However, CURRENT_DATE returns the date and time set for the client's operating system environment, adjusted to the session time zone.

Options A, B, E, and F are incorrect based on Oracle's documentation:

A is incorrect because SYSDATE is independent of the NLS_DATE_FORMAT setting.

B is incorrect because CURRENT_TIMESTAMP includes time zone information, which can differ from CURRENT_DATE.

E is incorrect because CURRENT_TIMESTAMP differs from SYSDATE by including fractional seconds and time zone.

F is incorrect as SYSDATE can be queried in any SELECT statement, not just from DUAL.

In Oracle SQL, aggregation functions such as AVG and MAX cannot be nested directly inside each other and must be used in conjunction with GROUP BY on the column(s) that are not included in the aggregate function. Also, the HAVING clause filters groups after aggregation is applied.

A. This query will not execute successfully because it improperly nests MAX inside AVG. Oracle SQL does not allow this type of nested aggregation without a subquery.

B. This query will not execute successfully for the same reason as A, it improperly nests MAX inside AVG.

C. Similar to A and B, this query improperly nests SUM inside MAX, which is not allowed without a subquery.

D. This query will execute successfully. It filters rows based on the HIRE_DATE using a correct date format (assuming '9' refers to '09' or '1999' due to the NLS_DATE_FORMAT being 'DD-MON-RR'), then groups the remaining rows by DEPT_ID and calculates the sum of SALARY for each department.

E. This query will not execute successfully because it improperly nests MAX inside AVG without a subquery, and it incorrectly attempts to GROUP BY SALARY, which is already being aggregated.

References:

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

Oracle Database SQL Language Reference, 12c Release 1 (12.1): "GROUP BY Clause"

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

C: True. A SQL statement may include multiple single row subqueries in different parts of the statement, such as in the SELECT list, WHERE clause, or HAVING clause. Each subquery must independently satisfy the requirement of returning a single row to avoid runtime errors.

D: True. Single row subqueries can be used in the HAVING clause. This allows for filtering groups based on conditions evaluated against individual or aggregated values returned by the subquery. The subquery must return a single value to be valid in this context.

E: True. Single row subqueries are often placed on the right side of the comparison operator in a SQL condition. This positioning is typical because the left side often references a column or an expression related to the main query, while the subquery on the right side dynamically provides a value for comparison.

Non-equi joins are joins where the join condition is based on something other than equality. In Oracle SQL, you can perform non-equi joins using various clauses:

A. The ON clause can be used: True, the ON clause can specify any condition for the join, including non-equijoins. It is not limited to equijoins.

E. The Oracle join syntax can be used: The traditional Oracle join syntax, which uses the WHERE clause to specify the join condition, can be used for all types of joins, including non-equijoins.

References:

Oracle Database SQL Language Reference 12c, especially the sections on join clauses including the ON clause and Oracle's proprietary join syntax.

B. CREATE INDEX price_idx on products (price); E. ALTER TABLE products DROP UNUSED COLUMNS; F. DROP TABLE products.

Comprehensive and Detailed Explanation WITH all References:

When a table is in read-only mode, most types of modifications are prohibited. However, certain operations can still be performed.

A. Incorrect. You cannot drop a column from a table that is in read-only mode, as it is a modifying operation. B. Correct. You can create an index on a read-only table. Creating an index does not modify the actual rows within the table; it builds a separate structure used for faster access. C. Incorrect. The SET UNUSED statement marks one or more columns as unused so they can be dropped when the database is not busy. This operation is considered a modifying operation and therefore is not allowed on a read-only table. D. Incorrect. Truncate is a DDL operation that would delete all rows from a table. This operation is not allowed on a read-only table. E. Correct. The ALTER TABLE ... DROP UNUSED COLUMNS statement is used to drop columns that have been previously marked as unused using the SET UNUSED statement. This operation is allowed because it only affects previously marked unused columns, not actively used data. F. Correct. Dropping a table is allowed even if it's in read-only mode because it is a DDL operation that does not operate on the rows of the table but rather on the object itself.

The behavior of read-only tables and the operations that can be performed on them are detailed in the Oracle Database SQL Language Reference and Oracle Database Administrator's Guide.

A: This statement is correct. It uses the TO_DATE function with a proper date string, format mask, and NLS_DATE_LANGUAGE setting.

B: This statement will not execute successfully because the syntax of the TO_CHAR function is incorrect. The date string should be a DATE data type when used with TO_CHAR, and the format mask and NLS parameter are incorrectly specified.

C: This statement will not execute successfully because it is redundant to use TO_CHAR and then immediately convert it back to a date with TO_DATE without specifying a proper format mask.

D: This statement is correct. It converts a string to a DATE using TO_DATE and then back to a string with TO_CHAR, without specifying a format which defaults to the session’s NLS_DATE_FORMAT.

E: This statement will not execute successfully because TO_CHAR is used incorrectly; the first argument must be of DATE data type when you're using a date format mask.

References for the TO_DATE and TO_CHAR functions and their proper usage can be found in the Oracle Database SQL Language Reference 12c documentation.