Psychonauts Has a lot of awesome levels but probably my favorite for the combination of gameplay and awesomeness is lungfishopolis.
In fallout 3, the vault where they tested maddening noise generators on a bunch of innocent musicians and I keep expecting crazed violinists to attack me or SOMETHING but instead it's just constantly nothing all the time freaking me out further.
Thief 3: The Cradle.
Old world blues, a new vegas dlc, is pretty dang funny.
In Darkest of Days, at one point you get to use a future machine gun with person-seeking bullets in the civil war.
The best part of indigo prophecy was where it turned the tables on you and you had to IGNORE quicktime events to survive, because each time you succeeded at using your psychic abilities you saw monsters lurking and it drove you crazy.