How can I get a value corresponding to the maximum average in this SQL exercise?-CodePudding

I tried to solve this exercise, but I'm stuck when it comes to show the CCode where I have the maximum average of students. I attach the pic of the exercise and the database tables. Thanks in advance.

Here's my code:

SELECT CO.TCode, CO.CCode
FROM COURSE CO
WHERE CO.CCode NOT IN(
  SELECT CCode
  FROM COURSE
  WHERE Topic <> 'database')
AND CO.CCode =(
  SELECT C1.CCode
  FROM (SELECT CCode, AVG(AttendingStudent#) MEDIA
       FROM LECTURE
       GROUP BY CCode) C1
  WHERE MAX(C1.MEDIA) AND C1.CCode = CO.CCode
)

I don't think the maximum in the WHERE clause can work.

CodePudding user response：

From Oracle 12, you can order the subquery and use FETCH FIRST ROW ONLY to get the maximum (or FETCH FIRST ROW WITH TIES if there would be join highest, but then you would need to change from an = comparison to IN).

SELECT CO.TCode,
       CO.CCode
FROM   COURSE CO
WHERE  CO.CCode NOT IN( SELECT CCode
                        FROM   COURSE
                        WHERE  Topic <> 'database')
AND    CO.CCode = ( SELECT c.CCode
                    FROM   Course c
                           INNER JOIN LECTURE l
                           ON (c.ccode = l.ccode)
                    WHERE  l.TCode = CO.TCode 
                    GROUP BY CCode
                    ORDER BY AVG(AttendingStudent#) DESC
                    FETCH FIRST ROW ONLY
                  )

Otherwise, you can use a HAVING clause:

SELECT CO.TCode,
       CO.CCode
FROM   COURSE CO
WHERE  CO.CCode NOT IN( SELECT CCode
                        FROM   COURSE
                        WHERE  Topic <> 'database'
                      )
AND    CO.CCode IN ( SELECT CCode
                     FROM (
                       SELECT TCode,
                              CCode,
                              AVG(AttendingStudent#) MEDIA
                       FROM   LECTURE
                       GROUP BY TCode, CCode
                     ) C1
                     WHERE  c1.TCode = CO.TCode
                     HAVING c1.MEDIA = MAX(C1.MEDIA)
                   )

Note: Your first sub-query is not correct as you want to find the teacher that has not taught a non-databases course and not the non-databases courses. It's almost correct though as you only need to change the column you are comparing on but I am sure you can correct it.

CodePudding user response：

Try using the following as subquery:

SELECT C1.CCode
FROM (SELECT CCode, 
             AVG(AttendingStudent#) MEDIA
       FROM LECTURE
       GROUP BY CCode) C1
GROUP BY C1.CCode
HAVING MAX(C1.MEDIA) AND C1.CCode = CO.CCode

You will be aggregating on "C1.MEDIA" and grouping on "C1.CCode", but the aggregation will just happen inside the HAVING clause, leaving only the codes for selection.

Some tips on how to use the HAVING clause here.