Scary: DCA from the North. Aspen: Get off the plane there and look around, you are surrounded by mountains and wonder how in the heck your going to get out without smashing into one of them. LGA gives me the willies when I see how the planes land on top of that sea wall.
For the view: DCA from the North. SFO over the bay bridge approach is neat, but the easterly take off that turns north, around the city then out to sea over the golden gate is breathtaking.
For the ride: John Wayne. Or any approach with a last minute go-around call.