# Result of a query in SQL

Let us say that we have to write a query for:

Find the distinct names of all students with percent>90 in courseno=107

given three relations:

``````Students (rolno:integer,sname:string)
Courses (courseno:integer,cname:string)
Registration (rollno:integer,courseno:integer,percent:real)
``````

According to an exam solution,the query solution is:

``````SELECT DISTINCT S.name
FROM Students as S,
Registration as R
WHERE R.rollno=s.rollno
AND R.courseno=107
AND R.percent>90
``````

I think that if two students(say there are two students having same name so its name would be eliminated) so I am worried about use of `DISTINCT` here.

• The exam says "Find the distinct names" so why are you worried about `DISTINCT`? – ypercubeᵀᴹ Feb 10 '14 at 14:44
• Agree with @ypercube. If the question was "find the names of the distinct students" your concern would be valid. But it isn't. – Martin Smith Feb 10 '14 at 14:46
• It's a good example of something you will see in the real world. And in the real world you should defiantly question the request and confirm what they mean. In an exam though you can't ask questions so you have to take the question as written. – Kenneth Fisher Feb 10 '14 at 15:38

I see the point you are making: if there is more than 1 student with the same name, enrolled in course 107, with a percentage greater than 90%, then your query will not distinguish between them.

Given the limited amount we know about the dataset, the solution provided does do the job, but I would have added the caveat about duplicate student names, or proposed an alternative solution:

``````SELECT S.name,
count(*)
FROM   Students as S,
Registration as R
WHERE  R.rollno=s.rollno
AND  R.courseno=107
AND  R.percent>90
GROUP  BY S.name
``````

and in this way, we list distinct names, but are aware when there are multiple records selected for the same name.