Download the Template 

   1. Log into your Mercer | Mettl account, click on the My Questions tab on the top-left and then click on the upload icon next to the Add Question button on the top-right end.

 2. A popup to Upload Questions via Excel will appear. Under the Upload DB Query Questions section, click on the Get Template button. 

    3. A window will open asking you to download the template in the format of an excel file. Download the file to continue. 

    4. As you open the excel file, you will see that it contains four sheets: Instructions, Questions, DB Types and Test Cases.

• Instructions: This sheet contains information and instructions on how to fill in data in the Questions sheet, DB Types and the Test Cases sheet.

• Questions: This sheet contains the questions that need to be uploaded, along with their respective details.

• DB Types: This sheet contains the Database Variants along with their details.

• Test Cases: This sheet contains the test cases associated with the questions mentioned in the Questions Sheet, along with their details.

1. Open the Questions sheet in the excel file.

2. Fill in all the questions that need to be uploaded, along with the relevant details, as mentioned below:

Note: Do not change the headers of this sheet as it will cause an error when uploading the excel file on the platform.

• Question ID: Enter a unique numeral ID value for all questions. This ID is used in linking Test Cases & DB Types to a particular database query question

• Topic: Enter the topic name under which you want the question to be saved on the platform.  

Note: Multiple questions can have the same topic name, but one question can have only one topic name. However, questions for varying topics can be uploaded simultaneously. 

• Difficulty Level: Enter the appropriate difficulty level for the question i.e. (Easy, Medium, Difficult).

• Question Text: Enter the description of the question or the problem statement here, which 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.”

• Database Schema: Enter the name of the database schema the question is based on.

The corresponding database schema should already exist otherwise it has to be created before uploading the database query questions. 

To view the existing schemas or to create a new one, visit https://mettl.com/corporate/manage-db-schemas or log into your Mercer | Mettl account and go to Account Settings >> Account >> Global Settings >> Manage DB Schemas.

• Num of DB Types: A database question can be implemented in six Database Variants, i.e., MSSQL 2016, Oracle 19c, MySQL 5.7, MySQL 8.0, PostgreSQL 14.2, MariaDB 10.6. Enter the number of database variants supported in the schema the question is based on.

• Num of Test Cases: Enter the number of testcases you want the test taker’s queries to be run against.

• Custom Category – Other Properties (Optional): Use this field if you want to tag your questions based on any custom criterion. This field is optional and can be left empty.

Fill the DB Types Sheet in the Excel File

1. Open the second sheet named DB Types.

2. Fill in all the database variants associated with the questions, with the following details:

Note: Do not change the headers of this sheet as it will cause an error when uploading the excel file on the platform.

• Question ID: Enter the Question ID of the question you want to enter the database variants and related fields for. 

• DB Type: Enter the name of the database variant for which you want to add expected queries. The database type can be either MSSQL, Oracle, MySQL, PostgreSQL or MariaDB.

• DB Version: Mention the version of database being used. The version should be synonymous with the database type. Enter "19C" for Oracle, "2016" for MSSQL, "5.7" or "8.0" for MySQL, "10.6" for MariaDB and "14.7" for PostgreSQL.

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

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

Note:  A single question can have multiple DB Type. For every DB Type, the DB Version, Expected Query and Order By has to be specified.

• Is Default: Enter TRUE for whichever database variant you want to appear on the test-taker's screen by default, for all the other DB Type enter FALSE.

Fill the Test Cases Sheet in the Excel File

1. Open the fourth sheet which is named Test Cases.

2. Fill in all the Test Cases associated with the questions, with the following details:

Note: Do not change the headers of this sheet as it will cause an error when uploading the excel file on the platform.

• Question ID: Enter the Question ID of the question you want to enter the testcases and related fields for.

• Test Case Index: Enter the serial number of the test case.

• Test Case Name: Enter the name of the test case, it will be displayed while running the queries.

• Data Set Name: Enter the name of the dataset to which the test case will refer.

• Test Case Marks: Enter marks to be assigned to this particular test case. 

• Is Default: Enter TRUE to make the default testcase same as this weighted (graded) testcase, otherwise enter FALSE.

Note: Always mark Is Default TRUE when there is only one test case for a question.

Upload the Excel File

1. After filling in all the question(s), DB Type(s) and their test case(s) details in the Excel file, save it. 

Note: It is advised not to copy the contents of the template file and add it in a separate excel file as it may cause errors while uploading the secondary excel file.

2. Log into your Mercer | Mettl account, click on the My Questions tab on the top-left and then click on the Upload Arrow next to the Add Question button on the top-right end.

3. A popup to Upload Questions via Excel will appear. Click on Upload DB Query Questions and select the excel file with the Bulk Upload Code Snippet Questions.

4. Now, either of the two scenarios will take place:

i. The File gets uploaded successfully: Successfully updated n questions will appear on your screen, n being the number of questions uploaded in bulk. This means that there are no errors in the excel sheet and all the questions have been uploaded successfully. Click on Upload More? To upload another excel sheet having Bulk Upload Questions or else click on the cross to exit. 

ii. The File does not upload: On the Upload Questions via Excel window, an error message will pop up saying Errors found in uploaded file. Please try again after correcting all errors. Click on Get error file to download the Excel file which points out the exact question filled in incorrectly and shows the reason for the error. Correct the mistakes and try uploading the file again.