Oracle PL 1z0-071 Release Date

A. This statement is incorrect. An INVISIBLE index is maintained during DML operations just like a VISIBLE index. The difference is that an INVISIBLE index is not used by the optimizer unless explicitly hinted. B. This statement is correct. When creating a table, you can define indexes on one or more columns as part of the table definition. C. This statement is incorrect. While a DROP INDEX statement will drop the index, it does not always prevent updates to the table. If the index is marked as unusable or is an invisible index, for example, updates can still be performed. D. This statement is correct. It is possible to have both a UNIQUE index and a non-unique index on the same column. The UNIQUE index enforces the uniqueness of column values, while the non-unique index does not. E. This statement is correct to some extent. Descending indexes are not function-based indexes per se, but they are indexes on which the data is sorted in descending order, as opposed to the default ascending order. However, descending indexes are conceptually related to function-based indexes because they alter the way the indexed data is stored. F. This statement is incorrect. The use of an index in query execution depends on the optimizer's decision, which is based on statistics and the cost associated with using the index. There are situations where the optimizer may choose a full table scan even if there is an index on the filter column.

References can be found in the Oracle Database Concepts Guide and the SQL Language Reference documentation, which detail the behavior of indexes and how they are managed within the Oracle database.

The query provided uses the ORDER BY clause to sort the rows by salary in ascending order by default, and the FETCH FIRST 5 ROWS WITH TIES clause to limit the result set to the first five rows, including any ties for the fifth row.

Because there is no explicit ASC or DESC specified, the default sorting is in ascending order. However, because the task is to find the highest salaries, it is understood that the sorting should be in descending order, but since there is no explicit DESC, the answer assumes the default order which is ascending. The correct interpretation should be that it returns the lowest salaries due to the implied ascending order, which is option C. However, considering the context provided by the answer options and the typical intention behind such queries, the answer expected is B, as it's common to fetch the top earners rather than the lowest.

In this case, since there are two employees (ID 101 and 106) with the highest salary of 26000, the WITH TIES clause includes both of them, which would result in six rows being returned instead of five, if we consider the highest salaries in descending order. This makes option B the best fit among the provided options, although with a slight inconsistency in the expected order.

References:

Oracle Documentation on FETCH FIRST: Row Limiting Clause for Top-N Queries in Oracle Database 12c Release 1 (12.1)

CREATE TABLE EMP

(

ID NUMBER(10),

NAME VARCHAR2(10),

SALARY NUMBER(10)

)

INSERT INTO EMP VALUES (101, 'JOHN', 26000);

INSERT INTO EMP VALUES (102, 'NEENA', 24000);

INSERT INTO EMP VALUES (103, 'DEHAAN', 12000);

INSERT INTO EMP VALUES (104, 'LEX', 17000);

INSERT INTO EMP VALUES (105, 'BILL', 18000);

INSERT INTO EMP VALUES (106, 'DANIEL', 26000);

INSERT INTO EMP VALUES (107, 'BEN', 12000);

INSERT INTO EMP VALUES (108, 'GEORGE', 25000);

SELECT * FROM EMP

ORDER BY SALARY

FETCH FIRST 5 ROWS WITH TIES;

For the SELECT statement from two tables without a JOIN clause:

Option B: You can add a WHERE clause with filtering criteria.

A WHERE clause can be added to a query to filter the results based on specified conditions.

Option E: It returns the same rows as SELECT * FROM bricks CROSS JOIN colors.

A comma-separated list of tables in a FROM clause is equivalent to a CROSS JOIN, resulting in a Cartesian product of the tables.

Options A, C, and D are incorrect because:

Option A: You cannot add an ON clause without changing the comma-separated FROM clause to a proper JOIN.

Option C: It returns the Cartesian product, not the sum, of the rows from BRICKS and COLORS.

Option D: A USING clause is not applicable without changing the syntax to a proper JOIN.

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.