Trek III is in my mind the best use of the self-destruct in all Trek. The scene is just so well done, starting with Kirk and the others arming it, and ending with the Klingons on the bridge listening to the countdown and Kruge yelling at them to get out.
Setting the self destruct should harder than what TNG and VOY depicted. TOS did it right in Last Battlefield and TSFS.
Well TNG it required Riker or two other senior officers when Picard activated it. Janeway could just turn the thing on/off at will by herself.
In the cinema for STIII... I'd read the book, so I knew what was coming. But as the countdown hit six or so, you could hear the intakes of breath, and the stifled mutters of 'No, they can't...' as it sank home that it wasn't going to be cancelled.
I did actually find the depiction of it in "Let That Be Your Last Battlefield" and TSFS to be the best. Even the laborious process of going through the senior officers to get the thing turned on... it really amps up the drama. I did find TNG's depiction of it equally well done in "1001001" and "Where Silence Has Lease", too. But the trouble with a device like the self-destruct is that if it gets used too often it just becomes a silly trope. It loses it's punch after a little too many uses, so (for example) when Picard tries to do it in "Nemesis" we just roll our eyes.
According to Voyager, a single hand phaser fired directly into a warp core is enough to destroy the entire ship.
IIRC, isn't there a Klingon who basically threatens to do the same thing to the 1701-D in TNG's "Heart of Glory"? Strictly speaking it was a Klingon weapon rather than a phaser, but still.