Best tool for assisting writing complex SQL-queries?
I often find myself spending a lot of time figuring out why certain SUM()-aggregates sum up wrongly in SQL-queries. This problem often occurs if I do not take care when adding a JOIN, resulting in duplicate values being summed up etc. If I work with a big query with lots of JOINs, nested subqueries, GROUP BYs etc. things get very complicated to debug.
I would like to know if there exist a tool that will make it easier to construct and debug complicated queries, by graphically illustrating the data being summed up, joined together etc.
An example of what I'm looking for is illustrated in http://www.imada.sdu.dk/~sorenh07/misc/datacentric-querytool.png
It is of course just an early mockup, and I am interested in any comment related to this.
Answer
IMHO, the best "tool" for this is a textbook. This is one of those areas where the software can't do it for you. Sure, it can build simple selects & joins, but not aggregate functions or groupings, or sub-selects, etc. For that, you've got to learn to do it the old fashioned way -- by hand/wetware. I recommend: "Head First SQL" by Lynn Beighley, via O'Reilly Press.
- Get a book.
- Install the database of your choice.
- Import some test data.
- Try to execute queries using as many functions as possible.
Practice, practice, practice.