sql-reference.md

SQL Reference

Complete reference for SQL syntax supported by MiniDB.

Table of Contents

1. Overview
2. Data Types
3. SELECT Statement
4. INSERT Statement
5. UPDATE Statement
6. DELETE Statement
7. Limitations
8. Examples

Overview

MiniDB supports a simplified subset of SQL focused on key-value operations. This makes it easy to learn while maintaining the familiar SQL syntax.

Supported Operations:

• ✅ SELECT (point queries and full table scans)
• ✅ INSERT (single row)
• ✅ UPDATE (single row by key)
• ✅ DELETE (single row by key)

Not Yet Supported:

• ❌ CREATE/DROP TABLE (tables created automatically)
• ❌ JOINs
• ❌ Aggregations (COUNT, SUM, AVG)
• ❌ GROUP BY / ORDER BY
• ❌ Subqueries
• ❌ Multiple columns
• ❌ Complex WHERE clauses (only key = value)

Data Types

MiniDB uses a simplified key-value model:

Key

• Type: 64-bit signed integer (int64_t)
• Range: -9,223,372,036,854,775,808 to 9,223,372,036,854,775,807
• Usage: Primary key for each row
• Must be: Unique within a table

Examples:

-- Valid keys
1
42
-100
9999999999

-- Invalid (will cause parse error)
'abc'        -- strings not allowed as keys
3.14         -- floats not allowed
NULL         -- NULL not allowed

Value

• Type: Variable-length string
• Max Length: Limited by available memory
• Encoding: UTF-8
• Can contain: Any characters including spaces

Examples:

-- Valid values
'Alice'
'Hello World'
'Special chars: !@#$%^&*()'
'Multi-line
text'
''           -- empty string is valid

-- Must be quoted
Alice        -- ERROR: missing quotes

SELECT Statement

Retrieve data from the database.

Syntax

-- Point query (specific key)
SELECT * FROM table_name WHERE key = value

-- Full table scan (all rows)
SELECT * FROM table_name

Parameters

• table_name: Name of the table (e.g., users, products)
• key: Integer key to search for
• value: Integer value to match

Return Value

Returns all matching rows with their key-value pairs.

Examples

-- Get single row by key
SELECT * FROM users WHERE key = 1
-- Returns: 1 -> Alice

-- Get another row
SELECT * FROM users WHERE key = 42
-- Returns: 42 -> Bob

-- Get all rows (table scan)
SELECT * FROM users
-- Returns:
--   1 -> Alice
--   42 -> Bob
--   100 -> Charlie

-- Query non-existent key
SELECT * FROM users WHERE key = 999
-- Returns: (empty result set)

Performance Notes

• Point query (WHERE key = value): O(log n) - uses B+ tree index
• Full scan (no WHERE): O(n) - reads all rows
• Recommendation: Use point queries when possible for better performance

Errors

-- Missing WHERE keyword
SELECT * FROM users key = 1
-- ERROR: Invalid SELECT syntax

-- Invalid key type
SELECT * FROM users WHERE key = 'abc'
-- ERROR: Cannot parse WHERE key

-- Missing FROM
SELECT * users WHERE key = 1
-- ERROR: Invalid SELECT: missing FROM clause

INSERT Statement

Add a new row to the table.

Syntax

INSERT INTO table_name VALUES (key, value)

Parameters

• table_name: Name of the table
• key: Integer primary key (must be unique)
• value: String value (must be quoted)

Return Value

• Success: 1 row affected
• Failure: Error message (e.g., duplicate key)

Examples

-- Insert a single row
INSERT INTO users VALUES (1, 'Alice')
-- Returns: 1 row affected

-- Insert with different types of values
INSERT INTO users VALUES (2, 'Bob Smith')
INSERT INTO users VALUES (3, 'charlie@example.com')
INSERT INTO users VALUES (4, 'Value with spaces')
INSERT INTO users VALUES (5, '')  -- empty string

-- Insert with negative key
INSERT INTO users VALUES (-1, 'Negative key allowed')

-- Attempt duplicate key
INSERT INTO users VALUES (1, 'Different Alice')
-- ERROR: Duplicate key: 1

Constraints

1. Unique Keys: Each key must be unique within table
2. Key Type: Must be integer
3. Value Format: Must be quoted string

Errors

-- Missing VALUES keyword
INSERT INTO users (1, 'Alice')
-- ERROR: Invalid INSERT: missing VALUES

-- Missing parentheses
INSERT INTO users VALUES 1, 'Alice'
-- ERROR: malformed VALUES clause

-- Unquoted value
INSERT INTO users VALUES (1, Alice)
-- ERROR: malformed VALUES clause

-- Duplicate key
INSERT INTO users VALUES (1, 'Alice')
INSERT INTO users VALUES (1, 'Bob')
-- ERROR: Duplicate key: 1

UPDATE Statement

Modify an existing row.

Syntax

UPDATE table_name SET value = new_value WHERE key = key_value

Parameters

• table_name: Name of the table
• new_value: New value to set (quoted string)
• key_value: Key of row to update (integer)

Return Value

• Success: 1 row affected
• Not Found: 0 rows affected
• Failure: Error message

Examples

-- Update existing row
UPDATE users SET value = 'Alice Smith' WHERE key = 1
-- Returns: 1 row affected

-- Update to empty string
UPDATE users SET value = '' WHERE key = 2

-- Update non-existent key
UPDATE users SET value = 'Test' WHERE key = 999
-- ERROR: Key not found: 999

