Hi

I've been a commercial analyst for just over a year and spend a lot (most) of my time writing (mostly ad-hoc) SQL queries, mostly in Oracle's SQL Developer but also in Microsoft's SQL Server. Prior to this role I had no experience with SQL and only hobby experience and 3 university courses that involved coding. I enjoy coding a lot and want to raise the quality of my code especially the outputted results and also my productivity. 

So far I've read two solid texts on Oracle SQL and T-SQ, and "The Clean Coder" by Robert C. Martin. All books have been superb introductions to the languages and some best practices (unit testing).

I'm now wanting to have a more robust method for testing the quality of the data my queries output before I hand them over to management.

I've asked around the office, googled and looked around Amazon but haven't found much pertaining to testing SQL queries, although there is a lot relating to test driven development it mostly relates to non-SQL languages.

What I've come up with so far is

i) test what others tell me about the tables and test the opposite

ii) check for duplicates where there shouldn't be any

iii) break the query down to bite size pieces (UNIX approach do one thing and do it well) and put the output in to views or tables

iv) run unit tests over those views/tables

v) get the plan, code, output peer reviewed by a SME

vi) try a different approach to answer the question. Are the results similar?

vii) spot check the results

viii) write down all the checks I've done

ix) visualise the data results

What I'd like to find is a thorough way to test, that can pick up the things I didn't know that I didn't know. 

Thanks in advance