1

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.

  • 2
    The exam says "Find the distinct names" so why are you worried about DISTINCT? – ypercubeᵀᴹ Feb 10 '14 at 14:44
  • 1
    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
1

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.