I swear that fucking Piano took ten years off my life.

Anyway, my first thought was the Well in Ocarina of Time. Especially Dead Hand, holy shit that guy...

The hidden research lab in Metroid Prime 3. Oh boy, that freaked me out.

Also, the Intro in Super Metroid:

Can't think of anything better than that awful piano.

The first time I saw the Giga Bowser in Super Smash Bros. Melee I was so scared. I thought what kind of crazy fucked up mod is this?

The first time I played a haunted mansion level on SM World SNES. When you first figure out that the Boos only stop if you look at them. You first frantically try to get away fast but that just brings them closer.

Also when that hand grabs you in TLoZ NES and returns you to the begging of the dungeon. 

When you finally battle Metroids in nes and the big Metroid in snes. 

When you see an explosive dude in ZombiU and your instinct is to pull the trigger and you die. 

Waterwraith in Pikmin 2, nothing beats it. Having the previous caves followed pretty much the same route, Submerged Castle came off as a huge surprise. 

God damn Unagi... 

I was around 4-5 years old when I played this game for the first time. That asshole scared the crap out of me... He STILL creeps me out.

I would think anyone who has played it... can probably still remember the exact sound of that piano crashing after them as they ran from it.....

Maybe pathway 3 in StarWing on the SNES was pretty cool as well, the first time Andross reforms and it's into a demon head instead of his normal mannequin face... took me back a bit at the time.
https://youtu.be/oYjSoWeHrzI?t=6m12s


Lavander town....

Luigis mansion fighting a dead babies ghost in the crib... really no idea how that got passed the Nintendo family friendly ideals

I'd have to think more about it, but over the years with Nintendo there have been a great many shock moments where you actually felt like you had to be quiet at your console while you played them... Samus Aran X comes to mind when she is tearing the station apart looking for you, you are powerless... hiding away from her... knowing that a single noise could alert it to where you are curled up in your ball just hoping it will leave you be.... literally the game puts you into the fetal position until the thing which would annihilate you moves on. Hard to replicate moments like that, because it firstly requires years of building up a bounty hunter like Samus as a fearless being.... only to show you absolutely powerless against a foe that would kill you just for the sake of it.

Nintendo game... with a enemy killing machine as a foe

Mamma Mia!

GFS Valhalla - Metroid Prime 3

... DARKNESS COMING...

Also... the first time I heard the Unown Signal in Pokémon Gold I shat my pants.