GATE CSE 2019 | Question: 51

Question

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

$$\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 ________

Comments

Detailed Video Solution with Variation: https://youtu.be/zb6vXpZVo1E?t=5636&feature=shared

Exactly Similar GATE Question: https://youtu.be/zb6vXpZVo1E?t=5275&feature=shared 

(MUST Watch for a Observation to Quickly Solve such questions)

---

There is no condition like:    S.Roll_no = P.Roll_no
 
So this query performs a CARTESIAN PRODUCT (cross join) between:
there are 25 rows after cross-product (last row from performance has marks < 84 so it is discarede)
after that GROUP BY student name so, 5 rows (5 distinct students present)
and sum of marks of every student is same 

---

shukriya parth...

Answer 1

Group by Student_name $\implies$ number of distinct values of Student_name

in the instance of the relation all rows have distinct name then it should results $5$ tuples !

Comments

@Shaik Masthan sir,

SELECT S.Student_name, sum(P.Marks) -----> 4.Display

FROM Student S, Performance P             ------> 1. Cross product

WHERE P.Marks >84                                  ------> 2. Conditon check

GROUP BY S.Student_name;                     -----> 3.group By

---

 

• Without a HAVING Clause: When a GROUP BY clause is used without a HAVING clause, the number of rows in the output is equal to the number of groups formed.

• With a HAVING Clause: When both GROUP BY and HAVING clauses are used, the number of rows in the output will be at most the number of groups formed, depending on the conditions specified in the HAVING clause.

---

thnx helped alot !

Answer 2

5 rows

Comma by default means cross product not natural join

Comments

Why not? I didn’t understand the concept.

---

For natural join it will be explicitly mentioned or we have to assume in case of same attribute name?

---

@Manoj Kumar Pandey

select * from (A join B on C1=C2) -------> means projection on naturnal join                                                                                             (selection on cross product)

select * from A, B ---> means projection on  cross product then projection on that

Answer 3

Remember this basic rule in sql

SELECT= PROJECTION

FROM= CROSS PRODUCT

WHERE= SELECT CONDITION

here in the given query we need to take cross product which returns 25 query with keeping in mind the condition to jave marks > 84 so when we group by our answer would be

Amit 452

Priya 452

Vinit 452

Roshan 452

Smita 452

So 5 tuples

Answer 4

One nice observation - 

$GroupBy()$ with $Having$ Clause  $\Rightarrow$     No. of tuples <= No of Groups

$GroupBy()$ without $Having$ Clause $\Rightarrow$    No. of tuples = No of Groups

Comments

NO. It is actually GROUP BY+no Having Clause+The attribute based on which Grouping is done are all having UNIQUE values = (No. of Tuples = No. of Groups).

---

So having clause will force the condition of marks>84 and return 2 tuples right roll 1 and 2?

Answer 5

The SQL query performs a cross join between the Student and Performance tables because there is no join condition specified. The WHERE clause filters only the Performance records where Marks > 84. 

• Every student is paired with each of those five marks → each student contributes the same five marks to their group.

• SUM(P.Marks) = 86+95+90+89+92 = 452. So every student gets 452.

Result (what the query actually returns):
 

Student_name	sum(P.Marks)
Amit	452
Priya	452
Vinit	452
Rohan	452
Smita	452

The query returns 5 rows

Comments

Thnx @Gopika_G !!!

Answer 6

EVERY STUDENT WILL BE MATCHED WITH EVERY OTHER SUBJECT WHERE THE SUBJECT’S  MARKS ARE GREATER THAN 84  NOW WE WILL FIND 5 SUBJECTS HAVING MARKS GREATER THAN 84 THEN 5 STUDENTS WITH 5 SUBJECTS LEADS TO 25 AND FINALLY THEY ARE GROUPED BY STUDENT NAME SO 5 ROWS