The opening of the game contains this. You save the USS Khitomer, and you get back to your old ship... to find out that the entire senior staff you'd been talking to earlier are all dead now and that you and the ensign you met earlier on the Khitomer are now the senior staff on board. And it's time to go fight the Borg. And you do.
Specifically, the game opens with your character's first Crowning Moment of Awesome. In the middle of a massive starfleet battle, your character is sent over to assist the U.S.S. Khitomer. Going on a killing spree against the malfunctioning Borg on-board, you proceed to rescue the ship. Their victory is triumphantly prevented, despite your ship's command staff being executed by the Borg, you assume command and then take your vessel around to rescue the other damaged vessel's personnel. From there, you head down planetside to rescue a bunch of colonists from assimilation and then bring your ship around to join the fleet in blowing up a damaged Borg cube.
In a nice reversal of the Star Trek movie, while you're in command of your own starship at the end of the battle, you're still only promoted to a lieutenant's rank. Your vessel is also one of the smallest and weakest in Starfleet. Still, you can understand why Starfleet stands up and takes notice of your actions.
You escort a Vulcan ambassador to the monastery at P'jem. Once you arrive, the Klingons claim that the "ambassador" is in fact an Undine (aka Species 8472), and try to blow you up to get at him. Turns out... they're right. You fight the "ambassador", first on the ground, then you fight the Tethys-class dreadnought he has hidden in orbit, your officers noting with some panic that your little light cruiser doesn't have a prayer of beating it alone. You shoot down the plasma torpedos the dreadnought fires at you, trying to hold on... and then an entire fleet warps in and blows away the dreadnought. This is the second storyline mission after the tutorial.
You're sent to meet a contact who knows something about what a rogue Klingon "Ambassador" is plotting. She isn't in the Ferengi-owned bar you were supposed to meet her in, and the owner is playing dumb. How do you get him to talk? You play Odo to his Quark, and inspect his establishment for safety code violations, starting a bar brawl in the process.
You get to save the Enterprise from B'vat. Yes, thatEnterprise, and you're in the past to start with because B'vat has kidnapped thatMiral Paris. Later on in the mission, the Enterprise shows up to pay back the favor.
The Enterprise has joined the battle!
B'vat's last project before being defeated was resurrecting the Doomsday Machine to use against the Federation. His men succeed in his stead. You proceed to use nuclear torpedos powered by material from the core of a destroyed planet to destroy - yes, destroy - the Doomsday Machine by firing straight down its gullet, practically like Kirk did, all while a massive Federation fleet engages an equally massive Klingon fleet to ensure the Klingons don't stop you.
Also in the same mission, K'Valk steals a shuttle to do a Taking You with MeHeroic Sacrifice on the Doomsday Machine. While loudly singing the Klingon War Song. He fails but he does enough damage for you to be able to take it out.
"The Sisko has asked that we entrust this task to you." No spoilers, but that entire sequence, full stop.
Hell, most of the things that happen in Cardie space are awesome to varying degrees. The climax of it all is worth the price of admission to STO by itself, however.
So you're sent on what seems like a routine mission to meet another ship. You find it under attack by a Borg sphere and ride to the rescue... and then, out of nowhere, Q shows up, rewinds time, and makes you fight the Sphere again. You again defeat the Sphere... and Q rewinds time again, only now replacing the Sphere with a wing of Probes and demanding you do it one more time... with feeling! And then the three Borg cubes show up. "Intense, isn't it?" And that's just the intro to the real mission.
New Objective: Tell Q to Get On With It.
The Reman Feature Episode "Cutting The Cord" in its entirety. Your ship blasts through the Tal Shiar's planetary defenses, and you beam to the surface along with tons of Starfleet ground troops to raise some hell. Unfortunately, thanks to some local Applied Phlebotinum, your BOFFs have landed too far away to assist you. Is this a problem for you? Hell, no. You then proceed to wage an almostOne-Man Armyfight against the Tal Shiar, while calling in additional Starfleet reinforcements and orbital bombardment from your ship. This sequence culminates in you calling in an orbital strike to destroy a massive Iconian gateway...which your ship does with a full spread of Photon Torpedoes. And the mission isn't even over yet!
"Colosseum", the mission just before "Cutting the Cord", puts you in an arena and forces you to fight with melee weapons, including a Vulcan lirpa - while the famous music from "Amok Time" plays.
"Boldly they Rode", pretty much all of it. From EVA walking across the Dominion Captured Deep Space Nine station, to the final battle and the ENTERPRISE-F'S Big Damn Heroes entrance!
Denise Crosby Special Guest starring for the 3rd Anniversary as Tasha Yar. And in the "Legacy of Romulus" expansion, as Sela.
The 3rd Anniversary Mission is a direct sequel to the Next Generation Episode 'Yesterdays Enterprise'. When the Enterprise-C dived back into the portal at the end of that episode, it was actually thrown even further into the future, where it was captured by the Tholians, who rule a large portion of Federation space in the altered timeline. The player characters alternate self, here just a freighter captain, and slave to the Tholians, teams up with Tasha Yar (Voiced by Denise Crosby), steal back the Enterprise-C, and fight their way past Tholian ships to the portal to return the ship to its final battle. And when it seems the Enterprise is outgunned, you receive backup from a Federation timeship from the 29th century.
The mission has additional awesome through out. Such as seeing B'Vat or Shon decide to duel a Tholian to the death just for the off-chance of the timeline being restored (and hence never living their horrible lives) and the sequence where you board the Enterprise (seeing the starship LOOM OVER YOU as you walk down the gantry) and TAKE COMMAND OF IT, issuing Tasha Yar orders. REALLY.
Also a meta example: seeing your three most senior Bridge Officers as NPCs in the mission and watching their dejected lives can inspire some players to want to restore the timeline.
In a case of Throw It In, the fate of T'nae in the original version of the mission was left ambigious. The latest version on Tribble, preparing for Legacy of Romulus has her as the Enterprise-C Science officer to reinforce the fact she is on the Enterprise-C and that this T'nae's fate is in the air.
On the Klingon side of the game, you're sent to assist a fleet of Klingon ships in battle. When you get there, the Klingons have already been wiped out...by the Fek'Ihri, who are the Klingons' version of the legions of hell. The arc progresses with you running the Fek'Ihri off of Qo'NoS, storming your way through Klingon hell, and finally taking down the Klingons' answer to the Devil. At the end, you receive a bat'leth just like the fabled Sword of Kahless, forged by Kahless himself to honor your achievement.
Operation Gamma: going up alone against a Jem'Hadar attack ship while in a shuttlecraft, after having gone up against several wings of Jem'Hadar fighters. All this to the background of a Starfleet/KDF fleet attacking the Jem'Hadar as a distraction to let you sneak into the Gamma Wormhole.
The entirety of the Romulan PC's storyline. Literally going From Nobody to Nightmare on the Tal Shiar. It really pays off in the the Romulan version of the Cloaked Intention Series which ends with Hakeev going Sanity Slippage then getting called out by Sela, and then getting blasted in the head by the PC with the best Pre-Mortem One-Liner you can imagine for the character.
From above, the mission where you Meet with the Tal Shiar member trying to get you to join instead of just killing you. Things naturally go terribly awry, and the enemy captain orders you to stand down. When you reply with "You can't take me down with one ship.", An entire Tal Shiar FLEET warps in. The implication that you're powerful and dangerous enough to warrant such a response is a Moment Of Awesome in and of itself, but as icing on the cake, your response is to signal a fleet of your own people and the faction you chose to ally yourself to decloak around you. The ensuing clash has enough weapon fire to be called a full scale battle
How about the announcement that the Voth, space dinosaurs, are going to have their own T-Rex Mounts that shoot lasers while their riders rock Power Armor.
The announcement that Michael Dorn is returning as Worf starting in Season 8. 
Sphere of Influence: Not only do you get to team up with Ambassador Worf, near the end you get to command him in battle as a Bridge Officer, alongside Captain's Shon and Koren, and A'dranna. Yes, you're commanding one of the most well-known characters in ST, along with the captain's of the Federation + KDF's current flagships. How many would have a chance to do that?
Your enemy? At least thirty Elachi ships, mostly of high-ranking types. Opposed by yourself + the aforementioned team in a powerful Carrier ship, the Enterprise-F and Bortasqu', and every other available ship from all three factions. Then you pretty much kick their asses.
Then afterward, Worf says it was an honor to fight alongside you.
Another subtle one from the episode. The new Romulan Flagship Commander? Tiaru Jarok, the daughter of Admiral Jarok from TNG'sThe Defector who gave up his career and later his life so that she could live in a peaceful Romulus. Romulus may be gone, but she's now defending the peace her people have on New Romulus in his name.
The Free to Play trailer qualifies. Not just for being really pretty and showing off the game. But also because of the monologue reminding that while this is an era of war, they are still Starfleet officers and their first duty is still the truth.