The Gateway to Computer Science Excellence
0 votes
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?

in Databases by Active (2.5k points) | 73 views

1 Answer

0 votes

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?

please ...
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?
Quick search syntax
tags tag:apple
author user:martin
title title:apple
content content:apple
exclude -tag:apple
force match +apple
views views:100
score score:10
answers answers:2
is accepted isaccepted:true
is closed isclosed:true
50,737 questions
57,356 answers
198,482 comments
105,252 users