The kind that aren't manufactured...
But the most scenic are natural rivers, large lakes, the ocean, other large bodies of water. But from a strategic point of view, they are all basically the same, although crashing waves or a swift running river can be distracting.
As for individual holes, I can't think of a great use of a stream, 18 at TPC Sawgrass is probably the best use of a pond I can think of right now, lake I am not sure about, I am not sure I've played many courses that were immediately on lakes, Edgewood Tahoe just the other day was, but Tahoe is not in play off the tee on 18 (but on that, what exactly counts as a lake? For these purposes, I personally wouldn't really count Lake Michigan or the other Great Lakes as a "Lake," but thats just personal preference), and Ocean I have to say 18 at Pebble Beach.