I'm surprised that there's been no mention of the Myst III [http://www.youtube.com/watch?v=nEQQ8d3bQ68] soundtrack here. It really is an incredible game with an incredible soundtrack to it. I remember getting goosebumps as I watched the intro to it, just because it was that well directed. Hell, the entire game was like watching a good film.
(edit, whoo, someone else got there in the time that it took me to post this! I agree with them)
And one that's managed to stick in my head, at least, is the soundtrack from LBA2 [http://www.youtube.com/watch?v=X3YMfaqvWBg]. My earliest gaming memory which still contains one of the most wonderful little theme songs in existence. Yes, I can still sing the ferryman's song.
And there was the Roman Settling Theme 1 from Settlers IV [http://www.youtube.com/watch?v=o9plP_RSC8U&feature=related], but then you'll all find that one annoying. Myself, I found myself going around school humming it for far longer than was necessary, just because it was catchy. Seriously, click that link, flick directly to 0:45, and then listen to it from there without getting it stuck into your head for the next 3 days. Those who found that annoying, though, should listen to Mayan Standard Theme 1 [http://www.youtube.com/watch?v=xVGsYOdVjg4&feature=channel_page].