For the humour:
At the end of deadly creatures, you're playing as a scorpion playing fighting a man with a shotgun. To defeat him, you have to sneak up his trouser leg and, y'know...hit the weak point for massive damage XD. The first time you do this, he stumbles into a shelf , knocking a terrarium off it and releasing the Gila lizard inside. Unfortunately for him, its one of only two poisonous lizards in the world and it thinks that he looks tasty. As he deals with the lizard, the scorpion crawls out of his trouser leg. And chuckles. The expressions on the man's face are priceless.
For the emotion:
Aeris's death. 'nuff said.
For the sheer awesomeness:
Seeing an epic creature the first time in Spore
When Cloud cam down the stairs of the Shinra building on a Motorbike in FF VII
Dropping a giant cave troll into a pit of lava in Resident Evil 4. Made more cool by the fact that if you got too close while was flailing around, he would pull you in as well .