Free Access Oracle 1z0-071 New Release

Views in Oracle have specific properties regarding how they can be used and what limitations apply to them:

A. Oracle does not allow indexing directly on views. However, a view can display data from indexed tables.

B. The WITH CHECK OPTION clause on a view prevents DML operations that would result in rows that do not satisfy the view’s filter criteria. It doesn't prevent certain rows from being updated or inserted in the underlying table through the view per se.

C. The tables (or other views) referenced in the creating query of a view must exist at the time the view is created, making this statement true.

D. Views do not require privileges to be re-granted when the view is updated; they are separate from the underlying object privileges, making this statement true.

E. The WITH CHECK OPTION clause is used to ensure that all data modifications through the view are consistent with the view's defining query, not to prevent rows from being displayed.

References:

Oracle Database SQL Language Reference, 12c Release 1 (12.1): "CREATE VIEW"

Oracle Database Concepts, 12c Release 1 (12.1): "Views"

The statement provided uses the UNION operator, which combines the results of two or more queries into a single result set. However, UNION also eliminates duplicate rows. Both queries in the union are selecting the same values 1 and ' John', thus the result is one row because duplicates will be removed.

SELECT 1 AS id, ' John' AS first name FROM DUAL UNION SELECT 1 , ' John' AS name FROM DUAL ORDER BY 1;

Given that both SELECT statements are actually identical in the values they produce, despite the differing column aliases, the UNION will eliminate one of the duplicate rows, resulting in a single row being returned.

References:

Oracle Documentation on UNION: SQL Language Reference - UNION

The UNION ALL operator in SQL combines the results of two or more SELECT statements into a single result set:

Option A: True. Since UNION ALL does not eliminate duplicates, it does not ignore NULLs during any duplicate checking; however, it's worth noting that UNION ALL does not perform duplicate checking at all.

Option B: False. Unlike the UNION operator, UNION ALL does not eliminate duplicates; it includes all duplicates in the output.

Option C: False. The column names do not need to be identical in each SELECT statement; however, the result set will use the column names from the first SELECT statement.

Option D: True. For UNION ALL to work correctly, the number of columns and their data types in each SELECT statement must match, but the column names can differ.

Option E: False. UNION ALL does not inherently sort the output; it merely combines the results as they are retrieved. Any sorting would need to be explicitly specified using an ORDER BY clause.

A. Only the second statement will display departments with no employees. This is true because the second statement uses a LEFT JOIN to include all departments from the departments table, even those without matching entries in the employees table. When there are no employees in a department, the AVG(salary) will be NULL, and the department will still be displayed1.

C. Both statements will execute successfully if you add e.avg_sal to the select list. This is correct. Both statements calculate e.avg_sal as an average salary, either through a subquery or a join operation. Adding e.avg_sal to the select list will display the average salary alongside the departments. However, it’s important to note that the first statement will not display departments with no employees because it does not use a join that would allow for NULL values from the employees table2.

References:

Understanding SQL JOINs - Stack Overflow1.

Oracle Documentation on JOIN Operations2.

Note: The other options are incorrect because:

B. The first statement will not display departments with no employees since it does not use a join that includes departments without matching employee records.

D. As explained, the first statement will not display departments with no employees.

E. There is a typo in the option; it should be E.AVG_SAL. Even if corrected, the first statement alone would not execute successfully because it does not include a join to bring in the avg_sal value.

F. The second statement will display departments with no employees, but the first statement will not, so this option is incorrect.