The fight with Koloktos, which is one of the best boss fights in Zelda history. You even have to use a BFS it itself uses in a similar manner to using weapons dropped by enemies in The Wind Waker, a feature absent since then.
The Ancient Cistern. Yeah, it's a little annoying at first, but then you realize that Link is practically acting out an Indiana Jones movie. Between the whip and the trap upon finding the idol/big key it's something to behold. Oh, and it's hard not to laugh when you're being chased by Zombie Bokoblins up a rope.
Link going against an army of Bokoblins, Stalfoses, and Moblins in order to get to Zelda and Ghirahim. The numbers of the Bokoblins and Moblins have to be seen to be believed. Link then fights Ghirahim in his One-Winged Angel form, having to push him off the edge of the platform he is on. Ghirahim unleashes everything on Link right after they are on ground level, including wielding a HUGE sword.
At this point, a single strike of the Master Sword can send three-four Bokoblins flying: these are the same Bokoblins which were probably a challenge in the early part of the game when most players are still learning to use the motion+ controls. This makes this scene the best one to display Link's growth in strength and skill, which is helped by the fact that Ghirahim himself does not even try to hide to his own troops that he is sending them to a certain death
The fact that they can't even block without being knocked back only adds to it. And the fact that the block doesn't stop Link's attack anyways.
At any time during that sequence, use a horizontal Skyward Strike; it will cleave through tons of them like they're paper.
The Death Glare he sends the storming army of Bokoblins alone justified the choice to make the graphics a halfway point between WW and TP. He looks as badass as in TP, but that's the sort of facial expression that can only be properly shown via cell-shading.
Additionally, the final strike on Demise. Link takes a flying leap into the air, spinning while lightning strikes his sword, then slams down into the middle of the biggest bad's chest. A moment later, lightning flashes in the background. Epic.
This one he gives to Ghirahim after he interrupted a rather heartwarming moment beats every single one. THAT is your signal to crap your pants and run away scared out of your wits because Link will KILL you.
The final "Jam Session", playing the song of the hero, and hearing the main theme for the series play for the first time in the game; it has a special meaning because this is the first Link, which means it's chronologically the first time it's heard.
Zelda. Zelda herself is a CMOA. After falling from the sky into a world she wasn't entirely sure existed, she managed to get herself through a monster-strewn woods while being actively pursued, fought her way through the Skyview temple alone and unarmed, and made it through Eldin Volcano. Yes, she ended up getting captured and would have been caught by Ghirahim if it wasn't for Impa, but the fact that she made it there in the first place is pretty amazing. And, even after all that, she's still willing to sacrifice herself for the good of the entire world. You know someone is pretty damn amazing when even Demise gives some reluctant props.
Pulling the Master Sword out of its stone is something Link has done in many, many Zelda games to date. In Skyward Sword, however, it is actually YOU, the player, who does the pulling thanks to Wii Motion Plus. And it is a glorious moment.
Who ever thought that Impa would be a Badass Grandma? Surviving thousands of years in the Sealed Temple so when Zelda arrives she can set in motion the right things. THAT is awesome.
Ghirahim finally, finallyreceiving a well-deserved death at the hands of Demise. Sure, it was likely part of his plan, and he seemed to be enjoying it, but just knowing you're finally rid of the Smug Snake is gratifying.
The moment when Zelda upgrades the Master Sword into the True Master Sword. After hours of gameplay, Zelda fans finally get to hear the Master Sword theme. And it is epic.
Who could forget the Death of the Imprisoned. After sealing him so many times, Link finally uses the full power of the Triforce (for the first time since the end of A Link to the Past). To drop a piece of Skyloft onto The Imprisoned just as he was getting out of his recent seal.
Link's Big Damn Heroes moment at the Temple of Time. At first, Link can only watch as Ghirahim fights Impa and Zelda, but then he realizes that the barrier preventing him from jumping in is weakening, so what does Link do? He jumps into the air, and slams right into Ghirahim just as he's about to finish off Impa. Made even more awesome if you choose the Ironic Echo dialogue choice.
After Link has been robbed of all his items, forced to go through Eldin Volcan without so much as attracting the attention of Bokoblins, getting the bombs. You got your Bomb Bag back! Time to blow something up.
Real life example: Many composers who prefer MIDI music to orchestrated music say this because they believe that Variable Mixing is impossible to do with orchestrated music. That would mean that Skyward Sword did the impossible, because the Sky overworld theme is completely orchestrated, but uses Variable Mixing. Even the MIDI tunes in the game, which are actually recorded like the orchestrated music, use Variable Mixing.
When Ghirahim kidnaps Zelda after her awakening, Link is thrown aside like a rag doll. Groose, the very definition of The Unchosen One, steps in front of him, ready to protect Grannie and fight.
One for the animators/programmers in this game as well. With nary a spoken word, they managed to convey more emotion and character in Link than some characters achieve with hours of spoken dialogue. Through just his facial expressions, you can feel Link's joy, sorrow, fear, and rage, and it paints the picture of the most complex and heroic Link yet.