Allow me to show you a few examples:
Right as the main menu comes up players of Rome: Total War are treated to this little melody. Evoking images of huge battles, lost glories and heroes of old all faded in the mists of time, I still think it surpass any of the music found in any other Total War game (Even Rome II).
Command and Conquer: Red Alert wasn't my favorite real time strategy series, but I have to admit that the opening credits song does a good job of making you feel like a badass leader. Incidentally, I wonder where they got that audio clip of the officer shouting orders? It sounds like something out of the first World War...
Persians in space would be one way to describe the music of Homeworld. While the original is still my favorite, Homeworld: Cataclysm was a worthy successor if only for the terrifying foe you must face. Forget Dead Space! If you want a horror game set in a science fiction backdrop this is the game for you, but I digress. Choosing one song is difficult since all of them are all so excellent. For the sake of brevity though here's the track from the first level.
MechWarrior 2 could have gone with something generic and still gotten four out of five stars, but rather than settle for OK the composer opted to give the game a distinct vibe with tribal sounds. It's quite a unique score (especially for the time) and actually has a large number of distinctly memorable tracks. Here's one of the first you'll hear when you climb into the cockpit of a clan battlemech:
While supposedly inspired by the "Black Company" series of fantasy novels by Glen Cook, Myth: The Fallen Lords always felt to me like an answer to the question; what if Frodo and Sam failed in their task and Sauron was able to reclaim the One Ring? That's the plot in a nutshell, and boy do they ever do a good job of capturing feelings of melancholy and desperation from the moment the game starts. There's more to it though, under the sadness runs a current of hope, bravery and grim determination in the face of overwhelming odds.
Moving on to something a bit more light hearted, here's the main menu music for Saints Row: The Third. Personally, I'm more of a Grand Theft Auto guy, but I have to admit this music is a great way to get players psyched up for what's to be found in this series. A perfect blend of the coolness of rap mixed with the enthusiasm of rock, superbly represents what it's like being one of the Steel Port Saints.
This last entry is pure nostalgia so take it with a grain of salt. The Space Quest theme predates dedicated sound cards so what your going to hear is a remastering of sorts. Nevertheless, it still represents the original very well; complete with adventure, mystery, intrigue as well as an undercurrent of whimsy that makes this venerable collection of titles distinct.
I should conclude by saying that tastes in music are highly subjective. So while you might not find my choices particularly agreeable, I imagine you will find they reflect a certain kind of preference when it comes to music in gaming. Especially since it can be (and often is) one of the things we remember most after setting down the controller...er...keyboard and mouse...? Well, you know what I mean.