Hmm, lessee… What games have I played that had death scenes in them?
The first that comes to mind, obviously, is Aeris in Final Fantasy VII. TBQH, I hated her so it wasn't quite as sad for me as it should have been. However, even though I only got around to playing this game myself about a year ago and therefore knew a lot of major plot elements beforehand (like the fact that she's killed mid-game), it was still kind of sudden and I was still able to appreciate the drama of the moment. Cloud's reaction is what's important anyway, and even though I didn't feel much loss at her death myself, I felt plenty of empathy grief for our favorite blonde, spiky-haired swordsman. There's a reason that scene is so classic.
While we're on the subject of Final Fantasy, I guess you could count the end of FFX as a "death scene" for Tidus. Although, of course, the good ending of FFX-2 fixes everything which is the only reason to ever play that game. Probably extremely biased here (X is my favorite FF) but I've always thought it was pretty emotional. Intangible goodbye hug = saddest thing ever. And Yuna's heartbreaking speech afterward sticks with me to this day.
…OOH OOH! I just thought of a good one: Legend of Zelda: Ocarina of Time. The Great Deku Tree. It's a brief cutscene, but I've always thought it was amazingly executed. His powerful final words, his slowed speech, the creaking of his branches as his leaves start to fall and the color seemingly drains from his bark… And then, Mido's emotional reaction at the death of their leader/father-figure/guardian. The Tree's body remains in the clearing, and you're reminded of his sacrifice every time you come to Kokiri Forest. But then the Deku Tree Sprout appears, and his predecessor's death is given even more meaning as a new era begins.
…I freaking love Zelda.
Also from Zelda: Impa from Skyward Sword. She's just like Odysseus' dog, waiting all those years just to see her master one last time. D':
Not-so-great deaths? The only one that immediately comes to mind is Axel. I've never played Days, but Axel's death in Kingdom Hearts 2 was still devastating to me because WHY DID IT HAVE TO HAPPEN? By far the coolest guy from Organization XIII and all I remember from that fight is him having to die because "it was the only way" or something equally convoluted. (Okay, okay, I admit I don't remember much of what happened at that part of the game. I just remember being upset by it because Axel was coooool. XD Then seeing his gravestone in that room toward the end just made it even worse because SERIOUSLY WHY.)
Ooh, also, I'm still playing through Final Fantasy V, so maybe it'll seem more meaningful later, but so far Galuf's death is kind of similar to Axel's because it just left me wondering WHY. I don't want your granddaughter on my team, Galuf! Give me back my hilarious, bad@$$ old man character!
I feel like I'm forgetting some deaths, though. Did anyone important die in Tales of Symphonia? Or ToS: Dawn of the New World? … Suikoden III? … What about other Zelda games? If I think of more, I'll be back.
(If non-RPGs count, I guess I should also praise Halo: Reach for actually making a rather emotional ending by throwing the player into a hopeless situation where you're forced to die a hero. I'm not generally a huge fan of FPSs, but that was a pretty cool game because of that moment.)
