Creating a custom SQL question

Creating a custom SQL question

1. Log in to HackerEarth Assessment using admin credentials

2. Click Library.

3. Select My library and choose SQL under Question types.

4. Click Add sql question.

Creating a Problem Statement

The question-creation template is displayed on your screen with the following fields:

• Problem Name
• Problem Statement
• Difficulty level

5. Add the name of your question in the Problem Name field.

6. In the Problem Statement field, enter the problem statement that you want the candidate to solve. A good problem statement has the following:

• Description: This part describes the problem that the candidate must solve. This part also describes what the issues are and what is required of the candidate to solve the problem. 
• Table description: This part contains all the relationship details and sample data. For example, if there are two tables and one of them has some relationship with the other table regarding joins or foreign keys, then all the relevant information is to be stated in this section.

7. Set the complexity of your question from the Difficulty level list.

8. Click Save SQL Question.
The existing SQL template will be loaded with the following additional fields to enable you to create a question:

• Editorial
• Sample explanation
• Allowed Databases
• Schema creation scripts
• Sample Testcase 
• Testcase Files
• Edit checker limits
• Answer
• Tags
• Code Snippets

Writing an editorial and sample explanation

9. In the Editorial field, describe one or more ideal approaches that a candidate can take while solving this problem. The editorial will be displayed only after the test ends.

10. In the Sample Explanation field, give an example or describe relevant use cases for a candidate’s reference. This will guide them in selecting the right approach required to solve the problem.

Setting up databases and creating schemas

11. You can use the same schema file (SQL file) for all supported databases instead of uploading them individually by clicking Use single scheme script for all allowed databases 

Notes

• All the required constraints must be added to this .sql file only. For example, refer to the sample schema in this sample .sql file.

                       Sample schema

Adding sample test cases

12. You can add a sample test case manually by entering the table name and uploading the test case. Enter the data in Sample test case to upload the test cases that will be used to set up a test database for a candidate. When the candidate clicks Execute & Run, the queries are executed in this database. These test cases are not scored.
You have two types of test cases:

• Input test cases
• Output test cases
  
  1. Input test cases
  Important: The data in input data files must be pipe-separated (‘|’) as shown in the following image. The data must also not contain any column names.

                                 Sample input test case

Upload the input test cases in the Input section by following these steps:

• Click Upload File.
• Navigate to the folder where the file is saved.
• Select the .txt file.
• Click Open.
  

2. Output section

Important: The data in output data files must be space-separated as shown in the following image. The data must also not contain any column names.

Upload the output test cases in the Output section by following these steps:

• Click Upload File.
• Navigate to the folder where the file is saved.
• Select the .txt file.
• Click Open.
  

This is how you can easily add sample test cases.

Adding actual test case files

A similar procedure can be performed for adding the Actual test case files. These test cases are the hidden test cases that get evaluated based on the candidate’s submission. 

13. Click +Add Test case and follow the similar steps used to add sample test cases. 

Making checker settings

16. In Edit checker limits, you are required to set the following parameters:

• Time Limit is the maximum time duration (in seconds) in which a candidate's code must be executed for individual test cases. The maximum time limit that can be set is 10 seconds.
• Max Code Size is the maximum size (in KB) of code that can be submitted by a candidate.
• Memory limit is the maximum memory (in MB) that a candidate's code can utilize while being executed.
• Max Tables is the maximum number of tables. This value is set to 10 tables by default. However, it can be changed based on your requirements.
• Max Rows per Table is the maximum number of rows in a table. This value is set to 30 rows by default. However, it can be changed based on your requirements. 

Note: If the table contains 100 rows then max rows per table must be any number that is greater than or equal to 101.

Adding the answer

17. You can add your solution to the problem in the Answer section. For example, you want the candidate to write a query to print the count of the unique number of engineers having a ‘software’ job from the table employee. In the Answer section, you must write the correct solution or query to solve the problem that will be 

select count(engineers) from employees where engineers = ‘software’. 

Adding tags

Tags are important because they allow you to segregate different question types in your library. This makes it easier to search for the type of question you are searching for.  For SQL questions you have the following four tags that can be used:

• SQL
• MS SQL
• Postgresql
• Oracle

18. Add Tags in the field provided

Saving the question

19. Click Save question once all the requirements have been completed.

 

You can also watch this video to learn how to create a custom SQL question.