Oracle Updated 1z0-071 Exam Questions and Answers by leena

Primary and foreign key constraints are foundational to relational database design:

• Option A: Incorrect. Both primary and foreign key constraints can be defined at the column or table level.
• Option B: Incorrect. Foreign key columns do not need to have the same names as the corresponding primary key columns in the parent table. They must be of the same data type and size.
• Option C: Incorrect. If a foreign key constraint is enforced without ON DELETE CASCADE, deleting the parent row will either prevent the delete (due to constraint) or require a prior deletion or update of the child rows.
• Option D: Correct. A table can indeed have only one primary key, which uniquely identifies each row, but it can have multiple foreign keys linking to primary keys of different tables.
• Option E: Correct. Both types of keys can be defined at either level, providing flexibility in how constraints are applied and enforced.
• Option F: Incorrect. A table can have multiple foreign keys as stated, each referencing a different parent table.
• Option G: Correct. If the ON DELETE CASCADE option is set for a foreign key, then deleting a parent row will automatically delete the corresponding child rows, maintaining referential integrity.

The SQL query must find all customers with last names starting with A or B, regardless of case, and a credit limit below 1000:

• B. WHERE (INITCAP(cust_last_name) LIKE ‘A%' OR INITCAP(cust_last_name) LIKE ‘B%') AND cust_credit_limit < 1000: The INITCAP function initializes the first letter to uppercase for comparison. However, it should be noted that using INITCAP is not necessary when using the LIKE operator with a wildcard % following a single character, because it will not correctly filter all last names that start with an upper or lower case A or B.
• D. WHERE (UPPER(cust_last_name) LIKE 'A%’ OR UPPER(cust_last_name) LIKE ‘B%’) AND cust_credit_limit < 1000: This correctly filters last names beginning with A or B in any case and includes only those with a credit limit below 1000. The UPPER function is used to convert cust_last_name to uppercase before comparison.

References:

• Oracle Database SQL Language Reference 12c, especially sections on string functions and conditions.

This question appears to be a duplicate of question no. 269, and the correct answers provided there apply here as well.

B. True. A DDL statement automatically commits any outstanding uncommitted transaction in the session. E. True. Exiting SQL*Plus without explicitly committing a transaction will cause the uncommitted transaction to be rolled back, not committed.

A, C, and D are incorrect for the same reasons provided in the explanation for question no. 269.

• 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.