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.