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:
Characters swimming, floating, or treading water on the surface, or wading in water at least chest deep, have improved cover (+8 bonus to AC, +4 bonus on Reflex saves) from opponents on land. Landbound opponents who have freedom of movement effects ignore this cover when making melee attacks against targets in the water. A completely submerged creature has total cover against opponents on land unless those opponents have freedom of movement effects.
(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:
If you don’t have line of effect to your target he is considered to have total cover from you. You can’t make an attack against a target that has total cover.