Exam 70-761 topic 1 question 156 discussion

Update: in addition, answer of Box 3 must be HAVING, since where does not allow aggregate functions

it is: 1. Table 2. SUM(cp.NumStudents) AS NumStudents 3. Having

you can only do this using group by and having. An alias after the sum is even permitted. select A.EMPLOYEENUMBER, sum(B.AMOUNT) AS TOTALSUM from TBLEMPLOYEES INNER JOIN TBLTRANSACTIES B ON A.EMPLOYEENUMBER = B.EMPLOYEENUMBER GROUP BY A.EMPLOYEENUMBER HAVING SUM(B.AMOUNT) > 0

Need alias and then move on

2) The result set must include only one score for each employee for each course. If you want to solve this part of the question: ;with Evals_CTE (courseId, EmployeeId, EvalScore) AS ( select CourseID, EmployeeID, EvalScore from Evaluations ) select EmployeeId, courseId, AVG(EvalScore) AvgScore from Evals_CTE group by EmployeeId, courseId All that said and done the question is asking two different things that can only be answered with two different approaches. I think we got two for one here. let's move on to the next question :)

The question has two parts: 1)You must create a report that returns course identifiers and the average evaluation score for each course Ok If you want to solve this part use: ;with Evals_CTE (courseId, EmployeeId, EvalScore) AS ( select CourseID, EmployeeID, EvalScore from Evaluations ) select courseId, AVG(EvalScore) AvgScore from Evals_CTE group by courseId

You are welcome to use this sample date below: use test3 go create table Evaluations( EvaluationID int not null, EmployeeID int not null, CourseId int null, EvalScore int null ) insert into Evaluations values (1, 11, 2000, 89), (1, 11, 2000, 91), (2, 11, 3000, 90), (2, 11, 3000, 80), (3, 11, 4000, 78), (3, 11, 4000, 88), (4, 22, 2000, 90), (4, 22, 2000, 60), (5, 22, 3000, 70), (5, 22, 3000, 90), (6, 22, 4000, 88), (6, 22, 4000, 98), (7, 33, 2000, 80), (7, 33, 2000, 90), (8, 33, 3000, 80), (8, 33, 3000, 90), (9, 33, 4000, 86), (9, 33, 4000, 86), (10,44, 2000, 70), (10,44, 2000, 90), (10,44, 3000, 70), (10,44, 3000, 60), (10,44, 4000, 70), (10,44, 4000, 90)

That is right the Group by Clause is missing here. The example given above has the Group By that is why it works. Question needs some corrections.

is it only me who doesn't see GROUP BY clause?

You have to provide a name for every column in a table...including the table returned by the function, so the 2nd choice should be the one with a name. Check something similar: https://stackoverflow.com/questions/20251911/column-alias-error-with-sql-server-udf Tested: create table employees(ID int, orderAmount INT); GO insert into employees values(1, 5),(2,5),(3,5),(4,4),(5,1); GO CREATE function MyF(@var int) returns table as Return select ID, sum(orderAmount) as S from employees group by ID having sum(orderAmount) > @var; GO Select * from MyF(4);

Need a Group By C.Course and it has to be Having Sum(cp.NumStudents) > @totalStudents

having wont work without a group by

You must provide alias for column, otherwise you will get compilation error when creating function. Group by is another thing which is missing here but let's suppose that is not part of this question.

having is correct

Missing alias as well

I agree where cannot be used. Should be having

'Group by' is missing as well.