"There are around over a thousand species of scorpions, of which 25 to 50 have venom that is dangerous to humans. OK, fine, but Bond assassins probably use one of the dangerous ones. Even then, the adult mortality rate for a sting is around one percent. So you'd really need a whole suitcase full of scorpions and for Bond to lay still for you for a bit while they get 100 stings in. And if he's being that cooperative, you could honestly just kill him with the suitcase."
In Real Life, scorpions are mostly shy, retiring arachnids, preferring to stay out of trouble and only using their pincers and stinging tails to subdue prey or defend themselves (some even use them to calm down their uncooperative partners while mating).
In fictionland, scorpions often make appearances as deadly foes. This is probably a combination of Rule Of Cool and their distinctively Bad Ass appearance, but it does tend to get them a fair bit of demonization due to Animal Stereotypes. Scorpions, being of the arachnid class, are distantly related to spiders, and as with spiders and their fangs, scorpions are pretty much universally known for their tails, and are, admittedly, pretty scary things to come across, large or small. Because of their venom, they are used as Animal Assassins. You can often expect scorpions in Shifting Sand Land. Spider Tanks may have the appearance of a scorpion. May be stabbed.
However, as the above Cracked quote points out, the majority of scorpion stings are not dangerous to humans. The Emperor Scorpion, probably the largest and most iconic scorpion species, is frequently seen, despite being one of the least venomous species and are even popular as pets (popular as far as arachnid fans go, anyway).
Pokémon has a subversion in Ash Ketchum's Gligar (later Gliscor) during the Sinnoh arc. Not that Gligar generally look too menacing to begin with, but this one has such a tendency towards Cry Cute and The Glomp with its trainer that it's borderline impossible to be the slightest bit intimidated, even when it evolves into its more threatening-looking final form. On the other hand, Ash's Rival during the same arc, Paul, has a Gliscor who is more threatening, and a Drapion who can be downright terrifying. The trope is played even straighter with the Drapion who is owned by Pokémon Hunter J, which is just as ruthless and sociopathic as its trainer.
Mac Gargan/the Scorpion from Marvel Comics, of course. He was created - with funding from J Jonah Jameson, no less - to take out Spider-Man, and actually trounced Spidey in their first fight. He's been mostly B-list since then, however, eventually ditching the Scorpion suit entirely to become the new Venom. He now has a female sort-of-Legacy Character, Carmilla Black.
In Marvel Star Wars, in order to get information out of someone, Luke Skywalker once has to prove his bravery by using chopsticks in his left hand to fish something out of the same bowl as an alien scorpion creature.
Subverted by Sammy Stingtail of the Joey Fly series. While reckless and clumsy, he is also unarguably one of the good guys and loyal to the title Detective Animal.
The 1950's science fiction film The Black Scorpion involves a volcanic eruption in Mexico unleashing gigantic scorpions upon the land. Some closeups of goofy puppet heads aside, the stop-motion scorpions are fairly nightmarish, esp. in a scene where one attacks a telephone repair crew.
In Hook, the eponymous pirate locks a pirate who bet against him in a large trunk called "The Boo Box." Apparently it is a common and deathly frightening punishment for pirates on the Jolly Roger, because the man Screams Like a Little Girl, is thrown into the trunk, and has a bunch of scary scorpions dropped on him while the others scream "Boo!"
In The Cave, Jack encounters a cavern full of them, upon which they try to chase him off.
There's a very old story called "The Scorpion and the Frog." Basically, the scorpion wants the frog to get him across a river. The frog initially refuses, afraid the scorpion will sting him if he lets him on his back. But the scorpion assures him he won't, since then they'd both drown. So the frog relents and lets the scorpion hop on board. Midway across the river, the frog feels a sharp pain in his back and realizes the scorpion's stung him. He demands to know why he did it, since now they'll both drown. The scorpion replies, "It's inmy nature."
In another version, the final line is "Because this is the Middle East."
Chakotay tells a version with a fox instead of a frog to make a point about an unwise alliance with the Borg.
In Classical Mythology, when Artemis fell for the great hunter Orion, her brother, Apollo, didn't approve, so he sent a giant scorpion to kill him. Orion and the scorpion killed each other, but Zeus was so impressed with their battle he put the two as constellations - Orion and Scorpius.
The Mastyrial came in two versions. The Desert variety was a 6 foot long with a sonar sense that could detect invisible enemies, pincers and tail that could inflict lethal damage, a tough shell that gave immunity to blunt weapons and the ability to quickly regenerate damage. The Black version was smaller (3 feet long) and did less damage, but they hunted in packs and had psionic abilities that allowed them to lure their victims closer and attack them.
The Scorpian also had two versions. The Barbed type was 8-12 feet long, covered with barbs that damaged anyone that attacked it from close range, and its attacks did massive damage. The Gold was much smaller (1-2 feet long) but still did tremendous damage with its sting.
In 3rd Edition scorpions could grow up to 80 feet long.
In Eberron, scorpions replace spiders as the Drow animal obsession.
Warhammer's Tomb kings can summon giant undead scorpions from underground.
The Dark Eldar's Talos in Warhammer 40000 is a giant-scorpion-like skimmer, with huge claws and a weaponized tail.
The Apocalypse expansion also has a mechanized scorpion for Khornate Chaos Space Marines.
Dying Earth RPG: the Silver Desert has large scorpions (among other hazards).
Space Master supplement Aliens & Artifacts. Ice Scorpions, which are found in Arctic ecosystems and are 3 meters long.
Master Atlas. The Gemsting scorpion is 4-6 feet long and injects a respiratory poison.
Kingdom of the Desert Jewel. Giant scorpions in Gethrya inject a poison that causes delirium for 1-20 hours.
The Giant Scorpion in the GURPS Bestiary, which is 2 yards long.
Car Wars: Autoduel Quarterly magazine Vol. 7 #2 adventure "Mutant Zone". Hanger A in the Air Force base is home to a giant mutant scorpion, the victim of a biotoxin spill.
Deadlands has Vinegaroons which are scorpions about the size of a pony. (Vinegaroons aren't actually true scorpions, but when you're about be eaten by something the size of a pony, your average cowpoke ain't inclined to quibble.)
The Morrow Project. One of the mutant monsters walking the Earth 150 years after a nuclear holocaust is a scorpion 1.5 meters long with a poison that has about a 50% chance of killing the average human.
Mutant Future: giant mutant scorpions are around 6 feet long and have a sting that injects a lethal poison.
Atlantis: The Lost World generic RPG setting. The Kanawalla desert of Mu has scorpions that are only three inches long but have venom so virulent it can kill a mule in seconds.
Warcraft's mages had the ability to summon scorpions.
They make a reappearance in Warcraft 3 under the name of Arachnathid and covered in fur, in the polar regions for some reason.
Giant scorpions also make a reappearance in World of Warcraft, this time called Scorpids. Hunters can tame them as pets. There are also regular sized scorpions as critters in some areas, and one scorpion non-combat pet, and now a scorpion mount. The arachnathids have inexplicably disappeared.
Fire scorpions appear in the Firelands, and Druids of the Flame can transform into them.
There are (radiation-mutated) scorpions everywhere in all of the Fallout games.
There's a radscorpion queen in Fallout New Vegas who is extraordinarily difficult. Tough, fast, lots of damage, and fought in extremely close quarters so that escaping damage is impossible. You fight her along with a couple of her kids, too, just for extra hurt.
Giant Scorpions exist in Puzzle Quest, where their major abilities are (a) hiding (letting them do double damage until you damage them out of hiding) and (b) poisoning you. However, once you've clocked three of them, you can capture and tame one to act as your steed (no access to the poison, but you do get their Hide ability).
Sierra liked this trope. Gigantic black scorpions are also a common foe is the desert setting of Quest for Glory II, and have a powerful venom that makes them one of the more dangerous foes in the game.
Graham also encounters a scorpion in King's Quest V: Absence Makes the Heart Go Yonder. Despite being significantly smaller than the one Valanice comes across, it's also far more deadly, resulting in an instant death as soon as you reach the screen it shows up on (whereas Valanice's giant scorpion rather courteously gives you some time to deal with it before attacking).
Space Quest 3: "OH NO! A venomous scorpazoid. Watch out."
The second act of Titan Quest, set in Egypt, is full of large scorpions (black ones as large as a men and yellow ones big as grown elephants.) as well as the infamous Scorpos, a hybrid of man and scorpion. Their king is the boss that protects one of the artifacts necessary to continue the story.
Surprisingly, even if he's named after the creature, Scorpion isn't really heavily based on the trope. However, he CAN turn into a literal giant scorpion for a Fatality.
Bug! had loads of them in the desert stage. Some just walked back and forth, others tried to attack with claws, and a few tried to sting bug with their tail. Thankfully, they all took one hit to die.
Giant Desert Scorpions from Dwarf Fortress, who in addition to being very, very large and lethally venomous have prehensile stingers and a penchant for snatching weapons off attackers and using them in addition to their claws and sting.
In The Order of the Stick#692, a large desert scorpion threatens Belkar's pet cat Mr. Fluffy. Belkar kills it and roasts it over a campfire.
Vriska from Homestuck corresponds to the astrological sign of Scorpio. Actually, she's more closely related to spiders, but most definitely scary.
In The Descendants story 'Little Girl Lost', the team encounters dog sized scorpions, only to discover that killing them makes them combine into even bigger scorpions.
The Nightmare Before Christmas: while planning (and singing about) the capture of Santa Claus, the girl Shock has a box of large scorpions that she uses to illustrate her plan for herself and her brothers to hide in a box that Santa would open so they could kidnap him.
Kuzco and Pacha were briefly attacked by several scorpions after being chased by several jaguars.
Scorponok from Transformers transforms into a giant scorpion.
Unless if it was Movie Scorponok, however. And Beast Wars Scorponok (Optimus Primal and Cheetor are the exact size of the animals they scanned, and Scorponok is their height, so he may not even be human-sized.)
However, that's still giant compared to an actual scorpion. The giantest, however, is G1 Scorponok (the transformed form of The Hive's base.) Short of Unicron and Primus, G1 Scorponok may be the biggest Transformer of them all.
Beast Wars also has Quickstrike, whose beast mode is a scorpion...with a cobra for a tail!
An episode of Xiaolin Showdown was actually about Kimiko accidentally setting an evil scorpion demon named Sibini free after accidentally smashing a Shen Gon Wu called the "Mosaic Scale" (also Sibini's supposed prison) to pieces, and as a result, she and her partners had to put it back together again otherwise Sibini will wreak havoc upon the entire world.
Another episode featured a Shen Gon Wu called the "Emperor Scorpion", which actually served as some sort of mind-control device.
Fievel is almost killed by one in An American Tail: Fievel Goes West. Considering he's a mouse it's not that surprising.
Teen Titans introduces Terra by having her running from/fighting a giant scorpion.
Six and Bobo encounter a nest of giant scorpions in the Generator Rex episode "Breach".
Averted and then played straight in the Sniz & Fondue cartoon "A Toxic Tail" on KaBlam. Fondue buys a Madagascar Scorpion to keep Sniz out of his room. Sniz tells him that he's not scared since the scorpion is in a tank. Once it gets loose though, everyone freaks out since "one sting and it kills."
In one episode of The Grim Adventures Of Billy And Mandy, Grim and Dracula find themselves stranded in the desert and end up eating the scorpions. It's shortly revealed the scorpions were babies belonging to a horrified giant mother scorpion, who attacked them in retaliation.
Giant Scorpion: My babies!''
Dracula: Well, maybe if you didn't make your babies so delicious, Dracula wouldn't have had to eat 'em!''
Duck Tales The Movie Treasure Of The Lost Lamp; the bottom of the chamber where Collie Baba's treasure is kept has quite a few big ones, which Launchpad dubs "Sumo wrestling scorpions." Scrooge dismisses them as they're at the bottom of very deep pit, but then Evil Sorcerer Merlock pulls a lever that causes the platform where the treasure is to lower...
Smaller scorpion species, such as the desert scorpion, are pretty dangerous. Should you come across one, the best thing to do is not to give it an excuse to attack you.
In general, the biggest scorpions, like the Emperor and the few others kept in the pet trade, are minimally venomous. This is because their claws are so big they can kill their prey with brute force and don't need venom, whereas the smaller scorpions do.
At least one Arizona highway "rest area" has signs warning of poisonous snakes and scorpions. Kinda unsettling if you're an East Coast city boy. On the lighter side, the sign shows outline pictures of a scorpion and a coiled rattlesnake—but the text warns that "Poisonous snakes and insects inhabit the area".