Skip to content
GATETECH
Official PYQ · 2019Q51Tuple Calculus and SQLNAT2 marks

GATE CS 2019 Q51 — Tuple Calculus and SQL

Explain with AI

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

Roll_noStudent_name1Amit2Priya3Vinit4Rohan5SmitaTable: studentRoll_noSubject_codeMarks1A861B951C902A892C923C80Table: Performance\overset{\text{Table: student}}{\begin{array}{|l|l|} \hline \text{Roll\_no} & \text{Student\_name}\\ \hline 1 & \text{Amit} \\ \hline 2 & \text{Priya} \\ \hline 3 & \text{Vinit} \\ \hline 4 & \text{Rohan} \\ \hline 5 & \text{Smita} \\ \hline \end{array}} \qquad\overset{\text{Table: Performance}}{\begin{array}{|l|l|l|} \hline \text{Roll\_no} & \text{Subject\_code} & \text{Marks}\\ \hline 1 & \text{A} & 86 \\ \hline 1 & \text{B} & 95 \\ \hline 1 & \text{C} & 90 \\ \hline 2 & \text{A} & 89 \\ \hline 2 & \text{C} & 92 \\ \hline 3 & \text{C} & 80 \\ \hline \end{array}}

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 ________

View worked solution

Answer: 5

To find the number of rows returned by the SQL query, let us analyze the query step-by-step:

1. Identify the Join Condition

Notice that the query performs a cross join (Cartesian product) because there is no join condition (such as S.Roll_no = P.Roll_no) specified in the WHERE clause:

FROM Student S, Performance P

2. Apply the Filter Condition

The WHERE clause filters the Performance table for rows where Marks > 84:

  • Roll_no 1, Subject A: 86>8486 > 84 (True)
  • Roll_no 1, Subject B: 95>8495 > 84 (True)
  • Roll_no 1, Subject C: 90>8490 > 84 (True)
  • Roll_no 2, Subject A: 89>8489 > 84 (True)
  • Roll_no 2, Subject C: 92>8492 > 84 (True)
  • Roll_no 3, Subject C: 80>8480 > 84 (False)

There are exactly 55 rows in the Performance table that satisfy this condition.

3. Cartesian Product

The Student table contains 55 unique students:

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

Since a Cartesian product is performed, each of these 55 students is paired with each of the 55 filtered rows from the Performance table, resulting in 5×5=255 \times 5 = 25 intermediate rows.

4. Group By Clause

The query groups the result by S.Student_name:

GROUP BY S.Student_name;

Since all 55 student names in the Student table are distinct, the grouping operation will create exactly 55 groups (one for each student name).

Each group produces exactly one row in the final output containing the student's name and the sum of marks (which will be 86+95+90+89+92=45286 + 95 + 90 + 89 + 92 = 452 for each student).

Thus, the number of rows returned by the query is 55.

Free practice · Your answers on this page are not saved.

Published
Report a correction
Keep exploringMore Tuple Calculus and SQL questions