PDF 1z0-071 Study Guide

The LIKE operator is used in SQL to search for a specified pattern in a column. To find all customers residing in cities starting with 'D' followed by at least two characters, you need to use the LIKE operator with the appropriate wildcard pattern:

• A. SELECT * FROM customers WHERE city LIKE 'D_%'; The underscore () wildcard character in SQL represents a single character. The percent (%) wildcard character represents zero or more characters. So 'D%' will match any city starting with 'D' followed by at least one character (as there is one underscore).

References:

• Oracle Database SQL Language Reference 12c, specifically the section on pattern matching with the LIKE condition.

• A: CURRENT_DATE returns the current date in the session time zone, not with a TIMESTAMP WITH TIME ZONE data type.
• B: CURRENT_TIMESTAMP returns the current date and time in the session time zone with a TIMESTAMP WITH TIME ZONE data type. This is correct.
• C: SYSDATE returns the current date and time from the operating system of the database server in the DATE data type.
• D: LOCALTIMESTAMP returns the current date and time in the session time zone with a TIMESTAMP data type, without the TIME ZONE.

The TIMESTAMP WITH TIME ZONE data type and relevant functions are documented in the Oracle Database SQL Language Reference 12c.

Set operators allow you to combine multiple queries into a single query. The key points for each option are:

A. An expression in the first SELECT list does not need to have a column alias; however, the column alias given to an expression in the first SELECT list is used as the column name of the output.

B. This statement is true. When CHAR columns of different lengths are combined with a set operator, Oracle will pad the shorter values with spaces to match the length of the longest value. This behavior is consistent with the SQL standard and Oracle's treatment of the CHAR datatype.

C. Oracle Database performs implicit conversion between compatible data types where necessary when using set operators. For example, a NUMBER can be implicitly converted to a VARCHAR2 and vice versa if the context requires it.

D. This is true. When multiple set operators are used in a single query, the INTERSECT operator takes precedence over the UNION and UNION ALL operators. This order of operation can be overridden by using parentheses.

E. Not all set operators work with all data types. For instance, LOB and LONG RAW data types cannot be used with set operators.

References:

• Oracle Database SQL Language Reference, 12c Release 1 (12.1): "Set Operators"
• Oracle Database SQL Language Reference, 12c Release 1 (12.1): "Data Types"

Regarding the provided SQL INTERSECT query:

• D. 1 ROW: The INTERSECT operation will compare the two SELECT statements and return rows that are identical between them. Both queries are designed to return the same values ('1' for the ID, 'John' for the name, and NULL for the commission), hence one row that is identical between the two datasets will be returned.

Incorrect options:

• A: Only one identical row exists between the two datasets.
• B: There is an identical row; thus, it is not zero.
• C: There is no error in the syntax or execution of the query.