Re: Examination

"Clairwil" <clairwil_at_hotmail.com> wrote:

> I'd like to know if somebody can tell me how to do this:
>
> I have such a db:
>
> Student (IdStudent, Name, Surname, Address)
> DegreeThesis (IdThesis, Ttle, IdTeacher, IdStudent, Mark, Year)
> Teacher (IdTeacher, Surname)
>
> What I'm asked to find is
>
> all the IdThesis that have got the best mark for each year for each
teacher
> who's now linked to (at least) two theses not yet evaluated (it means mark
> =0).
>
> I can find rightly all the teacher and all best marks, but I can't link
them
> correctly to their theses: it selects IdThesis related to a correct
teacher
> also if the mark is not the best of that year, but it is present in the sq
> Mark IN.
>
> This is my SQL code:
>
> SELECT IdThesis FROM
> (SELECT IdThesis, IdTeacher, Mark, Year FROM DegreeThesis
> WHERE IdTeacher IN
> (SELECT IdTeacher FROM
> (SELECT IdTeacher , COUNT(*) FROM DegreeThesis WHERE Mark= 0
> GROUP BY IdTeacher HAVING COUNT(*)>=2)
> )
> AND Mark IN
> (SELECT MaxMark FROM (SELECT IdTeacher , Max(Mark) AS MaxMark
,Year
> FROM DegreeThesis WHERE Mark<> 0 GROUP BY IdTeacher , Year )
> );

I'm not sure that I got the point, but try this (not tested):

SELECT D1.IdThesis
  FROM DegreeThesis D1
 WHERE D1.Mark = (

       SELECT MAX(D2.Mark)
         FROM DegreeThesis D2
        WHERE D2.Year = D1.Year)
   AND D1.IdTeacher IN (
       SELECT IdTeacher
         FROM DegreeThesis
        WHERE Mark=0
        GROUP BY IdTeacher
       HAVING Count(*) >= 2)

As an alternative, you can replace IN with EXISTS and correlated (sub)query. And, btw, your original code is very "interesting"...

Regards,
Damjan S. Vujnovic

University of Belgrade
School of Electrical Engineering
Department of Computer Engineering & Informatics Belgrade, Yugoslavia

http://galeb.etf.bg.ac.yu/~damjan/ Received on Tue Feb 11 2003 - 10:46:10 CET