The GATE Grind

GATE 2019 CS – Question 61

Databases · Relational Model: Relational Algebra, Tuple Calculus, SQL · 2 marks · Numerical answer

A relational database contains two tables Student and Performance as shown below:

Roll_no.Student_name
1Amit
2Priya
3Vinit
4Rohan
5Smita
Roll_no.Subject_codeMarks
1A86
1B95
1C90
2A89
2C92
3C80

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

All Relational Model: Relational Algebra, Tuple Calculus, SQL questions →