How to Create a Database Query Question via Mercer | Mettl UI

How to create a database query question via Mercer | Mettl UI

The process to create a Database Query question via Mercer | Mettl UI can be divided into two steps: 

Creating and Defining the Database 

To create and define a database, follow the steps mentioned below: 

1. After logging in to your Mercer | Mettl account, click on Account Settings on the right most corner, then click on Account and go to Global Settings.

2. In the Global Settings Page, click on Manage DB Schemas. 

3. You will now be able to see and edit only the name of the already existing schemas (if any) by clicking on the edit button. Click on the Add New Schema button to add a new database.

4. Add the respective details of your database and select the variant(s) from the given list of supported variants. 

5. Now in the next step you have to define your database schema by entering syntactically correct data definition queries (DDL commands). This can be done in either of two ways: 
  1. Click on Upload Schema to upload a .sql file containing the queries (the file’s size should be less than 5MB). 

  1. Click on Write Schema to write the queries in the Schema Definition Query Editor and then click on Save Changes 
Note: If you choose multiple variants, then you have to upload/write schema queries for all the variants that you 
6. Once the DDL queries have been added, let's proceed to DataSet(s) section to fill data into our schema.  

For this, Click on Add Dataset and proceed by adding a dataset title. You again have the option to upload a .sql file (the file’s size should be less than 5MB) by clicking on Upload SQL or write syntactically correct DML command to insert data into the dataset by clicking on Write Query.  

You also have the option to import datasets for all the tables created, separately by selecting the Import Dataset option. Select the table you want to import data for, from the dropdown and then click on the Import Dataset button to upload a .csv file (Please make sure that the first row contains the column names, and the file size should not exceed 5 MB).

If you have selected multiple variants, you will have to give datasets for all the variants separately, like shown in the image below.

You also have the option to add multiple datasets. After you are done defining all the datasets, click on Save Schema to finish saving the schema. 

Creating a Database Query Simulator Question

To create a Database Query Simulator Question, follow the steps below:

1.  After logging in to your Mettl account, click on the My Questions tab on the top and then click on the Add Question button on the top-right.

2. Click on Select Question Type and go to Application Based Questions in the drop-down options and then select Database Query Question.

Provide a topic name to which the question is supposed to be added by clicking on the Select Topic button. In case the topic is not one that already exists, you can create a new one.

3. Enter the question-specific information in various fields like the image given below:

Question Details: The description of the question will come here, which should include the problem statement. The Problem Statement should include specific instructions on the desired output of the query.
For instance, “Write a query to get top five employees from each department in ascending order of their name. Employees without any department are to be excluded. 
The output should show ENAME, DEPTNAME, SALARY attributes in the same order.”
Note: You can make use of the editor menu on top to add specific formatting in the problem statement such as images, code snippets, etc.
• Difficulty Level:  Assign a difficulty level to your question. You can set it to Easy, Medium, or Difficult by clicking on the drop-down. 

• Question Tags (Optional): This field stores the meta-data about the question. 
Note: You can leave this field empty.
• Select Schema: Select a schema from the dropdown menu. These are the same schemas which you have created.  
Clicking on Manage Schema will take you to the All-Database Schema page where you can view and edit the existing schemas. 
Note: Only the name of a database can be edited, no other feature of a database can be changed once it is created. 
After selecting the Schema, the database tab gets opened.

Expected Query: Enter the correct output query that will be used to retrieve an output dataset against which the output retrieved from the test-taker’s query will be checked for grading.
Note: Make sure this query is syntactically correct. In case you have added multiple database variants, you will be required to enter expected output queries for all of them in separate query editors.

Order By (Optional): Enter the order in which the output of the expected query would be displayed in case the question demands the output to be in a specific order.  
Use the following format for entering the order: `field name(s)` [asc/desc]

You can leave this field empty as required by the question.

You can click on View Schema to view details of your schema.
If there are multiple database variants available in the schema that you have chosen, you have to write the expected query for all the variants. 

Enable Mark as Default for whichever database variant you want to appear on the test-taker's screen by default.

Click on Add Test Case to proceed.

4. Now enter testcase details accordingly. Enter the Testcase Title, select the Testcase Dataset from the drop down and enter the Testcase Marks. If the database schema has only one dataset, enable the is Default checkbox to make the default testcase same as the weighted (graded) testcase. 

If the database schema that you have selected has more than one dataset, you can add multiple test cases depending on the number of datasets in your schema. In this case you can enable the is Default checkbox for any one test case of your choice. 

Every Test Case is associated with a particular data set and one of them is marked as is Default which makes that testcase also the default testcase. The Default Test Case gets checked when the test-taker clicks on the Check Query Button and the weighted testcases are executed when the test-taker clicks on the Run Query Button. The testcase holds a grade which will be awarded to the test-taker only if it passes. 

Click on the Save button to finish saving your question.