Letter grades

Give each student a letter grade: A+ for 80 or more, A for 70 to 79, B for 60 to 69 and F below 60.

What to build

A query that turns marks into letter grades with CASE.

Requirements

• Test the conditions from the highest grade down.
• Give F to anything below 60, with ELSE.
• Sort by marks from highest, then by name.

Tables

Each test fills these tables with its own rows, then runs your SQL:

CREATE TABLE results (
  id INTEGER PRIMARY KEY,
  name TEXT NOT NULL,
  marks INTEGER NOT NULL
);

Output

Columns name, marks and grade, from the highest marks down. Equal marks are in alphabetical order of name. Your result is compared with the expected one, column names included, so name each column exactly as asked (use AS).

Constraints

Marks are whole numbers from 0 to 100.

Example

When a test starts with:

INSERT INTO results VALUES
  (1, 'Rahim', 85),
  (2, 'Karim', 72),
  (3, 'Nadia', 91),
  (4, 'Tania', 64),
  (5, 'Sumon', 48);

your SQL should print:

name   marks  grade
-----  -----  -----
Nadia  91     A+
Rahim  85     A+
Karim  72     A
Tania  64     B
Sumon  48     F
(5 rows)

Why it works

CASE uses the first condition that is true, so checking 80 before 70 before 60 gives each mark exactly one grade.

Examples

CREATE TABLE results (
  id INTEGER PRIMARY KEY,
  name TEXT NOT NULL,
  marks INTEGER NOT NULL
);
INSERT INTO results VALUES
  (1, 'Rahim', 85),
  (2, 'Karim', 72),
  (3, 'Nadia', 91),
  (4, 'Tania', 64),
  (5, 'Sumon', 48);

name   marks  grade
-----  -----  -----
Nadia  91     A+
Rahim  85     A+
Karim  72     A
Tania  64     B
Sumon  48     F
(5 rows)

CREATE TABLE results (
  id INTEGER PRIMARY KEY,
  name TEXT NOT NULL,
  marks INTEGER NOT NULL
);
INSERT INTO results VALUES
  (1, 'Rina', 80),
  (2, 'Imran', 79),
  (3, 'Joya', 70),
  (4, 'Kamal', 69),
  (5, 'Lima', 60),
  (6, 'Mitu', 59);

name   marks  grade
-----  -----  -----
Rina   80     A+
Imran  79     A
Joya   70     A
Kamal  69     B
Lima   60     B
Mitu   59     F
(6 rows)

Each test makes its own tables, runs your SQL on them, and compares what your queries return. Changes never carry over from one test to the next.

Hints

Solution

SELECT name, marks,
  CASE
    WHEN marks >= 80 THEN 'A+'
    WHEN marks >= 70 THEN 'A'
    WHEN marks >= 60 THEN 'B'
    ELSE 'F'
  END AS grade
FROM results
ORDER BY marks DESC, name;

Lesson: CASE, views and set operations →