1z0-071 Exam Dumps - Oracle PL Questions and Answers

Assuming the statement involves creating a new table SALES1 from the existing SALES table:

B. SALES1 created with 55,000 rows: If the statement involved creating SALES1 by selecting all rows from SALES (such as with a CREATE TABLE AS SELECT operation), then all 55,000 rows would be copied if no WHERE clause limited the selection.

E. SALES1 has NOT NULL constraints on any selected columns which had those constraints in the SALES table: When creating a table using the CREATE TABLE AS SELECT syntax, any NOT NULL constraints on columns in the original table will also apply to the new table.

Incorrect options:

A: PRIMARY KEY and UNIQUE constraints are not automatically copied with a CREATE TABLE AS SELECT; they must be explicitly redefined.

C: The table is created with rows if the original SELECT statement (assumed here) selected rows.

D: The statement about creating a table with 1 row is incorrect based on the assumed full selection of the original table's rows.

In Oracle SQL, substitution variables (&1) can be used to dynamically replace a value in a SQL statement at runtime. To avoid being prompted for a value when executing a query that includes a substitution variable:

A. Use the DEFINE command before executing the query: By setting a value for a substitution variable using the DEFINE command, Oracle will use this value instead of prompting the user for input.

D. Execute the SET VERIFY OFF command before executing the query: The SET VERIFY OFF command suppresses the prompt for substitution variables by turning off the verification feature. This makes Oracle use the currently defined value for the variable.

References:

Oracle Database SQL Language Reference 12c, particularly the sections on substitution variables, the DEFINE command, and the SET command.

The requirements specify that we need to select book titles based on a price condition and a date condition, and then sort the results by the date of purchase.

A. This query will not execute successfully because there is a syntax error in the price condition. It should be price < 500 OR price > 1000.

B. This query is incorrect. The requirement specifies that the price must be less than 500 or more than 1000, not in a list of values.

C. This query is correct. It selects books where the price is not between 500 and 1000 (thus, less than 500 or more than 1000) and the purchase date is before January 17, 2007, ordered by purchase date in descending order.

D. This query is incorrect because it selects books with prices between 500 and 1000, which is not what the requirement specifies.

Understanding Oracle's data dictionary views is crucial for database management:

Option C: USER_TABLES displays all tables owned by the current user.

USER_TABLES is a view that shows all tables owned by the user executing the query.

Option F: All users can query DBA_TABLES successfully.

While all users can attempt to query DBA_TABLES, only users with the appropriate privileges can retrieve information from it. This option is tricky; "querying" is possible for all, but "retrieving data" depends on privileges.

Options A, B, D, and E are not entirely correct:

Option A and Option E are incorrect because USER_TABLES shows tables regardless of SELECT privileges.

Option B is incorrect; one does not need SELECT privileges on each table to view them in DBA_TABLES, but they need appropriate system privileges to access DBA_TABLES.

Option D is incorrect as ALL_TABLES displays tables accessible to the current user, not just owned by them.

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.

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.

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

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"

Single-row functions operate on single rows and return one result per row. Let's look at each option in the context of Oracle 12c SQL:

A. CONCAT: This function can only combine two values at a time. If you need to concatenate more than two values, you have to nest CONCAT functions or use the || operator.

B. MOD: The MOD function returns the remainder of a division operation, not the quotient.

C. CEIL: This function returns the smallest integer that is greater than or equal to a specified number. It works with both positive and negative numbers.

D. FLOOR: It returns the largest integer that is less than or equal to the specified number, not greater than or equal to.

E. TRUNC: This function can indeed be used with both NUMBER and DATE values to truncate them to a specified number of decimal places or to a particular component of a date.

References:

Oracle Database SQL Language Reference 12c Release 1 (12.1), Functions

Oracle Database SQL Language Reference 12c Release 1 (12.1), CONCAT Function

Oracle Database SQL Language Reference 12c Release 1 (12.1), MOD Function

Oracle Database SQL Language Reference 12c Release 1 (12.1), CEIL and FLOOR Functions

Oracle Database SQL Language Reference 12c Release 1 (12.1), TRUNC Function

The SQL UPDATE statement is used to modify existing records in a table. In the given statement, there is a subquery that selects the JOB_ID and SALARY of EMPLOYEE_ID 200 and sets these values to the corresponding columns of EMPLOYEE_ID 100.

B. Employee 100 will have SALARY set to the same value as the SALARY of employee 200: This is true because the subquery selects the SALARY of employee 200 and the outer update statement sets this SALARY to employee 100.

C. Employee 100 will have JOB_ID set to the same value as the JOB_ID of employee 200: Similarly, the JOB_ID for employee 100 is updated to match the JOB_ID of employee 200 because of the subquery.

References:

Oracle Database SQL Language Reference 12c, particularly the section on the UPDATE statement and subqueries.