Adding SQL Tests

Summary

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

Test Options

There are two "modes" you can use (options are covered in more detail later):

  • 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.

The options are as follows:

Description

A short summary of this test (shown to users if test is shared)

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:

SELECT * FROM people WHERE age > 65;

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.

NB: 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

If disabled, then the test will run the SQL Statement when the test is run. If enabled, the user's query will be used.

Enforce Order

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

Example

A complete SQL database test

Here we have two queries being run against a DB. For labs where a user is manipulating a DB heavily, a pre-configured DB can be used to ensure the accuracy of the results.

There are 3 fields in the SQL Test results:

  1. Solution Query - the query used get the results the check is expecting.

  2. Expected Results - the results the check is expecting to be returned by the user's query

  3. Actual Results - what the current state of the user's DB is returning. Visible in the SQL Query Editor.

SQL Query Editor