Don Burke once bubbled...
Hi Bomb,
The number 1 cause of death in divers is drowning.
You can run out of air stuck somewhere. You can get yourself so tired that you surface out of sight of the boat and drift off to oblivion. You can jump in the water grossly overweighted with your tank valve shut. You can panic at depth, spit out your regulator, and try to breathe water. If you are dealing with very deep diving, or oxygen enriched gasses (or both) you can oxtox yourself, have a convulsion and spit out your regulator while unconcious. I'm sure there are some ways of drowning while diving that I've forgotten.
Behind drowning, there are expansion problems which fall into two categories:
DCS - the fizzy soda bottle problem
Embolism - Your lungs are like a balloon in an eggshell. Once they are full, adding more is a very bad thing. The lungs get most of the press, but other parts of the body can get you too.
Also behind drowning is the ever-present heart attack. A long swim in a nasty current may do someone in if he isn't in shape.
Getting attacked by critters gets some press, but it's pretty rare unless you go looking for trouble.
The bottom line is to never hold your breath on SCUBA, dive with a buddy, do your gear checks, monitor your gas supply, don't get inside or under anything (wreck, pipe, cave, etc) without proper training, be aware of how you are going to get out of where you are and back to your starting point, ascend slowly, watch your health, don't go looking for trouble, and stay within your training..
Take it slow and you'll be fine.