A song that always picks me up and I always end up singing along with is "No Rain" by Blind Melon. Another song that always has an affect on me is "November Rain" by Guns N Roses. Ah, to be a young, depressed college student again.
I agree. "Best of Both Worlds" was awesome. It was still early in the show's run, so it wasn't totally impossible to imagine killing off Picard, making Riker the new captain, and making Commander Shelby the new First Officer.