1z0-071 Questions Bank

A: Insert statements can be done through a view only if all NOT NULL constraints without default values of the base table are included in the view. Therefore, statement A is incorrect.

B: The WITH CHECK OPTION ensures that all DML operations performed through the view result in data that conforms to the view’s defining query. It affects DELETE as well as other DML operations, making statement B incorrect.

C: Similar to inserts, DELETE operations can be done through a view unless the view contains constructs that inherently do not support it, such as certain joins or set operations. Statement C is generally incorrect.

D: If a view does not include all NOT NULL columns without defaults of the underlying table, it cannot be used to add rows because the missing columns will lack values. This makes statement D correct.

E: This statement is incorrect as primary keys do not affect querying through views; they affect insert and update operations where uniqueness and non-nullability are enforced.

F: If the defining query of a view includes DISTINCT, the view generally cannot be used to perform update or insert operations as it may not be able to uniquely determine rows. This makes statement F correct.

When evaluating whether expressions in SQL queries are valid, consider data type compatibility and the operations performed:

Option A: Valid. The expression performs arithmetic operations (multiplication and division) on numeric data types (unit_price, constants). These operations are allowed and make sense in a mathematical context.

Option B: Valid. This query calculates the difference between two dates (expiry_date and delivery_date), which results in a numeric value representing the number of days between the dates. The result is then multiplied by 2, which is a valid operation on a numeric result.

Option C: Invalid. The expression unit_price || 5 attempts to concatenate a numeric value with a number, which is not valid without explicit conversion to a string. Moreover, the use of quotes around "Discount" is syntactically incorrect in this context.

Option D: Invalid. The expression expiry_date * 2 attempts to multiply a DATE datatype by a numeric value, which is not a valid operation.

Option E: Invalid. Similar to Option C, it incorrectly attempts to concatenate a number directly with a numeric value without conversion. Additionally, the aliasing with quotes is incorrectly placed.

Option F: Valid. This query simply adds two numeric columns (unit_price and surcharge), which is a valid and commonly used arithmetic operation in SQL.

The query's goal is to return a list of countries that have no departments linked to them.

Option B and Option D are the correct answers because they use set operations that will effectively return countries that do not have a corresponding entry in the departments table:

Option B uses the NOT IN subquery to exclude countries that have departments linked to them. It looks for country_id values in the countries table that are not present in the list of country_id values associated with locations that are, in turn, associated with departments. This will correctly return countries that have no departments.

Option D uses the MINUS set operator, which subtracts the results of the second SELECT statement from the results of the first. This statement will return all countries from the countries table minus those that have an associated department_id in the departments table, effectively listing countries with no departments.

Option A and Option C are incorrect because:

Option A will not execute successfully as it tries to join tables using a column (country_id) that doesn't exist in the departments table, which will lead to an error.

Option C's use of INTERSECT is incorrect for this requirement. INTERSECT returns only the rows that exist in both queries. Since we want countries with no departments, using INTERSECT would actually return the opposite of what is required.

References:

Oracle Documentation on NOT IN clause: SQL Language Reference - Subquery

Oracle Documentation on MINUS operator: SQL Language Reference - Set Operators

Therefore, the correct options are B and D, which use subquery exclusion and the MINUS set operator, respectively, to accurately identify and return countries without departments.

Regarding the DROP TABLE command:

A. ALL constraints defined on HR.EMPLOYEES are dropped: Dropping a table will automatically drop all constraints associated with that table.

B. The HR.EMPLOYEES table may be moved to the recycle bin: In Oracle, unless explicitly using PURGE, dropped tables go to the recycle bin, allowing recovery.

E. All indexes defined on HR, EMPLOYEES are dropped: When a table is dropped, all its indexes are also automatically dropped.

Incorrect options:

C: Synonyms are not automatically dropped when the table is dropped; they become invalid.

D: Sequences are independent objects and are not dropped when a table is dropped.

F: Views are not dropped; however, they will return errors upon being queried if their base table is dropped.