Some ones that I've been impressed with.

• Relative to the technical limitations of the time, maybe the title music for Solstice for the NES, a Tim Follins creation. The sound hardware of the time imposed pretty severe limitations on what one could do, and Follins was famous for finding ways around them. I didn't hear this until many years after it was actually done, and it's decent even in absolute terms:

  https://www.youtube.com/watch?v=4_gObHt1uZA

• The music for the Civilization series has been pretty good. Here's "Baba Yetu" for Civilization IV:

  https://www.youtube.com/watch?v=IJiHDmyhE1A

  First video game piece to ever win a Grammy award, for "Best Instrumental Arrangement Accompanying Vocalist(s)". Performed by the Grammy-winning Soweto Gospel Choir with the Royal Philharmonic Orchestra.

• In terms of openings that really do a pretty good job of building a lot of intensity and setting the stage for the rest of the game, I think I'd give credit to some Bethesda games, both of which involve attempts to execute the main character: Skyrim and Fallout: New Vegas:

  Video

  Skyrim: https://www.youtube.com/watch?v=vsRA5BG3N8E

  Fallout: New Vegas: https://www.youtube.com/watch?v=BxOiVxTB0KY

Blizzard's cinematics always impressed back in the day. SC, D2, War3, and then WoW.

I like games that just drop you right into the action

Although I prefer the sequel more, personally, Sonic Riders has my absolute favorite opening. I also think the Sonic Unleashed opening cutscene when you first start the game is amazing as well.

Bet you can't tell I'm a sonic nerd. /s

Hey, you. You’re finally awake. You were trying to cross the border, right? Walked right into that Imperial ambush, same as us, and that thief over there.