When to Use Long Exposure for Water
Since I know you all are thinking it, let's get the most obvious situation when you would talk about using a slow shutter speed and water out of the way: waterfalls. In order to accentuate the movement of the water in the scene a slow shutter speed is often used. The blurring of this movement gives the waterfall a soft, silky, artistic look to it that many people find appealing. Depending on the speed of the water, the desired smoothness of the water, and the light given to you in the scene, most waterfall images will probably be shot somewhere between 1/4 second and 3 seconds. Of course, this doesn't mean that longer or shorter exposures won't get you what you want either.
Another time when you can use a long shutter speed to photograph water would be when capturing waves rolling in and onto a beach. In this type of photography you can try to capture the waves rolling onto the pebbles of the beach, around a large rock, into some driftwood, or whatever your particular scene gives you. Use strong foreground objects, possibly interacting with the incoming (or receding) waves, to provide a strong composition for the image. Similar shutter speeds to waterfall photography can be used here as well.
When there are large constant waves splashing into a rock wall, a dock, etc. you can get some really interesting effects by using slow shutter speeds. Leave the shutter open for an extended period of time (5 seconds, 10 seconds, or even longer if possible) and the troughs and peaks of the waves will all merge together into a haze of movement. You are at the mercy of the weather, scene, and environment for this type of photography, but the rewards can be fantastic.
When experimenting with night or astrophotography, consider including a lake or ocean in the composition and watch what the exposure time of several minutes does to the surface of the water. The slight movement of the water over 15 or more minutes will give the whole body of water a smooth consistent feel to it. Far from the glassy look of water in the morning and just as far from a regular shot of a lake, this type of image is attractively unique.