I'm not really an expert on the field. But, have you considered that maybe SQL is massive for good reasons?
The subset of SQL that covers majority of use cases is actually small and probably mostly covered by the standard.
All the rest of SQL which makes it a massive beast is for all those cases where adding the logic to the database would've been the most efficient for all those who need that extra bit of efficiency.
I think SQL is massive because of two major reasons:
1. It has a highly inconsistent, non-generalized syntax
2. It tries to reuse the language for everything -- from schema specification, to querying, to administration, to query engine hints, to procedural language definitions
The RDBMS in total is a highly complex system, but SQL adds a lot of accidental complexity on top of it.
Also for a new DB, you hardly need to support the whole suite of features to produce a useful system -- you just need to constrain the recommended usecases. See SQLite for the quintessential example -- they dropped MVCC and a lot of management/multi-tenancy features as they targetted a surprisingly large niche.
As it turns out, there's a wide variety of data management needs -- SQL is often a beast because most RDBMS's have a goal of targetting all of them.
It's slightly more likely that modern SQL is the byproduct of several query language specifications accelerated to ludicrous speeds and set to collide with each other. Out one end of the apparatus, SQL-86 emerged.
A little known fact about this experiment: the residue splattered along the interior of the apparatus was recovered and named LDAP.
The subset of SQL that covers majority of use cases is actually small and probably mostly covered by the standard.
All the rest of SQL which makes it a massive beast is for all those cases where adding the logic to the database would've been the most efficient for all those who need that extra bit of efficiency.