TYPO3 Core

I don't understand the (real) use case. If you do not define a column in the CREATE TABLE statement (or one of its "extension", adding other fields), any existing column will be suggested to be renamed as zzz_<column_name> in Install Tool, which lets you then safely remove them, again with the Install Tool.

As such I don't see a high need to actively support ALTER TABLE statements.

The only use-case would be to be able to remove existing columns like deleted or hidden or so. You can deactivate them (modify TCA) which helps get the desired effect (for example have records permananetly removed upon delete or offering no disabled/hidden-functionality). But you can not get rid of those DB-columns.
extension_builder currently autogenerated deleted, disabled/hidden and starttime/endtime. But I've also submitted a feature for review there to allow to not generate those fields in the first place (#56965).

I've seen that, but as said, deactivating them in TCA is not enough, if you remove them from the CREATE TABLE statements in the first place, they will be suggested for removal in Install Tool, without any change needed.

Yes, but often you can't just remove the CREATE TABLE. Currently extension-builder autogenerates things like starttime/endtime. And even if you remove from TCA they'll still be in DB. (I've sent a patch to extension-builder for review. If interested please review - not that big actually #56965).
The other case is that not your extension but some other or core provided the CREATE TABLE and there is nothing you can do about it.