Basic SQL yeah, probably. Actually knowing SQL and how to write good SQL and understanding the tradeoffs between different query plans (and the tradeoffs between different schemas). That's rare.

And yeah, obviously, there will be bugs in your SQL. And writing good tests for the database layer is always tricky because there's usually some pushback to setting up a database on the CI instance, and so on. It's not simple, but ignoring it by using an ORM doesn't make it simpler, it just means you have less options when it goes wrong.

It makes sense that some kinds of schema optimizations are fairly esoteric, because the services most devs work on don't need them. An ORM is something you import on day one to speed up development; while rearranging tables to remove joins may not be needed (or even justifiable based on metrics) for another five years. I would hope that the ORM is flexible enough to be irrelevant to the trade-off between schemas though. Your comment about not having a database in CI brings back nightmares! Reminds me of how at my first job they had us running SQLite in CI and then shipping to Postgres, which worked about as well as you can imagine haha