Student Performance Categories
You are working with a student performance dataset. For an upcoming report, you need to assign a performance category to each student based on their score. The categories are defined as follows:
- 'Excellent' for scores 90 or above
- 'Good' for scores between 80 and 89
- 'Average' for scores between 60 and 79
- 'Below Average' for scores below 60
Your task is to generate a list showing each student's name and their assigned performance category.
Tables:
students:student_id(integer, PK),name(varchar(100)),score(integer)
Return the name and the calculated performance_category in that order. Handle ties by alphabetical order of name. NULL scores should be categorized as 'No Score'. If the result set is empty, return an empty result.
Tables
CREATE TABLE students (
student_id integer PRIMARY KEY,
name varchar(100) NOT NULL,
score integer
);Example data
INSERT INTO students (student_id, name, score) VALUES (1, 'Alice', 95), (2, 'Bob', 82), (3, 'Charlie', 70), (4, 'David', 45), (5, 'Eve', 89), (6, 'Frank', 90), (7, 'Grace', 60), (8, 'Heidi', NULL), (9, 'Ivy', 79), (10, 'Jack', 80);
Your query is also checked on hidden datasets with different data.
Hints
-- Select student names and their performance categories
SELECT
name,
-- Calculate performance category here
FROM
students
Your query runs on the example tables shown with the problem, then on hidden datasets when you submit.
Write standard SQL. MySQL habits work too: IFNULL, IF, YEAR(), DATE_FORMAT, GROUP_CONCAT, `backticks` and LIMIT 5, 10. It runs on PostgreSQL, so integer division keeps whole numbers (5 / 2 = 2): write 5.0 / 2 for 2.5.