BenchMark Quizzes by MDFT Pro

Complete The T-SQL Query To Convert Assessment Scores

You are working with a Microsoft Fabric warehouse at MDFT Pro that contains a table named StudentAssessments. The table contains the following columns:

Column name	Data type
student_id	Integer
assessment_name	varchar(50)
assessment_score	varchar(20)

The assessment_score column contains numeric scores like ‘85’, ‘92’, etc., but also contains text values like ‘Incomplete’, ‘N/A’, or ‘Pending’ for students who haven’t completed their assessments.

You need to complete a T-SQL statement that meets these requirements:

Output the student ID
Output the assessment score as an integer, but return NULL for non-numeric values

You have the following T-SQL query:

SELECT
    student_id AS studentID,
    __________ AS score
FROM StudentAssessments

How should you complete the query?

Choose the correct answer from the options below.

Explanations for each answer:

CONVERT(int, assessment_score) is incorrect. Using CONVERT will raise an error when encountering non-numeric values like 'Incomplete' or 'N/A', causing the query to fail rather than returning NULL for invalid values.
CAST(assessment_score AS int) is incorrect. CAST will raise an error when it encounters non-numeric values, causing the entire query to fail instead of returning NULL for values that cannot be converted.
TRY_CAST(assessment_score AS int) is correct. TRY_CAST attempts to convert a value to the specified data type and returns NULL if the conversion fails. For numeric scores like '85' or '92', it returns the integer value. For non-numeric entries like 'Incomplete' or 'N/A', it gracefully returns NULL without causing an error, which meets the requirement exactly.
SELECT(assessment_score AS int) is incorrect. SELECT is not a conversion function. It is used to retrieve data from tables, not to convert data types. This syntax is invalid and will cause a SQL error.

Learn more about the T-SQL TRY_CAST function:

TRY_CAST Function

Practice Exam

Complete The T-SQL Query To Convert Assessment Scores

Discuss this question on social media: