chore: enhance the logic for pure query checks

[GaoYusong]

• Removed the trick of checking database() function and made it explicit. The trick can lead to false positives, e.g., subqueries could be reduced.
• Changed len(tables) > 0 to check if there are data tables (tables in DuckDB).
• Walk through subquery nodes to check for nested tables.