Oracle PL 1z0-071 Full Course Free

The ORDER BY clause in Oracle SQL is used to sort the result set of a query by one or more columns, and it comes with its own set of rules and behaviors:

• D. Column aliases can be used In the ORDER BY clause: True, column aliases that are specified in the SELECT clause can be used in the ORDER BY clause to refer to the columns to sort the results by.
• E. In a character sort, the values are case-sensitive: Oracle sorts results in case-sensitive order by default when using the ORDER BY clause with character columns, unless otherwise specified by the NLS_SORT parameter.

References:

• Oracle Database SQL Language Reference 12c, which provides details on sorting query results using ORDER BY.

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 given SQL commands:

• Option A: ROLLBACK;
• Option C: ROLLBACK TO SAVEPOINT post_insert;

In Oracle SQL, date arithmetic can be performed directly, subtracting one date from another to get the number of days between them:

• Option A:
• Option D:
• Option F:

Options B, C, and E are incorrect because:

• Option B and Option C: You cannot divide by a date or divide a date by a number.
• Option E: You cannot subtract a date from a number.