Question:
I have two tables, “grade” and “assignment”:
TABLE FIELDgrade grade (char i.e. A..F) low_range high rangei.e. 'A', 80, 100 'B', 60, 80 etc.assignment s_ref assign_no grade (numeric i.e. 78)
I need to add the students’ grades (in character form) to Assignment to create a new view. I tried sub-queries but can’t get them to work.
Answer:
There are a couple of things to set up first to get this to work. First let’s create the tables:
create table grade (grade char(1), low_range int, high_range int)gocreate table assignment (s_ref int, assign_no int, grade int)go
Now let’s fill them up with some sample values. Here are your grade levels:
insert grade values ("A", 80,100)insert grade values ("B", 75,79)insert grade values ("C", 70,74)insert grade values ("D", 65,69)insert grade values ("F", 0,64)
Note that I made these ranges discrete; there is no overlap in the range. Now let’s add some test results for our student refs:
insert assignment values(1,1,83)insert assignment values(1,2,90)insert assignment values(1,3,77)insert assignment values(2,1,65)insert assignment values(2,2,88)insert assignment values(2,3,80)insert assignment values(3,1,50)insert assignment values(3,2,100)insert assignment values(3,3,88)insert assignment values(4,1,77)insert assignment values(4,2,70)insert assignment values(4,3,68)insert assignment values(5,1,80)insert assignment values(5,2,93)insert assignment values(5,3,99)insert assignment values(6,1,80)insert assignment values(6,2,88)insert assignment values(6,3,89)go
Now just check to see that everything you intended made it into the tables:
select * from gradeselect * from assignment
I pretended that we have six students who took three tests each, each with a number grade that needs to be reported as a letter grade. We can do the join to the ranges and change the values like this:
SELECT s_ref, assign_no, assignment.grade, CASE WHEN assignment.grade between 80 and 100THEN "A"WHEN assignment.grade between 75 and 79 THEN "B"WHEN assignment.grade between 70 and 74 THEN "C"WHEN assignment.grade between 65 and 69 THEN "D"WHEN assignment.grade < 64 then "F"END as 'letter grade'FROM GRADE, ASSIGNMENTWHERE assignment.grade >=grade.low_range and assignment.grade <= high_range ORDER BY s_ref, assign_no
Watch out when you structure the WHEN THEN in the CASE statement because you need to ensure that no values fall through the cracks—that you are testing continuous values in the grade ranges.
Hope this helps.
