Exam 70-761 All Questions

View all questions & answers for the 70-761 exam

Exam 70-761 topic 1 question 140 discussion

Actual exam question from Microsoft's 70-761

Question #: 140
Topic #: 1

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 are developing a report that aggregates customer data only for the year 2014. The report requires that the data be denormalized.
You need to return the data for the report.
Which Transact-SQL statement should you run?

A.

B.

C.

D.

E.

F.

G.

H.

Show Suggested Answer

Suggested Answer: G

by Tr4ckz at Oct. 8, 2019, 8:57 a.m.

Comments

Submit Cancel

Tr4ckz

Highly Voted 5 years, 8 months ago

IMO this should be D, as denormalized results are required (Pivot) and as well aggregated results, whicha re not provided with the given answer G.

upvoted 14 times

Ilray

5 years ago

But you don't get historical data, which also duplicates rows in main table, hence denormalized

upvoted 4 times

...

d_m

4 years, 9 months ago

cannot be D as you have >=2014 whereas the question specifies with 2014

upvoted 1 times

lh2607

4 years, 6 months ago

D doesn't say >= 2014. Not sure where you got that from.

upvoted 2 times

lh2607

4 years, 6 months ago

Ah just realised you think E is D. D is the one above what you're thinking.

upvoted 1 times

...

Prides

Highly Voted 5 years, 4 months ago

the answer is CORRECT. it met the system_time period

upvoted 6 times

...

Vermonster

Most Recent 4 years, 5 months ago

D is correct - denormalized by pivot for dates that are in 2014. Question doesn't ask for inserts/updates n 2014 which is what the system times would give

upvoted 1 times

Vermonster

4 years, 5 months ago

Amended.. getting tired? Would need to use YEAR() around date - I don't see anything still on the screen that qualifies....

upvoted 1 times

...

Andy7622

4 years, 6 months ago

won't the query in option G be able give as some data from 2015 ? Looks like G is wrong

upvoted 1 times

Andy7622

4 years, 6 months ago

Sorry , It won't because Actually G is correct , because Time range 00:00:00 through 23:59:59.9999999 for datetime2. https://docs.microsoft.com/en-us/sql/t-sql/data-types/datetime2-transact-sql?view=sql-server-ver15 Looks like G is correct.

upvoted 1 times

...

zeinaa

4 years, 9 months ago

correct answer is D pivot since its only for 2014

upvoted 2 times

...

TheDUdeu

4 years, 11 months ago

You want data from 2014 not 2015 12 am. G is wrong time frame but best answer out of them all.

upvoted 3 times

...

loceweh520

5 years, 2 months ago

I believe G is also wrong, BETWEEN is inclusive so it will include data also from 2015.

upvoted 5 times

BabyBee

4 years, 7 months ago

'2015-01-01 00:00:00.0000000' won't include data from 1/1/2015 as it will end right before the day starts.

upvoted 3 times

...

dodor

5 years, 3 months ago

D is wrong. We need the data for the year 2014. NOT the data created in 2014, rows created in 2013 can still be very relevant data in 2014. Hence system_time is required.

upvoted 3 times