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 $\le$ score $<$ 60, grade B if 60 $\le$ score $<$ 80, and grade A if 80 $\le$ score. Write SQL queries to do the following:

a. Display the grade for each student, based on the marks relation.
b. Find the number of students with each grade.

---

a. 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

b. 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.