The basics:
1) No Flash
2) Lens perpendicular to aquarium side
3) Fast shutter speed (or a tripod if stationary subject)
Then, work on:
4) Proper exposure
5) Proper color balance (in-camera or photoshop)
6) Low ISO noise
That's actually quite a simplification, there's really a lot more to it. Any camera works to a point, but I find the really jaw dropping shots are much easier to achieve with a quality SLR and lenses. SLR lenses collect more light than p&s lenses, allowing faster shutter speeds. They also have shallower depth of focus, which allows you capture smooth blurred backgrounds when you want.
My preferred setup for in-tank shooting is a Canon Rebel XT with a Canon EF 100mm 2.8 Macro lens. My best coral shots have been macros taken on a tripod with manual exposure, manual focus, and a shutter release cable.
sirjohn has the right idea: take lots of pictures. Digital film is cheap.
Take lots of pictures with different settings, look at them afterwards and see what worked and what didn't. Practice and learning the camera are the keys to taking consistently good pics.