Qn.
A school recently conducted its annual examination and wishes to know the list of academically low performing students to organize extra classes for them. Write a query to return the roll number and names of students who have a total of less than 100 marks including all 3 subjects.
There are two tables: student information and examination_marks. Their primary keys are roll_number.
Solution:
SELECT a.roll_number,a.name
FROM student_information a
INNER JOIN examination_marks b
ON a.roll_number = b.roll_number
GROUP BY b.roll_number
HAVING SUM(b.subject_one + b.subject_two + b.subject_three) < 100;
Explanation:
1. SELECT a.roll_number, a.name: This part selects the roll number and name columns from the student_information table (aliased as a).
2. FROM student_information a INNER JOIN examination_marks b ON a.roll_number = b.roll_number: This line specifies the tables involved in the query and how they are joined. It performs an inner join between the student_information table (aliased as a) and the examination_marks table (aliased as b) based on the common roll_number column.
3. GROUP BY b.roll_number: This part groups the result set by the roll_number column from the examination_marks table (b). This means that all rows with the same roll_number will be aggregated together.
4. HAVING SUM(b.subject_one + b.subject_two + b.subject_three) < 100: This line filters the grouped results using the HAVING clause. It calculates the sum of marks obtained in all three subjects (subject_one, subject_two, and subject_three) for each group (each student), and then checks if this sum is less than 100. If it is, the group (student) is included in the final result set.
Hacker Rank Basic Skill Student Analysis Problem Solution
