Query for students passed or not passed in specific Subjects [closed]

I have a Table with Column Headings:

• ID Student_Name
• Roll_Number
• Subject1MarksObtained
• Subject1TotalMarks
• Subject2MarksObtained
• Subject2TotalMarks
• Subject3MarksObtained
• Subject3TotalMarks
• Subject4MarksObtained
• Subject4TotalMarks

I want to write a query to output the results for individual student who have pass at least three of the subjects.

Answer

Without seeing the data, lets make some assumptions:

1. A pass is awarded for a subject if the marks obtained for that subject are equal to or more than 50% of the total marks available for that subject.
2. The name of the table is called Enrollment

To return a list of students who have passed at least 3 subjects we can use a query similar to the following:

This solution uses CASE to evaluate a 1 for a pass and a 0 for fail for each subject, then we sum those results and only return rows that have a score of 3 or more.

SELECT ID, Student_Name, Roll_Number
FROM Enrollment
WHERE 
  ( CASE WHEN (Subject1MarksObtained / Subject1TotalMarks) >= 0.5 THEN 1 ELSE 0 END
  + CASE WHEN (Subject2MarksObtained / Subject2TotalMarks) >= 0.5 THEN 1 ELSE 0 END
  + CASE WHEN (Subject3MarksObtained / Subject3TotalMarks) >= 0.5 THEN 1 ELSE 0 END
  + CASE WHEN (Subject4MarksObtained / Subject4TotalMarks) >= 0.5 THEN 1 ELSE 0 END
  ) >= 3  

There are different way to approach this, but this query is simple to read and gets the job done.

If you are querying an access table, then CASE WHEN is not supported but you can use IIF or SWITCH to achieve the same results:

SELECT ID, Student_Name, Roll_Number
FROM Enrollment
WHERE 
  ( IIF( (Subject1MarksObtained / Subject1TotalMarks) >= 0.5, 1, 0)
  + IIF( (Subject2MarksObtained / Subject2TotalMarks) >= 0.5, 1, 0)
  + IIF( (Subject3MarksObtained / Subject3TotalMarks) >= 0.5, 1, 0)
  + IIF( (Subject4MarksObtained / Subject4TotalMarks) >= 0.5, 1, 0)
  ) >= 3