Authentic Oracle Certification Exam 1z0-071 Questions Answers

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.

To find customer names where the last name starts with Mc or MC, the correct queries are:

A. WHERE UPPER(SUBSTR(cust_name, INSTR(cust_name, ' ') + 1)) LIKE 'MC%'This query converts the substring of cust_name that comes after the first space (which should correspond to the last name) to uppercase and checks if it starts with 'MC', which will match both 'Mc' and 'MC'.

E. WHERE SUBSTR(cust_name, INSTR(cust_name, ' ') + 1) LIKE 'Mc%'This query checks if the substring of cust_name after the first space starts with 'Mc'. However, this query will only match last names that start with 'Mc' and not 'MC' unless the database is using a case-insensitive collation.

Options B, C, and D are incorrect:

B is incorrect because the syntax is not valid in Oracle SQL; the LIKE clause cannot be used to match multiple patterns in that way.

C is incorrect because INITCAP would not only capitalize the first letter of 'mc' or 'Mc' but would lowercase all other letters, which is not the intended action.

D is incorrect because, like option C, INITCAP is not the appropriate function for this use case and it will not correctly identify names that start with 'MC'.

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.