Castlevania 64 - https://www.youtube.com/watch?v=kekitaCO6Wk

Forsaken 64 - https://www.youtube.com/watch?v=DxEmVInRWek

Bad Rats..

I love intro from the first Left 4 Dead. Could watch it over and over.
Not so much from the second though

from these i've only played FC3... so my vote goes there...

Old but still good:
Homeworld

Mafia 1

Rendered in game or pre-rendered?

Dragon age, if we take the full intro, not just video

Other: Pokémon Alpha Sapphire and Omega Ruby.

Inb4 hate.

Squishy: The Suicidal Pig

Why, who could forget about Battleblock Theater?

Mafia: The city of lost Heaven: https://www.youtube.com/watch?v=wBfL_Fh-Q9U
Return to Castle Wolfenstein: https://www.youtube.com/watch?v=Fz2C9bxikNE
Dragon Age: Origins: https://www.youtube.com/watch?v=Q94rdV9M1M4

Every Borderlands game!

Final Fantasy 8
MGS2 Sons of Liberty
Bioshock
nothing else really comes to mind

It's a PS3 game, but The Last of Us. Goodness, it made me cry :(

My answer whenever this comes up. Armored Core: For Answer

Wild ARMs 3 - https://www.youtube.com/watch?v=SvoueEiVyWE
Xenoblade Chronicles - http://youtu.be/w09yYmqEu0E

Freespace

https://www.youtube.com/watch?v=NJ-xcgBL1mY

Sonic Boom

Dawn of War - https://www.youtube.com/watch?v=RtAH7kGEqic