Water Levels are a paradox. On one hand, many notorious sections of games are Water-themed. On the other hand, it makes a lot of sense to include them, considering how common and versatile water is. And there have been a number of cases where these levels have been awesome.

• Jolly Roger Bay (Super Mario 64) - This water level allowed one to navigate 3D spaces at a slower pace than flying allowed. The objectives in this stage support the game's swimming mechanics, encouraging calm and collected prodding of the environment. And although Mario can drown, the air mechanic and ability to heal with coins lets one take risks.
• Launch Octopu's Stage (Mega Man X) - Instead of using water physics to mess with your progress, this one uses it to make the level more open ended. Your higher jumps allow you to stay by the surface, tread the ocean floor, and switch back and forth. You had a mini-boss serpents and submarines below, ships on the surface, secrets to find, and the Octopus-themed boss can have its tentacles cut off with the cutter weapon.
• Swamp Palace (Zelda: A Link Between Worlds) - This dungeon plays with the fairly common idea of changing water levels, like Ocarina of Time's infamous Water Temple. Both even focus on the Hookshot. Unlike the Water Temple, the 2D perspective makes tracking puzzles easier. The Hookshot is more tightly integrated into the design than in Ocarina of Time, thanks to its use in activating switches and moving platforms. 

What are your favorites?

MegaMan Legends 2: Nino Ruins.

Use to hate this dungeon but when I played it again and again I actually quite enjoyed it.

Pulstar level 2

All of Wave Race 64

I despise water levels. Even if the water level is decent, it's far from being in the top 10 levels.

Jolly Rogers Lagoon and Atlantis in Banjo Tooie are much more memorable than Mario 64's levels, IMO.

Still, my favorite would have to be Amiss Abyss from DKC: TF (many other DKC levels, too). ALso that boss in Shadow of the Colossus; it was great and very doable for a water-based area. Also, Undersea Palace in Crono Trigger, though that doesn't really count. It does have the best music ever, though

If they count, I really liked the surfing levels in Super Mario Galaxy.
Even though it's really one of my least favorite levels, I would also add the hotel basement level in The Last of Us because it gives me absolute nightmares and is so memorable because of it. Water level + pitch black + completely alone + huge difficulty spike + WHAT IS THAT BEHIND ME + first real encounter with the scariest enemies in the game (stalkers) + there's a bloater too!? + why won't the key card work!? + there's too many of them!! = screw it, I'm done. It also made me paranoid around generators for the rest of the game.

The Water temple from Ocarina of times is obviously the best water level of all time... Said no one ever!

I would say the water levels from Super Mario 3D World were quite fun though :P

Super Metroid- underwater level
Blaster Master- level 5
Wind Waker- all of it and the underwater ancient castle hyrule
Wave Race- all of it
Jet Force Gemini- Water Ruin

Dire Dire Docks from Mario 64! The level is whatever, but that dang music puts me in a reeeeal good place, every time. Super nostalgic.

Every other water level in history can kiss my heiny, though! XD

EDIT:  The crashed freighter in Metroid Prime.  Again, largely because of the music, but the whole thing is just really haunting and cool.