Just Business. A gunfight and an on-rails motorcycle chase with the aftermath of the bad deal with the Russian Mafia come together to form one of the most memorable missions in the whole series.
Reuniting The Families. The Grove Street gang decide enough is enough and they must get the families back together to push the Ballas back. The meeting goes to shit when SWAT shows up and CJ stays behind in order to rescue Sweet while Ryder and Big Smoke ditch him. CJ fights his way through the motel by himself, finds his brother, and they attempt to escape, but a police chopper shows up. What does CJ do? He shoots it down with nothing but a machine gun. Ryder and Big Smoke come back to save CJ and Sweet and the gang drive wildly through the streets to shake the cops while you pick them off through the roof of the car. Cops try to jump onto the hood of the car to punch you in the face, Big Smoke drives through a freaking car wash to blind the cops, and the gang plays a game of chicken with a low flying helicopter that tries to slice you up with its rotor blades. Big Smoke describes it best at the end:
Big Smoke: Shit! That's gonna be a hell of a story to tell when we passin' the blunt!
Burning Desire. Yes, CJ did start the fire at the Vago's house in the first place, but rushing in a crumbling building to save Denise from a fiery grave was still very heroic of him.
End of the Line. There's the one-man raid on Big Smoke's crack palace, and most notably chasing Tenpenny - who's driving a damn fire truck, mind you - as Sweet desperately hangs on to the ladder, not about to let him get away with everything he's done. And after having Tenpenny torment you and your loved ones for about the whole game, forcing the corrupt bastard off the bridge overlooking Grove Street is immensely satisfying. Even with the release of Grand Theft Auto IV, it's still the most exciting end to a GTA game yet, and debatably the most satisfying until Grand Theft Auto Vand itsOption C.
Hands down, "End of the Line" is the most satisfying ending mission to any GTA game before or since. You smash into a barricaded crack compound using a SWAT riot tank, running down about a dozen enemy gang members in the process, then storm your way through four floors packed to the brim with assault rifle-toting Mooks, culminating in the long-awaited showdown between CJ and Big Smoke. Once you've taken care of him, Tenpenny sets the place ablaze, forcing you to make your way back out of the compound, putting out fires while contending with even more mooks until you escape Just in Time as the compound gets blown to Kingdom Come. Then you chase Tenpenny's getaway fire truck going full tilt throughout most of Los Santos while Sweet clings onto it for dear life and rioters firebomb your car from all directions. Once you've finally got Sweet safe in your car, he takes over the driving while you fend off just about every faction in the city chasing you down. It all concludes when the fire truck drives off a bridge onto Grove Street, where Tenpenny is left to die from his injuries (or, more likely, to be torn apart by the rioters who want him dead). And remember, this all takes place in one mission. Needless to say, the HSQ is high on this one.
From the LP: Tenpenny decides to have Sweet beaten up while in jail. Toreno, who in the LP is a god-tier Magnificent Bastard, calls him and tells him to knock it off, demonstrating he has power and connections Tenpenny can only dream of.
The ultimate Moment Of Awesome however, is the story how Sweet put the Families on top of Los Santos. Read everything here.
Is no one going to mention the final mission before CJ is forced to leave Los Santos for the first time, "The Green Sabre"? He's just found out from Cesar that Big Smoke and Ryder have betrayed him to C.R.A.S.H. and he's come to the all out war zone between the Ballas and the Grove Street Families only to find Sweet injured and the police closing in on him. Something inside him seems to snap and he annihilates the Ballas as he pops out of cover, fueled by rage and grief.
YO! BALLAS! I'M TAKING YOU MOTHERFUCKERS! YOU HEAR ME?! I'M TAKING YOU ALL DOWN, BITCHES!
Stowaway for being a mission that wouldn't be out of place in a James Bond film. First, Carl jumps on a motorcycle, make his way inside the plane wich is taking off while being shot at by government agents and avoiding rolling barrels. Then you walk to the front of the plane while avoiding more barrels and killing agents with whatever your melee weapon is at the moment, before picking a parachute, placing a satchel charge on the plane and jumping out of it as it explodes.
Vertical Bird. While there's no denying that it's a difficult mission, think about it: you infiltrate a military aircraft carrier by boat like James Bond, stealth your way through the heavily armed guards and steal a freaking Harrier style jump jet. Then the game takes an Ace Combat style turn whereupon you can take out some water bound targets. And if you make it through all of this alive, you can take the Hydra home to your own personal airfield and use it whenever you want. A well-earned reward for a job well done.
The one where you break into Area 51 and steal a jetpack.
How about "A Home in the Hills", the mission where you parachute onto the roof of the mansion, and systematically fight your way through? Slightly dampened by the chase sequence at the end in the crappy car, but still awesome.
And the one where you fly behind a plane, JUMP OUT, AND FREEFALL ONTO SAID PLANE AND CLIMB IN.
Most of the rail-shooting missions of the game are pretty fun. Mostly because all you need to do is concentrate on shooting without worrying about crashing, but also because it feels like a response to your likely frequent griping along the lines of "Oh yeah, [Sweet/Smoke/Ryder/whoever], you want me to drive for a change?"