Querying Student Pass Status in SQL

In this article, we’ll explore a problem that involves querying student pass status in SQL. We have a table named Enrollment with columns for student ID, roll number, and marks obtained in each subject. The goal is to write a query that outputs the results for individual students who have passed at least three subjects.

Understanding Pass Status Criteria

To approach this problem, we need to define what constitutes a pass status in SQL. In this case, we’ll assume that a student passes a subject if they obtain marks equal to or more than 50% of the total marks available for that subject.

SQL Query Approach

The query provided in the question uses the CASE statement to evaluate a 1 for a pass and a 0 for fail for each subject. We’ll break down this approach and explore alternative methods using SQL functions like IIF() or SWITCH.

CASE Statement Method

The original query uses the following syntax:

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;

Here’s how this query works:

For each subject, we use the CASE statement to evaluate whether the marks obtained are greater than or equal to 50% of the total marks available.
If the condition is true (marks obtained >= 50%), we assign a value of 1; otherwise, we assign 0.
We add up these values for all four subjects and filter the results where the sum is greater than or equal to 3.

IIF() Method

If you’re querying an access database, the CASE statement may not be supported. In this case, you can use the IIF() function to achieve similar 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;

The IIF() function takes three arguments: a condition, a value to return if the condition is true, and an optional default value. In this case, we use it to replace the logic inside the original CASE statement.

SWITCH() Method

Another alternative for querying access databases is using the SWITCH function:

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

The SWITCH function takes two arguments: a condition and the value to return if the condition is true.

Query Optimization

To optimize the query for performance, consider using indexes on the columns used in the filtering criteria. Additionally, you can use window functions like ROW_NUMBER() or RANK() to simplify the query and improve readability.

Example Use Case

Here’s an example table structure:

+---------+----------+------------+
| ID      | Roll_num | Subject1  |
|          |          | Marks Obtained|
+---------+----------+------------+
| 1       | R123     | 50         |
| 2       | R456     | 60         |
| 3       | R789     | 40         |
+---------+----------+------------+

+---------+----------+------------+
| ID      | Roll_num | Subject2  |
|          |          | Marks Obtained|
+---------+----------+------------+
| 1       | R123     | 50         |
| 2       | R456     | 60         |
| 3       | R789     | 40         |
+---------+----------+------------+

+---------+----------+------------+
| ID      | Roll_num | Subject3  |
|          |          | Marks Obtained|
+---------+----------+------------+
| 1       | R123     | 50         |
| 2       | R456     | 60         |
| 3       | R789     | 40         |
+---------+----------+------------+

+---------+----------+------------+
| ID      | Roll_num | Subject4  |
|          |          | Marks Obtained|
+---------+----------+------------+
| 1       | R123     | 50         |
| 2       | R456     | 60         |
| 3       | R789     | 40         |
+---------+----------+------------+

Running the Query

Using any of the query methods, you can run the following query to retrieve students who have passed at least three subjects:

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;

This query will return the desired results for each student, including their ID, roll number, and marks obtained in each subject.

Conclusion

In this article, we’ve explored a problem that involves querying student pass status in SQL. We discussed three methods to solve this problem using the CASE statement, IIF() function, and SWITCH() function. By understanding the criteria for pass status and optimizing the query for performance, you can efficiently retrieve students who have passed at least three subjects.

Last modified on 2024-07-30