You create and populate two tables by using the following Transact-SQL statements:
CREATE TABLE CurrentStudents (
LastName VARCHAR(50),
FirstName VARCHAR(50),
Address VARCHAR(100),
Age INT);
INSERT INTO CurrentStudents VALUES
(‘Fritz’, ‘David’, ‘181 Kline Street’, 14)
,(‘Reese’, ‘Paul’ , ‘4429 South Union’, 14)
,(‘Brown’, ‘Jake’ , ‘5401 Washington Ave’,14)
,(‘Smith’, ‘Tom’ , ‘124 Water St’, 14)
,(‘Holtz’, ‘Mary’ , ‘984 Mass Ct’, 14)
,(‘Robbins’, ‘Jan’ , ‘4449 Union Ave’, 14)
,(‘Larsen’, ‘Frank’ , ‘5812 Meadow St’, 14)
,(‘Bishop’, ‘Cathy’ , ‘14429 Skyhigh Ave’, 14)
,(‘Francis’, ‘Thomas’ , ‘15401 120th St’, 14)
CREATE TABLE NewYearRoster(LastName VARCHAR(50), FirstName VARCHAR(50), Address VARCHAR(100), Age INT);
INSERT INTO NewYearRoster VALUES
(‘Fritz’, ‘David’, ‘181 Kline Street’, 15)
,(‘Reese’, ‘Paul’, ‘1950 Grandview Place’, 15)
,(‘Adams’, ‘Wilbur’, ‘4231 W. 93rd’, 15)
,(‘Adams’, ‘Norris’, ‘100 1st Ave’, 15)
,(‘Thomas’, ‘Paul’, ‘18176 Soundview Dr’, 15)
,(‘Linderson’, ‘Danielle’, ‘941 W. 37 Ave’, 15)
,(‘Moore’, ‘Joshua’, ‘2311 10st Ave’, 15)
,(‘Dark’, ‘Shelby’, ‘1987 Fifth Ave’, 15)
,(‘Scharp’, ‘Mary’, ‘1902 W. 303rd’, 15)
,(‘Morris’, ‘Walt’, ‘100 12st St’, 15);
You run the following MERGE statement to update, insert and delete rows in the CurrentStudents table
MERGE TOP (3) CurrentStudents AS T USING NewYearRoster AS S ON S.LastName = T.LastName AND S.FirstName = T.FirstName
WHEN MATCHED AND NOT (T.Age = S.Age OR T.Address = S.Address) THEN UPDATE SET Address = S.Address, Age = S.Age
WHEN NOT MATCHED BY TARGET THEN INSERT (LastName, FirstName, Address, Age) VALUES (S.LastName, S.FirstName, S.Address, S.Age)
WHEN NOT MATCHED BY SOURCE THEN DELETE;
You need to identify the total number of rows that are updated, inserted, and deleted in the CurrentStudent table.
Which total number of rows should you choose?
A. 9
B. 0
C. 3
D. 6
