What is the Query for this

ramk_1978 · December 19th, 2004, 10:49 AM

Hi,

my question is as follows:

Student Table:

Student Id Student Name
S1 Ram
S2 Kumar

Subject Table:
Subject Id Subject Name
I1 Maths
I2 Physics

Marks Table:
Subject Id Student Id Marks
I1 S1 100
I1 S2 50
I2 S1 Null
I2 S2 ---
I2 S1 45

Question is:
1. Query to display Student Name,Subject Name, Marks and if Marks is null indicate it âABSENTâ on display orderby Student Name and Subject Name.

2. Query to display Subject Name, Number of Students Passed or Failed considering pass marks as >=50 and considering other than this as Fail.

Expecting for reply.

A.D.Ramkumar

jemacc · December 19th, 2004, 03:32 PM

May want to experiment with case, for example:

Code:

SELECT a.studentName,b.subjectname, c.marks,
   Type = 
  CASE 
    WHEN (c.marks) = Null THEN 'Absent'
    WHEN (c.marks) = '---'THEN 'Absent'
  END
FROM marks c JOIN students a ON c.studentID= a.StudentID

Jaime E. Maccou

Jonax · December 19th, 2004, 04:30 PM

1.

Code:

-- I defined the field Marks as Integer, that's the reason for the CAST...
SELECT StudentName, SubjectName, ISNULL(CAST(Marks AS VARCHAR(6)),'Absent') AS Marks
FROM tbStudent INNER JOIN tbMarks
ON tbStudent.StudentId = tbMarks.StudentId
INNER JOIN tbSubject ON tbMarks.SubjectId = tbSubject.SubjectId

2.

Code:

SELECT SubjectName, SUM(Passed) AS Passed, SUM(Failed) AS Failed
FROM tbSubject INNER JOIN (
/*
This query (/derived table) get's the maximum mark achieved by a student in a given subject
and introduces the fields Passed and Failed...
*/
SELECT 
StudentId, 
SubjectId, 
CASE 
    WHEN ISNULL(MAX(Marks), 0) >= 50 THEN 1 
    ELSE 0
END
AS Passed, 
CASE 
    WHEN ISNULL(MAX(Marks), 0) < 50 THEN 1 
    ELSE 0
END
AS Failed

FROM tbMarks
GROUP BY StudentId, SubjectId

) AS tbDerived
ON tbSubject.SubjectId = tbDerived.SubjectId
GROUP BY tbSubject.SubjectName