Someone under the water has total cover from someone on the land. If you are in the water but not under it you have partial cover from a land based opponent. What I'd like to know is if you are in the water (but not under it) and want to attack someone underneath the water, do they have cover and is it total or partial?
From the DMG 3.5, page 93:
(the same rule in the Pathfinder Reference Document)
My own interpretation of this rule is as follows:
If you are attacking with a weapon and you aren't subject to freedom of movement, then the surface of the water blocks line of effect. This means that two creatures who are at least chest deep in water have improved cover relative to each other. A creature who is completely submerged has total cover and therefore cannot be attacked.
PHB 3.5, page 152: