Oracle 1z0-071 Exam Dumps

Question 6 of 243

Which two are true? (Choose two.)

CONCAT joins two character strings together.

CONCAT joins two or more character strings together.

FLOOR returns the largest positive integer less than or equal to a specified number.

INSTR finds the offset within a character string, starting from position 0.

INSTR finds the offset within a string of a single character only.

FLOOR returns the largest integer less than or equal to a specified number.

Correct Answer: A, F

CONCAT joins two character strings together, allowing them to be combined into one. FLOOR returns the largest integer less than or equal to a specified number, which means it rounds down the number to the nearest integer. These statements are true based on the definitions of the functions.

Question 7 of 243

Examine these SQL statements which execute successfully:

Which two statements are true after execution? (Choose two.)

The primary key constraint will be enabled and IMMEDIATE.

The foreign key constraint will be enabled and DEFERRED.

The primary key constraint will be enabled and DEFERRED.

The foreign key constraint will be disabled.

The foreign key constraint will be enabled and IMMEDIATE.

Correct Answer: A, E

After executing the given SQL statements, the primary key constraint (emp_emp_no_pk) will be enabled and IMMEDIATE. The statement does not specify deferment for the primary key; thus, it is enabled immediately. The foreign key constraint (emp_mgr_fk) will also be enabled and IMMEDIATE by default because there is no mention of disabling or deferring it in the given statements. Although there is a step that disables the primary key constraint, it is immediately followed by an enabling statement, setting it to its default state of being IMMEDIATE.

Question 8 of 243

Examine this SQL statement:

Which two are true? (Choose two.)

All existing rows in the ORDERS table are updated.

The subquery is executed before the UPDATE statement is executed.

The subquery is not a correlated subquery.

The subquery is executed for every updated row in the ORDERS table.

The UPDATE statement executes successfully even if the subquery selects multiple rows.

Correct Answer: A, D

In the provided SQL statement, all existing rows in the ORDERS table are updated, even if no match is found in the subquery, causing the customer_name to be set to NULL for those rows. The subquery is a correlated subquery, meaning it is executed for every row in the ORDERS table to get the appropriate customer name from the CUSTOMERS table. The subquery is not executed before the UPDATE statement as the subquery must run for each row being updated to retrieve the correct value. Furthermore, if the subquery selects multiple rows, the update statement would fail with an error.

Question 9 of 243

Which two statements are true about TRUNCATE and DELETE? (Choose two.)

DELETE can use a WHERE clause to determine which row(s) should be removed.

TRUNCATE can use a WHERE clause to determine which row(s) should be removed.

TRUNCATE leaves any indexes on the table in an UNUSABLE state.

The result of a TRUNCATE can be undone by issuing a ROLLBACK.

The result of a DELETE can be undone by issuing a ROLLBACK.

Correct Answer: A, E

DELETE can use a WHERE clause to determine which row(s) should be removed, allowing for selective deletion of data. Additionally, the result of a DELETE operation can be undone by issuing a ROLLBACK, as DELETE is a Data Manipulation Language (DML) statement and changes made by DML statements are logged for the possibility of reversal. TRUNCATE, on the other hand, does not use a WHERE clause and is a Data Definition Language (DDL) statement, making its results non-rollbackable and affecting the entire table instead of selective rows.

Question 10 of 243

The STORES table has a column START_DATE of data type DATE, containing the date the row was inserted.

You only want to display details of rows where START_DATE is within the last 25 months.

Which WHERE clause can be used?

WHERE TO_NUMBER(start_date - SYSDATE) <= 25

WHERE MONTHS_BETWEEN(start_date, SYSDATE) <= 25

WHERE MONTHS_BETWEEN(SYSDATE, start_date) <= 25

WHERE ADD_MONTHS(start_date, 25) <= SYSDATE

Correct Answer: C

To determine if the START_DATE is within the last 25 months, we can use the MONTHS_BETWEEN function which returns the number of months between two dates. Since we are interested in rows where the START_DATE is within the last 25 months from the current date, we should calculate the number of months between the current date (SYSDATE) and the START_DATE, ensuring this value is less than or equal to 25. Therefore, the appropriate condition is MONTHS_BETWEEN(SYSDATE, start_date) <= 25.