Creating a Database Coding Problem [SELECT Query-Based]

Created by DoSelect Content Devs, Modified on Mon, 7 Apr at 8:53 PM by DoSelect Content Devs

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 the Dashboard
2. Problem Name
3. Expected Solving Time (in minutes)
4. Problem Description
5. Evaluation Parameters
6. Seed Data (Preloaded SQL)
7. Stub
8. Sample Solutions
9. Test Cases
- 9.1. Sample Test Case
- 9.2. Hidden Test Case
10. Sanity Check

1. Creating a New Database Problem from the Dashboard

1.1. Landing on the Dashboard

Upon logging into the DoSelect Platform, you arrive at the Home Dashboard.

Navigate to the Library tab at the top of the page.
Action: Click on the Library tab to access the question bank.

1.2. Navigating to Problem Creation

Inside the Library, you will find collections such as DoSelect Questions and My Company Questions.
At the bottom-right corner, there is a "+" button for problem creation.
Action: Click on + Create a new problem.

1.3. Configuring Basic Problem Details

A dialog titled Create New Problem appears.

Fill in the following:

Name: Concise and meaningful (max 45 characters)
Problem Type: Select Database
Level: Easy / Medium / Hard
Evaluation Mode: Choose SELECT Query-Based
(Note: Evaluation mode cannot be changed later)
Action: Click Create to continue.

2. Problem Name

This is the title of your problem. It should reflect the core objective or task of the SQL problem clearly.

Example:
Problem Name: Views: Get User Details

3. Expected Solving Time (in minutes)

Indicates the average time a candidate is expected to take to solve the problem.

Example:
Expected Solving Time: 15 minutes

4. Problem Description

This section outlines the full context and technical requirements of the problem.

4.1. Environment Specifications & Instructions

Explain the system expectations, RDBMS engine, and any required standards or syntax conventions.

Example:
All queries must be written in MySQL. Ensure every SQL statement ends with a semicolon (;).

4.2. Type of Database

Specify the RDBMS the problem is designed for.

Example:
Type of Database: MySQL

4.3. Database Name to be Used

Mention the database name the candidate must use in the queries.

Example:
Database Name: SampleDB

4.4. Role of Buttons

Outline how key UI buttons will be used:

TRY THIS PROBLEM: Execute SQL queries
RUN CODE: View result of your query
SUBMIT: Evaluate your query against test cases

4.5. Problem Context

Introduce the real-world or technical scenario behind the problem. Set up the narrative.

Example:
You are designing a reporting view for a user management system.

4.6. Existing Information (ER Diagram)

Use diagrams or structured data to indicate existing table structure.

Example ER Diagram:

+------------+
|   users    |
+------------+
| user_id    | (Primary Key)
| username   |
| email      |
| password   |
| created_at |
+------------+

4.7. Table Descriptions

Give a brief explanation of each table and its columns.

Example:
Table: users

user_id: INT, Primary key
username: VARCHAR
email: VARCHAR
password: VARCHAR
created_at: DATETIME

4.8. Problem Statement

Clearly describe what the candidate needs to do.

Example:
Create a view named user_details that shows the user_id, username, and email from the users table. Then write a query to retrieve all data from this view.

4.9. Constraints

Mention any restrictions or specific rules to be followed.

Example:

Use CREATE VIEW syntax only
View name must be exactly user_details
Must retrieve all columns defined in the view

5. Evaluation Parameters

5.1. Difficulty Level

Helps categorize the question for adaptive testing and recommendations.

Options:

Easy
Medium
Hard

Example:
Difficulty Level: Easy

5.2. Scoring

Customize the score and penalty.

Example:

Score: 5
Penalty: 0

5.3. Maximum Re-submissions Allowed

Specify the number of attempts allowed.

Example:
Max Re-submissions: 0 (no limit)

5.4. Execution Time Limit

Total time (in seconds) allowed to run all test cases.

Example:
Execution Time Limit: 20 seconds

5.5. Allowed Databases

Mention the allowed database environment.

Example:
Allowed Database: MySQL

5.6. Skill Tags

Helps in topic-wise filtering of the problem.

Example:
Skill Tags: SQL Views, SELECT Query, View Creation

5.7. Discovery Tags

Tags for topic discovery across the problem bank.

Example:
Discovery Tags: SQL Views, Using Views

5.8. Insight Tags

Used for analytics on topic performance.

Example:
Insight Tags: SQL Views

6. Seed Data (Preloaded SQL)

This section contains the SQL statements to create and populate tables before running the user’s query.

Example Seed Data (MySQL):

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

Optional boilerplate SQL that helps guide the candidate.

Head: Non-editable (auto-provided setup)

USE SampleDB;

Body: Editable section (where candidate writes queries)

/*
* Write your SQL query below.
* End each statement with a semicolon.
*/

Tail: Empty (not applicable for MySQL)

8. Sample Solutions

Provide a correct SQL solution for internal validation and sanity checks.

Example (MySQL):

USE SampleDB;

CREATE VIEW user_details AS
SELECT user_id, username, email
FROM users;

SELECT * FROM user_details;

9. Test Cases

Test cases verify the correctness of the user’s query by executing it and comparing the result with the expected output.

Each test case must include:

SQL statements to validate the candidate's query
Expected output in tabular form
Sample or Hidden designation
Weightage (1–5)

9.1. Sample Test Case

Test Case:

SELECT * FROM user_details;

Is Sample: Yes
Weightage: 3

9.2. Hidden Test Case

Test Case:

INSERT INTO users (user_id, username, email, password, created_at)
VALUES (4, 'dave', 'dave@example.com', 'newpass', '2025-01-01 10:30');

SELECT * FROM user_details;

Is Sample: No
Weightage: 2

10. Sanity Check

After adding a sample solution and test cases, run the Sanity Check to verify whether the test cases pass. This ensures the correctness of both the problem and solution before publishing.