GATE CS 2019 Q.61 (CS Q.51): SQL Queries and Integrity Constraints

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

Student

Roll_no.	Student_name
1	Amit
2	Priya
3	Vinit
4	Rohan
5	Smita

Performance

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 ________.

Your answer

Official answer and solution

Official answer: 5
From: Official GATE 2019 answer key

Solution

Analyze the query execution step by step: Cartesian product, WHERE filter, and GROUP BY aggregation.

Consider the query:

SELECT S.Student_name, sum(P.Marks)
FROM Student S, Performance P
WHERE P.Marks > 84
GROUP BY S.Student_name;

First, observe that the FROM clause specifies Student S, Performance P without any join condition equating S.Roll_no and P.Roll_no. This produces a Cartesian product (cross join) between the two tables.

Next, the WHERE condition filters rows from the product where P.Marks > 84. In the Performance table, the marks are:

Roll 1: A (86 > 84), B (95 > 84), C (90 > 84) -> 3 rows
Roll 2: A (89 > 84), C (92 > 84) -> 2 rows
Roll 3: C (80 is not > 84) -> 0 rows
Thus, exactly $3 + 2 = 5$ rows of Performance satisfy Marks > 84.

Because it is a Cartesian product, each row of Student is paired with all 5 qualifying rows from Performance.

The Student table has 5 distinct student names: Amit, Priya, Vinit, Rohan, and Smita.

The GROUP BY S.Student_name clause aggregates the rows by Student_name. Since there are 5 distinct names, exactly 5 groups are formed. Each group produces exactly one row in the query result.

Therefore, the query returns 5 rows.

Final answer

Common trap

Assuming an implicit natural join on Roll_no, which would mistakenly filter out students Rohan and Smita (who are not in Performance) and Vinit (whose marks are not > 84), yielding 2 rows.

The answer is the one in the official GATE answer key. The solution is this site's own explanation.

Source

Paper: GATE 2019 Computer Science and Information Technology (CS)
Question: 61 of 65, printed as CS Q.51
Section: Computer Science and Information Technology

Report an error in this question

GATE questions are the work of the organising institutes. This site is independent and is not affiliated with them.

Question

Solution

Source