Implement an in-memory SQL-like table
Problem: In-Memory SQL-like Table Implementation
Implement a simplified in-memory SQL-like database system designed to manage a single table.
The system must support a fixed schema, defined by a list of column names. Each row in the table will be represented as a mapping from column names to their respective values. Values can be assumed to be either strings or integers and must support comparison operations.
Core Operations:
- INSERT: Allow the insertion of a new row, where a value must be provided for each column defined in the table's schema.
- SELECT with Projection: Enable querying rows while specifying a subset of column names to be returned (e.g.,
SELECT colA, colC). - Filtering (WHERE Clause): Support filtering rows based on a condition applied to a column. At a minimum, equality comparisons (
colX = value) must be supported. Candidates may optionally implement additional comparison operators. - Ordering (ORDER BY Clause): Support sorting the result set by a specified column in ascending order. Optionally, support for descending order can be implemented.
Clarifications and Error Handling:
- Invalid Column Names: The system should gracefully handle or validate cases where invalid column names are provided in
SELECT,WHERE, orORDER BYclauses. - Missing Columns in Rows: Define and handle the behavior for rows that might be inserted with missing column values. If disallowed, validation should occur during the insert operation.
- Output Format: The final output for a
SELECToperation should be a list of projected rows, ordered according to theORDER BYclause.
API Design and Performance:
Candidates are required to design a suitable API for these operations (e.g., insert(row), select(columns, where_clause, order_by)). Alternatively, parsing SQL-like strings is an acceptable approach if preferred. The implementation must be efficient, considering typical interview constraints of up to tens or hundreds of thousands of rows.
AI interview prep
Core dilemma
Balancing efficient query execution (filtering, projection, sorting) on a growing in-memory dataset against the complexity of data structures and the need to avoid full-blown database engine features, given the 'tens or hundreds of thousands of rows' constraint.
3 tradeoffs and 3 stress tests locked
Interview prep
Unlock the interviewer X-Ray rubric, tradeoff matrix, stress tests, clarifying questions, and key concepts.
Implement an in-memory SQL-like table
Problem: In-Memory SQL-like Table Implementation
Implement a simplified in-memory SQL-like database system designed to manage a single table.
The system must support a fixed schema, defined by a list of column names. Each row in the table will be represented as a mapping from column names to their respective values. Values can be assumed to be either strings or integers and must support comparison operations.
Core Operations:
- INSERT: Allow the insertion of a new row, where a value must be provided for each column defined in the table's schema.
- SELECT with Projection: Enable querying rows while specifying a subset of column names to be returned (e.g.,
SELECT colA, colC). - Filtering (WHERE Clause): Support filtering rows based on a condition applied to a column. At a minimum, equality comparisons (
colX = value) must be supported. Candidates may optionally implement additional comparison operators. - Ordering (ORDER BY Clause): Support sorting the result set by a specified column in ascending order. Optionally, support for descending order can be implemented.
Clarifications and Error Handling:
- Invalid Column Names: The system should gracefully handle or validate cases where invalid column names are provided in
SELECT,WHERE, orORDER BYclauses. - Missing Columns in Rows: Define and handle the behavior for rows that might be inserted with missing column values. If disallowed, validation should occur during the insert operation.
- Output Format: The final output for a
SELECToperation should be a list of projected rows, ordered according to theORDER BYclause.
API Design and Performance:
Candidates are required to design a suitable API for these operations (e.g., insert(row), select(columns, where_clause, order_by)). Alternatively, parsing SQL-like strings is an acceptable approach if preferred. The implementation must be efficient, considering typical interview constraints of up to tens or hundreds of thousands of rows.