Assess candidates' database skills with SQL questions. These questions can be configured for multiple languages: MySQL, PostgreSQL, and/or Microsoft SQL. This article will cover:
Setting up a new SQL question
1. After selecting SQL from the Choose question type modal, you will be taken to the question creation window. Start by giving your question a name and (optionally) adding any labels within the Global Settings.
Note that when you click in the Labels field, you'll see your existing labels. You can also type in a new label.
2. Next, select an environment for the question with the + ADD AN ENVIRONMENT button above Global Settings. You can select multiple environments by adding them one at a time.
3. Click the Environment Settings icon in the left navigation bar, below Global Settings. Here, you can configure settings specific to the environment you chose:
a. SETTINGS: Add your question description to the Description field
b. TESTS: Configure test cases for your question (see next section for more detailed instructions: Adding & configuring test cases for your question)
Optional: In the code editor the right, you can also add your author solution for the question in the authorSolution.sql file. This can be used to automatically generate the correct outputs for your test case inputs.
4. To select another environment to add to your question, click the Environments button near the top of your screen.
To remove the environment, simply click on it.
5. To navigate between the environment you've selected, click the name of the environment in the top left.
6. When you're done setting up the question, click SAVE ALL in the bottom right.
Adding & configuring test cases for your question
The TESTS tab of the SQL question creation window allows you to customize the following settings for each environment:
Note that you'll need to configure these Environment Settings separately for each environment you select for your question. Customizing each of these settings is optional.
Setup and teardown scripts
You can edit the setup and teardown scripts by clicking on the respective script name. The setup or teardown script will appear in the code editor to the right.
- The setup script will be used to initialize the database before running each test case. Note that both the setup and teardown scripts must be compatible with the environment that you chose (e.g. MySQL).
- The teardown script will be used to reset the environment after running each test case.
Add as many test cases as you'd like by clicking the + ADD A TEST CASE button. When you add a test case, it will create two files in the IDE, e.g. "Test Case 1 - Input" and "Test Case 1 - Output," where you provide the inputs and outputs for the test. For each test case, you can:
- Rearrange the order in which tests cases will appear to the candidate by clicking and dragging above or below others.
- Autogenerate output from the author solution with the arrow in a square icon. You can also do this for all test cases using the checkboxes to the right of the list of test cases.
- Delete the test case with the trash can icon.
Add any tables that should be visible to the candidate (i.e. the tables they will need to access to write their solution). You can manually add tables or autodetect tables in your starter code under the Show Tables heading. Add and name tables manually, or click the AUTODETECT & REVIEW TABLES button.
Choose whether to make the first half of your test cases visible or candidates, or select a custom number of test cases visible. Note that you can order your test cases in the Test Cases section.
Making some of your test cases hidden to candidates is a best practice for preventing cheating.
Questions? Contact firstname.lastname@example.org