The later stages of the firefighter profession have your sim pull one CMoA after another
The ghost hunter profession
The fact that Bigfoot is the supreme master of everything. Need somebody to do your gardening? Bigfoot! need a flower arrangement? Bigfoot. Need home repairs? Bigfoot! need a makeover? Bigfoot. Need a robot? Bigfoot!
The horse death animation. The Grim Reaper gets on the horse, strikes a pose, and rides off into a portal.
When a sim with "Brave" trait beats up the burglar.
Getting Pangu's Axe, without cheating. The scene itself is just amazing.
Sims 3 "Dragon Valley" (All of it) if you're willing to pay the DLC, you get a neighbourhood descended from medieval culture, filled with Elven people and you can get pet Dragons that you can use to play with, freeze your needs, or set fire to your enemies. If you get the premium set, your Sim can practice archery.