Albion has the Toronto, a ship that can transform into a mining facility, that automatically builds/repairs itself, and is virtually indestructible. And the whole thing is controlled by a single computer, and manned by a relatively small crew.
The USG Ishimura from Dead Space. A mining spaceship that rips part of a planet and suspends it beneath the ship, ready to be mined. It is, however heavily industrial in design, only the higher ups get fancy furniture like sofas, end tables and double beds.
The main ship from the Descent series. It was a battle cruiser compressed to a rather tiny one-man ship. The Pyro-GX for the first two games, and the older Pyro-GL in the third. Of course, in the third you also had the Phoenix interceptor, and the Magnum-AHT heavy fighter, which you could choose between once you acquired them. In the expansion, you also got the ultra-new Black Pyro. It had the added perk of being controlled, in game, by a keyboard.
Disgaea: Hour of Darkness has the battleship Gargantua; the human SDF as a whole has an entire battalion of slightly lesser ships that Laharl blows through in a single cutscene.
Flonne: I want a model of this ship!
She gets satisfaction in the anime... only better.
Gordon: You imbecile! Now's not the time to be impressed! Gordon: That's the Astro Cannon, the EDF's ultimate weapon. Gordon: Demon or angel, you won't escape a shot unscathed!
One wouldn't expect starships to show up in what is generally a Medieval European Fantasy series like The Elder Scrolls, but one would be wrong. In background lore, during the Second Cyrodiilic Empire in the late 1st Era, the Empire was in a space race to explore Aetherius (the realm of magic) against the fAltmer (High Elves) of the Aldmeri Dominion. The Aldmeri used Sunbirds, ships somehow literally made from the Sun. (Which, in the ES universe, is actually a portal to Aetherius through which magic flows into Mundus, the mortal realm.) The Empire, on the other hand, used "Mothships", enormous Ancestor Moths bred, hollowed out, and flown into the void on strength of willpower alone. (Ancestor Moths have a special supernatural connection which also allows them to be used to somewhat protect mortal readers from the power of the Elder Scrolls, which is why the Scrolls are kept and read by the Cult of the Ancestor Moth.) The results of these expeditions have largely been lost to history, though it is known that both sides set up colonies on the moons of Nirn (which are said to be the rotting corpse/"flesh divinity" of the "dead" god, Lorkhan).
The Escape Velocity series has a number of cool ships. Nova alone has the cool-looking Modified Starbridge, the hideously powerful Polaris Raven, the very cool Auroran Thunderforge, and the Series Mascot Kestrel... and the Manticore - built out of junked Leviathans, it's probably the best ship for piracy in the game as its primary weapon, a ring of eight ion cannons named the Crown of Thorns, only disables and doesn't destroy targets, and its huge cargo carrying capacity can be converted to carry weapons.
EVE Online has many of these, but the Veldnaught, piloted by Chribba, gets special mention. Beyond being a capital ship, it is located in high-security space, where capital ships are not allowed to enter, but the Veldnaught was already THERE when the rule was put in place. So what does Chribba use it for? Mining.
The Rifter class of frigate also fits in this trope. Like all Minmatar ships, Rifters look like they're made of scrap. This article (non-canon) claims that the first Rifters were constructed during the Minmatar Rebellion out of actual scrap, with converted plasma cutters for engines. But they don't just fly well - the Rifter is commonly held to be the best combat frigate in the game. Every pilot in Goonfleet knows that the original Rifters were built from bicycles and televisions stolen from the Amarr.
The Magathron Navy Issue was at one point considered one of the most aesthetically pleasing ships in the game, given that its base design was already badass looking. However, unlike the regular issue, it sported a jet black paint scheme with blue lighting. To the point that people bought it just because it looked cool. This changed when the Dominion expansion altered the paint scheme to a horribly ugly forest camo. Chided by players as being perfect to hide the ship behind all the trees in space. The price of the Mega Navy Issue took a nice drop in response.
The Scorpion received an epic visual overhaul with the deployment of Tyrannis, which was especially well received by the player base.
Basically, any ship in EVE can be considered a "Cool Starship" depending on your standards and sense of aesthetics. Except the noob ships. And now even the noob ships look cool now that they have also received a visual overhaul on par of that of the aforementioned Scorpion.
The hunter's dropship in Evolve fits into the rustbucket classification. The Laurie-Anne is a modified tug, without any fancy weapons or even an interstellar drive. What it does have is an engine large enough to power a much larger ship, built-in material assemblers, a Rank-Rajat Mind, the ability to uplink to various orbital systems for additional firepower or tracking devices, and the rugged durability to survive things that trashed military transports.
Final Fantasy IV has one of the understated ones. The Lunar Whale is a mobile free inn with an enormously fat bird that serves as a Hyperspace Arsenal access point. It may not be the biggest out there, but it's more than enough for world-saving adventurers battling the forces of ultimate evil.
Not to mention that it provides, for the first and last time in the series, access to the moon. (VIII came close, though).
The FreeSpace series turns the Cool Starship into an art form. Both games feature several classes of massive capital ships that are thousands of times the size of the player's fighter, armed with not one but severalWave Motion Guns apiece, including a fleet of 80 alien juggernauts that, together, blow up a star.
By the second game, both sides have bigger nastier juggernauts that could take out a Lucifer without breaking a sweat. Unfortunately, the Shivans have the aforementioned 80 of them (that we see; they may well have far more elsewhere), but there's only one Colossus.
The Freespace Open Source Project, a massive and still highly active collaborative effort that constantly updates the game with improved code, AI, mechanics, missions, and entire new mods/total conversions, understandably has its own fair share of cool ships. The Blue Planet series, in particular, has been praised for the designs of its original ships.
While Galactic Civilizations II allowed you to build your own Cool Starships in a lego-esque design menu, a special mention has to go to the Arnor vessels you can sometimes find in special anomalies and turn to your side. Just one of these vessels has sufficient power to take entire enemy fleets of dreadnoughts on and shrug them off with minimal damage. God help you if you try to fight one of these in the hands of someone with a couple of Military Resource starbases. Your game is officially over.
The Galaxip Dragoon from the Galaxian 3 series. What amounts to a small capital ship gifted with enough maneuverability to pull of an Airstrike Impossible. It's later upgraded into the even cooler Dragoon-J2◊, which is bigger while still providing the same level of agility.
The Elsior and Luxiole from the Galaxy Angel gameverse. Indoor ocean. With whales.
Gratuitous Space Battles asks you to design a fleet of these and throw them at the enemy. There's a lot of them, and the visual design is such that there's enough variety for most people to find something cool somewhere in the game.
The CSS Astrid from Ground Control deserves a definite mention - it's introduced by way of hammering an opposing ship belonging to a technologically superior faction into retreat with just a couple of salvoes from its cannon... It then goes on to reappear in the sequel as a 300-plus-year-old derelict which just happens to still be in working order, and house an engine which would allow it to reach the next galaxy over...
The Pillar of Autumn was a Halcyon-class cruiser. According to the novels, this class of ship had been retired years previously and were destined for the scrap yard. They were slow, under armoured, had very few weapons, and were generally considered a joke among the fleet. The Autumn itself was in bad shape and could be accurately described as a junk-heap. This made it the perfect candidate to ferry the resident Super Soldiers on their top-secret mission, and as such was refitted with top-of-the-line reactors and experimental weapons. That's right, a techno-miracle disguised as a rustbucket. That said, the Pillar of Autumn was chosen for the mission because the Halcyon-class was designed with an absurd amount of internal structural bracing. It could take a beating to the point of looking like swiss cheese and still maintain basic structural integrity. ...then they put in the self-cooling engines, special MAC Gun ammo and capacitors, and replaced an entire set of shuttle-bays with missile launchers, and put Jacob Keyes in command, and it became the most badass ship in the fleet. You first meet it in the first game, as Cortana drives it around Halo's solar system terrorising ships twelve times its size.
Eventually, the UNSC would create the Autumn class, a successor to the Halcylon class based upon the improvements that the Pillar of Autumn introduced.
Other ships in the original trilogy include the significantly less-cool In Amber Clad and Forward Unto Dawn. However, you also get to see High Charity - a giant ship/asteroid/city, which more than holds its own in the awesomeness category.
The UNSC frigates (the class that IAC and FUD belong to) get a slight coolness boost in Halo: Reach by performing heavy fire support for you on multiple occasions.
The Covenant flagship Ascendant Justice from the novel Halo: First Strike, which is enhanced by Cortana to the point of "ridiculous badassness" (superior shields, pinpoint jump-drives, one-hit-kill weapons, etc.). Welding the Ascendant Justice to the hulk of a UNSC ship, solely for the purpose of providing More Dakka.
The Long Night of Solace, the Covenant Supercarrier in Halo: Reach, counts, partly due to its impressive entrance (one-shotting a UNSC Frigate from out of nowhere), and partly because of its sheer size - the thing is huge. At 29 kilometers long, Covenant Supercarriers are the largest warships ever made by the Covenant, and only about a dozen of them are known to have existed throughout the Human-Covenant War. As a comparison, it would dwarf even Star Wars Executor-class Star Destroyer (19 km), with Mass Effect's Citadel (47 km) beating it out with twice that length. A Covenant Supercarrier is about 11 kilometers wide and about three kilometers "tall". They also carry hundreds of thousands of troops (and all of the armored vehicles, aircraft, gigantic cloaking-teleporting-jamming pylons, and more). One wonders how even the Covenant build these, let alone in significant numbers.
Pretty much any Forerunner ship as described in The Forerunner Saga: some are made up primarily from hardened light, which can be turned transparent in some areas to simulate being completely outside. Of particular note is the unnamed ship used by the Didact, which, while small compared to some other ships, was equipped to singularly wage a large battle, and the Fortress ships, which were 50 kilometers long and capable of destroying Halos on their own.
The UNSC Infinity, introduced in Halo 4 and first mentioned in the novel Halo: Glasslands. The largest ship (3.5 miles in length) to be constructed by the UNSC, it was meant as a last resort in case the Covenant destroyed Earth. It's also the training location for the SPARTAN-IV program. It was also upgraded with the latest reverse-engineered Covenant and Forerunner tech. And then it crashes in its first trip. Fortunately, on the planet where Master Chief is.
While we're on the subject, The Didact's cruiser, the Mantle's Approach. It doesn't see much action, due to being destoyed by a Havok nuke warhead, but it looks amazing, and it takes the Infinity to actually do any notable damage to it.
The Mothership from Homeworld. Six kilometers of the last hope of survival for your race, controlled by a woman directly linked into the processing system, capable of churning out fleets of ships in mere hours and traversing lightyears in the blink of an eye... And that's not even getting into what the Bentusi have.
Hiigaran Battlecruiser and Interceptor from the second game.
Not to mention the Kuun-Lan command ship from the stand-alone expansion pack. Better armed than the Mothership and can pack a fleet-destroying Wave-Motion Gun.
Of course, the dreadnaught has a repulsor field that can bounce that shot back into the command ships' face. Had a couple of mishaps because of that... The overwhelming majority of the Somtaaw fleet is composed of cool ships: kamikaze ships with holographic camouflage; unmanned Leeches that attach onto the enemy's hull and use concentrated acid to chew through the drive plasma conduits while hacking into the target's computers and suppressing damage alarms; Sentinels that combine into AT-Field wannabes; Dervish frigates that do ballet maneuvers while tagging enemy fighters with fast-tracking ion cannons; dreadnoughts to match any main faction's heavy cruiser, plus missile batteries and the above-mentione repulsor field... the list goes on.
The Naggarok the ship that The Beast entered this galaxy in is one of the most intimidating ships in that entire series. It's huge, armed, infectious, and once the super-advanced engines are repaired it can cross vast distances in seconds, while it would take your fastest ships tens of minutes. Something that large simply should not move that fast!
It can do that because it has an inertialess drive. Though if we are already there, the counter to the Naggarok must be mentioned: Super Acolytes. A fighter with dual rapid-fire ion cannons. Mind you, that's destroyer-level firepower packed into a fighter!
Apparently it's so cool it needs a second mention here, apart from being the first videogame entry. The Sajuuk from Homeworld 2 is a very cool one as well, packing the most powerful weapon in the game. In addition, remember the thingy that enabled the Mothership to make such long-range hyperjumps? Sajuuk has three which means it can hyperjump during battle with absolutely no cost. Let's recount: a super-heavily armored, 5km long and 1,500,000 ton ship with a Phased Cannon Array, medium-range nanite cannons, quite fast self-repair and slow speed offset by the ability to execute tactical hyperjumps with no resource cost.
The Pride of Hiigara is, effectively Mothership 2.0, designed explicitly for the purpose of fighting the Vaygr. Unlike the original multi-purpose Mothership, the Pride is smaller and isn't designed to carry half a million of settlers. However, she has forward-facing fighter launch hangars, so they can be deployed much faster, as well as internal research facilities, to avoid protecting vulnerable research ships. Oh, and she can move (albeit slowly), which means that, if anyone ever tries to put a giant asteroid on a collision course with her, she can just move out of the way.
The Vaygr battlecruiser has a powerful triple-cannon at the bow, capable of dealing tremendous damage to any ship, as well as VLS missile launchers at the top.
As achronic as Inca may be, El Dorado's golden starship, the Tumi still stands out, being able to blow away Conquistador space fighters with plasma bolts, and your flight computer Dalan even guides your projectiles to them. It also comes with a small cache of missiles and smart bombs, though these are finite.
The sequel Inca 2: Wiracocha ups the ante with the Tumi fitted into a larger chassis, allowing for increased ammo capacity, a Tractor Beam, and torpedoes for blowing up space stations.
Infinite Space has dozens of cool ships for the player to build and destroy. Each star nation has its own aesthetic, ranging from the blocky grey Standard Human Spaceship look of Libertas to funky purple Adisian designs and Lugovalian battleships that wouldn't look out of place in a Zentraedi fleet.
Infinity: The Quest for Earth features ships as long as 5000 meters, or about three times of the Star Wars Imperial Star Destroyer mentioned above.
Kingdom Hearts and Kingdom Hearts II take this a step further, as they let you build your own Cool Ship out of spare parts. A couple of the premade designs are fairly cool too, particularly 2's Falcon Peak. And while the Gummi Ships do not have the coolest name of all time, let it be known that these ships are made out of transdimensional matter and are capable of inter-universal travel.
Makai Kingdom introduced the Warship Yoshitsuna (not to be confused with Disgaea's similarly-named sword weapon), which is stated to be an explicit planet-buster. It makes a reappearance in Disgaea 3 as an enemy's Pirate Ship, and in Disgaea 4 as an obtainable ship part. In the sequel to Prinny: Can I Really Be the Hero?, Asagi Kurosugi somehow got a hold of it, and when defeated at the end of Asagi Wars, tries to use it to detonate the Netherworld!
Marathon: Pretty much any ship controlled by Durandal - the beginning of Marathon 2 sees him destroy a Phfor battlegroup using a single stolen scoutship... which he had upgraded to be able to hit targets a twice the maximum distance a dedicated warship could; ten thousand years after that game he's upgraded to a Jjaro dreadnaught renamed Manus Celer Dei - given that a Jjaro space station was capable of creating black holes as a weapon (to combat EldritchAbominations), you're left wondering what a Jjaro warship could do to top that...
The Normandy of Mass Effect. Not only is it really fast, it is also a stealth ship. ...and incredibly efficient, to the point of being arguably Awesome, but Impractical. But hey, you can't put a price tag on cool! (Though, between themselves, the Systems Alliance and the Turian Hierarchy managed).
The Normandy SR2 built by Cerberus in the second game to replace the SR1 after it got blown up is even cooler. Everything from the original design was amped up, including the luxury; Joker likes the new leather seats. And it has an Artificial Intelligence that eventually takes total control over the entire ship (don't worry she's not evil) and describes the ship as being her body. One trailer features the Normandy leading a squadron of fighters as if it were one itself, and not to mention this memorable scene where it outruns Cerberus' advanced fighters. Yeah, the SR2 is pretty much the definition of Cool Ship. Oh, and there's even a bar if you fork over the cash for some DLC.
In Mass Effect 3, she gets another overhaul, including an Alliance Blue paint job, the ability to provide fire support to the player (in some instances), and new areas opened up. How cool is the Normandy in ME3? A fully upgraded Normandy is worth almost as much War Assets as an entire Alliance fleet.
Meanwhile, on the Big Bad side of the equation, we have Saren's flagship Sovereign. A massive, vaguely arthropod-like starship that's Nigh-Invulnerable, engages two full-strength battle fleets simultaneously, and is implied to only lose by plot device. Did we mention that it's actually sentient and the one calling all the shots?
The Shadow Broker has a pretty damn sweet vessel which is basically a giant flying fortress modified to be able to perfectly hide within thunderstorms.
The Destiny Ascension. The flagship of the Citadel fleet, this oversized asari dreadnought dwarfs even the largest human warship, takes a full crew of 10,000 and alone packs as much firepower as the rest of the asari fleet combined. An excited volus who was given a guided tour of the Destiny remarks that he was on the ship for six hours and only saw a tenth of the interior. Though she can't stop the entire geth fleet on her own and if you don't send reinforcements (the Renegade option) she will be destroyed. Which can bite you as she's a War Asset in 3.
The geth flagship is a massive dreadnought that can lay waste to the entire quarian fleet, even though it looks like a giant cockroach. Unless, of course, Legion disables its weapons and defenses. Keep in mind that the quarian dreadnoughts (or civilians ships that pretend they're not dreadnoughts) are armed to the teeth with the Thanix cannons.
Mass Effect: Andromeda continues the tradition of the previous games with the Tempest, Normandy's smaller, speedier cousin, right down to suspiciously sharing some of the highly experimental systems built just for her. It's also been tested against drunken krogan. But no guns (the recoil would blow out all her fancy windows). And thanks to some unfortunate circumstances, she's also the Last of Her Kind (the others got smashed to pieces).
There's also the Nexus, a Citadel-inspired mobile space station, designed to cross the intergalactic void in a mere 600 years.
The various Hunter-class gunships from the Metroid series. GFS Olympus from Prime 3 probably also counts, if only because it has its own Mother Brain Aurora Unit onboard, where Auroras are usually limited to larger installations - like, oh, entire cities.
The Lincoln in Might and Magic is a backup Ancient seedship. It is also fully capable of travelling around the galaxy for years without refueling, comes fully stocked with compliments of killer robots, blaster weaponry and wetsuits, can be parked underwater for years without problems and is easy to use even if you're not experienced with Ancient technology. Sheltem had a plan to turn XEEN into this, but luckily he was foiled (using a planet, albeit a flat one that would die from it, as a starship is damn cool).
The XTM in Millennia: Altered Destinies deserves a special mention. It's an organic-looking ship capable of space/time jumps and can go between galaxies in seconds (provided you get the right parts). It's strong enough to fight entire fleets, especially if you get better weapons. It's got everything you need to guide four separate species to dominance in a galaxy about to be taken over by a hostile force. It recharges by making a close pass of a gas giant, something that would likely crush any other ship like a tin can.
Nexus: The Jupiter Incident featured the Angelwing, an highly-advanced ancient cruiser built by the Precursors and capable of taking on fleets, especially after the Mechanoid upgrade. It has the added benefit of looking really sleek and deadly, while most other ships in the game look blocky and fairly ugly.
Almost as unique are three strange ships you find and fight in one level. While they lack shields, they are capable of Teleport Spamming, and you're likely to lose a few ships in your task force, as trying to exit the battle is pointless when the enemy can teleport.
No Man's Sky has several. Larger craft tend to be of the "flying brick" format, while smaller craft like fighters tend to lean more toward such staples of Sci-Fi like Elite and Star Wars; continuing on that one of the game's gameplay tenets focuses on gathering resources and materials to upgrade your Cool Starship so that you can explore further in the universe.
Olimar's starship, The Dolphin, from Pikmin is a cool starship if only because it is the size of a soda can, yet can travel between star systems at hyperspeed, can more or less withstand an asteroid impact and re-entry, and can even repair itself. And the ship from Pikmin 2 is certainly cool after its bling upgrade.
Ratchet & Clank: Going Commando: Ratchet's ship can get many weapon, boost, and shield upgrades, each of which shows up in a visible add-on to the ship model when landed. Paint and bodywork can also be done ingame.
Ratchet & Clank: Up Your Arsenal: The Phoenix, large star cruise commanded by Captain Sasha that acts as a hub in the game. Carries both Ratchet's ship and a troop carrier in an onboard hangar. The troop carrier delivers Ratchet to some battles. Crew quarters onboard has the latest gaming console, which causes Ratchet to propose marriage to Captain Sasha when she describes it.
The Future series (Tools of Destruction, Quest for Booty and A Crack in Time) has the Aphelion, a Lombax fighter with an AI. In the first game, she has laser blasters and homing plasma missiles. In the third game, she is upgrade by the Zoni, giving her several different lasers and missiles, extra shields, boosters, a grappling hook, and a spiffy new paint job. She can also survive entering a black hole, and has a spacious comfortable interior.
In SD Gundam G Generation Cross Rays, the battleship Carry Base, which was once just a recolored Space Ark from Mobile Suit Gundam F91, got itself a nifty redesign which divorced it from its base and made it its own distinct ship. It has a nice fearsome silhouette and you'll be using it a lot until you can access canon ships.
The first game and its expansion have the UNSF Omaha, a United Nations space carrier orbiting Earth in 2019. When the alien invaders start curbstomping Earth, the Omaha is the only thing that puts up an effective defense.
The second game features the Cortez, a mercenary ship much smaller than the Omaha, but more than capable of exploring space through an alien Portal Network. The finale of the second game also has a colony ship built by the Sensci, cool because it's the only ship anyone's built with a true FTL drive that bypasses the Portal Network.
Sins of a Solar Empire has plenty of awesome ships but the ones that stand out are the Capital Ships which are essentially Hero Units. Rebellion takes it even further by introducing Titan ships, massive starships that dwarf the Capital Ships, there two types for Loyalist or Rebels for each race.
Spore, as quoted above, deserves special mention in that you get to make your own Cool Starship, just like pretty much everything else in the game. It's not too hard to create a real gem. This is aided by the creator featuring parts from several of the most iconic items mentioned on this page, allowing players to produce an amalgamation of an X-wing with the Enterprise if they so wish, or even incorporate parts intended for a Cool Car, Cool Boat or Cool Plane.
Star Control had a number of these, but then there's the Vindicatornote You can actually name it whatever you want, but that's its default name, the protagonist's starship from Star Control II. Properly equipped, it was far and away the most powerful ship that could be fielded, outclassing even the mighty Ur-Quan Dreadnoughts... and it was just a Precursor utility ship, not even designed for combat!
And not even *finished*... the factory ran out of minerals long before construction was complete, so it's only the skeleton of a starship and *still* scares the crap out of most of the races you meet.
Meanwhile the Ur-Quan have as their flagship the Sa-Matra, a bona fide Precursor warship. Its main weapons are said to have the capability to blow up ships from across a solar system, its point defenses can blow up entire fleets single-handedly, and is so heavily armored that it takes a bomb capable of an Earth-Shattering Kaboom to finally destroy it.
In StarCraft II, the protagonists' starship, the Hyperion, a stolen Dominion battlecruiser, qualifies. Also, the Void Seeker (Zeratul's ship) and possibly the Shield of Aiur (Artanis' Ship).
Each of the expansions features its own immensely cool command vessel for the protagonists and their army to run around the galaxy in: Heart of the Swarm has Kerrigan's Leviathan, whereas Legacy of the Void will feature the "ark ship" Spear of Adun.
It really needs to be said in detail how awesome the Spear of Adun really is. When you hear the term "arkship", you might think of a glorified transport shuttle. Wrong. The Spear of Adun is colossalnote 74 kilometres in length, next to the 7-10km long Leviathans Kerrigan used in Heart of the Swarm, powered by a synthetic star which also sustains the entire population of the arkship. It has a vast factory bay with enough manufacturing capacity to produce absolutely anything the Protoss might need, rivalling even the planet Shakuras. Its considerable armament and defensive capabilities allow it to destroy multiple ambushing Terran battlecruisers with ease, and only the entire Golden Armada ramming into it is able to do any meaningful damage (which is repaired by the epilogue). It has highly manoeuvrable warp drives, enough orbital strike lasers to glass a planet, a chronosurge which decuples the flow of time for absurd production speeds, and reconstruction beams to repair anything mid-fight. It houses an entire legion of some of the ancient Protoss' greatest warriors and most brilliant minds in comfortable stasis, ready at a moment's notice to step out and give aid to their descendants in the darkest of times.
Great Fox in the Star Fox series. Unfortunately, it gets destroyed during the events of Star Fox: Assault and is replaced with a less cool variant in Star Fox Command. In the smaller ship classes, we also have the series' ever-present Arwing fighters, as well as Star Wolf's Wolfen.
The Starship Titanic: a t-shaped hotel in space with a luxury restaurant, bar, canals and lots of marble.
Star Trek Online, naturally, allows you to command the cool starships of the franchise practically at-will. They even slipped in the original Enterprise design as a preorder bonus just because.
The Imperial-Class Assault Cruiser, which is a reskin of the Sovereign-Class and arguably more popular.
The Typhoon-Class, which was meant to be a filler ship but became so popular people are begging for a playable version.
The Excalibur, Vesper and Exeter-class Cruisers as well. They're explicitly a 2409 redesign of the old Constitution-class simply because Starfleet needed a new ship class for patrols and decided they should update Kirk's design because of its iconic value. Admiral Janeway commands one, the U.S.S. Tucker.
The Avenger-class, Starfleet's first official battlecruiser that looks like a more common sense version of the U.S.S. Vengeance.
The Fleet Patrol Escort is a very popular design being a tankier DPS escort.
And everything on the KDF side, really, especially the non-Klingon ships.
The Romulans are no slouch either with the iconic designs as well new ones like Arkif Battle Warbird.
All three playable faction Flagships are this: The Odyssey-class U.S.S. Enterprise-F, the Klingon Bortasqu'-class I.K.S. Bortasqu', and the Romulan Falchion-classR.R.W. Llieset
There's also Lockbox and Lobi ships, specialized craft from an enemy faction, like the Tal Shiar Adapted Destroyer and Adapted Battlecrusier, which are basically Romulan ships outfitted with Borg technology that makes them look like the feared Narada.
Knights of the Old Republic has the Ebon Hawk, a clear homage to the Millennium Falcon, though it's trimmed in red. The crime lord you steal it from proudly calls it "the fastest ship on the Outer Rim", and on the planet Korriban you find that it's been around for quite a while, long enough to be famous among smugglers. It has several storage compartments of varying levels of secrecy and actually is very well maintained, though Jolee Bindo complains that the food synthesizer needs cleaning.
The TIE Phantom from Rebel Assault II? "No ship that small has a cloaking device," indeed.
The TIE Fighter series of games featured the TIE Defender, which was the fastest starfighter in the galaxy; as well-armed as some bombers; shielded (unusual for TIEs); equipped with hyperdrive; and some models had a tractor beam. Something of a Game-Breaker, it didn't make it into other similar games, leaving you with only the second best TIE Advanced starfighter.
One training mission in the game with the TIE Defender puts you up against two souped-up Corellian Transports, with massively fast upgraded engines, armour and weaponry. That's right, you are capable of killing the Millennium Falcon in one of these things. Twice.
The Rogue Squadron series of games combines this trope with Cool Car by providing a cheat that lets you pilot a Buick. Really.
Subnautica has the Aurora, a crashed terraforming ship of which you are the only surviving crew member.
Super Mario Bros.: The Galaxy games bring some good stuff to the table. Super Mario Galaxy has Rosalina's Comet Observatory, which looks like a magical fantasy castle turned spaceship, with features such as eternal waterfalls and a star-shaped (as in, actual burning star) core. Super Mario Galaxy 2 gives us Starship Mario, which is not only shaped like Mario himself, but also doubles as a planetoid with its own gravitational pull.
In Super Smash Bros. Brawl's Subspace Emissary, one of the main plot object's is the Halberd, which even destroys easily the Great Fox. Fortunately it is eventually recovered by the heroes. Then an even larger ship/cannon comes from a portal and blows it up. But four smaller, equally cool ships (and Olimar's pitiful lil' tin-can of a rocket) emerge to continue the fight. The Catch? The extremely large enemy ship is destroyed by Kirby using a Dragoon, essentially a flying hovercraft that (canonly) only allows for one pink ball to ride it. And how did Kirby blow up the gunship? By crashing the Dragoon into it. Without even scratching the paint. Even Newton's Laws are no match for Kirby.
The Touryst has an ancient rocket-ship hidden beneath the TOWA monument on Touryst Island. The vacationing protagonist and his mentor take it for a joyride at the end of the game looking for further adventure.
The Liset ships used by the Tenno in Warframe are stupidly cool. The ships themselves are a little over 10 metres in length, with a design reminiscent of manta rays. The Tenno owner rides the underside into battle in a way that is specifically reminiscent of how ninjas rode kites to infiltrate their destinations. The ship features directional thrusters for superior maneuverability (it can pretty much hover vertically in order for you too awesomely power-walk into the underside recess after killing all the bad guys), a speed unmatched by any other ship class in the Origin System, and a "Void Mask" to make itself almost undetectable; combined, its speed and stealth mean your primary way of getting around the system is using the Solar Rails controlled and blockaded by the enemy; intercepted enemy transmissions reveal that Tenno Lisets are routinely detected barely seconds before making the rail jump, with the blockading ships helpless to stop them. Furthermore, the Liset is considerably bigger on the inside (another of the Orokin's Void tricks), is run by an only slightly defective Cephalon, and likely features its own Torsion device so it can enter the Void with only a key (whereas the Grineer need a key, a Torsion Beam and a working Orokin Portal).
The Liset actually attaches to a much larger Orbiter, which is what the Tenno can walk around in while not in a mission.
The new Rising Tide update tasks the player to reconstruct a Railjack, a ship which was used in open combat against the Sentients. The following Empyrean update lets you take it into space and into open combat. Bring a crew for this one.
The Lombardia returns in Wild ARMs XF, although you only get to see it in cutscenes. As a Shout-Out to 3, it's classified as a "Dragon Class" subspace vessel. It's also cool as hell.
Its/his (her?) first appearance was in Wild ARMs 2, where it's explained to have traveled from another dimension full of biomechanical dragons. This was their excuse for putting a Transforming Mecha in a Desert Punk setting. Sadly, it didn't join the heroes until after the one mission that required them to actually go into space.
The TCS Midway of Wing Commander Prophecy is Confed's first experimental megacarrier, and the player's home base. The Midway carries hundreds of fighters, battleship-grade capitol ship weapons, lots of anti-fighter turrets, around two thousand crew, gets upgraded with an alien Wave-Motion Gun, and almost singlehandedly wipes out an alien invasion, supported only by one squadron of fighters from another carrier (that are subsequently transferred to the Midway), a Kilrathi corvette or two, and a few hapless supply ships. Another example from the same game would be the Nephilim's Tiamat Battleship/Carrier-a ship so big, it takes two missions to destroy.
Arguably, any of the "home base" ships from the series qualify, if only because Luke Motherfucking Skywalker is flying off of them. The only possible exception is the TCS Lexington from the beginning of WC4, once Blair defects. For the non-base options, there's the Vesuvius class of supercarriers (one of which gets a Gunship Rescue moment, under the command of Captain William Eisen), from WC4, and the Bannockburn, a converted transport with a cloaking device, used by James "Paladin" Taggart, in the novel Fleet Action to sniff out the Kilrathi Hakagas being built deep in kat space.
The TCS Concordia most certainly deserves special mention, if only for its Phase Transit Cannon. Watching it take out cruisers in one shot never gets old, to the point that a mission was put into the fanmade Wing Commander Standoff where the player is diverted to "rescue" it, apparently just to give them a chance to see it annihilate a Kilrathi Cruiser with a single shot, yet again. Seeing that ship's wreckage in the intro to WC3 still ranks up there as one of my most heartbreaking scenes in gaming.
The whole collection qualifies, more or less. For example, the Boron Shark from the second installment X2 had a huge propeller out of rotating tentacles around the engine. Since the Boron race is a marine species, their ships are specifically designed to work under water as well. The other one is a freighter called Dolphin which makes the same swimming moves as the naming animal.
The M6 Springblossom◊. The fastest and most advanced corvette in the game, with very heavy firepower and cheap for a ship of its class. Although, it is a little squishy. It is still the most popular corvette in the game, tied with...
The M7 Hyperion◊. It's not as fast or well-armed as the Springblossom, but it is considerably tougher, and it has hangar bays for fighters. Oh, and it's a Paranid ship, so it lookswicked.
The Avenger in XCOM 2 is a converted alien transport ship that despite its humble beginnings and complete lack of weaponry manages to be extremely impressive.
Xenosaga was full of these, being a full-on scifi as it was. The Rhinemaidens from the beginning of the story, the Elsa, and of course the huge megaship Durandal that's practically as big as the space station it docks with to become the Kukai Foundation's HQ. The Dammerung, as well, which is among the biggest ships ever covering 10,000 square kilometers (just under the size of the country of Lebanon).