Result of a query in SQL

distinctsql-standard

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.

Best Answer

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.

Related Question