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

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

DRAG DROP -
Note: This question is part of a series of questions that use the same scenario. For your convenience, the scenario is repeated in each question. Each question presents a different goal and answer choices, but the text of the scenario is exactly the same in each question in this series.
You query a database that includes two tables: Project and Task. The Project table includes the following columns:

The Task table includes the following columns:

You need to find all projects that have at least one task that took more than 50 hours to complete. You must also determine the average duration of the tasks that took more that took more than 50 hours to complete for each project.
How should you complete the Transact-SQL statement? To answer, drag the appropriate Transact-SQL segments to the correct locations. Each Transact-SQL segment may be used once, more than once or not at all. You may need to drag the split bar between panes or scroll to view content.
Select and Place:

Show Suggested Answer Hide Answer
Suggested Answer:
by M4x at Sept. 11, 2019, 2:59 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
M4x
Highly Voted 5 years, 8 months ago
I think the where should be: datediff(hh, t.starttime, t.endtime) > 50
upvoted 16 times
Bartek
5 years, 7 months ago
I think it can be both. 1. datediff(hh, t.starttime, t.endtime) > 50 is right answer becouse datediff is returning value as INT and INT can be compared with ">50" 2. dateadd(hh, 50, StartTime) > EndTime : logic is right and syntax is quite good too becouse dateadd function returns datetime value and EndTime is datetime value too
upvoted 1 times
melvin9900
4 years, 10 months ago
may be the extra bracket at the end in the option is throwing it off. datediff(hh, t.starttime, t.endtime)) > 50
upvoted 1 times
...
jonasdv
4 years, 6 months ago
I do not think the second option is valid. if the task took for instance 70 hours then adding 50 to the begin task will not make it greater than end time.
upvoted 5 times
...
...
mattia_88
5 years, 6 months ago
No! "t.endtime" could be null if the task not yet finished.
upvoted 3 times
mattia_88
5 years, 6 months ago
sorry. is correct datediff(hh, t.starttime, t.endtime) > 50
upvoted 2 times
...
...
Anette
4 years, 11 months ago
And with one bracket :)
upvoted 1 times
...
...
itdoesntmatter
Highly Voted 5 years, 7 months ago
I would say that the correct answer is: datediff(hh, t.starttime, t.endtime) > 50. And it is AVG.. never heard about AVR in SQL server.
upvoted 11 times
BobSnijders
5 years ago
Lol, indeed. Didn't even notice.
upvoted 1 times
...
Anette
4 years, 11 months ago
yes its correct. And tested AVR is not a built-in function. For Average in SQL Server it is AVG function :)
upvoted 1 times
...
...
Andy7622
Most Recent 4 years, 4 months ago
first of all AVG instead of AVR))... Second of all it's as M4x said.
upvoted 1 times
...
eduardogtc
4 years, 7 months ago
The correct answer is datediff(hh, t.starttime, t.endtime > 50 I did the test bellow and worked: select * into #testdif from (values ( '2020-01-01 1:00' , '2020-01-01 2:00' ), ( '2020-01-01 1:00' , '2020-02-01 2:00' ), ( '2020-01-01 1:00' , '2020-01-01 20:00' ), ( '2020-01-01 1:00' , null ), ( '2020-01-01 1:00' , '2020-01-01 3:00' ) ) as d (a,b) select * from #testdif where datediff( hh, a,b ) > 50 To produce the same result, the operator in the dateadd needs to be "<" instead or ">", as the example bellow (tested): select * from #testdif where dateadd( hh, 50, a ) < b
upvoted 3 times
...
vermeilyn
4 years, 11 months ago
Nvm, you can do without GROUP BY for one single column of values
upvoted 1 times
...
vermeilyn
4 years, 11 months ago
How can you use AVG() if there is no GROUP BY? This question is weird.
upvoted 1 times
...
prakash101179
5 years, 7 months ago
I think datediff(hh, t.starttime, t.endtime) > 50 is correct answer. 'dateadd(hh, 50, StartTime) > EndTime' doesnt solve the purpose because if task starts for ex- at 23 Sep 2019 13:00 pm and took 10 hours to complete (which means finished at 23 Sep 2019 23:00 PM). Above logic interpret it something like this - '25 Sep 2019 15:00 pm > 23 Sep 2019 23:00 PM' which is not correct in this case.
upvoted 8 times
AnsB
5 years, 3 months ago
NO. IF the EndTime is NULL, you will get NULL for datediff.
upvoted 1 times
Hoey
5 years, 1 month ago
But we cannot even use > to compare NULL value, in this case, dateadd is also wrong.
upvoted 1 times
...
BobSnijders
5 years ago
But the WHERE clause filters for 'IS NOT NULL'. I still think Datediff is the correct answer tho.
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