How to build this Query? - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

How to build this Query?

Author

Topic

daipayan
Posting Yak Master

181 Posts

Posted - 2009-02-19 : 15:59:59

Am now in great problem, I don't know how to start preparing the following stuff:
I have a guidelines for calculating marks:

---------------------------------------
Points	Scores	              Grade
---------------------------------------
8	91 to 100	        A+
7	83 to 90	        A
6	75 to 82	        B+
5	67 to 74	        B
4	59 to 66	        C+
3	51 to 58	        C
2	43 to 50	        D+
1	35 to 42	        D
0	0 to 34	                F		
0	Deniel of Grade	        DN 
0	Absent	                ABS
0	With Draw From Course 	WFC
---------------------------------------

Scores of student are being stored in student_marks table in marks column
on the basis of marks, the grade and point will generate, for e.g.
if marks is 89, then grade is A and point is 7.

Now on the basis of above table, marks for each course in each semester will be shown as follows:

------------------------------------------------
Course	Credit	Grade	Points	*Quality Points
------------------------------------------------
C-1	3	A+	8.00	     24.00
C-2	3	B	5.00	     15.00
C-3	3	ABS	0.00	      0.00
C-4	1.5	WFC	0.00	      0.00
C-5	1.5	DN	0.00	      0.00
C-6	AC	A+	 -	       -
------------------------------------------------
*Total	15			     39.00
------------------------------------------------

*Quality Points = Credit x Points
*Audit Course ( AC) are not included in GPA calculation, while Absent ABS and Withdraw From Course WFC and Deniel of Grade (DN)
*Credit is being given in Course_details table in credit column
On the basis of above data,

Say, First Semester GPA = (Total Quality Points/Total Credit)
i.e First Semester GPA = 39/15 = 2.60
and overall GPA = [Total Quality Points (39.00+52.5)/Total Credit Hours (15+15)]
i.e overall GPA = (39.00+52.5)/(15+15) = 3.05
here 52.5 is second semester total quality points.

And last rule is:
min 4.00 GPA is the pass mark at the end of First Year, where First year consist of 3 semesters
and min 4.50 at the end of Second Year, where Second year consist of last 3 semesters
If the GPA falls below, the student stands Disqualified and this shud b shown in somewhere.

OR

---------------------------
F	D		D+
---------------------------
3	0		0
2	1	OR 	1
1	2		0
1	1		2
0	1		3
0	2		2
0	4		0
0	0		5
---------------------------

This F,D,D+ are the grades
e.g. if a student get 2F or 1D or 1D+, he is disqualified from the course.

This is my WHOLE DATA and I don't know how to start, I just had to put the scores in student_marks table and by following the above rules, I have to view the results, I don't need to store the data, just need to view.

I know, am asking lot from you peoples, but please help me, atleast guide me, how to start...please

Daipayan

sakets_2000
Master Smack Fu Yak Hacker

1472 Posts

Posted - 2009-02-20 : 05:28:28

Have you given this a try ? I'm sure we can help you with your code. Also, You're missing a lot of information here, provided tables etc etc.

daipayan
Posting Yak Master

181 Posts

Posted - 2009-02-20 : 06:43:27

Thanks for replying.
Am first trying this, for the grading part:

SELECT dbo.student_marks.student_ID, dbo.student_marks.course_code, dbo.course_details.course_credit, SUM(dbo.student_marks.marks), 
Grade = 
	CASE
	WHEN (SELECT SUM(dbo.student_marks.marks) from  dbo.student_marks GROUP BY dbo.student_marks.student_ID, dbo.student_marks.course_code) BETWEEN 100 and 91 then 'A+'
	WHEN (SELECT SUM(dbo.student_marks.marks) from  dbo.student_marks GROUP BY dbo.student_marks.student_ID, dbo.student_marks.course_code) BETWEEN 90 and 83 then 'A'
	WHEN (SELECT SUM(dbo.student_marks.marks) from  dbo.student_marks GROUP BY dbo.student_marks.student_ID, dbo.student_marks.course_code) BETWEEN 75 and 82 then 'B+'
	WHEN (SELECT SUM(dbo.student_marks.marks) from  dbo.student_marks GROUP BY dbo.student_marks.student_ID, dbo.student_marks.course_code) BETWEEN 67 and 74 then 'B'
	WHEN (SELECT SUM(dbo.student_marks.marks) from  dbo.student_marks GROUP BY dbo.student_marks.student_ID, dbo.student_marks.course_code) BETWEEN 59 and 66 then 'C+'
	WHEN (SELECT SUM(dbo.student_marks.marks) from  dbo.student_marks GROUP BY dbo.student_marks.student_ID, dbo.student_marks.course_code) BETWEEN 51 and 58 then 'C'
	WHEN (SELECT SUM(dbo.student_marks.marks) from  dbo.student_marks GROUP BY dbo.student_marks.student_ID, dbo.student_marks.course_code) BETWEEN 43 and 50 then 'D+'
	WHEN (SELECT SUM(dbo.student_marks.marks) from  dbo.student_marks GROUP BY dbo.student_marks.student_ID, dbo.student_marks.course_code) BETWEEN 35 and 42 then 'D'
	WHEN 'Deniel of Grade' then 'DN'
	WHEN 'Absent' then 'ABS'
	WHEN 'With Draw From Course' then 'WFC'
	WHEN 'Audit Course' then 'A+'
	ELSE 'F'
	END
