# Re: Examination

From: D Guntermann <guntermann_at_hotmail.com>
Date: Tue, 11 Feb 2003 22:18:52 GMT
Message-ID: <HA60nE.7uv_at_news.boeing.com>

I'm hoping that you aren't asking for an exam answer for school. I'll make the assumption you are not, despite the title:

If I understand the problem statement correctly... [return?] "best mark for each year for each teacher".

That's the key! Your query came close to solving the problem, but the subquery returning the maximum mark, summarized per teacher and year, does not correlate those values to values in the main higher level query block (idteacher and year). A consequence of this will be that a side affect occurs when another teacher who has the same mark as another teacher's highest mark (not necessessarily his or her highest!), regardless of the year, will also be listed. A slight modification will do it for you.

/* modification of poster's query */
SELECT Year, idteacher, IdThesis
FROM (SELECT IdThesis, IdTeacher, Mark, Year

```         FROM DegreeThesis base
WHERE IdTeacher IN (SELECT IdTeacher
FROM (SELECT IdTeacher , COUNT(*)
FROM DegreeThesis
WHERE Mark= 0
GROUP BY IdTeacher
HAVING COUNT(*)>=2)
)
AND Mark IN (SELECT Max(Mark)   /* simplified */
FROM DegreeThesis
WHERE Mark<> 0
AND base.idteacher = idteacher  /*
correlation to upper level query */
AND base.year = year                /*
correlation to upper level query */
GROUP BY IdTeacher , Year
```
))
/

Alternatively, using existential quantifiers...

SELECT year, idteacher, idthesis
FROM degreethesis a
WHERE EXISTS (SELECT 'x'

```                  FROM degreethesis x
WHERE x.idteacher = a.idteacher
AND x.mark = 0
GROUP BY x.idteacher
HAVING COUNT(idthesis) > 1)
AND EXISTS (SELECT 'x'
FROM degreethesis y
WHERE y.idteacher = a.idteacher
AND y.year = a.year
GROUP BY year, idteacher
HAVING MAX(mark) = a.mark)
```
/

HTH, Dan

"Clairwil" <clairwil_at_hotmail.com> wrote in message news:3C12a.104860\$ZE.2948691_at_twister2.libero.it...
> 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, ­Tětle, 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 )
> );
>
> You've been so great in past..I hope you're great again...
>
> Many thanks
>
> A newbie
>
>
>
>
>
>
>
Received on Tue Feb 11 2003 - 23:18:52 CET

Original text of this message