Exam 70-461 topic 1 question 249 discussion

DRAG DROP -
You develop a Microsoft SQL Server database for an educational institution. The database contains tables named ExamEvaluation and ProjectEvaluation, which are used to track student scores. The tables are created by using the following code:

Students are evaluated based on the following rules:
Each student will complete a maximum of one exam and one project.

✑ A student must complete at least one exam.
✑ A single exam consists of multiple sections, where the student is evaluated based on the average score of all sections.
✑ A single project consists of Section A and Section B, where the student is evaluated by adding 60 percent of Section A and 40 percent of Section B.
✑ At any given time, a student may have completed only an exam, or both an exam and a project.
You want to create a summary report of student evaluations.
You need to write a query that will return evaluation results for students who have completed any form of evaluation, grouped by the year of undertaling. A sample result set is shown in the following table.

Which five Transact-SQL segments should you use to develop the solution? To answer, move the appropriate Transact-SQL segments from the list of Transact-
SQL segments to the answer area and arrange them in the correct order.
Select and Place:

Suggested Answer:
Box 1: SELECT..

The outer select statement -
Box 2: (SELECT ..AVG(SectionScore)
Need the average score: A single exam consists of multiple sections, where the student is evaluated based on the average score of all sections.

Box 3: LEFT OUTER JOIN -
There might be no finished Project
Box 4: ON..

Box 5: (SELECT ..SUM -
Need the SUM function to handle addition of NULL values.
References:
https://docs.microsoft.com/en-us/sql/t-sql/functions/sum-transact-sql