Disregarding security issues, it breaks a level of abstraction. If you provide an API for say "user data", the client doesn't have to know the schema behind it. Whether the data is stored in a single table or multiple tables. Imagine if you have been using a system for a while and then they decide to alter the schema. You would have to relearn the schema, change all the queries you've been using, etc. Whereas with an API, backend changes wouldn't affect you as you'd still be getting the same "user data" via the API.

Another problem is the SQL flavor implemented by RDBMs. Every major RBDMs has their own distinct flavor of SQL. So forcing everyone else to learn a specific flavor of SQL is going to be a hassle. Especially if the RDBMs was changed.

But the biggest issue is security and performance ( especially true for sensitive data ).

APIs are there to make things secure, user friendly, consistent and of course robust in the face of back-end changes. I doubt most companies or people would allow direct access to their DBs. If people wanted to run queries on their dataset, they might let you download the dataset and run the queries locally.