Design SQL - Practice Coding Problems

Design SQL - Problem

You are given two string arrays, names and columns, both of size n. The ith table is represented by the name names[i] and contains columns[i] number of columns.

You need to implement a class that supports the following operations:

Insert a row in a specific table with an id assigned using an auto-increment method, where the id of the first inserted row is 1, and the id of each new row inserted into the same table is one greater than the id of the last inserted row, even if the last row was removed.
Remove a row from a specific table. Removing a row does not affect the id of the next inserted row.
Select a specific cell from any table and return its value.
Export all rows from any table in csv format.

Implement the SQL class:

SQL(String[] names, int[] columns) Creates the n tables.
bool ins(String name, String[] row) Inserts row into the table name and returns true. If row.length does not match the expected number of columns, or name is not a valid table, returns false without any insertion.
void rmv(String name, int rowId) Removes the row rowId from the table name. If name is not a valid table or there is no row with id rowId, no removal is performed.
String sel(String name, int rowId, int columnId) Returns the value of the cell at the specified rowId and columnId in the table name. If name is not a valid table, or the cell (rowId, columnId) is invalid, returns "<null>".
String[] exp(String name) Returns the rows present in the table name. If name is not a valid table, returns an empty array. Each row is represented as a string, with each cell value (including the row's id) separated by a ",".

Input & Output

Example 1 — Basic Operations

$ Input: names = ["users", "products"], columns = [3, 2]

› Output: SQL object created with two tables

💡 Note: Creates a SQL database with 'users' table (3 columns) and 'products' table (2 columns). Auto-increment IDs start at 1 for each table.

Example 2 — Insert and Select

$ Input: ins("users", ["John", "25", "Engineer"]) → sel("users", 1, 2)

› Output: true → "25"

💡 Note: Insert row into users table gets ID 1. Selecting row 1, column 2 returns "25" (the age field).

Example 3 — Export Table

$ Input: After inserting two rows → exp("users")

› Output: ["1,John,25,Engineer", "2,Jane,30,Manager"]

💡 Note: Export returns all rows in CSV format with row ID as first column, followed by data columns.

Constraints

1 ≤ n ≤ 100
1 ≤ names[i].length ≤ 20
1 ≤ columns[i] ≤ 20
All table names are unique
At most 1000 calls to ins, rmv, sel, and exp in total

Visualization

Tap to expand

Understanding the Visualization

Input

Table names and column counts define database schema

Process

Hash maps provide O(1) access to tables and rows

Output

Efficient database operations: insert, remove, select, export

Key Takeaway

🎯 Key Insight: Hash maps transform O(n) linear searches into O(1) constant-time lookups for all database operations

Asked in

a Amazon 35 G Google 28 M Microsoft 22 f Facebook 18

The optimal solution uses hash maps for O(1) table and row lookups. Key insight: nested hash maps (table name → row ID → data) enable constant-time access for all operations. Best approach: Hash Map Design with Time: O(1), Space: O(m).

Common Approaches

Approach	Time	Space	Notes
✓ Brute Force with Arrays	O(n)	O(m)	Store all table data in arrays and search linearly for operations
Hash Map Based Design	O(1)	O(m)	Use hash maps for O(1) table and row lookups with efficient data management

Brute Force with Arrays — Algorithm Steps

Store table names and column counts in arrays
Use 2D arrays for table data with linear search
Iterate through all tables to find matches

Visualization

Tap to expand

Step-by-Step Walkthrough

Table Lookup

Linear search through table names array

Row Operations

Linear search through row data arrays

Result

Return found data or perform operation

Code -

solution.c — C

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

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

typedef struct {
    char tableNames[MAX_TABLES][MAX_STR_LEN];
    int tableColumns[MAX_TABLES];
    char tableData[MAX_TABLES][MAX_ROWS][MAX_COLS][MAX_STR_LEN];
    int rowIds[MAX_TABLES][MAX_ROWS];
    int rowCounts[MAX_TABLES];
    int nextIds[MAX_TABLES];
    int numTables;
} SQL;

SQL* createSQL(char names[][MAX_STR_LEN], int* columns, int numTables) {
    SQL* sql = (SQL*)malloc(sizeof(SQL));
    sql->numTables = numTables;
    
    for (int i = 0; i < numTables; i++) {
        strcpy(sql->tableNames[i], names[i]);
        sql->tableColumns[i] = columns[i];
        sql->rowCounts[i] = 0;
        sql->nextIds[i] = 1;
    }
    
    return sql;
}

int ins(SQL* sql, char* name, char row[][MAX_STR_LEN], int rowLen) {
    int tableIdx = -1;
    for (int i = 0; i < sql->numTables; i++) {
        if (strcmp(sql->tableNames[i], name) == 0) {
            tableIdx = i;
            break;
        }
    }
    
    if (tableIdx == -1 || rowLen != sql->tableColumns[tableIdx]) {
        return 0;
    }
    
    int rowId = sql->nextIds[tableIdx]++;
    int rowIdx = sql->rowCounts[tableIdx]++;
    
    sql->rowIds[tableIdx][rowIdx] = rowId;
    for (int j = 0; j < rowLen; j++) {
        strcpy(sql->tableData[tableIdx][rowIdx][j], row[j]);
    }
    
    return 1;
}

void rmv(SQL* sql, char* name, int rowId) {
    int tableIdx = -1;
    for (int i = 0; i < sql->numTables; i++) {
        if (strcmp(sql->tableNames[i], name) == 0) {
            tableIdx = i;
            break;
        }
    }
    
    if (tableIdx == -1) return;
    
    for (int i = 0; i < sql->rowCounts[tableIdx]; i++) {
        if (sql->rowIds[tableIdx][i] == rowId) {
            for (int j = i; j < sql->rowCounts[tableIdx] - 1; j++) {
                sql->rowIds[tableIdx][j] = sql->rowIds[tableIdx][j + 1];
                for (int k = 0; k < sql->tableColumns[tableIdx]; k++) {
                    strcpy(sql->tableData[tableIdx][j][k], sql->tableData[tableIdx][j + 1][k]);
                }
            }
            sql->rowCounts[tableIdx]--;
            break;
        }
    }
}

char* sel(SQL* sql, char* name, int rowId, int columnId) {
    static char nullResult[] = "<null>";
    
    int tableIdx = -1;
    for (int i = 0; i < sql->numTables; i++) {
        if (strcmp(sql->tableNames[i], name) == 0) {
            tableIdx = i;
            break;
        }
    }
    
    if (tableIdx == -1 || columnId < 1 || columnId > sql->tableColumns[tableIdx]) {
        return nullResult;
    }
    
    for (int i = 0; i < sql->rowCounts[tableIdx]; i++) {
        if (sql->rowIds[tableIdx][i] == rowId) {
            return sql->tableData[tableIdx][i][columnId - 1];
        }
    }
    
    return nullResult;
}

SQL* solution(char names[][MAX_STR_LEN], int* columns, int numTables) {
    return createSQL(names, columns, numTables);
}

int main() {
    printf("[]\n");
    return 0;
}

Time & Space Complexity

Time Complexity

⏱️

O(n)

Each operation requires linear search through tables and rows

✓ Linear Growth

Space Complexity

O(m)

Space proportional to total number of rows across all tables

✓ Linear Space

23.4K Views

Medium Frequency

~35 min Avg. Time

890 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

Constraints

Visualization

Related Problems

Common Approaches

Brute Force with Arrays — Algorithm Steps

Visualization

Code -

Time & Space Complexity

Select Compiler