This is a very open ended question. The British Sub Aquatic Club1 publishes an annual incident report covering accident in their area. This is an excellent report if the question is about the dangers to trained SCUBA divers.
Generally speaking the dangers vary depth, time, physical condition, and choice of breathing gas.
Equipment failure is rarely a fatal danger in recreational SCUBA diving (as defined by PADI).
The physical condition of diver plays a role in a majority of accidents.2 Heart attack is common in older divers with poor cardiovascular condition whom mistake the sport as low-impact.
A divers training is the most critical factors to ensure a dive is conducted safely. Lack of sufficient training/knowledge in this activity can be fatal.5
Decompression sickness is the most commonly discussed. This relates to the loading of nitrogen in a persons blood under the increased partial pressure of a given gas at depth.3 Surfacing from a dive results in small bubbles in a persons blood. Duration, depth, and physique of a diver dictates the size of the bubbles.4 Small bubbles are reabsorbed but larger bubbles can have fatal consequences. I would also note today it is common dive with mixed gases other than air.6
The above is related to Nitrogen narcosis. Nitrogen Narcosis is the mental impact of having an increased amount of nitrogen in your bloodstream resulting in a mental state similar to drunkenness. This can lead to life threatening errors and mis- communication.
Air embolism is the result of the expansion of gas which destroys / rips tissues. Under controlled diving condition a diver surfaces slowly (30ft per minute) which allows the gases to slowly exit the body. In the event of a rushed or emergency ascent (or if a diver holds their breath) air can break the tissues and enter other parts of the body. Most common is a rupture of the lung which can lead to bubbles forming in the neck. This is why the 1st mantra of scuba diving is "breath continuously, never hold your breath."
3.) The air you breath is 21%Oxygen 78%Nitrogen 1%Argon
4.) The US Navy produces a set of Nitrogen loading tables based on time and depth
5.) PADI is the best know training agency in the sport www.padi.com
6.) Nitrox, noted as EanX, is a mix where the Oxygen content is
adjusted to X%
Being squished by the water pressure, running out of air, getting trapped underwater, getting raped by a crab and in genral death