Student Analysis HackerRank SQL Solution (SQL Basic Certification)

Student Analysis is one of the five questions in the HackerRank SQL (Basic) certification test. It is the easiest of the five if you read the condition carefully.

What the question asks

A school wants a list of weak students so it can run extra classes for them. Each student has marks in three subjects. Return the roll number and name of every student whose three marks add up to less than 100.

“Less than 100” is strict. A student with exactly 100 is not on the list.

Tables

Both tables use roll_number as the primary key, so each student has at most one row of marks.

Table	Column	What it holds
`student_information`	`roll_number`	Student’s roll number (primary key)
`student_information`	`name`	Student’s name
`examination_marks`	`roll_number`	Roll number (primary key, matches the student)
`examination_marks`	`subject_one`, `subject_two`, `subject_three`	Marks in each subject

Solution

student-analysis.sqlMySQL

SELECT s.roll_number, s.name
FROM student_information s
JOIN examination_marks e ON e.roll_number = s.roll_number
WHERE e.subject_one + e.subject_two + e.subject_three < 100;

How it works

The names are in student_information and the marks are in examination_marks, so we join the two on roll_number.
Each student has one row of marks with three columns. Adding the three columns gives the total for that row.
The WHERE clause keeps only the rows where that total is below 100.

You don’t need GROUP BY or SUM() here. SUM() adds values down a column across many rows. Our three marks sit side by side in one row, so plain + is enough.

The version in my GitHub repo uses GROUP BY b.roll_number HAVING SUM(...) < 100. It also passes and gives the same result, because there is only one marks row per student. The version above is shorter and easier to explain in an interview.

Mistakes that fail the hidden tests

Using <= 100. A student with exactly 100 marks gets included. In my test data that added a third student who should not be there.
Checking each subject separately. subject_one < 100 AND subject_two < 100 ... is a different question. The rule is on the total.
Returning extra columns. The output should be roll number and name only. If you add the total as a third column for debugging, remove it before you submit.
Joining on the wrong column. Join on roll_number in both tables. Joining on name breaks as soon as two students share a name.

Try it on sample data

These are my own rows, not HackerRank’s. They include a total of exactly 100, a student with no marks row and a missing (NULL) mark.

student_information

roll_number	name
101	Aarav
102	Diya
103	Kabir
104	Meera
105	Rohan
106	Sana

examination_marks

roll_number	subject_one	subject_two	subject_three	Total
101	40	30	29	99
102	40	30	30	100
103	70	80	70	220
104	10	20	15	45
106	20	NULL	10	NULL

The Total column is only there to help you check. It is not in the real table.

Output

roll_number	name
101	Aarav
104	Meera

Diya (exactly 100) and Kabir (220) are out. Rohan has no marks row, so the join drops him. Sana has a NULL mark, and anything plus NULL is NULL in SQL, so her row fails the condition too. I ran this in MySQL and in SQLite and got the same two rows.