Students Short of 75% Attendance in a Course

Easy
123.

Students Short of 75% Attendance in a Course

EasyBasics

MySQL

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

ColumnType
roll_noint
course_codevarchar
classes_heldint
classes_attendedint

Primary key (together): 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.

Example

The rows must come back in the order shown.

Attendance

roll_nocourse_codeclasses_heldclasses_attended
2101CS3014030
2101MA2014229
2102CS3014038
2102EC2103620
2103MA2014232
2103CS3014029
2104HS1013030

Output

roll_nocourse_codeattendance_pct
2101MA20169.05
2102EC21055.56
2103CS30172.5
Read the hints, the editorial and the reference solution
-- Write your MySQL query below

Sign in to write, run and submit queries

A free account — no card, no trial clock. You come straight back here.

Run your query to see its result next to the expected one, example by example.

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

ColumnType
roll_noint
course_codevarchar
classes_heldint
classes_attendedint

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_nocourse_codeclasses_heldclasses_attended
2101CS3014030
2101MA2014229
2102CS3014038
2102EC2103620
2103MA2014232
2103CS3014029
2104HS1013030

Output

roll_nocourse_codeattendance_pct
2101MA20169.05
2102EC21055.56
2103CS30172.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_code

Another 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 ASC

Another 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 →