Note: This question is part of a series of questions that use the same or similar answer choices. An
answer choice may be correct for more than one question in the series. Each question is independent
of the other questions in this series. Information and details provided in a question apply only to that
question.You create a table by running the following Transact-SQL statement:
You need to return normalized data for all customers that were added in the year 2014.
Which Transact-SQL statement should you run?
A.
B.
C.
D.
E.
F.
G.
H.
Explanation:
The following query searches for row versions for Employee row with EmployeeID = 1000 that were active at
least for a portion of period between 1st January of 2014 and 1st January 2015 (including the upper boundary):
SELECT * FROM Employee
FOR SYSTEM_TIME
BETWEEN ‘2014-01-01 00:00:00.0000000’ AND ‘2015-01-01 00:00:00.0000000’
WHERE EmployeeID = 1000ORDER BY ValidFrom;
https://msdn.microsoft.com/en-us/library/dn935015.aspx
I mean how does that answer to the question and how does it return normalized data . i think the answer is missing , probably a query with unpivot
0
3
Why shouldn’t it be H?
The requirement was to show customers that were added in 2014. So this should be reflected in the DateCreated. It has nothing to do with the explanation of the answer given. Answer G is looking for records that have been (at least partly) been active in 2014 which doesn’t mean that the customer was added in 2014. There was also no requirement to show the avg annual revenue.
25
3
I think the answer should be H.
The table used the SYSTEM_VERSIONING for maintaining history. Maintaining History is one reason for thinking in denormalization. Therefore, when you refer to normalized data you should only use the contents of the main table, i.e. exclude the history data, do not use FOR SYSTEM_TIME.
13
2
H
22
2
LETTER H IS CORRECT.
SELECT * FROM Q28.Customers
WHERE DateCreated >=’2014-01-01 19:45:15.00′ AND DateCreated <='2014-12-31 19:45:15.00'
SELECT * FROM Q28.Customers
WHERE DateCreated
BETWEEN '2014-01-01 19:45:15.00' AND '2014-12-31 19:45:15.00'
3
1
G is the correct answer.
DateCreated is of type Datetime2(3) and if we would use between ‘20140101’ and ‘20141231’ this would be equal to between ‘2014-01-01 00:00:00.000000’ and ‘2014-12-31 00:00:00.000000’. This does not include the customers created in the last day of the year so based on this renders answer H as incorrect.
7
2
Both H and G could be right because the question doesn’t explicitly say that the customers added during 2014 are still kept active. Some customers added could be deleted and moved to the CustomerHistory table.
1
0