Of interest here is that our heroic officers have broad powers of use of lethal force, in all the eras and spinoff shows. They also appear to be the de facto law enforcers of the Federation, facing no competition from any civilian organization. Basically, then, death penalty can befall any perpetrator if the officer on the field deems this appropriate. This in no way contradicts Picard's statement that death penalty has ceased to be viewed as being a effective deterrent - it's merely a convenient way to stop the perpetration and especially perpetuation of a crime for good.
That's a good one. I had similar thoughts today.
But "Turnabout Intruder" is specific that for TOS General Order 4 carries a death sentence.
I imagine it has to do something with high treason, like turning a starship over to the Romulans or Klingons.
Considering Captain Ron Tracey's greed for profit in "The Omega Glory" I'd say General Order 4 might have been a good deterrent to prevent him from just doing such a thing.