19,098 views

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 ________

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.

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 !

@Shaik  sir, what will be tuple values for Rohan & Smita?

Is here we have to relate tuples with primary constraint??

what will be tuple values for Rohan & Smita?

no need of tuple values to answer this query, right ?

Is here we have to relate tuples with primary constraint??

when we apply group by with some attributes, then in the result those attributes should be unique !

no need to bother about remaining attributes, if you use any of the remaining attributes uses in select clause, then it may generate error some times !

what are those 5 tuples? can you please write
edited
there is only one condition which is P.Marks > 84

therefore just remove those tuples from P which are ≤ 84.

Now there are 5 tuples in the relation P.

Now Do the cross product ! ==> Total 5 x 5 = 25 tuples in the result of cartesian product after applying where clause also.

Now we have to use Group By clause.

$$\begin{array}{|l|l|} \hline \textbf{S.Student_Name} & \textbf{SUM(P.marks)} \\\hline \text{Amit} & 86+95+90+89+92=452 \\\hline \text{Priya} & 86+95+90+89+92=452 \\\hline \text{Vinit} & 86+95+90+89+92=452 \\\hline \text{Rohan} & 86+95+90+89+92=452 \\\hline \text{Smita} & 86+95+90+89+92=452 \\\hline\end{array}$$

5 is Correct answer. I ran this query:

How do we know that we have to take cross product?
Whatever table is present in From clause you have to join all of them and filter the row, based on the condition specified in Where.

@Rahulgupta2509

FROM Student S, Performance P

from a,b,c,

means a×b×c

@ sir , how u get sum 452 , please see this similar question where sum is caculated by group wise but i am not able to see that actual difference between these two queries . Please help! https://gateoverflow.in/8225/gate2015-1-27

i dont see any difference... both are same... after grouping, marks will be addition.
sir,in this question marks are added of all the students name together (all have same sum value) but in the linked question sum is performed after grouping of students name wise.
why sum is same for this question, why not for the question in the link ?

because of the where condition, it is different.

it is like natural join in question which is in the link but not in THIS question. here also sum performed after grouping.
Got it sir, thank you so much!
is this true really ??
If let’s say I have two tables and they have same attribute name and can apply natural join but cross product is given, in that case do we still apply cross product? And For the natural join are they always going to mention explicitly?
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.
5 rows

Comma by default means cross product not natural join

@Manoj Kumar Pandey

even though there are common attributes it doesn't perform natural join !

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?
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
was it 5 I don't remember exactly. But relation Algebra was 1

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.

1
10,244 views