73 views

In this question:-

https://gateoverflow.in/8225/gate2015-1-27

I am not understanding how the query is returning 2 .

I think DISTINCT must be there to give the correct answer.

Can you give some logical explanation?

| 73 views

The answer is 2 as there are only 2 distinct student names.

SELECT S.studentName, Sum(p.marks)
FROM student S, performance P
WHERE S.rollno= P.rollno

This query will return only 1 Row. Confused?

SELECT distinct S.studentName, Sum(p.marks)
FROM student S, performance P
WHERE S.rollno= P.rollno

This will also return 1 row

SELECT S.studentName
FROM student S, performance P
WHERE S.rollno= P.rollno

This will return 6 rows.

SELECT distinct S.studentName
FROM student S, performance P
WHERE S.rollno= P.rollno

This will return 2 rows

Below is the result of the given query. Note that there are only two student names and query prints sum(P.Marks) for every student.

Student_Name     Marks
Raj                     310
Rohit                   140

by Active (4.8k points)
0
i understood the 1st and 2nd query but can you please explain the output in query 1 and query 2?

0
In query 2 there will be no repetitions of student name. Marks of the students with the same name will be summed up together.

In query 1, all student names will be displayed who satisfy the query. Duplicate names will be allowed.
0
you have explained what i have understood::

my doubt is since you have said that in 1st two queries only 1 row will be returned .   i am asking what will be the value in that row?

example if  we have

raj    20

rohit  30

rakesh  40

raj   30

then what will be the output?

in that one row what will be displayed and what will be the value of sum o marks?

+1 vote