Last Attempt 1z0-071 Questions

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"

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.

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.

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.