There are a few that stick out in my memories. Beware of spoilers for old games, but not the newer ones.
In
The Legend of Zelda: Ocarina of Time, two scenes in particular stick out. During the Ice Cavern, there is a scene where Sheik teaches Link the
Serenade of Water. Out of the 12 songs in the game, that one was my favorite, and the lines from Sheik are pretty meaningful. The second scene is right after the final boss, when Princess Zelda talks about her errors when her and Link where young, the tragedy that befell Hyrule, and how she fixes it by sending Link back to the past, in her words - "...Where you are supposed to be, the way you are supposed to be..." There's a moment, just before Link floats away where she opens her eyes and stares at you for a second, and then the camera pans up. Even in old N64 graphics, it's a great scene.
Final Fantasy X has several great scenes, and is the best FF of any I've played (1, 7, 8, 10, 12, Tactics Advance). My favorite scene is towards the end right after the big revelation.
As much as I enjoyed
Heavy Rain and
Beyond: Two Souls - entire games that are like very long cutscenes - nothing in particular sticks out.
The opening cutscene to
Anno 1404, an economic strategy game, is absolutely fantastic. That art style (is it watercolor?) is just great to look at.
I'm sure there are tons others, but that's all I can think of for now.