1Z0-071 practice question 16 of 35

Examine the description of the transactions table: Which two SQL statements execute successfully?

Full set: 326 questions
Answer options
A: SELECT customer_id AS "CUSTOMER-ID", transaction_date AS DATE, amount+100 "DUES" from transactions;
B: SELECT customer_id AS 'CUSTOMER-ID',transaction_date AS DATE, amount+100 'DUES' from transactions;
C: SELECT customer_id CUSTID, transaction_date TRANS_DATE,amount+100 DUES FROM transactions; Correct answer
D: SELECT customer_id AS "CUSTOMER-ID", transaction_date AS "DATE", amount+100 DUES FROM transactions; Correct answer
E: SELECT customer id AS CUSTOMER-ID, transaction_date AS TRANS_DATE, amount+100 "DUES AMOUNT" FROM transactions;
Correct Answer:
C. SELECT customer_id CUSTID, transaction_date TRANS_DATE,amount+100 DUES FROM transactions;; D. SELECT customer_id AS "CUSTOMER-ID", transaction_date AS "DATE", amount+100 DUES FROM transactions;
Explanation

Oracle SQL has specific rules for column aliases. An alias can be provided with or without the AS keyword. If an alias contains special characters (like a hyphen), spaces, or is case-sensitive, it must be enclosed in double quotation marks ("). Unquoted aliases cannot contain spaces or special characters. Statement C uses valid unquoted aliases with the optional AS keyword omitted. Statement D correctly uses a double-quoted alias ("CUSTOMER-ID") for an identifier containing a hyphen and another ("DATE") for a keyword, which is a best practice.

Why Incorrect

A. While Oracle's parser may execute this, using a data type keyword (DATE) as an unquoted alias is poor practice and often considered incorrect in an exam context.
B. This statement fails because it uses single quotes (') for aliases. Single quotes are reserved for string literals, not for identifiers like column aliases.
E. This statement fails for two reasons: customer id is an unquoted identifier with a space, and CUSTOMER-ID is an unquoted alias with a hyphen.

References

1. Oracle Database SQL Language Reference, 19c. "SELECT Statements", selectlist syntax. The documentation specifies that a column alias (calias) can be an unquoted identifier or a string enclosed in double quotation marks.

2. Oracle Database SQL Language Reference, 19c. "Schema Object Naming Rules". Section on "Nonquoted Identifiers". This section details that nonquoted identifiers cannot contain special characters like hyphens.

3. Oracle Database SQL Language Reference, 19c. "Literals". This section clarifies that text literals (strings) are specified using single quotation marks.

Scroll to Top