With aquarium photography, there are slightly different requirements from a camera than for outdoors or studio photography. The most important requirement is that the camera be a 35mm SLR type. These cameras use the common 35mm film and the image you see in the viewfinder comes into view through the lens and mirrors. These cameras use detachable lenses that must be purchased separately. Small "instant" or compact cameras do not use these lenses and the image you see when you look through those cameras are not what falls on the film. You absolutely have to be able to see your subject clearly and up close, therefore, instant or compact cameras are highly unsuitable. Compact/instant cameras are notorious for causing flash reflections to appear on the photo.
Most camera manufacturers sell 35mm SLR cameras. Which one to buy? I am highly biased towards Nikon cameras and lenses but I have seen spectacular images created by using most other makes of cameras. There is a certain amount of brand snobbery that is associated with photography and to be objective, you have to rise above the opinions. If cost is an issue, you can always buy used equipment; a reliable, used Canon, Nikon, Pentax or Minolta can be had for $200. New models cost $300-$900 or more, depending on the model. I purchased most of my equipment through mail order. There are many mail order photography stores in New York and I have used a couple of them without any problems.
Most amateur aquarium photography is done with hand-held cameras. This requires the camera to be comfortable. From a basic point of view, a camera is a mere container that houses and advances the film. Spending large sums of money on the camera body is unwise. I suggest purchasing a relatively cheap body and using the extra money on the lens. If you have an expensive camera body and a cheap lens, you will produce mediocre images but a cheap body mated to a high quality lens can create wonders.
Often, I find myself sitting in front of my tanks with a camera and flashes for hours. This gets very tiring for my wrists and arms. A light camera would help tremendously but I use my "fish camera" for nature and outdoors photography as well. So, I purchased an advanced model (Nikon N70), which happens to be heavier than I wanted. I plan to purchase a cheaper and lighter model (Nikon N50) for fish photography. When I set up temporary photo tanks exclusively for photographing fish, I can be in front of the tank for many hours at a time. In such instances, I rely on a tripod that allows me to point the camera anywhere I wish. The weight of the camera is less significant when I use a photo tank. I suspect most amateur fish photographers will not have a special photo tank, which means a light camera would be preferred.
camera specification to consider is flash sync. Flash sync of the camera is the window of time (shutter speed) in which the camera shutter remains open and the flash goes off. The duration of most flash bursts is about 1/1000th sec. It is difficult to time the shutter of the camera to remain open at the same time as the quick flash burst. Therefore, to be safe, most cameras will allow a maximum shutter speed when the flash is being used. This is usually a longer period than the flash burst. I recommend buying a camera with at least 1/125th sec. flash sync. I recommend a shutter speed of 1/125th sec. If the flash sync. is 1/60th sec., you are restricted to that speed during the shoot. In my opinion, photos taken at 1/60th second often are, even with the time-stopping ability of a flash, blurrier than those taken at 1/125th second. Even though the image is mostly lit by the flash, the ambient light is enough to make the shot taken at 1/60th second less crisp than ones taken at 1/125th second. Another rule of photography is that the shutter speed should be faster than the reciprocal of the focal length of the lens. A 60mm lens can only produce crisp images if the shutter speed is faster than the focal length of the lens 1/60th second or faster. Likewise, a 200mm telephoto lens would yield blurry images if the shutter speed were much less than 1/250th second. The flash sync. may not be critical with other types of photography but I found it important when I began shooting fish.
Medium formats? They are expensive, bulky machines for advanced photographers. There are only a few aquarium photographers in the world who use this format. These cameras use a much larger film that allows tremendous enlargement without image degradation.
In the end, you may find that the camera body makes the photography experience easier or more difficult but the quality of the images still depends on the lens and the lighting.