1Z0-071 practice question 28 of 35

Examine the description of the CUSTOMERS table: Which three statements will do an implicit conversion?

Full set: 326 questions
Answer options
A: SELECT * FROM customers WHERE insert_date=DATE’2019-01-01’; Correct answer
B: SELECT * FROM customers WHERE customer_id=’0001’; Correct answer
C: SELECT * FROM customers WHERE TO_DATE(insert_date)=DATE’2019-01-01’;
D: SELECT * FROM customers WHERE insert_date’01-JAN-19’; Correct answer
E: SELECT * FROM customers WHERE customer_id=0001;
F: SELECT * FROM customers WHERE TO_CHAR(customer_id)=’0001’;
Correct Answer:
A. SELECT * FROM customers WHERE insert_date=DATE’2019-01-01’;; B. SELECT * FROM customers WHERE customer_id=’0001’;; D. SELECT * FROM customers WHERE insert_date’01-JAN-19’;
Explanation

Implicit data type conversion occurs when Oracle automatically converts a value from one data type to another. Assuming standard data types where customerid is NUMBER and insertdate is TIMESTAMP, three options cause implicit conversion. In A, the DATE literal is converted to TIMESTAMP. In B, the character literal '0001' is converted to a NUMBER. In D, the character literal '01-JAN-19' is converted to a DATE (using NLSDATEFORMAT) and then to a TIMESTAMP to match the column's data type.

Why Incorrect

C: This statement uses the TODATE function, which is an explicit conversion, not an implicit one. E: Assuming customerid is NUMBER, this compares a NUMBER column to a NUMBER literal, so no conversion is necessary. F: This statement uses the TOCHAR function, which is an explicit conversion, not an implicit one.

References

Oracle Database SQL Language Reference, 19c, "Data Type Comparison Rules". This section details how Oracle handles comparisons between different data types, stating, "When comparing a character value with a NUMBER value, Oracle converts the character value to NUMBER." and "When comparing a DATE with a TIMESTAMP... Oracle converts the DATE to a TIMESTAMP."

Oracle Database SQL Language Reference, 19c, "Implicit Data Type Conversion". Table 2-10 shows the matrix of implicit conversions, confirming string-to-number and string-to-date conversions.

Scroll to Top