Depends on what you mean by "trivial".
You can use a database for most of the things most websites use them for without ever using a JOIN. It just takes application logic instead of SQL.
Sites like this aren't exactly heavy on the SQL. This site doesn't even use SQL. Something like Reddit or Slashdot would, but still wouldn't need JOINs.
> I've never yet seen a non trivial RDBMS based app where the existence of an ORM absolved developers from having a good grasp of SQL when they needed to drop below the abstraction layer provided by the ORM.
You don't have to drop below it; you can go "above" it into the application layer.
> If you have a PhD' from MIT and/or have a strong track record of writing Open Source software, creating and selling a startup, build robots (and electric unicylces ) for fun etc, [...] But then if you could do all that why would you look for a job?
Let's consider the case of someone with a PhD who ISN'T famous, and then you give him some kind of SQL test and he fails. "Man, what a load of bullcrap his PhD is! He can't even do a simple JOIN!"
There are a lot of people who aren't "PhD millionaires" or famous open-source magnates and yet are vastly capable. A lot of them don't write joins. And a lot of people who CAN write joins can't do a bunch of other stuff.
You know what else PG doesn't do? CSS. He runs a website but doesn't use CSS or SQL.
So, once again, the danger of using any litmus test is you can end up filtering out extremely capable people who don't do X (but could obviously pick it up quickly), while passing minimally-capable people who CAN do X.