Design SQL - Practice Coding Problems

Design SQL - Problem

Design a Simplified SQL Database System

You're tasked with building a lightweight in-memory database that mimics core SQL operations! Given two string arrays names and columns of size n, where the i-th table has name names[i] and columns[i] number of columns.

Your SQL class must support:
🔹 INSERT: Add rows with auto-incrementing IDs (starting from 1)
🔹 REMOVE: Delete specific rows by ID
🔹 SELECT: Retrieve individual cell values
🔹 EXPORT: Generate CSV format of entire tables

Key Challenge: Auto-increment IDs never reset, even after deletions! Each table maintains its own ID sequence.

Example: If you insert 3 rows (IDs: 1,2,3), delete row 2, then insert again, the new row gets ID 4, not 2.

Input & Output

basic_operations.py — Python

$ Input: names = ["users", "orders"], columns = [3, 2] Operations: - ins("users", ["john", "25", "engineer"]) - ins("orders", ["laptop", "999"]) - sel("users", 1, 2) - exp("users")

› Output: [true, true, "25", ["1,john,25,engineer"]]

💡 Note: Creates 2 tables, inserts rows with auto-increment IDs (both get ID=1), selects column 2 from users row 1 (returns "25"), then exports users table in CSV format.

auto_increment_after_deletion.py — Python

$ Input: names = ["products"], columns = [2] Operations: - ins("products", ["phone", "800"]) - ins("products", ["tablet", "600"]) - rmv("products", 1) - ins("products", ["laptop", "1200"]) - exp("products")

› Output: [true, true, null, true, ["2,tablet,600", "3,laptop,1200"]]

💡 Note: Inserts 2 rows (IDs 1,2), removes row 1, then inserts new row. The new row gets ID=3 (not 1), showing auto-increment never resets. Export shows remaining rows 2 and 3.

edge_cases.py — Python

$ Input: names = ["test"], columns = [1] Operations: - ins("invalid_table", ["data"]) - ins("test", ["a", "b"]) - sel("test", 999, 1) - sel("test", 1, 999) - exp("invalid_table")

› Output: [false, false, "<null>", "<null>", []]

💡 Note: Tests edge cases: invalid table name returns false, wrong column count returns false, invalid row/column IDs return "", and invalid table export returns empty array.

Visualization

Tap to expand

Understanding the Visualization

Database Creation

Initialize main hash table with table names as keys, each pointing to table metadata

Table Structure

Each table contains column count, row hash map, and auto-increment counter

Row Operations

Perform CRUD operations with O(1) complexity using direct hash access

Export Generation

Traverse row hash map and generate CSV with sorted row IDs

Key Takeaway

🎯 Key Insight: Use hash maps at both table and row levels to achieve O(1) performance for all operations, while maintaining separate auto-increment counters per table for proper ID management.

Time & Space Complexity

Time Complexity

⏱️

O(1)

Hash map provides O(1) average access for both table lookup and row operations

✓ Linear Growth

Space Complexity

O(n*m*k)

Same space as brute force but with hash map overhead, where n=tables, m=rows, k=avg columns

⚡ Linearithmic Space

Constraints

1 ≤ names.length = columns.length ≤ 100
1 ≤ names[i].length ≤ 20
1 ≤ columns[i] ≤ 20
At most 10⁴ operations will be called
1 ≤ row.length ≤ 20 for insert operations
1 ≤ rowId, columnId ≤ 10⁴ for access operations

Asked in

G Google 28 a Amazon 35 f Meta 22 ⊞ Microsoft 31 Apple 18 N Netflix 15

The optimal solution uses nested hash maps for O(1) operations: a main hash map for table lookup and individual hash maps for each table's rows. Key insight is maintaining separate auto-increment counters per table that never reset, even after deletions. This design pattern is commonly used in database systems and caching solutions.

Common Approaches

Approach	Time	Space	Notes
✓ Brute Force (Linear Search Arrays)	O(n*m)	O(nmk)	Store tables as arrays and search linearly for operations
Hash Map Design (Optimal)	O(1)	O(nmk)	Use nested hash maps for O(1) table and row operations

Brute Force (Linear Search Arrays) — Algorithm Steps

Store table names and column counts in parallel arrays
For each operation, linear search through table names
Store rows as arrays and search linearly by ID
For removal, mark rows as deleted or shift elements

Visualization

Tap to expand

Step-by-Step Walkthrough

Search Tables

Linear search through table array to find matching name

Search Rows

Linear search through rows to find target ID

Perform Operation

Execute the requested operation on found data

Code -

solution.c — C

#include <stdio.h>
#include <stdlib.h>
#include <string.h>

