Student Advisor

Student Advisor is one of the five questions in the HackerRank SQL (Basic) certification test. It looks like a copy of the other join questions, but it has two salary limits, one for each gender, and that is where people slip.

At least one popular GitHub repo for this test has the Merit Rewards query saved in its Student Advisor file. That query reads the employee and bonus tables, which this question doesn’t have, so it can’t pass. The query below is the correct one.

What the question asks

A university gives every student a faculty member as an advisor. Return the roll number and name of each student whose advisor is either:

• male, with a salary above 15,000, or
• female, with a salary above 20,000.

Both limits are strict. A male advisor on exactly 15,000 or a female advisor on exactly 20,000 does not count.

Tables

Solution

SELECT s.roll_number, s.name
FROM student_information s
JOIN faculty_information f ON f.employee_ID = s.advisor
WHERE (f.gender = 'M' AND f.salary > 15000)
   OR (f.gender = 'F' AND f.salary > 20000);

How it works

1. The advisor column in student_information holds the advisor’s employee_ID. Joining on that brings the advisor’s gender and salary onto the student’s row.
2. Each pair of brackets is one complete rule: a gender and the salary limit for that gender.
3. OR between the two brackets keeps a student if either rule is true.

Strictly speaking, SQL already runs AND before OR, so the query gives the same result without the brackets. I keep them anyway. They make the two rules obvious to anyone reading it, including you at minute 25 of the test.

Mistakes that fail the hidden tests

• One salary limit for everyone. WHERE f.salary > 15000 lets in students whose female advisor earns between 15,000 and 20,000. In my test that added two wrong students.
• Using >=. The limits are “more than”, so an advisor on exactly 15,000 (male) or 20,000 (female) must be left out. >= added two wrong students in my test.
• Mixing up the conditions. Something like f.gender = 'M' OR f.gender = 'F' AND f.salary > 20000 keeps every male advisor no matter the salary. Write each gender with its own limit, in its own brackets.
• Joining on the wrong columns. The link is student_information.advisor = faculty_information.employee_ID. Joining roll_number to employee_ID runs without an error and returns rubbish.
• Copying the wrong file. As mentioned above, check that the query you’re learning from actually uses student_information and faculty_information.

Try it on sample data

These are my own rows. Each advisor tests one edge: just above a limit, exactly on a limit, or between the two limits.

faculty_information

student_information

Output

Farhan and Harsh share advisor 5, and both appear, which is right: the question is about students, not advisors. Isha has no advisor, so the join drops her. I ran this in MySQL and SQLite and got the same three rows.