How to combine values in table

Question

The database has the schema students(name TEXT, score INTEGER), and there is a table called grades:

Grade   MIN_score    MAX_score
A       4            5
B       3            4
C       2            3

I want to select the names of all students and their grades according to the table, and turn A and B to 'pass' in the resulting table.

Below is my partial solution without turning A and B to 'pass' in the resulting table, and I wonder how to achieve that additional function.

SELECT name, grade
FROM students 
LEFT JOIN grades 
ON grade BETWEEN MIN_score and MAX_score;

Answer 1

Don't use between. You'll get duplicates.

select s.name, s.score,
       (case when g.grade in ('A', 'B') then 'Pass' end) as status
from students s join
     grades g
     on s.score > g.MIN_score and s.score <= MAX_score;

You need to be very careful about the join condition so a score of "4" is not treated as both an "A" and a "B" (as between would do).

Answer 2

You need to use case when expression, e.g.:

select case when grade in ('A', 'B') then 'Pass' else '' end

I believe you query should be something like this:

select name, score, case when grade in ('A', 'B') then 'Pass' else '-' end
from students
join grades on score between MIN_score and MAX_score