yes, its really cool, and lightweight. Just remember, when you work with multiple layers, you are going to have to issue the index query. Manifold and PostGIS do this implicitly with the spatial index. For some reason, spatialite does not, so you have to explicitly reference that last part.
What I do is issue a create table query as:
CREATE TABLE ExampleTable AS SELECT......
then, before issuing a query I always use DROP TABLE ExampleTable
Manifold still makes this easier with SQL since you can link drawings really easily, or highlight selected features (you can use QGIS linked to spatialite, but its still an import). However, without a new product in 4 years I've had to start thinking about other options for spatial SQL.