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

The LOCALTIMESTAMP function in Oracle Database returns the current date and time in the session time zone. Given that the session time zone was set to -5:00 with the ALTER SESSION SET TIME_ZONE command, LOCALTIMESTAMP will return the date and time adjusted to this session time zone.

The SYSDATE function, on the other hand, returns the current date and time from the operating system of the database server, which is not affected by the session time zone setting. In the result shown, SYSDATE returns 11-JUL-2019 11:00:00, which reflects the system time zone, not the session time zone.

The time difference between the DBTIMEZONE (+00:00) and the session time zone (-5:00) is 5 hours. Since SYSDATE shows the time as 11:00:00 in the system time zone, the LOCALTIMESTAMP adjusted to the session time zone would show the time as 06:00:00.

The correct answer is A: 11-JUL-2019 6:00:00.00000000 AM – 05:00. This represents the local timestamp in the session time zone, which is 5 hours behind the DBTIMEZONE.

Please note that the exact format returned by LOCALTIMESTAMP includes the fractional seconds up to 9 decimal places, and the time zone offset is specified as part of the timestamp.

Understanding the functionality of SQL joins and their application in a relational database:

Option A: Correct. A self join is used to relate or compare rows within the same table. It typically involves using table aliases to distinguish the different instances of the table in the query.

Option B: Incorrect. An inner join relates rows between two different tables based on a common condition, not within the same table.

Option C: Incorrect. SQL allows joining of rows from any tables, whether directly related by foreign keys or not, as long as there is a logical condition that can relate their columns.

Option D: Correct. Sometimes establishing a relationship between two specific tables might require joining through one or more intermediary tables, particularly in cases involving complex relationships or normalization.

Option E: Incorrect. While joins are often used to implement relationships, not every logical relationship necessitates a join condition, especially if data from only one table is required or if multiple relations are possible but not relevant to the specific query.

For creating tables and utilizing sequences in Oracle 12c SQL, certain rules apply:

Option A: This statement is valid. It creates a table with a primary key and dates, where the default value for end_date is SYSDATE. There are no constraints on sequences, and all syntax is correct.

Option B: This option is invalid because it attempts to use CURRVAL of a sequence as a default value for a primary key. CURRVAL cannot be used until the sequence's NEXTVAL has been called in the session.

Option C: Invalid syntax because 'start date' is not a recognized default expression in Oracle SQL. If the intention was to use 'start_date' as a reference, it still would not work correctly without proper syntax for conditional default values.

Option D: This syntax is invalid in Oracle SQL because the DEFAULT clause does not support relational conditions directly within the table definition.

Option E: Valid. It correctly uses NEXTVAL for a sequence in the DEFAULT clause for the primary key, ensuring that each new record has a unique primary key generated from the sequence. The start_date and end_date are assigned defaults of SYSDATE, which is syntactically correct.

The CONCAT function and FLOOR function in Oracle SQL have specific behaviors:

A. CONCAT function joins two or more character strings into one string, making this statement true.

B. FLOOR function returns the largest integer that is less than or equal to the specified number, making this statement true.

C. While CONCAT can join two strings together, this statement is incomplete as it can join more than two.

D. INSTR can find the offset of a substring within a string, not just a single character.

E. INSTR starts searching the string from position 1 in Oracle SQL, not position 0.

F. FLOOR does return the largest integer less than or equal to the specified number, but it can be any integer, not just positive ones.

References:

Oracle Database SQL Language Reference, 12c Release 1 (12.1): "Single-Row Functions"

For SQL queries involving set operations like UNION, UNION ALL, INTERSECT, and MINUS, the data types and number of columns in the SELECT statements being combined must be the same. Here is a breakdown of each query option:

Option A: This query successfully combines results from two tables using UNION ALL, which combines all rows from both SELECT statements, including duplicates. Both queries select two columns (prod_id and exp_date, with exp_date being NULL in the second query). This is valid as the number of columns and their data types match.

Option B: This query attempts to use INTERSECT, which returns only the rows common to both SELECT statements. However, the first SELECT statement chooses prod_id and prod_name, while the second only substitutes a constant value for prod_id without specifying from which table it selects prod_name. If "newproducts" (presumably a typo and should be "new_products") does not have the exact columns as "products", or if the data types do not match, it will fail.

Option C: This query uses UNION, which removes duplicates from the combined dataset. The use of SELECT * assumes that both "products" and "new_products" tables have the same columns in the same order with matching data types. This query will succeed if those conditions are met.

Option D: This query uses MINUS to subtract results of the second SELECT from the first. However, it fails because 'k' does not explicitly match a column name in "products", suggesting a potential error unless 'k' is indeed a valid column.

Option E: This query fails because the first SELECT statement fetches only one column (prod_id) while the second fetches two columns (prod_id and prod_name). UNION ALL requires the same number of columns with compatible types in all SELECT statements involved.

To calculate the length of employment, you would compare the current date to the HIRE_DATE and calculate the difference in years.

A. This is the correct answer. The expression calculates the number of days between the current date (SYSDATE) and the HIRE_DATE, then divides by 365 to convert it to years, and checks if it is greater than 5.

B. SYSTIMESTAMP includes a time component including fractional seconds and time zone information, making this comparison incorrect.

C. There are typos in this option (CUARENT_DATE should be CURRENT_DATE and hire_data should be hire_date). Even with the correct function and column names, CURRENT_DATE returns the current date in the session time zone, not in years.

D. There are typos in this option (SYSNAYW should be SYSDATE). Additionally, dividing by 12 incorrectly assumes that a month is equivalent to a year.

E. This is a repetition of option D with the same issues.

F. There are typos in this option (CUNACV_DATE should be CURRENT_DATE and hire_data should be hire_date). Additionally, dividing by 12 incorrectly assumes that a month is equivalent to a year.

References:

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

B. True. In Oracle, synonyms can be created for sequences, allowing them to be referenced with an alternative name. This can be particularly useful when sequences are accessed across different schemas.

C. True. Oracle provides the ability to create public synonyms which are available to all users in the database. A public synonym is accessible to any user that has the necessary privileges on the underlying object.

D. True. A synonym can be created by a user to refer to an object owned by another user, assuming that the creator of the synonym has the necessary privileges to access the object owned by the other user.

The provided DELETE statement uses a correlated subquery to determine which rows should be deleted from the EMPLOYEES table.

A. The subquery is indeed executed for every row in the EMPLOYEES table. This is because it references e.employee_id, which is a column from the outer query, making it a correlated subquery.

B. The subquery is a correlated subquery, as explained above.

C. The subquery is executed for each row, not before the DELETE statement.

D. Not all existing rows in the EMPLOYEES table are deleted, only those that have a corresponding employee_id in the EMP_HISTORY table.

E. The DELETE statement executes successfully even if the subquery selects multiple rows because the EXISTS condition only checks for the presence of rows, not their count.

References:

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

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

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

In Oracle, *_TABLES views provide information about tables.

B. USER_TABLES displays all tables owned by the current user, making this statement true. No additional privileges are required to see your own tables.

D. ALL_TABLES displays all tables that the current user has access to, either through direct ownership or through privileges, making this statement true.

A, C, E, and F are incorrect. Specifically:

A and E are incorrect because you do not need ANY TABLE system privileges to view tables in DBA_TABLES or ALL_TABLES; you need the SELECT_CATALOG_ROLE or equivalent privileges.

C is incorrect because as a user, you do not need additional privileges to see your own tables in USER_TABLES.

F is incorrect because not all users can query DBA_TABLES; this requires specific privileges or roles.

References:

Oracle Database Reference, 12c Release 1 (12.1): "Static Data Dictionary Views"

External tables using the ORACLE_DATAPUMP access driver have specific characteristics:

C. Creating an external table using the ORACLE_DATAPUMP access driver allows you to select data from other tables, including another external table or a regular table whose rows are stored in database blocks.

A, B, D, and E are incorrect. Specifically:

A is incorrect because creating an external table does not automatically create a directory object; the directory object must exist prior to or be created separately.

B is incorrect as it limits the creation to tables stored in database blocks, which is not a restriction.

D is incorrect because creating an external table does not create a dump file; it reads from an existing dump file created by Data Pump.

E is also incorrect because the dump file is not created as part of the external table creation process.

References:

Oracle Database SQL Language Reference, 12c Release 1 (12.1): "CREATE TABLE" for External Tables