Project ForgeA relational database contains two tables Student(Roll_no, Student_name) with 5 rows (rolls 1-5) and Performance(Roll_no, Subject_code, Marks) with 6 rows, of which 5 rows have Marks > 84 (only the roll-3 row has Marks=80, which does not qualify). The primary key of Student is Roll_no; the primary key of Performance is (Roll_no, Subject_code). Consider the SQL query:
SELECT S.Student_name, sum(P.Marks) FROM Student S, Performance P WHERE P.Marks > 84 GROUP BY S.Student_name;
The number of rows returned by the above SQL query is ____.
Explanation
The WHERE clause filters only Performance.Marks > 84 (5 qualifying rows) — critically, it contains NO join condition linking Student and Performance on Roll_no, so this query computes a full CARTESIAN PRODUCT between all 5 Student rows and the 5 qualifying Performance rows (25 combinations), not a proper join. GROUP BY S.Student_name then collapses these 25 combinations by the 5 distinct student names (every student appears exactly once per qualifying Performance row, i.e. 5 times each) — yielding exactly 5 groups, one per student.
Source: GATE CS 2019 (Set-2) (2019)
This question is part of a 15-day revision cycle — sign in and Project Forge will bring it back the day before you'd forget it.