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

5 I think.

---

I think it is 2 , because we needed to show the sum of marks the student got (based on the join condition we choose the student ) and when group by happens only 2 tuples remain ..

---

can you share the exact sql query ? I don't remember it , but all I could say is it was 5

---

Well I did not remember the exact complete question , therefore pardon me ,I just commented based on whatever I remembered.

---

....

---

Two roll numbered students marks were to be returned using group by clause.

Answer was 2.

---

As far as i remember , there was a cross join , and there was a group by , with no other condition. If this is true , then answer would be 5 , I might have missed something , kindly post the problem here if you remember

---

SELECT s.student_name, sum(p.marks)
FROM student s, performance P
WHERE p.marks>84
GROUP BY s.student.name

---

Yes in my opinion too there was no other join condition specified in the question so all tuples must be selected as the names in the first table mentioned were all distinct.

---

it'll be 5.

---

What is ur answer for predicate logic question

---

Actually there was roll no in both the tables over which the join would happen , which would result in 5 tuples , 2 for Amit and 3 for Raman(assumed names ),and when group by happens it would result in 2.

---

It was a cross join , not a natural join

---

Plz correct me if I am wrong but if some attribute is matching in cross product ,it is natural join by default on those attributes.

---

What's the purpose of explicitly mentioning natural join then in queries ? Just give commas and automatically natural join will be done.

---

I think There was also attribute comparison also.

---

SELECT s.student_name, sum(p.marks)
FROM student s, performance P
WHERE p.marks>84
GROUP BY s.student.name

@Sharad Shukla 5 can you tell me what am I missing here?

---

You are right. What's ur opinion on top 100, and top 200.

---

what about that?

---

Score?? what do u think ?

---

Which score will  be below rank 200

---

that for(r(),r(),r())

ques was for how many marks ?

---

I think 2 . Don't remember exactly .

Answer was 5 2

---

in some answer keys it is given 1M

---

This was 1 mark

---

ARP question was for 1 mark or 2 marks

---

@Ahabnnc @Deepanshu

What is answer for inherited sttribute ques ??

Q 53

And what is answer for GA question on directions ??

Q7

---

1. where clause returns 

now group by 

 

---

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.