Just a few from the top of my head:
-the end fight in Bionic Commando
-beating Mike Tyson in Mike Tyson's Punchout
-the ending to Zelda- A Link to the Past
-the ending to Super Mario 2- still on of my all time favorites, I fucking love that music.
-the cable car ride with Aeris in FF7. Aeris getting skewered. The heartbreaking realization that there's NO WAY to bring Aeris back.
-beating Super Mario Bros in 12 minutes
-the betrayal mission in Tie Fighter (mentioned a few times above, you guys are SO right on!)
-skipping school to play Ultima Underworld and realizing what true video game addiction is

-the feeling in my gut when I ran across an alien in X-COM in a dark corner with only 1 soldier who had only a few shots left... in a pistol...
-raiding a witches coven in Darklands, and then stopping the Apolcalypse
-all of Sam and Max
-making grog and the insult duels from Monkey Island