Exactly they both have scenarios that they work very well for just like you have with the server-side SQL RDBMS and the NoSQL ones. IndexedDB is fine for simpler apps or apps where there isn't a real need for joining, grouping and ordering the records in a very flexible way.
Web SQL is definitely dead in native Chrome apps. It was never born actually given IndexedDB was the only solution that was pushed.
I read all the concerns (it's a standard built around sqllite, and who wants that?!?!, etc.) and I still sigh a sigh of disappointment that it's come to this. I know, Web SQL isn't going away, as the author points out, but it will be a 2nd class citizen for the foreseeable future, and that's just a missed opportunity, imho.