Adding Columns Via Data Ranges

Adding Columns Via Data Ranges

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.

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.

Share the Post:
Heading photo, Metadata.

What is Metadata?

What is metadata? Well, It’s an odd concept to wrap your head around. Metadata is essentially the secondary layer of data that tracks details about the “regular” data. The regular

XDR solutions

The Benefits of Using XDR Solutions

Cybercriminals constantly adapt their strategies, developing newer, more powerful, and intelligent ways to attack your network. Since security professionals must innovate as well, more conventional endpoint detection solutions have evolved

AI is revolutionizing fraud detection

How AI is Revolutionizing Fraud Detection

Artificial intelligence – commonly known as AI – means a form of technology with multiple uses. As a result, it has become extremely valuable to a number of businesses across

AI innovation

Companies Leading AI Innovation in 2023

Artificial intelligence (AI) has been transforming industries and revolutionizing business operations. AI’s potential to enhance efficiency and productivity has become crucial to many businesses. As we move into 2023, several