I've created a view to query on but does not seem to be working properly.
CREATE VIEW SpeciesTypes AS
SELECT species.Name, count(animal.animalID) as number_of_animals
FROM zoo.species, zoo.animal
WHERE species.speciesID = animal.speciesID
GROUP by Name
SELECT name, max(number_of_animals)
If you want to get the name of the species having the greatest number of animals, using your view you can just use a
WHERE clause to restrict the result set.
SELECT t.name, t.number_of_animals FROM SpeciesTypes t WHERE t.number_of_animals = (SELECT MAX(number_of_animals) FROM SpeciesTypes)