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 170 discussion

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

HOTSPOT -
You create a table to store sales information for an online sales application by running the following Transact-SQL statement:

You have a historical report that summarizes the sales for each quarter and year. The query that generated the data for the report is no longer available. A representative report contains the following data:

You need to recreate the query for the report.
How should you complete the Transact-SQL statement? To answer, select the appropriate Transact-SQL segments in the answer area.
NOTE: Each correct selection is worth one point.
Hot Area:

Show Suggested Answer Hide Answer
Suggested Answer:
WHEN expression is a simple expression to which input_expression is compared when the simple CASE format is used and is any valid expression.
ELSE is the expression returned if no comparison operation evaluates as TRUE.
DATEPART returns an integer which represents the specified datepart of the date, such as day, month, year, quarter etc.
References:
https://docs.microsoft.com/en-us/sql/t-sql/language-elements/case-transact-sql?view=sql-server-2017 https://docs.microsoft.com/en-us/sql/t-sql/functions/datepart-transact-sql?view=sql-server-2017
by Dieter at Aug. 21, 2019, 1:29 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
Dieter
Highly Voted 5 years, 8 months ago
should be 1. option: GROUPING 2. option: GROUPING 3. option: year, quarter WITH ROLLUP
upvoted 31 times
mlourinho
5 years, 5 months ago
IF OBJECT_ID('TEMPDB..#SALES') IS NOT NULL DROP TABLE #SALES CREATE TABLE #SALES ( SalesOrderID INT IDENTITY NOT NULL , OrderDate DATETIME NOT NULL , Total MONEY NULL ) INSERT INTO #SALES(Total, OrderDate) VALUES ('8771886.36', '20130101') , ('14373277.48', '20140101') , ('12225061.38', '20130401') , ('8046220.84', '20140401') , ('14339319.19', '20130701') , ('13629621.04', '20131001') SELECT SUM(Total) AS Total , DATEPART(QUARTER, OrderDate) AS [Quarter] , YEAR(OrderDate) AS [YEAR] , GROUPING(DATEPART(QUARTER, OrderDate)) AS G1 , GROUPING(DATEPART(YEAR, OrderDate)) AS G2 FROM #SALES GROUP BY DATEPART(QUARTER, OrderDate), YEAR(OrderDate) WITH ROLLUP
upvoted 23 times
Prides
5 years, 3 months ago
thank you for the codes. it works exactly as it should be.
upvoted 3 times
...
MarcusJB
5 years, 2 months ago
I guess it should be vice versa from big to fine groupings: ... GROUP BY YEAR(OrderDate), DATEPART(QUARTER, OrderDate) WITH ROLLUP; Else you would sum up all quarters of all years and then all years without quarters.
upvoted 1 times
...
TheDUdeu
4 years, 8 months ago
This is 100 % correct. Tried the other ways not even close.
upvoted 1 times
...
...
Anette
4 years, 11 months ago
But there is no GROUPING(DATEPART(YEAR, OrderDate)) AS G2. It is just GROUPING((YEAR, OrderDate)) AS G2. And like this it doesn't work
upvoted 2 times
Anette
4 years, 11 months ago
OK. Tested. It is Grouping both. First With DATEPART and second without.
upvoted 1 times
BabyBee
4 years, 5 months ago
YEAR() is also a function, to get the year of a given date.
upvoted 1 times
...
...
...
chaoxes
4 years, 9 months ago
Your solution is correct, had it on exam and passed
upvoted 2 times
...
...
Vermonster
Most Recent 4 years, 4 months ago
Correct answer is Quarter, Year with ROLLUP. Not intuitive but you can see it summarizes quarter at highest level and NULLs Year so much. The rest is correct. Whole thing: SELECT SUM(Total) AS Total , DATEPART(QUARTER, OrderDate) AS [Quarter] , YEAR(OrderDate) AS [YEAR] , GROUPING(DATEPART(QUARTER, OrderDate)) AS G1 , GROUPING(DATEPART(YEAR, OrderDate)) AS G2 FROM #SALES GROUP BY DATEPART(QUARTER, OrderDate), YEAR(OrderDate) WITH ROLLUP
upvoted 1 times
...
Andy7622
4 years, 4 months ago
Ahh it can't be =NULL definitely
upvoted 1 times
...
Jiacheng
5 years, 2 months ago
if change case when to 'is null' then could use case when
upvoted 2 times
BabyBee
4 years, 5 months ago
You may think it works, but it doesn't, the difference here is that CASE won't know if that NULL is due to its column being part of a group or not. It will just return 0 or 1 if there is a NULL or not, but it won't be able to evaluate if it is a NULL coming from the column because it is NULL or a NULL because it is part of the grouping or not.
upvoted 1 times
...
...
anonimdom
5 years, 3 months ago
"WITH ROLLUP" - isn't it a MYSQL syntax?
upvoted 1 times
MarcusJB
5 years, 2 months ago
It's an old notation. WITH ROLLUP and WITH CUBE are non-ISO compliant syntax and will be removed in a future version.
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