You're probably going to get mixed opinions on this one. I enjoyed SS a lot, I know a lot of people who didn't really like it. The only big downside (s) for me is the lack of a giant world, and the flying kind of sucks.
That said, the characters are all great, the story is really good, and some of the dungeons were really nicely designed. (Ancient Cistern is still probably one of the best designed in the entire series)
Never played Orcarina. Played Twilight and didn't like it much.
LOVE Skyward Sword. It's really cool the way you actually have to slash with the controller, it's not too strenuous either except for certain fights where you're swinging like crazy and your arms get tired. As mentioned, everything was well done. 10/10 get it.