My last fortress used water for several things (the water came from a brook.)
-First and foremost was a moat. (The overall fortress design was an above-ground castle whose center held a staircase going straight down into the earth for about 15 z-levels). They're really easy to do so long as you do them on the surface.
-Second, a "flush" system for my trade depot. This one is a little bit more complicated and involved plumbing, plus a floor hatch, plus four floodgates and three sets of vertical bars. Essentially, the elven traders enter the depot and start setting up, at which time the three floodgates at the entrance to the room shut and the fourth gate which opens up to the moat also shuts. The floor hatch in the ceiling opens up and water pours in from above until everything is dead...then the fourth gate opens again and the bars keep items from draining out while the water empties into the moat.
-Third, plumbing so my arena could be converted on-the-fly into a stadium/drowning chamber. (similar concept to 2)
-Fourth, artificial waterfalls in statue gardens and/or dining halls. If done incorrectly this can go so horribly wrong that you won't have to worry about dwarves needing happy thoughts...ever. But if done correctly, this alone can deflect whatever impending tantrum spirals that a really nice dining hall can't.
...and yeah, Spoon pretty much beat me to most of the stuff.