SQL Tests
SQL tests allow you to pre-generate a set of results to compare against the records returned from a database query. You can use them to:
  • Check that specific results do (or do not) exist in the database (useful for checking INSERT, UPDATE, and DELETE statements)
  • Verify that the correct records are (or are not) returned from a
    SELECT statement
  • Ensure that records are returned in the correct order and with the correct headers

Adding a SQL Test

To add a SQL test, navigate to Checks in the left sidebar and use the New Check + button. Next, select the SQL Test option in the drop-down and see the settings below:

Settings

Description

A short summary of this test. Shown to users if the task is live and checks are shared (which is the default).

SQL Statement

A valid SQL statement (usually a SELECT) to retrieve data from the current database with. If this is a data test, then the same statement will be run against the user's database when the test is run. For example:
1
SELECT * FROM people WHERE age > 65;
Copied!
This query will also be shown to users if the test is shared, so it's best to illustrate good SQL formatting. A useful formatter can be found here.
If you'd like to check the structure of a table, use the DESCRIBE keyword in your statement instead of SELECT.

Results

The results generated from the SQL Statement and the current state of the database. Results are limited to the first 100 rows returned, so if order matters, use an ORDER BY clause in your SQL Statement.

Database

The name of the database to query. If left as the default {{database}} template, then the value set in the SQL results viewer will be used.

SELECT Query

There are two "modes" you can use:
  • Data test (SELECT Query option disabled): Test the data in the database using the same SQL Statement you wrote the test with.
  • Query test (SELECT Query option enabled): Test the SQL statement written in the file the user has open when the test is run.

Enforce Order

Enable if the order of the results matter (or if there are 100+ of them).