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

Based on the given PRODUCTS table and the SQL queries provided:

• Query 1: Excludes rows where prod_list is 10 or 20 and category_id is 1.
• Query 2: Includes rows where prod_list is neither 10 nor 20 and category_id is 1.
• Query 3: Excludes rows where prod_list is both 10 and 20 (which is not possible for a single value) and category_id is 1.

The correct answer is A, queries 1 and 3 will produce the same result. Both queries exclude rows where prod_list is 10 or 20 and include only rows from category_id 1. The NOT IN operator excludes the values within the list, and <> ALL operator ensures that prod_list is not equal to any of the values in the list, which effectively excludes the same set of rows.

Query 2, using the <> ANY operator, is incorrect because this operator will return true if prod_list is different from any of the values in the list, which is not the logic represented by the other two queries.

• A. PRODUCT_ID can be assigned the PRIMARY KEY constraint.

In Oracle Database 12c, a PRIMARY KEY constraint is a combination of a NOT NULL constraint and a unique constraint. It ensures that the data contained in a column, or a group of columns, is unique among all the rows in the table and not null. Given the PRODUCT_ID is marked as NOT NULL, it is a candidate for being a primary key because we can assume that it is intended to uniquely identify each product in the table.

The requirements specify that the data type must support date arithmetic, store a specific period (up to 10 years in terms of days), and be used for day-level calculations. The appropriate data type is:

• A: Incorrect. TIMESTAMP WITH TIMEZONE stores date and time with time zone information, which is not specifically needed for arithmetic or interval storage.
• B: Incorrect. TIMESTAMP stores date and time without considering time zone or providing interval arithmetic functionality.
• C: Incorrect. TIMESTAMP WITH LOCAL TIMEZONE adjusts the time zone but does not inherently support interval arithmetic or storage.
• D: Incorrect. INTERVAL YEAR TO MONTH is used for storing periods in terms of years and months, not suitable for daily calculations.
• E: Correct. INTERVAL DAY TO SECOND allows storage of periods in terms of days, hours, minutes, and seconds, meeting all the given requirements for precision and functionality in date arithmetic related to daily calculations.

In Oracle SQL, the WITH CHECK OPTION clause in a CREATE VIEW statement ensures that all data manipulation statements performed through the view must conform to the view's defining query.

• Option A: DELETE FROM emp80 WHERE department_id = 90;

Options B, C, and D do not violate the CHECK constraint because:

• Option B: A SELECT statement does not modify data, so it does not violate the CHECK OPTION.
• Option C: Selecting rows with department_id = 80 is consistent with the view’s definition.
• Option D: There is a syntax error, but assuming it meant to set department_id to 80 where it is currently 90, it still would not violate the CHECK constraint because the CHECK OPTION on a view does not prevent updates that do not change the rows to fall outside the view's filter. However, since department_id is supposed to be 80 as per the view definition, this update operation doesn't make logical sense, as there should be no rows with department_id = 90 to update.