GATE CS Mastery
Cheat sheets

GATE CS 2015 Set 1 · Databases

Question 37

NATPrinted as CS Q.272 marksNo negative markingEasy (estimate)

Question

Consider the following relations:

Student

Roll No Student Name
1 Raj
2 Rohit
3 Raj

Performance

Roll No Course Marks
1 Math 80
1 English 70
2 Math 75
3 English 80
2 Physics 65
3 Math 80

Consider the following SQL query:

SELECT S.Student_Name, sum(P.Marks)
FROM Student S, Performance P
WHERE S.Roll_No = P.Roll_No
GROUP BY S.Student_Name

The number of rows that will be returned by the SQL query is _____.

Official answer and solution
Official answer
2
From
Official GATE 2015 answer key

Solution

Count the number of distinct groups produced by the GROUP BY clause on the joined relations.

  1. Perform the natural join Student S JOIN Performance P ON S.Roll_No = P.Roll_No:

    • Roll 1 (Raj): matches 2 Performance rows (Math 80, English 70).
    • Roll 2 (Rohit): matches 2 Performance rows (Math 75, Physics 65).
    • Roll 3 (Raj): matches 2 Performance rows (English 80, Math 80).

  2. The query groups by S.Student_Name:
    The distinct values present in S.Student_Name are:

    • Raj (combines all rows for Roll 1 and Roll 3)
    • Rohit (combines all rows for Roll 2)

  3. Since GROUP BY aggregates all matching records into one output row per distinct grouping attribute value, exactly 2 distinct rows are produced (one for Raj and one for Rohit).

The query returns 22 rows.

Final answer

2

Common trap

Assuming the query groups by Roll_No (which would yield 3 rows) rather than Student_Name.

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

Source

Paper
GATE 2015 Computer Science and Information Technology (CS) Session 1
Question
37 of 65, printed as CS Q.27
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.