Spinner has put together three pretty interesting lists about music in the movies – Best Opening Songs: 25 First Tracks in Film That Left Us Wanting More, Best Closing Songs: 25 Final Tracks in Film That Made a Lasting Impression, and Best Opening and Closing Songs in Film: Readers' Edition. The best choice in all three lists, in my humble opinion, is “Don’t You (Forget About Me)” by Simple Minds from The Breakfast Club. One of my favorite opening tunes in a film is The Replacements’ “Bastards of Young” as used in Adventureland.
I’m not sure if I could pick my favorite opening and closing songs from a movies (other than the aforementioned Simple minds song), but the movie that I think made the most affective use of music was Pump Up the Volume.
Speaking of that Christian Slater classic, I performed this monologue in drama class in my junior year of high school. I still find this speech moving 20 years later.