What we have is generally good enough for the purposes of set manipulation. SQL is not a programming language, and should not be approached as a programming language. It's a way to express relational algebra.
This is similar to complaining about any other system of algebra having issues... and wanting to come up with a New Way. We don't really need a new way to describe linear algebra; what we have works. And it may seem unintelligible and a mess from the outside, but if you take the time to learn it, you'll understand why and how it's laid out the way it is, and learn to get over it.
The other point being, there are a set of operations you can do on sets. SQL is as low level as you're going to get in regards to that. Any new language would either: change the labeling of the underlying operations (e.g. changing joins as a label for Cartesian products to some other label), abstract away the low-level operations into something completely else (foolish, in my view), or tweak it in minor ways to suit the preferences of the individual (again, not worth the effort in my view, just learn the syntax). All of these are inefficient; and all endeavors to make it more efficient have failed (as far as I can tell) -- primarily because the people working on these projects (such as your self) do not have a sufficient maturity in this space to understand why things are done the way they are, i.e. "Chesterton's Fence."
> Many developers end up writing huge SQL statements (one statement that is hundreds of lines). You wouldn't do that in a "proper" programming language; but in languages like SQL (or maybe Excel) this will happen.
Programming language and set manipulation languages are incomparable. The first is a language for describing procedural instructions and the last is a way to describe set transformations. It's not wise to apply the same set of standards to both. It's like complaining a proof is tediously long, when the actual underlying operations are simple -- you're missing the point. SQL has to be long and detailed -- when you want to be sure the data you're receiving is exactly the way you specified it to be (i.e. no compiler shenanigans where it turns your rough intent into concrete instructions).
However, I will concede that I've seen some monstrous SQL in the wild... mostly written by people who don't really know SQL... and which could've been greatly shortened by knowing the little tricks that are database-specific (and similarly, I've seen enough people request a dataset to do processing on the server-side in herculean efforts, which could've been done faster and written quicker in the database).
> Another problem is proper encapsulation. Views can be used, but often developers have access to all tables, and if you have many components this becomes a mess. Regular programming languages have module system and good encapsulation.
I don't understand. Permissions can be tweaked however which way you want. Am I missing something?
> SQL statements often don't use indexes, for one reason or the other. With regular programming languages, it is harder to end up in this position (as you have to define the API properly: encapsulation). Regular programming languages don't require "query optimizers".
This is a trivial problem; but if you don't know why it happens, I can understand being befuddled by it. Query optimizers are involved so your access to the underlying data -- and the manipulations on it -- are done in the least costly fashion (i.e. in a rough sense to reduce the algorithmic complexity of your SQL to its least possible complexity) in regards to the set of hardware your cluster is running on, and the various access statistics involved. Regular programming languages do not have "query optimizers" -- but various libraries do have their own optimizations to reach the same end (as do compilers... compilers are nothing but optimizations upon optimizations in the same vein).
> SQL is used to reduce network roundtrips. Clients send SQL (or GraphQL) statements to the server, which are actually small programs. Those programs are executed on the server, and the response is sent to the client. (Stored procedures and for GraphQL persisted queries can be used - but that's often happening afterwards.) Possibly it would be better to send small programs written in a "better" language (something like Lua) to the server?
It would be very inefficient for general purpose. For most cases, the query optimizer can take your SQL and bring back your result sets in the most efficient way possible (in relation to the SQL you've written).
If you want to do the underlying operations yourself, instead of relying on the query optimizer, then you can use a compiled language like C and write your own extensions -- though it would take much more effort to do it properly than simply learning how your database works, and how to work with the query optimizer (rather than against).