3.5

Suppose that we have a relation marks(ID,score) and we wish to assign grades to students based on the score as follows: grade F if score \(<\) 40, grade C if 40 \(\leq\) score \(<\) 60, grade B if 60 \(\leq\) score \(<\) 80, and grade A if 80 \(\leq\) score. Write SQL queries to do the following:

  1. Display the grade for each student, based on the marks relation.
  2. Find the number of students with each grade.
  1. Display the grade for each student, based on the marks relation.
SELECT ID, 
    CASE
        WHEN score < 40 THEN 'F'
        WHEN score < 60 THEN 'C'
        WHEN score < 80 THEN 'B'
        ELSE 'A' 
    END
FROM marks
  1. Find the number of students with each grade.
WITH grades(ID,grade) AS (
    SELECT ID, 
        CASE
            WHEN score < 40 THEN 'F'
            WHEN score < 60 THEN 'C'
            WHEN score < 80 THEN 'B'
            ELSE 'A' 
        END
    FROM marks
) 
SELECT grade, COUNT(ID)
FROM grades
GROUP BY grade

As an alternative, the WITH clause can be removed, and instead the definition of grades can be made a subquery of the main query.