This document provides a structured guide to creating SQL-based database problems for assessment purposes. Each field below is described with its objective and how it affects the problem configuration, followed by a practical example.
TABLE OF CONTENTS
- 1. Creating a New Database Problem from Dashboard
- 2. Problem Name
- 3. Expected Solving Time (in Minutes)
- 4. Problem Description
- 5. Evaluation Parameters
- 6. Seed Data
- 7. Stub Code
- 8. Sample Solutions
- 9. Test Cases
- 10. Sanity Checks
1. Creating a New Database Problem from Dashboard
1.1. Landing on the Dashboard
When you log in to the DoSelect platform, you will arrive at the Home dashboard.
From here, you can manage your tests, review reports, or access the Library tab located on the top navigation bar.
Action: Click on the Library tab to access the question bank.
1.2. Navigating to Problem Creation
Once inside the Library, you will see categories like DoSelect Questions and My Company Questions.
At the bottom-right corner, you'll find a + (plus) icon.
Action: Click on the + Create a new problem button to initiate the problem creation process.
1.3. Configuring Basic Problem Details (Required)
A dialog box titled Create New Problem will appear. Here, you must configure the basic metadata of the problem.
Fields to Fill:
Name: Provide a meaningful, concise problem title. (Max: 45 characters)
Problem Type: Select Database
Level: Choose one - Easy, Medium, or Hard
Evaluation Mode:
Choose DDL/DML query based from the following (Note: This selection is final and cannot be changed later.):SELECT query-based
DDL/DML query-based
Action: Click on Create to proceed.
2. Problem Name
This is the title or identifier for your problem, which appears in the library and tests.
Purpose: Helps candidates and reviewers quickly understand the context of the question.
Example:
Views: Get User Details
3. Expected Solving Time (in Minutes)
Specify how long, on average, a candidate is expected to take to solve the problem.
Purpose: Helps in organizing timed assessments and understanding difficulty impact.
Example:
15
4. Problem Description
This section includes the core context, schema, and setup of the problem. It should be complete, clear, and logically structured.
4.1. Environment Specifications & Instructions
Provide instructions regarding the execution environment, such as syntax requirements and restrictions.
Example:
Use proper SQL syntax with semicolons. Avoid creating a database named DOSELECT, as it has restricted access.
4.2. Type of Database
Indicate which RDBMS the query will run on (e.g., MySQL, PostgreSQL, etc.).
Example:
MySQL
4.3. Database Name to be Used
Mention the exact database name to be used in all queries.
Example:
SampleDB
4.4. Role of Buttons
Explain the functionality of key interface buttons like:
RUN CODE: Executes the SQL statement
SUBMIT: Submits the final solution
TRY THIS PROBLEM: Opens an editor window to test candidate's solution
4.5. Problem Context
Explain what concept is being tested (e.g., Views, Aggregation, Joins) and what the candidate is expected to do.
4.6. Existing Information (ER Diagram)
Visually or textually describe the existing schema of tables involved.
Example ER Diagram:
+------------+
| users |
+------------+
| user_id | (Primary Key)
| username |
| email |
| password |
| created_at |
+------------+
4.7. Table Descriptions
Brief description of all involved tables.
Example:
Table: users
Stores user information such as ID, name, email, password, and creation date.
4.8. Problem Statement
Define the exact tasks the candidate needs to perform.
Example:
Create a view named user_details to show user_id, username, and email from the users table.
Then retrieve all data from this view.
4.9. Constraints
Specify any constraints, such as:
Column uniqueness
Query structure rules
Function usage limits
Example:
Only use SQL statements compatible with MySQL 8+
Do not drop or alter any existing tables
5. Evaluation Parameters
5.1. Difficulty Level
Select the correct difficulty tag for better filtering and recommendations.
Example:
Easy
5.2. Scoring
Set a numerical score and penalty value.
Example:
Score: 5
Penalty: 0
5.3. Maximum Re-submissions Allowed
Set a cap on the number of times a candidate can submit.
Use 0 for unlimited submissions.
Example:
0
5.4. Execution Time Limit
Set the time limit in seconds. Must be between 0 and 600.
Example:
60
5.5. Allowed Databases
Choose the RDBMS supported for this problem.
Example:
MySQL
5.6. Skill Tags
Tags that define what technical skills are being tested.
Example:
Views, SQL
5.7. Discovery Tags
Helps with content discovery and filtering.
Example:
Using Views, SQL Views
5.8. Insight Tags
Used for analytics and visualizing topic coverage.
Example:
SQL Views
6. Seed Data
This includes the initial database setup to simulate a real environment.
CREATE DATABASE SampleDB;
USE SampleDB;
CREATE TABLE users (
user_id INT PRIMARY KEY,
username VARCHAR(255) NOT NULL,
email VARCHAR(255) NOT NULL,
password VARCHAR(255) NOT NULL,
created_at DATETIME NOT NULL
);
INSERT INTO users (user_id, username, email, password, created_at)
VALUES
(1, 'alice', 'alice@example.com', 'abc123', '2024-12-01 10:00'),
(2, 'bob', 'bob@example.com', 'xyz456', '2024-12-02 11:30'),
(3, 'charlie', 'charlie@example.com', 'pass789', '2024-12-03 12:15');7. Stub Code
Add boilerplate code that will be auto-filled for candidates.
Head (non-editable):
USE SampleDB;
Body (editable):
/*
* Enter your query below.
* Please append a semicolon ";" at the end of the query
*/
Tail (non-editable):
Leave empty unless custom logging or cleanup is required.
8. Sample Solutions
Provide the correct working solution for evaluation.
MySQL:
USE SampleDB; CREATE VIEW user_details AS SELECT user_id, username, email FROM users;
9. Test Cases
You must include at least one sample and one non-sample (hidden) test case.
Test Case #1: Sample Test
Python Code:
import pymysql
db = pymysql.connect(database="SampleDB")
cur = db.cursor()
cur.execute("SELECT * FROM user_details;")
table_data = list(cur.fetchall())
expected_data = [
(1, 'alice', 'alice@example.com'),
(2, 'bob', 'bob@example.com'),
(3, 'charlie', 'charlie@example.com')
]
assert table_data == expected_data, f"Mismatch: {table_data}"Is Sample Test Case: Yes
Weightage: 5
Test Case #2: Hidden Test
Python Code:
import pymysql
db = pymysql.connect(database="SampleDB")
cur = db.cursor()
cur.execute("""
INSERT INTO users (user_id, username, email, password, created_at)
VALUES (4, 'dave', 'dave@example.com', 'newpass', '2025-01-01 10:30');
""")
cur.execute("SELECT * FROM user_details;")
table_data = list(cur.fetchall())
expected_data = [
(1, 'alice', 'alice@example.com'),
(2, 'bob', 'bob@example.com'),
(3, 'charlie', 'charlie@example.com'),
(4, 'dave', 'dave@example.com')
]
assert table_data == expected_data, f"Mismatch: {table_data}"Is Sample Test Case: No
Weightage: 5
10. Sanity Checks
Used to verify that test cases are logically aligned with the sample solution.
A passed sanity check indicates correctness.
A failed sanity check flags logical or syntax errors.
Was this article helpful?
That’s Great!
Thank you for your feedback
Sorry! We couldn't be helpful
Thank you for your feedback
Feedback sent
We appreciate your effort and will try to fix the article