During the First Dornish War, Rhaenys Targaryen and her dragon Meraxes, were killed when a very lucky shot from a scorpion bolt managed to pierce the dragon's eye and drive into her brain, killing Meraxes instantly in mid-flight and causing Rhaenys to fall to her death. This was a major blow to House Targaryen.
To combat Daenerys Targaryen's dragons, Cersei Lannister's new Hand of the Queen Qyburn commissions the construction of a very powerful custom scorpion. Qyburn has Cersei accompany him to the crypts below the Red Keep where the skulls of dead dragons are housed. Qyburn shows her the scorpion he's had built and invites her to fire the bolt at the skull of Balerion. The bolt pierces the skull easily. Cersei seems very pleased.
Though the weapon usually requires a team to operate, Bronn manages to operate Qyburn's scorpion during the battle of the Goldroad and fire it against Drogon while Daenerys is riding him. The bolt misses his eyes, however, and only strikes him in the shoulder - failing to deeply penetrate the dragon's iron scales (which apparently Qyburn nor anyone else considered for penetration), and only inflicting a minor flesh wound. Drogon then destroys the scorpion by blasting it with fire, and Bronn barely escapes. When he lands nearby, Drogon destroys the burning cart with a single swipe of his tail.
Later, scorpions are seen mounted on the walls of King's Landing. Despite the numerous scorpions present in the Battle of King's Landing, they failed to kill Drogon, who successfully evaded getting struck by a bolt and destroyed all the scorpions.
In the books
The difference between a scorpion and a ballista is that a ballista is far larger, and needs to be constructed on-site - it can't be moved with horses and be assembled reasonably quickly the way a scorpion can.