RSS Feed

Multiple Choice Questions - SQL Merge

Multiple Choice Questions - SQL Merge

1. If you are running multiple DML statements, for each DML operation, SQL Server processes data separately, resulting in more time to complete. However with Merge statement, there is a possibility to perform the same task in single statement thereby saving time.

Above statement is
A) True
B) False

2. When used after MERGE, . . . . . returns the total number of rows inserted, updated, and deleted to the client.

A) @IDENTITY
B) Rownum
C) @@ROWCOUNT
D) Action

3. Merge requires . . . . permission on the source table and INSERT, UPDATE, or DELETE permissions on the target table.

A) EXECUTE
B) SELECT
C) ALTER
D) Admin

4. The MERGE statement can have at most . . . . WHEN MATCHED clauses.

A) Four
B) Three
C) Two
D) One

5. Your customer is a restaurant, and has a hard_drinks_1 and a more recent hard_drinks_2 table. What is the best way to merge the data from the hard_drinks_2 into the hard_drinks_1, and update prices for those items that appear in both menus to the hard_drinks_2 prices?

Table: hard_drinks_1


Table: hard_drinks_2


A 
Merge into hard_drinks_1
using hard_drinks_2
on (hard_drinks_1.item = hard_drinks_2.item)
when matched then
update set price = hard_drinks_2.price
when not matched by source then
insert (item, price) values (hard_drinks_2.item, hard_drinks_2.Price);
B 
Merge into hard_drinks_1
using hard_drinks_2
on (hard_drinks_1.item <> hard_drinks_2.item)
when matched then
update set price = hard_drinks_2.price
when not matched by target then
insert (item, price) values (hard_drinks_2.item, hard_drinks_2.Price)
C 
Merge into hard_drinks_1
using hard_drinks_2
on (hard_drinks_1.item = hard_drinks_2.item)
when matched then
update set price = hard_drinks_2.price
when not matched by target then
insert (item, price) values (hard_drinks_2.item, hard_drinks_2.Price);
D 
Merge into hard_drinks_1
using hard_drinks_2
on (hard_drinks_1.price <> hard_drinks_2.price)
when matched then
update set price = hard_drinks_2.price
when not matched by target then
insert (item, price) values (hard_drinks_2.item, hard_drinks_2.Price);

6. When a MERGE statement is used with a data source that contains duplicated data, so that the same row in the target table will be updated more than once . . . . . .

A) Only first found row is update / delete.
B) All the found rows are updated / deleted.
C) Only the first found row is inserted.
D) An error is generated.

7. Merge statement must always end with a semicolon or else it will not execute.

A) True
B) False

8. $action is a column of type nvarchar(10) that returns one of three values for each row . . . . . . according to the action that was performed on that row.

A) INSERT', ‘MERGE’, or 'DELETE',
B) INSERT', 'UPDATE', or ‘SELECT’,
C) INSERT', 'UPDATE', or 'DELETE',
D) GRANT’, 'UPDATE', or 'DELETE',

9. While using merge statement the target_Table can be a . . . . . , similarly the source table can be a . . . . table.

A) table or view, merged
B) table or view, temporary
C) table or view, empty
D) table or view, derived

10. One of the clauses in the MERGE statement is WHEN NOT MATCHED. You should use this clause to insert new rows into the target table. The rows you insert into the table are those rows in the source table for which there are . . . . . rows in the target.

A) matching
B) no matching
C) can be A or B
D) none of above

Answers