#define MAX_TABLES 100
#define MAX_ROWS 1000
#define MAX_COLS 20
#define MAX_STR_LEN 100

typedef struct {
    char data[MAX_STR_LEN];
} Cell;

typedef struct {
    int id;
    Cell cells[MAX_COLS];
    int valid;
} Row;

typedef struct {
    char name[MAX_STR_LEN];
    int columns;
    Row rows[MAX_ROWS];
    int nextId;
    int valid;
} Table;

typedef struct {
    Table tables[MAX_TABLES];
    int tableCount;
} SQL;

SQL* sqlCreate(char** names, int namesSize, int* columns, int columnsSize) {
    SQL* obj = (SQL*)malloc(sizeof(SQL));
    obj->tableCount = namesSize;
    
    for (int i = 0; i < namesSize; i++) {
        strcpy(obj->tables[i].name, names[i]);
        obj->tables[i].columns = columns[i];
        obj->tables[i].nextId = 1;
        obj->tables[i].valid = 1;
        
        for (int j = 0; j < MAX_ROWS; j++) {
            obj->tables[i].rows[j].valid = 0;
        }
    }
    
    return obj;
}

Table* findTable(SQL* obj, char* name) {
    for (int i = 0; i < obj->tableCount; i++) {
        if (obj->tables[i].valid && strcmp(obj->tables[i].name, name) == 0) {
            return &obj->tables[i];
        }
    }
    return NULL;
}

bool sqlIns(SQL* obj, char* name, char** row, int rowSize) {
    Table* table = findTable(obj, name);
    if (!table || rowSize != table->columns) {
        return false;
    }
    
    int rowId = table->nextId;
    table->rows[rowId].id = rowId;
    table->rows[rowId].valid = 1;
    
    for (int i = 0; i < rowSize; i++) {
        strcpy(table->rows[rowId].cells[i].data, row[i]);
    }
    
    table->nextId++;
    return true;
}

void sqlRmv(SQL* obj, char* name, int rowId) {
    Table* table = findTable(obj, name);
    if (table && rowId < MAX_ROWS && table->rows[rowId].valid) {
        table->rows[rowId].valid = 0;
    }
}

char* sqlSel(SQL* obj, char* name, int rowId, int columnId) {
    static char result[MAX_STR_LEN];
    Table* table = findTable(obj, name);
    
    if (!table || rowId >= MAX_ROWS || !table->rows[rowId].valid ||
        columnId < 1 || columnId > table->columns) {
        strcpy(result, "<null>");
        return result;
    }
    
    strcpy(result, table->rows[rowId].cells[columnId - 1].data);
    return result;
}

char** sqlExp(SQL* obj, char* name, int* returnSize) {
    Table* table = findTable(obj, name);
    *returnSize = 0;
    
    if (!table) {
        return NULL;
    }
    
    char** result = (char**)malloc(MAX_ROWS * sizeof(char*));
    
    for (int i = 1; i < MAX_ROWS; i++) {
        if (table->rows[i].valid) {
            result[*returnSize] = (char*)malloc(MAX_STR_LEN * MAX_COLS);
            sprintf(result[*returnSize], "%d", i);
            
            for (int j = 0; j < table->columns; j++) {
                strcat(result[*returnSize], ",");
                strcat(result[*returnSize], table->rows[i].cells[j].data);
            }
            
            (*returnSize)++;
        }
    }
    
    return result;
}

void sqlFree(SQL* obj) {
    free(obj);
}

Time & Space Complexity

Time Complexity

⏱️

O(n*m)

O(n) to find table, O(m) to find/operate on rows where n=tables, m=rows

✓ Linear Growth

Space Complexity

O(n*m*k)

Store all table data where k is average columns per table

⚡ Linearithmic Space

Constraints

1 ≤ names.length = columns.length ≤ 100
1 ≤ names[i].length ≤ 20
1 ≤ columns[i] ≤ 20
At most 10⁴ operations will be called
1 ≤ row.length ≤ 20 for insert operations
1 ≤ rowId, columnId ≤ 10⁴ for access operations

89.2K Views

Medium-High Frequency

~25 min Avg. Time

1.8K Likes

Ln 1, Col 1

Smart Actions

💡 Explanation

AI Ready

💡 Suggestion Tab to accept Esc to dismiss

// Output will appear here after running code

Code Editor Closed

Click the red button to reopen

Input & Output

Visualization

Time & Space Complexity

Related Problems

Constraints

Common Approaches

Brute Force (Linear Search Arrays) — Algorithm Steps

Visualization

Code -

Time & Space Complexity

Constraints

Select Compiler