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
The query has `FROM Student S, Performance P` and **no join condition** between them. The only condition is `P.Marks > 84`.
**Step 1: filter Performance.** Marks above 84: $(1,A,86)$, $(1,B,95)$, $(1,C,90)$, $(2,A,89)$, $(2,C,92)$. That is 5 rows ($(3,C,80)$ is removed).
**Step 2: Cartesian product.** Every one of the 5 students is paired with every one of those 5 rows: 25 rows.
**Step 3: GROUP BY Student_name.** The 5 students have 5 different names, so there are 5 groups, each with its own `sum(Marks)`.
Number of rows returned $=\mathbf{5}$.
More Relational Model: Relational Algebra, Tuple Calculus, SQL questions
- GATE 2019 CS Q65
- GATE 2018 CS Q22
- GATE 2018 CS Q51
- GATE 2015 CS (CS1) Q31
- GATE 2015 CS (CS1) Q61
- GATE 2017 CS (CS1) Q33
All Relational Model: Relational Algebra, Tuple Calculus, SQL questions →