BenchMark Quizzes by MDFT Pro

Complete the T-SQL Statement to Show Student Counts by Registration Type

You work for MDFT Pro, a well-known training agency. You have been asked to analyze the student Registration data in your Microsoft Fabric workspace that contains a warehouse named TrainingWarehouse. The warehouse contains the following data:

Table name	Column name	Data type
Student	StudentID	Int
Student	StudentName	Varchar(128)
Registration	StudentID	Int
Registration	RegistrationType	Varchar(64)
Registration	StartDate	Datetime2

You need to create a T-SQL statement that will denormalize the tables and includes the RegistrationType attribute in the results. The solution must meet the following requirement:

Return the total number of students per registration type where there are more than two students registered.

You have the following T-SQL query:

WITH result AS(
    SELECT 
        s.StudentID,
        s.StudentName,
        r.RegistrationType
    FROM Student AS s
    LEFT OUTER JOIN Registration AS r ON r.StudentID = s.StudentID
)
SELECT COUNT(DISTINCT StudentID) AS TotalStudents, RegistrationType
FROM result
GROUP BY RegistrationType
_____________ COUNT(DISTINCT StudentID) > 2

How should you complete the statement?

Choose the correct answer from the options below.

Explanations for each answer:

CONTAINS is incorrect. CONTAINS is a T-SQL predicate used for full-text search operations on text columns. It cannot be used to filter aggregated results after a GROUP BY clause.
HAVING is correct.
LIMIT is incorrect. LIMIT is not valid T-SQL syntax. In T-SQL, you would use TOP to limit the number of rows. Additionally, this clause cannot be used to filter aggregated results after a GROUP BY clause.
WHERE is incorrect. The WHERE clause filters rows before they are grouped and cannot be used with aggregate functions like COUNT. When filtering based on aggregate results, HAVING must be used instead.

Learn more about filtering groups in T-SQL:

T-SQL Group Filtering

Exam Preparation Quiz

Complete the T-SQL Statement to Show Student Counts by Registration Type