Oracle Updated 1z0-071 Exam Questions and Answers by maisy

Queries that will execute successfully given the PRODUCT_STATUS table:

• C. SELECT prod_id || q'('s not available)' "CURRENT AVAILABILITY" FROM product_status WHERE status = 'OUT OF STOCK';This query will execute successfully because it uses the q quote operator correctly to handle the inclusion of single quotes in the string.
• D. SELECT prod_id || q'('s not available)' FROM product_status WHERE status = 'OUT OF STOCK';Similar to the previous query, it uses the q quote operator correctly and will execute without syntax errors.

Options A, B, E, and F are incorrect because of the incorrect or incomplete use of the quote operator:

• A is incorrect because it has an additional single quote before the FROM clause.
• B is incorrect because of a missing ending single quote after the q quote operator.
• E and F are incorrect because they both use the q quote operator incorrectly; q’(’s not available)' is not a valid use of the quote operator, and q"'s not available" incorrectly uses double quotes instead of single quotes.

In Oracle SQL, the ALTER TABLE statement is used to modify the definition of a table after it has been created. Specific changes are allowed depending on the current data type of the column and the data it contains.

A. You cannot change a VARCHAR2 column to NUMBER if the table contains rows with non-numeric values, which is the case with '123' as a VARCHAR2; it is a string, not a number.

B. You can reduce the size of a NUMBER column if the data will still fit within the new precision, but you cannot change a VARCHAR2 column to a smaller size if it contains values that exceed the new size limit.

C. You cannot change a NUMBER column to VARCHAR2 if the table contains rows because the existing data type of NUMBER does not match the new data type of VARCHAR2.

D. Similar to A, you cannot change the data type from VARCHAR2 to NUMBER if non-numeric data is present.

E. You can change a VARCHAR2(10) column to VARCHAR2(5) if all existing rows have values that fit within the new size, and you can change a NUMBER column to have a different scale (NUMBER(12,2)).

References:

• Oracle Database SQL Language Reference, 12c Release 1 (12.1): "ALTER TABLE"

The COUNT function is one of the most commonly used aggregate functions in SQL for determining the number of items in a group. Here's why the correct answers are B and C:

• A: Incorrect. COUNT can be used with any data type, not just NUMBER. It counts rows without regard to data type.
• B: Correct. COUNT(DISTINCT inv_amt) counts the number of unique non-null values in the column inv_amt. It excludes duplicates and ignores NULL values.
• C: Correct. COUNT(*) counts all rows in a table, including those with duplicates and those with NULLs in any column. It is a total row count regardless of content.
• D: Incorrect. You can use a WHERE clause with COUNT. The WHERE clause filters rows before counting, which is standard in SQL.
• E: Incorrect. COUNT(inv_amt) counts the rows where inv_amt is not NULL. Rows with NULL in the inv_amt column are not counted.

In Oracle SQL, the q quote operator can be used to define string literals that contain single quotes or other special characters without needing to escape them. The queries using the q quote mechanism, like in options A and C, will successfully return the string as it is, including single quotes within the string.

• A: Correct, it uses the q quote operator with the exclamation mark ! as the delimiter, which allows the string to contain single quotes.
• B: Incorrect, double quotes " in Oracle SQL are used for identifiers such as column names, not string literals.
• C: Correct, this also uses the q quote operator, with the square brackets [] as the delimiters.
• D: Incorrect, the backslash \ is not used as an escape character in Oracle SQL string literals, and the ESCAPE keyword is used incorrectly here.
• E: Incorrect, this does not account for the single quote within the string, which would terminate the string literal prematurely, and it lacks the q quote operator or proper escape mechanism.