The only game I haven't played on that list to finish would be StarFox 64 so I can't comment on it. Generally, I actually like water levels for their ability to mix up the gameplay (this was especially true of the legacy consoles, in which 2D platforming only offered so much gameplay). I remember the Sonic levels being difficult during childhood, but on a recent playthrough of the PS2 port of all four Genesis games I just blasted through them and I like the resistance effect. The Mario levels were certainly tricky, especially with the apparent ability to actually stomp the enemies requiring insane approaches, yet still enjoyable enough just avoiding them. I loved the maze-puzzle elements of the Zelda games water temples, so no complaints there and the time spent makes them a memorable component of their particular greatness. The Donkey Kong Country underwater bits were a tad overlong and tedious, but fun with the use of the swordfish.
In terms of foreboding, the last level of the first Sonic was especially frightening, what with pop-up spikes and limited lives, but I love the inherent difficulty those old platformers used to offer. The Megaman games used to have an exceptionally interesting take on water levels which again I appreciate, but I think the reuse of that gameplay feature in the recent Megaman titles (9 and 10) is a tad sadistic and over-unrealistic (I'm fundamentally against those titles in the series as a continuation of the old-style rather than progressing from 8 -- not to mention what feels like artificial difficulty). I used to be fairly terrified of the Duke Nukem 3D underwater portions, but again actually fairly fun playing it recently. The combination of darkness, short-sightedness, and an oxygen level against enemies that seemed to be suited especially for water made it a difficult proposition, but having the right ammo always did the trick there. I think the underwater portions of Tomb Raider blend well with the overall survival challenge in those games, though you certainly needed to plan your escape routes.
Now, the TMNT underwater level was INSANE. The gaps they made you swim through appeared impossible, at least until you realize part of your body is 'invincible'. Overall, that title just seemed to be bad game design, in one of the most sadistic games ever, especially with the turtle drowning >.< . Ha, the funny thing is though, I still hold it immune to dislike with the help of nostalgia and legacy console appreciation. Again, the variety was in fact very welcome with the relief of not drowning instantly for once.
The only underwater portion I remember to specifically dislike would probably be in Red Faction where you have to drop the sub through the ice. It just seemed to take away so many gameplay features, and felt very underpowered when having take on multiple subs, against the commando set of tools you are accustomed to. If it had a greater sense of mobility it would probably be quite a bit more enjoyable.
Sorry for the overlong explanation, I guess my main point is for gameplay variety, including underwater levels, don't hate; appreciate!