Students Short of 75% Attendance in a Course — SQL Problem
The university debars a student from a course's end-semester exam when their attendance in that course is below 75%.
- Difficulty: Easy
- Topics: Basics
- Dialect: MySQL
- Problem: #123
Problem statement
The university debars a student from a course's end-semester exam when their attendance in that course is below 75%. The exam cell has one row per student and course with the classes held and the classes the student attended.
Return every student-course pair whose attendance is strictly below 75%, with the columns roll_no, course_code and attendance_pct — the attended classes as a percentage of the classes held, rounded to 2 decimal places. Exactly 75% is not a shortage. Order the rows by roll_no, then by course_code.
Tables
Table: Attendance
| Column | Type |
|---|---|
| roll_no | int |
| course_code | varchar |
| classes_held | int |
| classes_attended | int |
Primary key: roll_no, course_code.
One row per student per registered course this semester; classes_held is always at least 1 and classes_attended never exceeds it.
Examples
Example 1
Attendance
| roll_no | course_code | classes_held | classes_attended |
|---|---|---|---|
| 2101 | CS301 | 40 | 30 |
| 2101 | MA201 | 42 | 29 |
| 2102 | CS301 | 40 | 38 |
| 2102 | EC210 | 36 | 20 |
| 2103 | MA201 | 42 | 32 |
| 2103 | CS301 | 40 | 29 |
| 2104 | HS101 | 30 | 30 |
Output
| roll_no | course_code | attendance_pct |
|---|---|---|
| 2101 | MA201 | 69.05 |
| 2102 | EC210 | 55.56 |
| 2103 | CS301 | 72.5 |
How to solve Students Short of 75% Attendance in a Course
Each row of Attendance is one student in one course, so the answer is a filter plus a computed column. A row is a shortage when classes_attended / classes_held < 0.75. Multiplying both sides by classes_held (always positive) gives the integer-only form classes_attended * 100 < classes_held * 75, which never touches a fraction and so cannot be affected by any rounding.
The percentage itself is classes_attended * 100 / classes_held. In MySQL / is decimal division, so 29 of 42 is 69.0476…, and ROUND(…, 2) gives 69.05. Round only the value you show — filtering on a rounded percentage could turn 74.996% into 75.00% and wrongly clear a student.
Exactly 75% (30 of 40) is allowed, which is why the comparison is strict. The statement fixes the order, so finish with ORDER BY roll_no, course_code. The query is one scan of the table.
Reference solution (MySQL)
SELECT roll_no, course_code,
ROUND(classes_attended * 100 / classes_held, 2) AS attendance_pct
FROM Attendance
WHERE classes_attended * 100 < classes_held * 75
ORDER BY roll_no, course_codeAnother way
SELECT roll_no, course_code, ROUND(100.0 * classes_attended / classes_held, 2) AS attendance_pct FROM Attendance WHERE classes_attended / classes_held < 0.75 ORDER BY roll_no ASC, course_code ASCAnother way
SELECT roll_no, course_code, attendance_pct FROM (SELECT roll_no, course_code, ROUND(classes_attended * 100 / classes_held, 2) AS attendance_pct, classes_attended * 4 - classes_held * 3 AS margin FROM Attendance) t WHERE margin < 0 ORDER BY roll_no, course_code← Overlapping Flash Sales on the Same Product · Courses Nobody Registered For in the Odd Semester →