I have been playing Zelda ever since I was little and I've beaten every game with ease. My personal favorites are majors mask and ocarina of time. But the newest Zelda, "skyward sword" scares the shit out of me! I was fine playing up untill I hit the first spirit trial. The first time I tried and failed, I actually peed my pants a little... And ever since, I've been too scared to even pick up my wii mote...

Being all alone, eerie music, strange aliens and architecture, and down to your last 20 points of energy.
THAT scared me.
Made me a pest to my older sister, because i would always fetch her boyfriend at the time to help out before i was dying, he was a lot better than me with those kind of games.
But I agree with MissSorel; Dead Space is pretty damn scary.
I know that, I'm making an example using three games.
I know right? Dead Space was really scary towards the end. Dead Space 2 lost all the scary when they decided to make Isaac Clarke speak. I know it might be better for story progression, but I liked it more when he was silent.
He does not seem scared anymore. If the character is not scared it kills the whole aspect of there being fear. >:(
And unrelated but I want a Moogle! Like really, which is strange lol :P
But I could make due with a ordinary Tonberry :D
And Majora's Mask isn't scary but it sure as hell is creepy.