Oracle Database Sql Certified Associate · Free Practice Question Hard
Question 31
View and examine the structure of ORDERS and CUSTOMERS tables.
Given: This UPDATE statement has been executed.
Exhibit 1:
- 1 UPDATE (
- 2 SELECT order_date, order_total, customer_id
- 3 FROM orders
- 4 )
- 5 SET order_date = 22-MAR-2019
- 6 WHERE customer_id IN (
- 7 SELECT customer_id
- 8 FROM customers
- 9 WHERE cust_last_name = 'Roberts'
- 10 AND credit_limit = 600
- 11 );
Indicate the statement which is true regarding the result? (Choose the best answer.)
-
A
It would not execute because line 9 does not have the proper syntax.
-
B
It would not execute because two tables cannot be referenced in a single UPDATE statement.
-
C
It would not execute because line 10 does not have the proper syntax.
-
D
It would not execute because line 5 does not have the proper syntax.
-
E
It would execute successfully.
-
F
It would not execute because a SELECT statement cannot be used in place of a table name.
Reveal correct answer
Correct answer: D
Explanation
The most direct and definite reason for the statement to fail is the incorrect syntax for the date literal on line 5.
It would not execute because line 5 does not have the proper syntax. This is TRUE and directly addresses the error with the date literal.
Line 5 must be corrected. The below statement will run successfully and update the order date field to 22-MAR-2019. Using the TO_DATE function for more explicit date conversion is often preferred for robustness:
- 1 UPDATE (
- 2 SELECT order_date, order_total, customer_id
- 3 FROM orders
- 4 )
- 5 SET order_date = TO_DATE('22-MAR-2019', 'DD-MON-YYYY') -- Using TO_DATE function
- 6 WHERE customer_id IN (
- 7 SELECT customer_id
- 8 FROM customers
- 9 WHERE cust_last_name = 'Roberts'
- 10 AND credit_limit = 600
- 11 );
Lines 1-4:
UPDATE (SELECT ... FROM orders): This syntax, where anUPDATEstatement targets a subquery (or an inline view), is valid in databases like Oracle. It allows you to update columns from the underlying table(s) that are exposed in the subquery. So, the claim that "a SELECT statement cannot be used in place of a table name" is generally false for this context.Line 5:
SET order_date = 22-MAR-2019: This is the problematic line. In SQL, date literals must be enclosed in single quotes. Without quotes,22-MAR-2019is interpreted as an arithmetic expression:22minus the string'MAR'(which would cause an error as 'MAR' is not a valid numeric operand for subtraction) minus2019.Correct Syntax would be:
SET order_date = '22-MAR-2019'ORSET order_date = TO_DATE('22-MAR-2019', 'DD-MON-YYYY')Because of this, the statement would not execute successfully.
Lines 6-11:
WHERE customer_id IN (SELECT customer_id FROM customers WHERE cust_last_name = 'Roberts' AND credit_limit = 600): This subquery is syntactically correct and references thecustomerstable. The presence of two tables being referenced (implicitlyordersvia the inline view andcustomersin the subquery) is also valid. The clausesWHERE cust_last_name = 'Roberts'(line 9) andAND credit_limit = 600(line 10) are both standard and correct SQL syntax.
Other options:
It would not execute because line 9 does not have the proper syntax. This is FALSE; line 9 (
WHERE cust_last_name = 'Roberts') is syntactically correct.It would not execute because line 10 does not have the proper syntax. This is FALSE; line 10 (
AND credit_limit = 600) is syntactically correct.It would not execute because a SELECT statement cannot be used in place of a table name. This is FALSE; in many SQL dialects (like Oracle), this is a valid way to update through an inline view.
It would not execute because two tables cannot be referenced in a single UPDATE statement. This is FALSE; referencing multiple tables via subqueries in
WHEREclauses is common and valid.It would execute successfully. This is FALSE due to the syntax error on line 5.
The Oracle/PLSQL TO_DATE function converts a string to a date.
Syntax
The syntax for the TO_DATE function in Oracle/PLSQL is:
- TO_DATE( string1 [, format_mask] [, nls_language] )
Parameters or Arguments
string1 - The string that will be converted to a date.
format_mask Optional. - This is the format that will be used to convert string1 to a date.
nls_language Optional. - This is the nls language used to convert string1 to a date.
Returns
The TO_DATE function returns a date value.
A. It would not execute because line 8 does not have the proper syntax. The WHERE clause is missing, which is necessary to specify the condition for updating the data in the table. Without the WHERE clause, all rows in the table would be affected.
B. It would not execute because two tables cannot be referenced in a single UPDATE statement. The UPDATE statement can only update data in one table at a time, so referencing two tables in the same statement is not allowed.
C. It would not execute because line 9 does not have the proper syntax. The column name in the SET clause is missing the table alias or table name, which is required to identify the specific column to be updated in the ORDERS table.
D. The UPDATE statement would not execute because line 4 does not have the proper syntax. The SET keyword is missing before the column name and value assignment, which is essential for updating the data in the table.
E. It would not execute because the UPDATE statement has syntax errors that prevent it from executing successfully. The missing SET keyword, WHERE clause, and incorrect table references would cause the statement to fail during execution.
F. It would not execute because a SELECT statement cannot be used in place of a table name. The UPDATE statement requires a table name to specify the target table for updating the data, and a SELECT statement cannot be used in this context.
Discussion
Think the marked answer is wrong, or have a better explanation? Share it below — comments appear after review.
