As you could read in the tutorial, there are two families of SCT functions in sqlwhat:
- result-based checks, that look at the result of the student’s query and the solution query
- AST-based checks, that look at the abstract syntax tree representation of the query and allow you to check for elements.
This article gives an overview of all result-based checks, that are typically used as the first argument to
On the lowest level, you can have the SCT fail when the student query generated an error with
Next, you can verify whether the student query actually returned a result with
Behind the scenes, this function first uses
has_no_error(): if the query resulted in an error, it cannot return a result:
More high-level, you can compare the number of rows of the student’s query result and the solution query result with
This is useful to check whether e.g. a
LIMIT clause was coded up correctly to only return a subset of results:
Similarly, but less-used, you can also compare the number of columns the student’s query and solution query returned. This function fails if the number of columns doesn’t match. It passes if they do match, even if the column names differ:
check_row(), you can zoom in on a particular record of the student and solution query result,
so you can compare there values later on with
has_equal_value() (see further).
The example below zooms in on the third row (with index 2) of the student query result, returning a state
that only considers the data for the third row. It fails if the student query does not contain 3 rows.
Similarly, and more often used than
check_row(), you can use
check_column() to zoom in on a particular column
in the student’s and solution query result by name. The function fails if the column cannot be found in the student query:
Often, the solution selects multiple columns from a table. Writing a
check_column() for every column in there would be tedious.
Therefore, there is a utility function,
check_all_columns(), that behind the scenes runs
check_column() for every
column that is found in the solution query result, after which it zooms in on these columns. Suppose you have a solution query
that returns three columns, named
column3. If you want to verify whether these columns are
also included in the student query result, you have different options:
# verbose: use check_column thrice Ex().multi( check_column('column1'), check_column('column2'), check_column('column3') ) # short: use check_all_columns() Ex().check_all_columns()
As an extra in
check_all_columns(), you can also set
False, which causes the function
to fail if the student query contains columns that the solution column does not contain.
True by default.
All of the functions above were about checking whether the number of rows/columns are correct, whether some rows/columns could be found in the query,
but none of them look at the actual contents of the returned table. For this, you can use
has_equal_value(). The function simply
looks at the student’s query result and solution query result or a subset of them (if
check_all_columns() were used):
# compare entire query result (needs exact match) Ex().has_equal_value() # compare records on row 3 Ex().check_row(2).has_equal_value() # compare columns title Ex().check_column('title').has_equal_value() # zoom in on all columsn that are also in the solution and compare them Ex().check_all_columns().has_equal_value()
has_equal_value() will order the records, so that order does not matter. If you want order to matter, you can set
Ex().check_all_columns().has_equal_value(ordered = True)
Finally, there is a utility function called
lowercase() that takes the state it is passed, and converts all column names in both
the student and solution query result to their lowercase versions. This increases the chance for ‘matches’ when using
Suppose the student did
SELECT Title FROM artists
while the solution expected
SELECT title FROM artists
Depending on the SCT you write, it will pass or fail:
# SCT that will fail Ex().check_column('title').has_equal_value() # SCT that will pass (because Title is converted to title) Ex().check_column('Title').has_equal_value()
For advanced examples on how result-based checks are typically used in combination with
check_correct(), check out the glossary!