SQL Execution Engine: Build a Mini SQL Interpreter Supporting SELECT, WHERE, and JOIN

Question

## Round 1 - Coding / SQL

## Problem

Implement an in-memory SQL execution engine that parses and executes simple SQL queries over Python dictionaries acting as tables. Support `SELECT`, `FROM`, `WHERE`, and `INNER JOIN`.

```python
class SQLEngine:
    def __init__(self):
        self.tables: dict[str, list[dict]] = {}

def create_table(self, name: str, rows: list[dict]) -> None:
        ...
    def execute(self, query: str) -> list[dict]:
        # parses and runs the SQL query string
        ...
```

## Example

```
engine = SQLEngine()
engine.create_table("users",  [{"id":1,"name":"Alice"},{"id":2,"name":"Bob"}])
engine.create_table("orders", [{"user_id":1,"item":"Book"},{"user_id":1,"item":"Pen"},{"user_id":2,"item":"Bag"}])

engine.execute("SELECT name, item FROM users JOIN orders ON users.id = orders.user_id WHERE name = 'Alice'")
# -> [{"name":"Alice","item":"Book"},{"name":"Alice","item":"Pen"}]

engine.execute("SELECT * FROM users WHERE id > 1")
# -> [{"id":2,"name":"Bob"}]
```

## Follow-ups

1. How do you tokenize and parse the SQL string without an external parser library?
2. How would you add support for `GROUP BY` and aggregation functions like `COUNT` and `SUM`?
3. How do you handle column name ambiguity when both tables have a column with the same name?
4. What optimizations would you apply (e.g., predicate pushdown, hash join) as table sizes grow?

SQL Execution Engine: Build a Mini SQL Interpreter Supporting SELECT, WHERE, and JOIN

Interview Experience

Full Details

About This Question

Topics

More Retool Interview Questions