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.