Login | Register   
LinkedIn
Google+
Twitter
RSS Feed
Download our iPhone app
TODAY'S HEADLINES  |   ARTICLE ARCHIVE  |   FORUMS  |   TIP BANK
Browse DevX
Sign up for e-mail newsletters from DevX


Tip of the Day
Language: SQL Server
Expertise: Beginner
Jul 20, 1999

Adding Columns Via Data Ranges

Question:
I have two tables, "grade" and "assignment":
 
TABLE       FIELD
grade       grade (char i.e. A..F)
            low_range 
            high range

i.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)
go
create 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 grade
select * 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 100
THEN "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, ASSIGNMENT
WHERE 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.

DevX Pro
 
Comment and Contribute

 

 

 

 

 


(Maximum characters: 1200). You have 1200 characters left.

 

 

Sitemap
Thanks for your registration, follow us on our social networks to keep up-to-date