Write a query to display the name of the department that has the maximum student count

Question

this is the schema Write a query to display the name of the department that has the maximum student count.

this is what is tried.

select d.department_name,count(s.student_id)
     from department d left join student s
     on d.department_id=s.department_id 
     group by d.department_name,d.department_id
     order by d.department_name;

and i think there is something missing in my code

Answer 1

You're almost there.
Order the result in descending order on the number of students and then take the first row:

SELECT department_name
FROM
(
  SELECT   d.department_name,
           COUNT(*) AS nr_students
  FROM     department d
  JOIN     student    s
    ON     d.department_id = s.department_id
  GROUP BY d.department_name
  ORDER BY nr_students DESC
)
WHERE ROWNUM <= 1;