[jgeraert]

I tend to abstract away my queries using database views. This way the query in application becomes very simple (select * from view where $simplepredicate).

In my test database (sqllite,hsqldb,derby,...) i create actual tables containing test data with the same definition as the views.

This allows me to have rather complex queries that are database specific and still have fast running database queries.

The views themselves i test separately. They are also easier to fix in production than code releases, and can also be replaced by materialized views if necessary.

Inserts/updates are typically simple too.