Though I'm not a huge fan of the game, Left 4 Dead has a fucking fantastic intro movie.
Shadows of the Damned does too, mainly because of the music and the abundant darkness. Castlevania really needs to take environmental cues from this game.
Oh, and how could I forget Neo Contra?