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

After the execution of the given SQL statements:

B. The primary key constraint will be enabled and IMMEDIATE. This is true. The last statement ENABLE CONSTRAINT emp_emp_no_pk; enables the primary key constraint immediately.

E. The foreign key constraint will be enabled and IMMEDIATE. This is correct as well. The foreign key constraint emp_mgr_fk was not explicitly disabled, so it remains enabled and in the immediate checking mode.

Options A, C, and D are incorrect:

A is incorrect because constraints are immediate by default; they are not deferred unless explicitly stated.

C is incorrect because the foreign key constraint emp_mgr_fk was never disabled with the given statements.

D is incorrect because there is no statement altering the foreign key constraint to a deferred state.

Equijoins are joins that use the equality operator (=) to match rows. Non-equijoins are joins that use operators other than the equality operator.

A. False. This condition is an equijoin as it uses the equality operator (=).

B. True. This join condition uses the BETWEEN operator, which is not based on equality.

C. False. A natural join is a type of equijoin where the join is made on all columns with the same names in both tables.

D. False. USING clause is used to specify an equijoin on one or more columns.

E. True. This join condition uses the greater than or equal to (>=) operator, making it a non-equijoin.

A: NEXTVAL is used to increment the sequence and return the next value; it does not give the last number generated.

B: CURRVAL returns the current value of the sequence, which is the last value generated in the user's current session. However, CURRVAL cannot be queried unless NEXTVAL has been called at least once in that session.

C: CURRVAL is used correctly, but the syntax 'sequence.CURRVAL' is not correct in Oracle SQL.

D: NEXTVAL is used to generate the next sequence number, not to retrieve the last one generated.

In Oracle Database 12c, when creating an external table using the CREATE TABLE ... ORGANIZATION EXTERNAL statement, there are certain clauses that are mandatory for the command to execute successfully.

Statement C, the LOCATION clause, is required. The LOCATION clause specifies one or more external data source locations, typically a file or a directory that the external table will read from. Without this, Oracle would not know where to find the external data for the table.

Statement E, the access driver TYPE clause, is also required. The access driver tells Oracle how to interpret the format of the data files. The most common access driver is ORACLE_LOADER, which allows the reading of data files in a format compatible with the SQL*Loader utility. Another option could be ORACLE_DATAPUMP, which reads data in a Data Pump format.

Statements A, B, and D are not strictly required for the command to execute successfully, although they are often used in practice:

A, the DEFAULT DIRECTORY clause, is not mandatory if you have specified the full path in the LOCATION clause, but it is a best practice to use it to avoid hard-coding directory paths in the LOCATION clause.

B, the REJECT LIMIT clause, is optional and specifies the maximum number of errors to allow during the loading of data. If not specified, the default is 0, meaning the load will fail upon the first error encountered.

D, the ACCESS PARAMETERS clause, is where one would specify parameters for the access driver, such as field delimiters and record formatting details. While it is common to include this clause to define the format of the external data, it is not absolutely required for the command to execute; defaults would be used if this clause is omitted.

For reference, you can find more details in the Oracle Database SQL Language Reference for version 12c, under the CREATE TABLE statement for external tables.

In Oracle SQL, the following syntactical rules apply:

A. This query will result in an error because there is no comma separating the column names FIRST_NAME and LAST_NAME. The correct syntax should include a comma to separate the column names in the SELECT list.

B. This query is correctly formatted with a comma separating the column names, so it will not result in an error.

C. This query will result in an error because an alias defined in the SELECT list (ANNUAL_SALARY) cannot be used in the WHERE clause of the same query level. It must be repeated in the WHERE clause as 12 * SALARY.

D. This query will execute successfully because 12 * SALARY is directly used in the WHERE clause, and ANNUAL_SALARY is used in the ORDER BY clause, which is allowed.

E. This query is correct and will not result in an error. It uses 12 * SALARY in both the WHERE and ORDER BY clauses.

F. Similar to option C, this query will execute successfully because ANNUAL_SALARY is correctly used in the ORDER BY clause, and the WHERE clause does not attempt to reference the alias.

References:

Oracle Database SQL Language Reference, 12c Release 1 (12.1): "Database Object Names and Qualifiers"

A. This statement is incorrect because TO_NUMBER expects a character string as an argument, not a date. Directly converting a date to a number without an intermediate conversion to a character string would result in an error. B. This statement is incorrect. Multiplying a date by a number does not make sense in SQL, and attempting to convert such an expression to a date will also result in an error. C. This statement is correct. Subtracting two dates in Oracle SQL results in the number of days between those dates, hence the result is a number. D. This statement is correct. Subtracting a number from a date in Oracle SQL will subtract that number of days from the date, returning another date. E. This statement is incorrect. As stated in C, subtracting a date from SYSDATE correctly returns the number of days between those two dates, not an error.

These concepts are explained in the Oracle Database SQL Language Reference, which details date arithmetic in SQL.

The provided SQL statement is an update statement that involves a subquery which is correlated to the main query.

A. The subquery is executed before the UPDATE statement is executed. (Incorrect)

This statement is not accurate in the context of correlated subqueries. A correlated subquery is one where the subquery depends on values from the outer query. In this case, the subquery is executed once for each row that is potentially updated by the outer UPDATE statement because it references a column from the outer query (o.customer_id).

B. All existing rows in the ORDERS table are updated. (Incorrect)

Without a WHERE clause in the outer UPDATE statement, this would typically be true. However, the correctness of this statement depends on the actual data and presence of matching customer_id values in both tables. If there are rows in the ORDERS table with customer_id values that do not exist in the CUSTOMERS table, those rows will not be updated.

C. The subquery is executed for every updated row in the ORDERS table. (Correct)

Because the subquery is correlated (references o.customer_id from the outer query), it must be executed for each row to be updated in the ORDERS table to get the corresponding cust_last_name from the CUSTOMERS table.