Probably Metal Gear Solid 2, the opening scene on the tanker is pretty badass. That entire section shows how much of an improvement the game is over MGS1 and the rain effect on the deck still looks good today.

This is a hard question, because I could probably think of another one later, but I have always loved the Resident Evil 4 intro, even if I am not the biggest fan of that game. It sets the atmosphere perfectly, it gives you the very basics of the game, and then has the audacity to throw you inside the village and expect to survive or bite the dust. And trying to hide or run will only penalize you with secret cutscenes and harder enemies. 

By the time you beat the opening, not only you have learned what to do, you are also conscious on what's about to expect. It's fantastic. 

A different one I liked was Alone in the Dark from 2008. The game was extremely mediocre all around, but it was one of my first 360 games and that point where you are just blinking like crazy to escaping a collapsing city in a taxi was just plain awesome, honestly. 

This is another awesome reply, for the same thing I mentioned with RE4. Not only it sets the atmosphere, tone and details in a perfect fashion, and it teaches you basically everything gameplay-wise, it also provides you the proof of evolution in every area when compared to its predecessor. 

Not many on VGC will have played it, but the first hour or Lost Odyssey is amazing!

He is still alive in the ending tho

I loved the ending to that game too. One of my all-time favourite games!

It's very good! Luckily the reputation of the game increased as time went on

I got it for Christmas 2012 and I was hooked until the very end. It's been my favourite Metal Gear game since I first played it.

i remember Sunset Overdrive being pretty hilarious.

no experience at all doing anything athletic, but to hell with it, i can shoot zombies while front flipping 20 feet into the air off of a car with ease

Was just going to say the exact same thing!

I also want to mention Castlevania: Symphony of the Night.  Replaying the ending of Rondo of Blood as Richter then storming the Castle as Alucard with the awesome soundtrack accompanying you was stunning and made it obvious early on that SotN would be something special.