GATE 2019 CS – Question 61
A relational database contains two tables Student and Performance as shown below:
| Roll_no. | Student_name |
|---|---|
| 1 | Amit |
| 2 | Priya |
| 3 | Vinit |
| 4 | Rohan |
| 5 | Smita |
| Roll_no. | Subject_code | Marks |
|---|---|---|
| 1 | A | 86 |
| 1 | B | 95 |
| 1 | C | 90 |
| 2 | A | 89 |
| 2 | C | 92 |
| 3 | C | 80 |
The primary key of the Student table is Roll_no. For the Performance table, the columns Roll_no. and Subject_code together form the primary key. Consider the SQL query given below:
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 ________.
Practise this question in The GATE Grind →
Show answer and explanation
Correct answer: 5
Explanation
There is no join condition, so the query takes the Cartesian product of Student with the Performance rows having marks above 84 (five rows). Every student pairs with these rows, so grouping by student name gives one group per student: 5 rows.