I miss the self play, which goes back to the arcades.
NES had several and here and there you used to see a few PC games.

This was back when I lived in a bottle and I used to sit there for hours watching Tomb Raider play itself.

God I miss those days.

Ocarina of Time had, for its day, an incredibly evocative title screen and music.

Wind Waker too.

I'm sure I'll think of examples that aren't Zelda games in time.

Life Is Strange
Contrast
Remember Me
Far Cry 3: Blood Dragon
The Talos Principle
Dragon Age: Origins
Planescape: Torment - sorry, but this game really is the answer to each and every thread! ;)
Gothic
Call of Juarez: Gunslinger
Fahrenheit / Indigo Prophecy (unsurprisingly, as the theme is composed by Angelo Badalamenti)
Stick It To The Man (although it's kind of cheating by using a well-known classic, and they're not even the first ones to come up with that idea)

Coolest intro and main menu ever (even if you don't necessarily stay in it for long):
Brutal Legend

Monty on the run, By Rob Hubbard on the C64.

Well, now I have to listen to "Baba Yetu" again!

There are quite many titles that fit but I find that after a hundred times I start skipping straight into the game on most days, still occasionally stop and enjoy.

Too many too list but in the early 90's my Amiga doubled as a music player. I would load games just to listen.

The only one I remember at the moment is for Unreal 2. I like the music and the video too.

Unreal Tournament 03
No One Lives Forever

Crusader: No Remorse - Andrew Sega, AKA Necros of Straylight, composed this brief title theme and it's one of the best pieces in the game. I have exported patterns from the MOD to use as a ringtone in the past.

Dune - The first time I heard Stéphane Picq's title music for Dune, I was amazed at how well it fit the setting of the Dune universe. To me, this tune embodies Dune more than any other from any media.

Little Big Adventure - The title theme by Philippe Vachey is a masterpiece which takes the listener on a voyage to distant places all by itself. I love that oboe.

MechWarrior 2: 31st Century Combat - Just as Stéphane Picq nailed the feel of Dune, Jeehun Hwang has done the same for Battletech with the MechWarrior 2 soundtrack. The music for the introduction sequence is still chilling and poignant.

Mirror's Edge - I love downtempo and loungy electronic music so the title theme, composed by Magnus "Solar Fields" Birgersson, suits me well. It's so calm and peaceful.

One Must Fall: 2097 - Composed by Kenny Chou, AKA C.C.Catch of Renaissance, the title theme is my favourite from the game. I could listen to it on repeat without getting tired of it, but I could say the same for most of Kenny's music.

Starpoint Gemini 2 - The title theme is licenced stock music, but it's really good.

Unreal - "Flightcastle" by Alexander Brandon, AKA Siren of Straylight, is a short but powerful compo that perfectly accompanies the castle flyby sequence and still gives me goosebumps.

Wing Commander - David Govett's heroic-sounding fanfare should be instantly recognisable to any gamer. I can't help but straighten up and stand a little taller whenever I hear it.

Hm many....

• Baldurs Gate, especially II and ToB, love the theme songs. also PST. and NWN, NWN2 (Storm of Zehir), Dragon Age, Tyranny, Witcher 1...pretty much every important RPG it seems ;)
• Unreal - The castle flyby scene, its just instant nostalgia
• Deus Ex HR/MD/Shadow Run series
• Dishonored 2
• Stalker SoC, Original menu theme
• Myst III and IV
• Marathon 2 and Infinity
• Shadow Tactics

Mass Effect 1 & 3 I stayed on the Title Screen, the theme on each are quite emotional.

ME1 - Giving a sense of awe and wonder.

ME3 - A surreal sound giving tinges of hope while seeing earth in the background being bombarded from the Reapers.

Two Worlds
Probably the only one where it happened a lot back when I played it.
Civilization IV, a few times, yes.
Others, not so much, maybe a couple of times, but nothing to really stick in mind.

Same here, but Amiga version.

And you probably already know this amazing cover version.