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

arjun sir pls reply

---

Reenakhanna That is on natural join, here cross product is considered as join condition is not given.

---

But sir,..when written in ques is join and we find two table having same attribute (here is Roll no.) ..then we do natural join . If not,plz explain.

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

best solution

---

In the link given question there is condition given so it is natural join. But in the given question we consider as cross product.

Hope you understand.

---

@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

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

was it 5 I don't remember exactly. But relation Algebra was 1

Comments

From where did you calculate ? Response sheet or answer key pushed ?

---

Good Ahbanc u will be in top २०० for sure if luck favour's u will be in top १०० , I am going down one by one , Silly mistakes of calculating combination,divide etc. ruined my exam 😑

---

Lets see, Even I don't know all the answers. Depends on the final answer key. Everything can change. Also, it seems this time top rankers will have very high marks so less chances it seems.