Sometimes using a very sweet or banal song with a super violent scene has been useful, for example "Stuck in the Middle With You" in Reservoir Dogs, "Danny Boy" in Miller's Crossing and "In Dreams" in Blue Velvet. Anybody know any others like that?
I don't know if this is a song, per se, but I loved the Requiem for a Dream theme, the one that keeps getting played in trailers, like LOTR. the piece, whatever it is, is so beautiful. other than that, I'd have to say Back in Black in the Iron Man opening.
My favorite use of a pop song in a trailer is in the trailer for The Layer Cake. Unfortunately the use of the song in the movie was not used as well. We've seen someone get beat up to pop music before (like in Goodfellas.) I don't know the name of the son. It's the one that goes "As I try to make my way to the ordinary world, I will learn to survive." Which reflects the films story perfectly.
Speaking of Scorsese. "Rubber Biscuit" as used in Who's That Knocking on My Door? was an inspired moment. I'll probably think of a better candidate later but that's my best under duress.
If you like musicals the number "Anderson, Topeka & Santa Fe" from a Judy Garland film (I'm not gay I swear * not that there's anything wrong with that) called The Harvey Girls is simply epic in scale and entertainment. Everytime you think it's ending it turns into something else. Unfortunately the rest of the film isn't anything to sing about.