American Idiot, by Green Day. One of those REALLY LOUD CDs, but it does make me grin. Makes the walls shake, too.
I think the Nirvana classic "Smells like teen spirit" takes some beating in this department also.
And there's always Beethoven's 5th Symphony. Probably the most memorable intro of all time. Or perhaps Bach's Toccatta and Fugue in D minor.
And finally Saint-Saens Piano concerto no 2. I love the intro, but I hate the second movement.