ExamTopics Logo
Mail Us[email protected]
Menu
Microsoft Discussions
exam questions

Exam 70-761 All Questions

View all questions & answers for the 70-761 exam
Go to Exam

Exam 70-761 topic 1 question 141 discussion

Actual exam question from Microsoft's 70-761
Question #: 141
Topic #: 1
[All 70-761 Questions]

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.

Show Suggested Answer Hide Answer
Suggested Answer: G
by Bartek at Sept. 21, 2019, 3:05 p.m.

Comments

Chosen Answer:
This is a voting comment (?). It is better to Upvote an existing comment if you don't have anything to add.
Switch to a voting comment New
Submit Cancel
New_user
Highly Voted 5 years, 4 months ago
Answer D is returning DEnormalized data using pivot, so it's wrong. G selects all data inserted, but NOT created in 2014. The only adorable answer is H
upvoted 20 times
Prides
5 years, 2 months ago
NOT H. if the dateCreated is 20141231, then H will miss those. Still G.
upvoted 1 times
Ondaenergetica
5 years, 2 months ago
FROM/ AND would exclude them . BETWEEN /AND would not .
upvoted 5 times
Ondaenergetica
5 years, 2 months ago
In addition Data in G is oviously NOT normalized
upvoted 2 times
...
anonimdom
5 years, 2 months ago
20141231 = 20141231 0:00. It will not exclude 20141231 0:00, but will exclude 20141231 0:01 and everything after it.
upvoted 2 times
MarcusJB
5 years, 2 months ago
Yes, regarding the last day in the year 2014 I'm with you, anonimdom, but the question is about the creation date, not the valid period. That's a big difference. The data could be corrected (e.g. typo in name) in 2016 but the customer was still created in 2014. And being completely precise: the end date in G is also not completely conform to the question because '2015-01-01 00:00:00.000000' is obviously not in 2014, right? ;-) So I would come to the result, that H is the nearest correct answer, but also no 100% solution.
upvoted 9 times
...
...
...
...
...
Billybob0604
Most Recent 4 years, 3 months ago
No, answer is G. Reason is that H does not query on the history table as it does not use a "for system_time" statement. Therefore H will not yield any records as it only contains actual data with records valid to 9999-12-31 23:59:59.99
upvoted 1 times
...
SimSql
4 years, 4 months ago
H Is the correct answer. The BETWEEN operator selects values within a given range. The values can be numbers, text, or dates. The BETWEEN operator is inclusive: begin and end values are included. All records with "dateCreated" column values BETWEEN '20140101' AND '20141231' are included.
upvoted 1 times
Vermonster
4 years, 4 months ago
except that dateCreated leaves out 20141231 times after midnight so unless you ordered right at midnight, it wouldn't be included in BETWEEN because the datetime2 translates the date to midnight. BETWEEN '20140101' and '20150101' would be a lot closer. Tested: DECLARE @xdate datetime2 SELECT @xdate='20141231 0:01' IF @xdate BETWEEN '20140101' AND '20141231' PRINT 'Yes' I don't think any are correct
upvoted 1 times
Vermonster
4 years, 4 months ago
also because SYSTEM_TIME would be when record was current on the system, not when it was added
upvoted 1 times
...
...
...
Andy7622
4 years, 4 months ago
I can't be G, because it doesn't use DateCreated G will give all the records for the 2014 , not customer added. Correct is H.
upvoted 1 times
...
sunz_1
4 years, 7 months ago
should add extra filter on created date on top of AS OF
upvoted 1 times
...
Aghie
4 years, 7 months ago
DateCreated is datetime2. so it cant be H. the closest is G
upvoted 1 times
...
Bartek
5 years, 7 months ago
The closest II be D -- statement with Pivot
upvoted 1 times
Tr4ckz
5 years, 7 months ago
if you compare it to #140 you'll find that D fits rather for the previous question. G seems to be fitting here (normalized data, not aggregated, 2014)
upvoted 5 times
mlourinho
5 years, 5 months ago
The question explicit "all customers that were added in the year 2014", therefore Year(DateCreated) = 2014
upvoted 1 times
...
...
Andy7622
4 years, 4 months ago
it's not normolized if it aggregates
upvoted 1 times
...
...
Community vote distribution
A (35%)
C (25%)
B (20%)
Other
Most Voted
A voting comment increases the vote count for the chosen answer by one.

Upvoting a comment with a selected answer will also increase the vote count towards that answer by one. So if you see a comment that you already agree with, you can upvote it instead of posting a new comment.

SaveCancel
exam
Someone Bought Contributor Access for:
SY0-701
London, 1 minute ago