Freespace has a pretty good intro. Especially considering how old that game is. Freespace 2 isn't too bad either though not quite as good as the first. It's a much better game though. One of the best, if not the best space combat game ever made.
Myst 4 , revelation can't seem to find any youtubes of it [edit] there are a few < click here for video link > but there's not the really good one i like.
i just remembered the other best title music: Doom 3. doom 3 and deus ex have music so good, that just listening to it makes you want to buy / play the game.
The command & Conquer intro Not really spectacular, but still pretty cool considering the setting. XIII intro Pretty cool style, moves into the game nicely. Rome Total War : Scipii I didn't care much for the original intro to the game, but I really liked the voice and monologue for the scipii one. There's a few others that are a nice to watch too.
I'd say the two best intro movies are the ones for Starcraft: Brood War, or Dawn of War. Excellent viewing right there.