-- Verify update worked
SELECT * FROM users WHERE key = 1
-- Returns: 1 -> Alice Smith

Implementation Note

Internally, UPDATE is implemented as:

1. Look up row by key
2. If exists: Remove old value, insert new value
3. If not exists: Return error

This ensures atomicity within a transaction.

Errors

-- Missing SET keyword
UPDATE users value = 'Alice' WHERE key = 1
-- ERROR: Invalid UPDATE: missing SET

-- Missing WHERE clause
UPDATE users SET value = 'Alice'
-- ERROR: Invalid UPDATE: missing WHERE

-- Invalid key type
UPDATE users SET value = 'Alice' WHERE key = 'abc'
-- ERROR: cannot parse WHERE key

DELETE Statement

Remove a row from the table.

Syntax

DELETE FROM table_name WHERE key = key_value

Parameters

• table_name: Name of the table
• key_value: Key of row to delete (integer)

Return Value

• Success: 1 row affected
• Not Found: 0 rows affected (error)
• Failure: Error message

Examples

-- Delete existing row
DELETE FROM users WHERE key = 1
-- Returns: 1 row affected

-- Verify deletion
SELECT * FROM users WHERE key = 1
-- Returns: (empty result set)

-- Delete non-existent key
DELETE FROM users WHERE key = 999
-- ERROR: Key not found: 999

Permanent Deletion

Warning: DELETE is permanent. There is no built-in way to recover deleted data unless you have backups or the operation was in a transaction that was rolled back.

Errors

-- Missing FROM keyword
DELETE users WHERE key = 1
-- ERROR: Invalid DELETE: missing FROM

-- Missing WHERE clause
DELETE FROM users
-- ERROR: Invalid DELETE: missing WHERE
-- (Safety feature: prevents accidental table truncation)

-- Invalid key type
DELETE FROM users WHERE key = 'abc'
-- ERROR: cannot parse WHERE key

Limitations

Current Limitations

1. Single Column Data Model

   • Only key-value pairs (can't have multiple columns)
   • No SELECT name, email FROM users

2. Simple WHERE Clauses

   • Only key = value supported
   • No AND, OR, NOT operators
   • No comparison operators (>, <, >=, <=, BETWEEN)

3. No Aggregations

   • No COUNT, SUM, AVG, MIN, MAX
   • No GROUP BY or HAVING

4. No Joins

   • Can't combine data from multiple tables
   • No INNER JOIN, LEFT JOIN, etc.

5. No Sorting/Ordering

   • No ORDER BY clause
   • Results returned in B+ tree order (sorted by key)

6. Single Row Operations

   • Can't insert multiple rows at once
   • No INSERT INTO ... SELECT FROM
   • No bulk updates or deletes

7. Auto-Created Tables

   • No CREATE TABLE or DROP TABLE
   • Tables created automatically on first use
   • No ALTER TABLE

Why These Limitations?

These limitations keep MiniDB simple and focused on core database concepts:

• Educational: Easier to understand implementation
• Performance: Simpler operations are faster
• Reliability: Less complexity = fewer bugs

See Architecture Overview for extension possibilities.

Examples

Complete Usage Example

-- Start with empty database

-- 1. Insert some data
INSERT INTO users VALUES (1, 'Alice')
INSERT INTO users VALUES (2, 'Bob')
INSERT INTO users VALUES (3, 'Charlie')
INSERT INTO users VALUES (4, 'Diana')

-- 2. Query individual rows
SELECT * FROM users WHERE key = 1
-- Output: 1 -> Alice

SELECT * FROM users WHERE key = 3
-- Output: 3 -> Charlie

-- 3. Full table scan
SELECT * FROM users
-- Output:
--   1 -> Alice
--   2 -> Bob
--   3 -> Charlie
--   4 -> Diana

-- 4. Update a row
UPDATE users SET value = 'Alice Smith' WHERE key = 1

-- Verify update
SELECT * FROM users WHERE key = 1
-- Output: 1 -> Alice Smith

-- 5. Delete a row
DELETE FROM users WHERE key = 4

-- Verify deletion
SELECT * FROM users
-- Output:
--   1 -> Alice Smith
--   2 -> Bob
--   3 -> Charlie
-- (Diana is gone)

-- 6. Try to update non-existent row
UPDATE users SET value = 'Test' WHERE key = 99
-- ERROR: Key not found: 99

-- 7. Insert duplicate key
INSERT INTO users VALUES (1, 'Another Alice')
-- ERROR: Duplicate key: 1

Practical Use Cases

User Directory:

INSERT INTO directory VALUES (1001, 'john@example.com')
INSERT INTO directory VALUES (1002, 'jane@example.com')
SELECT * FROM directory WHERE key = 1001

Configuration Storage:

INSERT INTO config VALUES (1, 'max_connections=100')
INSERT INTO config VALUES (2, 'timeout=30')
UPDATE config SET value = 'max_connections=200' WHERE key = 1
SELECT * FROM config

Cache:

-- Store cached data
INSERT INTO cache VALUES (12345, '{"name":"Alice","age":30}')

-- Retrieve from cache
SELECT * FROM cache WHERE key = 12345

-- Invalidate cache entry
DELETE FROM cache WHERE key = 12345

Next Steps

• Client Usage: How to connect
• Configuration: Server options
• Examples: More examples
• Performance: Tuning guide

---

For implementation details, see Query Processing.