Find the Highest Score in Each Class

This challenge focuses on using the MAX aggregate function in SQL to find the maximum value within groups. In educational or performance tracking scenarios, you often need to identify the top performer or highest score in different categories. This exercise will help you practice extracting such maximum values efficiently.

Problem Description

You are given a table named Scores that records student scores for different subjects. The table has the following columns:

• student_id (integer): A unique identifier for each student.
• subject (string): The name of the subject (e.g., "Math", "Science").
• score (integer): The score achieved by the student in that subject.

Your task is to write a SQL query that returns the highest score achieved for each subject. The output should be a table with two columns: subject and max_score, where max_score is the highest score recorded for that particular subject across all students.

Key Requirements:

• You must use the MAX aggregate function.
• The results should be grouped by subject.
• Each subject should appear only once in the output, with its corresponding highest score.

Expected Behavior: The query should iterate through all entries in the Scores table, identify distinct subjects, and for each subject, find the maximum score recorded.

Edge Cases to Consider:

• What if a subject has no scores recorded? (Assume for this challenge that all subjects in the table will have at least one score).
• What if multiple students achieve the same highest score for a subject? The query should still return that highest score.

Examples

Example 1:

Input Scores table:

Output:

Explanation: For "Math", the scores are 85, 92, and 92. The maximum is 92. For "Science", the scores are 78 and 95, with a maximum of 95. For "History", the only score is 88, which is also the maximum.

Example 2:

Input Scores table:

Output:

Explanation: For "Art", the scores are 90 and 90, with a maximum of 90. For "Music", the scores are 85 and 95, with a maximum of 95.

Constraints

• The Scores table will contain at least one row.
• The subject column will contain string values, and score will contain integer values.
• The score values will be between 0 and 100, inclusive.
• The query should execute efficiently, processing the table in a reasonable amount of time for datasets up to 10,000 rows.

Notes

• Remember that SQL queries are declarative, meaning you describe what you want, not how to get it. The database engine figures out the optimal execution plan.
• The GROUP BY clause is essential for applying aggregate functions to subsets of your data.
• Think about how the MAX function works in conjunction with GROUP BY.