Examine this SQL statement:
Which two are true? (Choose two.)
Exam 1z0-071 All QuestionsBrowse all questions from this exam
Question 8
Correct Answer: A, D
In the provided SQL statement, all existing rows in the ORDERS table are updated, even if no match is found in the subquery, causing the customer_name to be set to NULL for those rows. The subquery is a correlated subquery, meaning it is executed for every row in the ORDERS table to get the appropriate customer name from the CUSTOMERS table. The subquery is not executed before the UPDATE statement as the subquery must run for each row being updated to retrieve the correct value. Furthermore, if the subquery selects multiple rows, the update statement would fail with an error.
Discussion
meddheddyOptions: AD
A and D tested on Oracle 12c1
Fredderik91Options: BD
BD, A is incorrect only matching rows are updated, not all C is invalid, statement correct structure E is incorrect, in case multiple rows, error multiple rows in subquery
GENO123
definitely agree. Oracle will only update the rows that are matched
TofOra
No, it will update unmatched rows with NULL
HassanMkhlalati
Incorrect
hmatinnn
Actually i though in that way. But most likely customer table is PK. Orders table is FK. So it means the data that in the orders table also have in the customers table. And all rows will be updated.
subtype11Options: AD
Answer: A and D are correct. B is not correct because the subquery is executed for each row in the ORDERS table, not before the update statement is executed. C is not correct because the subquery is a correlated subquery, not a non-correlated subquery. E is not correct because if the subquery selects multiple rows, the update statement will fail with a "subquery returns more than one row" error.
CyberP
I think B is correct, as you said subquery will execute for each row its right but also update will update for each raw after fulfilling the condition of subquery :) Then how update will update each raw ? update will check its resources "subquery" is condition met ? yes ? then update after execute subquery and fully checking it.
TheOracleWasTaken
how is B not correct, isn't it obviously gonna run the subquery before it updates the row?
Fredderik91Options: AD
B (false), depending EXPLAIN PLAN. no always is executed first. C (false), ".o" is correlative E (false) is possible multiple rows, an error more than row. A and D is correct
Rik92Options: AD
AD are correct
alic_alexOptions: AD
E - wrong because will cause an error "ORA-01427: Subquery returns more than one row"
MakubOptions: AE
A and E are correct answers
Elnara87Options: AD
what is an answer in exam? i thin AD are correct.
Elnara87
I mean B not D
RamileuOptions: BD
SHOULD BE b AND d
hmatinnnOptions: AD
sHOULD BE a AND d
Lee_jong_sukOptions: AD
AD option is right
jm9999Options: AD
AD; cannot be B, correlated subqueries are not executed before the parent (outer) query. Cannot be E, cannot set customer_name to multiple values for each row
HassanMkhlalatiOptions: AD
A is correct D :It will retrieve the matching value for each row from the table orders with the table customers
NB196
Can someone explain why A is correct?
raduM
because it updates every row. If it does not have a match it will put the value null
hmatinnn
most likely customer table is PK. Orders table is FK. So it means the data that in the orders table also have in the customers table. And all rows will be updated.
CyberPOptions: BD
I think B is correct, subquery will execute for each row its right but also update will update for each raw after fulfilling the condition of subquery :) Then how update will update each raw ? update will check its resources "subquery" is condition met ? yes ? then update after execute subquery and fully checking it.
TofOra
The STATMENT update will execute first, then the action of updating will execute after the subquery. explain plan for update ordersQ8 o set customer_name = (select cust_last_name from customersQ8 where customer_id = o.customer_id); SELECT plan_table_output FROM TABLE(DBMS_XPLAN.DISPLAY('plan_table',null,'basic +note'));
kuffOptions: AD
AD is the answer
kaplanekOptions: AD
A nd D are correct