CODEWITHSUNDEEP
sql
Shaolin
Shaolin

Reputation: 434

Methodology to check if a SQL query is correct for a context

I am doing dozen of queries per day, and I admit sometimes I miss the context of the user's demand.
I would like to know if you have any tips to check / double check, if a SQL query is really doing what the context is asking.
For example, I have this context :
retrieve the firstname of 10 male students from the school "great_school" having 21 years old.
For this context I would write a pseudo query like this :

SELECT st.firstname
FROM studient st
JOIN school_studient sc_st ON sc_st.studient_id = st.id
JOIN school sc ON sc.id = sc_st.shool_id
AND sc.name = "great_school"
WHERE st.age = 21
AND st.sexe = "male"
LIMIT 10

How to be sure that this query is really doing what the context asked ?
It isn't about using EXPLAIN to check if the query is valid, it is about checking that the query has all the conditions needful.
Is there any tools who is able to read pseudo query and tell what it does in human language ?
I was thinking about a paper checklist with 2 columns : "fields to select" and "criterias", and then I tick everytime one item is in the query.
But isn't there more advance tools than a piece of paper ?

Upvotes: 1

Views: 113

Answers (1)

Neville Kuyt
Neville Kuyt

Reputation: 29649

Your question is asking for a recommendation for a software product, which is off topic on SO, but you might have more luck here.

However, I would focus more on a process than on a tool. I find it really helpful to work with sample data sets, and ask the end user to mark up what should and should not be included. The challenges in interpretation are usually much more around "what's in/out", "how do we aggregate (what's the group-by)" and "how do we sort" (your example grabs 10 random students).

If you can build a sample data set, and ask your users to say "I want these records to be included, those excluded", and "I want you to aggregate this column for every change in that column", you get a much higher quality specification. Once you find problems in the specification, you can adjust the sample data set to avoid that problem in future...

Upvotes: 1

Related Questions