May 27, 2009 at 2:45 pm
I have two tables, one where student grade is being recorded by the faculty and other, where marks denomination is being provided, means whenever student get the marks and when I run the T-SQL, by mix-matching the both student grade and marks denomination, it will show the student GPA.:
--StudentGrade
if object_id('dbo.StudentGrade') is not null
drop table dbo.StudentGrade;
create table dbo.StudentGrade(
StudentGradeID smallint identity not null,
Student varchar(10) not null,
Course varchar(10) not null,
Credit tinyint not null,
GradeNo tinyint not null,
Remark varchar(10) null,
constraint PK_StudentGrade primary key(
StudentGradeID
)
);
insert into dbo.StudentGrade(
Student,
Course,
Credit,
GradeNo,
Remark
)
select 'Steve', 'ASM01', 3, 50, null union all
select 'Steve', 'ASM01', 3, 43, null union all
select 'Bob', 'ASM01', 3, 0, null union all
select 'Bob', 'OB01', 3, 23, null union all
select 'Bob', 'OB01', 3, 59, null union all
select 'Bob', 'ASM01', 3, 100, null union all
select 'Andy', 'OB01', 3, 0, null union all
select 'Andy', 'ASM01', 3, 50, null union all
select 'Andy', 'ASM01', 3, 10, null union all
select 'Andy', 'OB01', 3, 70, null union all
select 'Andy', 'OB01', 3, 0, 'ABS';
--GradeRate
if object_id('dbo.GradeRate') is not null
drop table dbo.GradeRate;
create table dbo.GradeRate(
GradeRateID tinyint identity not null,
LowGradeNo tinyint not null,
HighGradeNo tinyint not null,
Grade varchar(2) not null,
Points tinyint not null,
constraint PK_GradeRate primary key(
GradeRateID
),
constraint AK_GradeRate_GradeRange unique(
LowGradeNo,
HighGradeNo
),
constraint AK_GradeRate_Points unique(
Points
)
);
insert into dbo.GradeRate(
LowGradeNo,
HighGradeNo,
Grade,
Points
)
select 0, 34, 'F', 0 union all
select 35, 42, 'D', 1 union all
select 43, 50, 'D+', 2 union all
select 51, 58, 'C', 3 union all
select 59, 66, 'C+', 4 union all
select 67, 74, 'B', 5 union all
select 75, 82, 'B+', 6 union all
select 83, 90, 'A', 7 union all
select 91, 100, 'A+', 8;
--T-SQL
SELECTsg.Student,
sg.Course,
sg.Credit,
sg.IsAbsent,
sg.GradeNo,
CASE sg.IsAbsent
WHEN 1 THEN 'ABS'
ELSE COALESCE(gr.Grade, 'X')
END AS Grade,
CASE sg.IsAbsent
WHEN 1 THEN 0
ELSE COALESCE(gr.Points, 0)
END AS Points,
CASE sg.IsAbsent
WHEN 1 THEN 0
ELSE COALESCE(gr.Points, 0) * sg.Credit
END AS Quality
FROM(
SELECTStudent,
Course,
Credit,
MIN(CASE Remark
WHEN 'ABS' THEN 1
ELSE 0
END) AS IsAbsent,
AVG(1.0E * GradeNo) AS GradeNo
FROMdbo.StudentGrade
GROUP BYStudent,
Course,
Credit
) AS sg
LEFT JOINdbo.GradeRate AS gr ON gr.LowGradeNo <= osg.GradeNo
AND gr.HighGradeNo >= osg.GradeNo
ORDER BYsg.Student,
sg.Course
I had did little modification in GradeRate table. I had included Course column, so that I can assign my own high and low grades for each and every courses, which will be different according to courses. For e.g.
ASM01 course may have following graderate:
-----------------------------------------------------------------
GradeRateID | Course | LowGradeNo | HighGradeNo | Grade | Points
-----------------------------------------------------------------
1 | ASM01 | 0 | 50 | D | 0
2 | ASM01 | 51 | 70 | C | 1
3 | ASM01 | 71 | 85 | B | 2
4 | ASM01 | 86 | 100 | A | 4
-----------------------------------------------------------------
OB01 course may have following graderate:
-----------------------------------------------------------------
GradeRateID | Course | LowGradeNo | HighGradeNo | Grade | Points
-----------------------------------------------------------------
5 | OB01 | 0 | 50 | D | 0
6 | OB01 | 51 | 70 | C | 1
7 | OB01 | 71 | 85 | B | 2
8 | OB01 | 86 | 100 | A | 4
-----------------------------------------------------------------
Once, this marks-denomination will me set according to the courses, now studentmarks will be calculated according to course marks denomination.
My problem is I am not getting, how should I modify T-SQL according to this new changes in the table.
Please help me, PLEASE!
May 27, 2009 at 6:28 pm
First, I want to thank you for posting the DDL and DML to setup the tables.
How does this code work for you?
--StudentGrade
if object_id('tempdb..#StudentGrade') is not null drop table #StudentGrade;
CREATE TABLE #StudentGrade(
StudentGradeID smallint identity not null,
Student varchar(10) not null,
Course varchar(10) not null,
Credit tinyint not null,
GradeNo tinyint not null,
Remark varchar(10) null,
constraint PK_StudentGrade primary key(StudentGradeID));
insert into #StudentGrade
(Student,Course,Credit,GradeNo,Remark)
select 'Steve', 'ASM01', 3, 75, NULL UNION ALL
select 'Steve', 'ASM01', 3, 43, NULL UNION ALL
select 'Bob', 'ASM01', 3, 82, NULL UNION ALL
select 'Bob', 'OB01', 3, 23, NULL UNION ALL
select 'Bob', 'OB01', 3, 59, NULL UNION ALL
select 'Bob', 'ASM01', 3, 100, NULL UNION ALL
select 'Andy', 'OB01', 3, 0, NULL UNION ALL
select 'Andy', 'ASM01', 3, 50, NULL UNION ALL
select 'Andy', 'ASM01', 3, 10, NULL UNION ALL
select 'Andy', 'OB01', 3, 70, NULL UNION ALL
select 'Andy', 'OB01', 3, 0, 'ABS';
--GradeRate
if object_id('tempdb..#GradeRate') is not null drop table #GradeRate;
CREATE TABLE #GradeRate(
GradeRateID tinyint identity not null,
Course varchar(10) not null, -- new column
LowGradeNo tinyint not null,
HighGradeNo tinyint not null,
Grade varchar(2) not null,
Points tinyint not null,
constraint PK_GradeRate primary key(GradeRateID),
constraint AK_GradeRate_GradeRange unique(Course,LowGradeNo,HighGradeNo),
constraint AK_GradeRate_Points unique(Course,Points));
insert into #GradeRate
(Course, LowGradeNo, HighGradeNo, Grade, Points)
select 'ASM01', 0, 50, 'D', 0 UNION
select 'ASM01', 51, 70, 'C', 1 UNION
select 'ASM01', 71, 85, 'B', 2 UNION
select 'ASM01', 86, 100, 'A', 4 UNION
select 'OB01', 0, 50, 'D', 0 UNION
select 'OB01', 51, 70, 'C', 1 UNION
select 'OB01', 71, 85, 'B', 2 UNION
select 'OB01', 86, 100, 'A', 4
--T-SQL
SELECT
sg.Student,
sg.Course,
sg.Credit,
sg.IsAbsent,
sg.GradeNo,
CASE sg.IsAbsent
WHEN 1 THEN 'ABS'
ELSE COALESCE(gr.Grade, 'X')
END AS Grade,
gr.Points,
CASE sg.IsAbsent
WHEN 1 THEN 0
ELSE COALESCE(gr.Points, 0) * sg.Credit
END AS Quality
FROM
(SELECT
Student,
Course,
Credit,
MIN(CASE Remark WHEN 'ABS' THEN 1 ELSE 0 END) AS IsAbsent,
AVG(1.0E * GradeNo) AS GradeNo
FROM #StudentGrade
GROUP BY Student, Course, Credit) AS sg
LEFT JOIN #GradeRate AS gr ON gr.Course = sg.Course
AND sg.GradeNo between gr.LowGradeNo AND gr.HighGradeNo
ORDER BY sg.Student, sg.Course
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
May 28, 2009 at 11:08 am
Thanks for giving me the solution.
Actually, am working in my college project, where am creating an automate system for managing all the college activities like inserting student details, calculating students marks, etc.
This code snippet is the part of marks calculation.
Sir, I have another query. In the T-SQL, you will see I had included following code:
CASE sg.IsAbsent
WHEN 1 THEN 'ABS'
ELSE COALESCE(gr.Grade, 'X')
END AS Grade
Here, ABS stands for 'Absent' means if student is absent in more than one exam in a particular course, his GRADE will be shown as ABS, now I also want to include DN also which means Denial of Grade.
I included this in the following part, but not working:
CASE sg.IsAbsent
WHEN 1 THEN 'ABS'
WHEN 1 THEN 'DN'
ELSE COALESCE(gr.Grade, 'X')
END AS Grade
Please help just once more!
May 28, 2009 at 5:35 pm
mail2payan (5/28/2009)
Thanks for giving me the solution.Actually, am working in my college project
I sure wish you had mentioned this before. What kind of credit do I get for doing your work?
CASE sg.IsAbsentWHEN 1 THEN 'ABS'
WHEN 1 THEN 'DN'
ELSE COALESCE(gr.Grade, 'X')
END AS Grade
Please help just once more!
Let me guess.. you always get 'ABS', and never 'DN'?
Maybe because of your faulty logic... you'll never reach the condition that gives the 'DN'. Look the "CASE" statement up in BOL.
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply