Examine this SQL statement:
Which two are true? (Choose two.)
1z0-071 Exam QuestionsBrowse all questions from this exam
1z0-071 Exam - Question 8
Show AnswerHide Answer
Correct Answer: ABD
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
17 commentsmeddheddyOptions: AD
Oct 1, 2022A and D tested on Oracle 12c1
Fredderik91Options: BD
May 28, 2023BD, 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
Jun 23, 2023definitely agree. Oracle will only update the rows that are matched
TofOra
Feb 2, 2024No, it will update unmatched rows with NULL
HassanMkhlalati
Sep 6, 2023Incorrect
hmatinnn
Dec 3, 2023Actually 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
Jan 28, 2023Answer: 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
May 26, 2023I 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
Aug 1, 2023how is B not correct, isn't it obviously gonna run the subquery before it updates the row?
Elnara87Options: AD
Jan 19, 2023what is an answer in exam? i thin AD are correct.
Elnara87
Jan 19, 2023I mean B not D
MakubOptions: AE
Feb 27, 2023A and E are correct answers
alic_alexOptions: AD
Mar 3, 2023E - wrong because will cause an error "ORA-01427: Subquery returns more than one row"
Rik92Options: AD
May 16, 2023AD are correct
Fredderik91Options: AD
May 13, 2024B (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
kaplanekOptions: AD
Apr 19, 2023A nd D are correct
kuffOptions: AD
May 8, 2023AD is the answer
CyberPOptions: BD
May 26, 2023I 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
Feb 2, 2024The 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'));
NB196
May 27, 2023Can someone explain why A is correct?
raduM
Jun 30, 2023because it updates every row. If it does not have a match it will put the value null
hmatinnn
Dec 3, 2023most 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.
HassanMkhlalatiOptions: AD
Sep 6, 2023A is correct D :It will retrieve the matching value for each row from the table orders with the table customers
jm9999Options: AD
Sep 16, 2023AD; 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
Lee_jong_sukOptions: AD
Dec 8, 2023AD option is right
hmatinnnOptions: AD
Jan 14, 2024sHOULD BE a AND d
RamileuOptions: BD
Jul 1, 2024SHOULD BE b AND d