Exam 70-761 topic 1 question 158 discussion

The first box should be DISTINCT CourseId, EmployeeId, EvalScore

I think to meet the condition: "The result set must include only one score for each employee for each course", EmployeeId must also be included in the group by clause. Now you only get one score for each course.

drop table evaluations create table evaluations ( evaluationID int not null, employeeID int null, courseID int null, evalScore int null ) insert into evaluations values (1, 123, 222, 18), (2, 124, 222, 12), (3, 125, 223, 20), (4, 126, 224, 21), (5, 126, 223, 35) select * from evaluations select courseid, employeeid, evalscore from evaluations ; with evals_CTE (courseID, employeeid, evalscore) as (select courseid, employeeid, evalscore from evaluations) select courseID, avg(distinct(evalscore)) from evals_cte group by courseid

No correct answer. Even with DISTINCT(CourseID, EmployeeID, EvalScore) you would get multiple rows for the course/employee if the eval scores were different.

To include one score for each employee for each course the DISTINCT clause is not enough; this would need a ranking function.

This portion is correct: SELECTCourseID, EmployeeID, EvalScore FROM Evaluations will bring in all the Course and their EvalScore. When you group by CourseID and get the AVG(EvalScore) you will get average score by course but not by employee so you need to group by employeeID and CourseID and also add EmployeeID to the select in order for the result set to be correct. If you want to see the employeeID per course you should show it.

this req: "The result set must include only one score for each employee for each course." seems like it would need a distinct courseid, empid, evalscore

so which is the correct answer for the first box?

how about select courseid, avg(distinct evalscore) in second box?