Design SQL

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

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

✓ Brute Force with Arrays

⏱️ Time: O(n) Space: O(m)

Use arrays to store table information and data. For each operation, iterate through arrays to find the correct table and perform linear searches for rows and cells.

Hash Map Based Design

⏱️ Time: O(1) Space: O(m)

Store table metadata in hash maps for instant table access. Use nested hash maps to store row data with row IDs as keys, enabling O(1) lookups for all operations.

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

static char tableData[MAX_TABLES][MAX_ROWS][MAX_COLS][MAX_STR_LEN];
static int rowIds[MAX_TABLES][MAX_ROWS];

typedef struct {
    char tableNames[MAX_TABLES][MAX_STR_LEN];
    int tableColumns[MAX_TABLES];
    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]++;
    
    rowIds[tableIdx][rowIdx] = rowId;
    for (int j = 0; j < rowLen; j++) {
        strcpy(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 (rowIds[tableIdx][i] == rowId) {
            for (int j = i; j < sql->rowCounts[tableIdx] - 1; j++) {
                rowIds[tableIdx][j] = rowIds[tableIdx][j + 1];
                for (int k = 0; k < sql->tableColumns[tableIdx]; k++) {
                    strcpy(tableData[tableIdx][j][k], 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 (rowIds[tableIdx][i] == rowId) {
            return 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() {
    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