The SQL Post Hook Query test type is useful for checking submitted queries that aren't explicitly a simple SELECT query. You can use it to check that certain insertions/deletions/modifications were made, or that certain columns/tables were modified. It can be very easy to set up, only requiring as little as a single SELECT query that you define to check that the submitted query did what it was supposed to do.
How it works
The SQL Post Hook Query tests are very similar to the Python Post Hook Script tests, just a bit more limited. Instead of a post hook written in python, you write the post hook in your variant of SQL. This post hook needs to be a SELECT statement that you'd like to run after the submission query, and the output of this SELECT statement will be used for comparison instead of the submission query itself.
The SQL Post Hook Query test will perform the following:
- Create a database
- Use your specified data set to seed the DB
- Run the specified query from the submission
- Run the SQL post hook
- Compare the post hook output for the submission with the solution's post hook output