There's more...

The Local SQL is based on the SQLite database and supports most of the SQLite SQL dialect.

All the read and write operations are performed through the TDataSet API with some extensions, which means that FireDAC performs the operations by converting SQL into dataset calls. This is the reason why you can execute SQL statements against any dataset—FDQuery, IBQuery, ClientDataSet, third-party components, and so on.

The possible applications of Local SQL are (from Embarcadero DocWiki):

• Heterogeneous queries: Queryable datasets have result sets from different DBs
• In-memory database: TFDMemTables serve the datasets
• Advanced offline mode: In this case, although the main DB is not accessible, an application is still able to perform SQL queries
• Advanced DataSnap client: The data delivered by the DataSnap driver to the client can be queried locally
• Simplified migration: A developer can use the third-party TDataSet objects in an application, and can use a FireDAC API to work with these data sources

Here some important notes (from Embarcadero DocWiki):

• The Local SQL engine does not support datasets with multiple result sets.
• The Local SQL engine supports the INSERT/UPDATE/DELETE SQL commands as transactions and savepoints. Also, it transforms the corresponding SQL commands intoTDataSet API calls.
• The Local SQL engine supports INSERT/REPLACE, but uses only primary key fields to find a record to replace when a primary or unique key constraint is violated. Additionally, when only several fields are specified in INSERT/REPLACE INTO tab (<field list>), the fields that are not specified get null values on updating.
• The Local SQL engine uses the TDataSet API with some extensions provided by the IFDPhysLocalQueryAdapter interface. FireDAC datasets implement this interface. Optionally, for non-FireDAC datasets, a developer can create a class implementing the interface and assign its instance to the TFDLocalSQL.DataSets[..].Adapter property.