How to Write Database Test Cases

OneCompiler supports test cases in databases for the automatic evaluation of code in database-based challenge problems. There are two ways the submitted code can be evaluated.

Little bit about init.sql

Before exploring different methods of writing test cases, let's understand init.sql.
init.sql helps setting up the data for the test. You can add the init.sql by following the below steps.

1. Click on the + icon next to queries.sql
2. Select Add init schema

1. Asking the user to print certain data and validate it

A common use case is to ask users to write queries that print specific data. For example, if we have added the continents init data, we can ask the user to write a query to print the total count, which we can then validate.

In such cases, there is no STDIN, so we can leave it empty or set it to NA. In the STDOUT section, we can specify the expected output.

2. Asking users to modify the data/ structure and validate the database state

If the problem requires the user to perform modifications on the database, we can test whether these changes were made correctly by executing queries against the final state of the database.

For instance, if the task is to delete the record named Antarctica from the continents table, we can write test cases as shown below to ensure the user has made the required changes.

These are the differerent ways you can add test cases in OneCompiler. You can also take this Challege to understand how the validations work
https://onecompiler.com/challenges/3zwxg494r/mysql-beginners-challenge