FROM  dbo.student_marks INNER JOIN dbo.course_details ON dbo.student_marks.course_code = dbo.course_details.course_code
GROUP BY dbo.student_marks.student_ID, dbo.student_marks.course_code, dbo.course_details.course_credit

But am getting the following error:

Server: Msg 8624, Level 16, State 25, Line 1
Internal SQL Server error.

Daipayan

sakets_2000
Master Smack Fu Yak Hacker

1472 Posts

Posted - 2009-02-20 : 06:48:44

Do you want to grade students basis the sum of all scores in different courses, or course-wise ?

daipayan
Posting Yak Master

181 Posts

Posted - 2009-02-20 : 06:58:26

quote:
Originally posted by sakets_2000

Do you want to grade students basis the sum of all scores in different courses, or course-wise ?

Yes, I want to grade them according to the courses as well as group by student ID
as
A particular divided into sub-category, e.g.
---------------------------------------
COURSE_ID MARKS_TYPE MARKS STUDENT_ID
---------------------------------------
ASM001 Mid-term 23 3015
ASM001 End-term 31 3015
OB001 Mid-Term 19 3001
ASM001 Project 37 3002

Daipayan

sakets_2000
Master Smack Fu Yak Hacker

1472 Posts

Posted - 2009-02-20 : 07:14:52

quote:
Originally posted by daipayan

quote:
Originally posted by sakets_2000

Do you want to grade students basis the sum of all scores in different courses, or course-wise ?

Yes, I want to grade them according to the courses as well as group by student ID
as
A particular divided into sub-category, e.g.
---------------------------------------
COURSE_ID MARKS_TYPE MARKS STUDENT_ID
---------------------------------------
ASM001 Mid-term 23 3015
ASM001 End-term 31 3015
OB001 Mid-Term 19 3001
ASM001 Project 37 3002

Daipayan

I didn't understand,
This should give you a report for all students, and their respective grades for all courses they appeared for.

SELECT 
	dbo.student_marks.student_ID, 
	dbo.student_marks.course_code, 
	dbo.course_details.course_credit, 
	dbo.student_marks.marks, 
	Grade = 
		CASE
		WHEN dbo.student_marks.marks BETWEEN 100 and 91 then 'A+'
		WHEN dbo.student_marks.marks BETWEEN 90 and 83 then 'A'
		WHEN dbo.student_marks.marks BETWEEN 75 and 82 then 'B+'
		WHEN dbo.student_marks.marks BETWEEN 67 and 74 then 'B'
		WHEN dbo.student_marks.marks BETWEEN 59 and 66 then 'C+'
		WHEN dbo.student_marks.marks BETWEEN 51 and 58 then 'C'
		WHEN dbo.student_marks.marks BETWEEN 43 and 50 then 'D+'
		WHEN dbo.student_marks.marks BETWEEN 35 and 42 then 'D'
		WHEN dbo.student_marks.marks ='Deniel of Grade' then 'DN'
		WHEN dbo.student_marks.marks ='Absent' then 'ABS'
		WHEN dbo.student_marks.marks ='With Draw From Course' then 'WFC'
		WHEN dbo.student_marks.marks ='Audit Course' then 'A+'
		ELSE 'F'
		END
FROM  
	dbo.student_marks 
	INNER JOIN dbo.course_details ON dbo.student_marks.course_code = dbo.course_details.course_code
ORDER BY 1,2,3,4,5

daipayan
Posting Yak Master

181 Posts

Posted - 2009-02-20 : 08:33:05

Sir, let me explain you properly, what I want. I had a student_marks table and the columns in the table are:

---------------------------------------
COURSE_ID MARKS_TYPE MARKS STUDENT_ID
---------------------------------------
ASM001     Mid-term   23     3015
ASM001     End-term   31     3015
OB001      Mid-Term   19     3001
ASM001     Project    37     3002
---------------------------------------

After excuting the CASE code, it shud appear in following way:

-----------------------------------
COURSE_ID STUDENT_ID Total* Grade
-----------------------------------
ASM001     3015       54     C
ASM001     3002       37     D
OB001      3001       19     F
-----------------------------------

here *Total of ASM001 = 23 + 31 = 54

Hope, now I can make you understand!

Daipayan

sakets_2000
Master Smack Fu Yak Hacker

1472 Posts

Posted - 2009-02-20 : 08:45:59

What if a student was 'Absent' in mid-term and scored 100 in end-term ? What would you want to grade him ? A+ or 'ABS' ???

sakets_2000
Master Smack Fu Yak Hacker

1472 Posts

Posted - 2009-02-20 : 09:04:27

quote:
Originally posted by daipayan

quote:
Originally posted by sakets_2000

What if a student was 'Absent' in mid-term and scored 100 in end-term ? What would you want to grade him ? A+ or 'ABS' ???

Yes, if student is absent then in marks column, the INPUT will be 'Absent' and Grade will be 'ABS'
and if he will score 100, grade will be 'A+'
please check my guideline that I given in my first post

Daipayan

Thats not my question.

daipayan
Posting Yak Master

181 Posts

Posted - 2009-02-20 : 09:06:05

quote:
Originally posted by sakets_2000

[quote]Originally posted by daipayan

[quote]Originally posted by sakets_2000

What if a student was 'Absent' in mid-term and scored 100 in end-term ? What would you want to grade him ? A+ or 'ABS' ???

Sorry for wrong reply......

Actually scores is calculated overall, it don't consider individually mid-term or end-term, if he/she is absent in any term, the will be ZERO automatically.
please check my guideline that I given in my first post

Daipayan

Daipayan

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources