Pass the Oracle PL 1z0-071 Questions and answers with ExamsMirror

A: True. The DESCRIBE command can indeed be used from SQL Developer as well as other Oracle database tools such as SQL*Plus. This command is used to display the structure of a table, view, or other object, showing information such as column names, data types, and whether a column is nullable.

B: True. The DESCRIBE command can be used to display the structure of an existing view, showing similar information as it would for a table. This includes the columns, their data types, and other pertinent details.

D: True. When DESCRIBE is used, it does display the NOT NULL constraints for columns that have this constraint. This is part of the basic information about the structure of a table or view that can help developers understand the requirements of the data stored therein.

A. Incorrect. DUAL consists of a single row and column. B. Incorrect. DUAL is a special one-row, one-column table present by default in all Oracle database installations and can be accessed by any user with SELECT privilege. C. Correct. Any user with the SELECT privilege can select from the DUAL table. It is a dummy table used primarily for selecting a constant, pseudocolumn, or expression not involving any tables. D. Incorrect. DUAL has only one row and one column. E. Correct. The DUAL table has a single VARCHAR2 column called DUMMY, which has a value of 'X'. F. Incorrect. DUAL can be used for a variety of purposes, such as selecting system constants, user environment variables, expressions, and sequences.

This information is detailed in the Oracle Database SQL Language Reference, which describes the properties and uses of the DUAL table.

Multiple INSERT statements allow data insertion into one or more tables based on different conditions or datasets:

Option A: False. Multiple INSERT operations can be performed using direct values, subqueries, or even default values, not exclusively through subqueries.

Option B: True. They can indeed be performed on relational tables, which is the standard use case in most relational databases.

Option C: True. INSERT operations can be performed on updatable views, assuming the view is not complex (involving joins, GROUP BY clauses, etc.).

Option D: True. Oracle allows INSERT operations on remote tables via database links, enabling distributed database interactions.

Option E: False. Direct INSERT statements cannot be performed on external tables. External tables are typically used for read operations, with data loading handled through utilities like SQL*Loader or external data processing tools.

Option F: False. Each INSERT statement inserts data into one table. While a single SQL command block can contain multiple INSERT statements, each one is directed at a single table.

E. True. Both & and && can prefix substitution variables in queries as well as in DML statements. & is used for defining a substitution variable that prompts the user every time the variable is encountered. && is used for a persistent substitution variable that, once defined in the session, will not prompt the user again for a value if used subsequently.

To calculate the date from a given base date with intervals, Oracle allows you to add or subtract intervals from dates. Since the NLS_DATE_FORMAT is set to DD-MM-YYYY, the output is expected to be in that format.

Option B seems to calculate a date that is 3 months and 7 days ahead of October 29, 2019, and then subtracts 360 seconds (which is 6 minutes), resulting in a time that is still within the same day.

Here's how the calculation in option B would work out:

Start date: 29-10-2019

Add 3 months: 29-01-2020

Add 7 days: 05-02-2020

Subtract 360 seconds: Since it's only a few minutes, the date remains 05-02-2020.

However, this does not match the provided result of 02-JAN-2020. We would need to consider the exact amount of time being subtracted or added to find the correct answer.

But upon reviewing the options, they all have various syntax errors such as a missing TO_CHAR function, incorrect quotes, and date formats not matching the session's NLS_DATE_FORMAT. Therefore, we would need to correct these issues to find the right answer.

Global temporary tables (GTTs) in Oracle have unique properties related to session-based visibility and data persistence:

B. A TRUNCATE command issued in a session causes all rows in a GLOBAL TEMPORARY TABLE for the issuing session to be deleted: True, TRUNCATE is session-specific for GTTs and will remove all rows inserted by the session that issues the TRUNCATE command.

D. A GLOBAL TEMPORARY TABLE's definition is available to multiple sessions: The structure of a GTT is persistent and shared across sessions, but the data within is session-specific.

E. Any GLOBAL TEMPORARY TABLE rows existing at session termination will be deleted: The data in GTTs is private for the session, and when the session ends, the data is deleted automatically.

References:

Oracle Database Concepts and SQL Language Reference 12c, especially the sections on temporary tables.

Regarding object privileges in an Oracle database:

B. Delete rows from tables in any schema except sys: Object privileges include DELETE on tables, which can be granted by the owner of the table or a user with adequate privileges, excluding system schemas like SYS due to their critical role.

E. Execute a procedure or function in another schema: EXECUTE is a specific object privilege that can be granted on procedures and functions, allowing users to run these objects in schemas other than their own.

Incorrect options:

A: Creation of roles is related to system privileges, not object privileges.

C: Setting default and temporary tablespaces for a user involves system-level operations, not object-level privileges.

D: Creation of foreign key constraints involves referencing rights, which, while related, are not directly granted through object privileges but need appropriate REFERENCES permission.

For question 138 regarding Oracle views, the correct answers are B, E, and G:

B. The WITH CHECK OPTION clause prevents certain rows from being updated or inserted: This clause ensures that all data modifications performed through the view result in data that conforms to the view's defining query.

E. Deleting one or more rows using a view whose defining query contains a GROUP BY clause will cause an error: Views that involve aggregate functions, GROUP BY clauses, or DISTINCT cannot be directly modified with DML operations such as DELETE, because the underlying data manipulation would be ambiguous.

G. Inserting one or more rows using a view whose defining query contains a GROUP BY clause will cause an error: Similarly to DELETE, INSERT operations into a view that uses a GROUP BY clause are generally not allowed because it's unclear how the new rows should be aggregated into the existing groups.

Other options are incorrect because:

A: The WITH CHECK OPTION clause does not prevent rows from being displayed; it restricts DML operations.

C: Views can be created with non-existent underlying tables or views using the FORCE option in Oracle.

D: Not all views are updatable, especially those involving joins, group functions, or distinct aggregation.

F: Updating a view does not impact the granted privileges unless the definition of the view changes.

Single-row functions can be used in the WHERE and HAVING clauses without requiring a subquery. However, aggregate functions, which operate on many rows to give one result per group, cannot be used in a WHERE clause unless a subquery is used because the WHERE clause is processed before the individual rows are aggregated into groups.

A. False. Single-row functions can be directly used in the WHERE clause.

B. False. Single-row functions can be directly used in the HAVING clause.

C. True. Aggregate functions cannot be used in the WHERE clause without a subquery because the WHERE clause filters individual rows before they are aggregated.

D. False. Aggregate functions are intended to be used in the HAVING clause, which is specifically for filtering groups of rows after they have been aggregated, and they do not require a subquery to be used there.

When creating an external table, which allows you to access data in a flat file as though it is a table inside the database, certain clauses are required:

A. the ACCESS PARAMETERS clause: This clause specifies the parameters required by the access driver to read the data files.

D. the LOCATION clause: This clause specifies the location of the data files that make up the external table.

References:

Oracle Database SQL Language Reference 12c, particularly the sections detailing the creation and management of external tables.