I’m migrating away from Google Sheets, so far with a lot of success, but I miss certain functions that at not included in the latest version of Calc. e.g =QUERY.
Query is a powerful function that I am finding it impossible to replicate with other functions that come with Calc
You would need the help of Base for this. It is possible to have a spreadsheet as datasource of an odb. Then you can query this datasource and drag the queries in other spreadsheets.
.
Not so powerful as using a true database, but if someone want’s to do it this way…
Thanks 
looks like It’s been there for a while. Hope someone picks it up and adds it soon so I can leave Google Sheets.
Some time ago, I played around with HSQLDB in-memory databases. You have to have Java installed for that.
This enables you to use any valid SQL clause, including join between tables. No need to create a separate .odb file.
Basic procedure is:
Define names for the data you want to use as SQL Tables
Format the header cells with the right format. HSQLDB is using that format for the data type. Currently supported is @ (text), Boolean, and numeric.
The rest is described in the sheet AnalysisStadtKreisLand
For non-German users: The raw data are a lists of Stadt (=town), Kreis (= county), and Land (=state) from Germany
Maybe a bit overkill …
inline-database - posted.ods (221.3 KB)
Without any macro code and overkill, everybody can use Base as is in order to query spreadsheet lists as well as ordinary databases by means of SQL SELECT statements.
Thank you
You are absolutely right with that. The motivation for my approach was for two reasons: (i) it uses only a single file, not an ods and an odb. This makes it easier if you want to iterate with more than one person. If you have something stable I would always recommend to go with base (ii) the raw data are stored within the calc document. So you do not risk data loss when going with the built in hsqldb database, which some users have reported to be somewhat risky
It seems that HSQLDB is going to get phased out from Libreoffice in favour of Firebird. [ Migrate from HSQLDB - The Document Foundation Wiki ]
Most likely latest HSQLDB 2.x versions wont see day of light in Libreoffice .
Thus can we have something like your suggested inline-database but uses Firebird instead of present HSQLDB 1.8
Until now, it is still the better database to be used with Base, given that you convert your embedded HSQLDB into a stand-alone database with a more recent driver when leaving the draft status.
Firebird has too many serious issues. Even MySQL/MariaDB is plays better with Base.