MiniDB SQL Syntax Guide

Quick Reference

This guide shows the correct SQL syntax for MiniDB commands.

✅ Supported Commands

1. SHOW TABLES

List all tables in the database.

SHOW TABLES

Example:

minidb> SHOW TABLES
['students', 'courses', 'users']

2. DESCRIBE / DESC

Show table structure (columns, types, constraints).

DESCRIBE table_name
DESC table_name          -- Shorthand alias

Example:

minidb> DESC students
{'columns': ['id', 'name', 'course_id'], 'primary_key': 'id', 'column_types': {'id': 'int', 'name': 'str', 'course_id': 'int'}, 'unique_columns': []}

minidb> DESCRIBE courses
{'columns': ['id', 'title'], 'primary_key': 'id', 'column_types': {'id': 'int', 'title': 'str'}, 'unique_columns': []}

3. CREATE TABLE

Create a new table with columns and optional types/constraints.

CREATE TABLE table_name (col1 [type] [UNIQUE], col2 [type], ...)

Types: int, str
Constraints: UNIQUE

Examples:

-- Basic table
CREATE TABLE users (id, name, email)

-- With types
CREATE TABLE students (id int, name str, course_id int)

-- With unique constraint
CREATE TABLE accounts (id int, email str UNIQUE, username str UNIQUE)

4. INSERT INTO

Insert a new row into a table.

INSERT INTO table_name VALUES (value1, value2, ...)

Examples:

-- String values need quotes
INSERT INTO students VALUES (101, 'Collins', 1)

-- Numbers don't need quotes
INSERT INTO courses VALUES (1, 'Computer Science')

-- Mixed types
INSERT INTO users VALUES (5, 'John Doe', 'john@example.com')

5. SELECT

Retrieve data from a table.

Select Specific Columns:

SELECT column1, column2 FROM table_name

Select with WHERE Clause:

SELECT * FROM table_name WHERE column operator value

Operators: =, !=, >, <, >=, <=, IN

SELECT with LIMIT Clause:

SELECT * FROM table_name [WHERE condition] LIMIT number

Examples:

-- All students
SELECT * FROM students

-- Specific student by ID
SELECT * FROM students WHERE id = 101

-- Students with course_id greater than 1
SELECT * FROM students WHERE course_id > 1

-- Students not in course 2
SELECT * FROM students WHERE course_id != 2

-- String comparison
SELECT * FROM students WHERE name = 'Collins'

⚠️ IMPORTANT: You MUST use the WHERE keyword!

❌ WRONG:

SELECT id=102 FROM students          -- Missing WHERE keyword
SELECT * FROM students id=101        -- Missing WHERE keyword

✅ CORRECT:

SELECT * FROM students WHERE id = 102
SELECT name, course_id FROM students WHERE id = 101
SELECT * FROM students LIMIT 5

6. SELECT with NESTED SUBQUERY

Filter results based on the output of another query using the IN operator.

SELECT * FROM table1 WHERE column IN (SELECT column FROM table2 WHERE condition)

Example:

-- Find students enrolled in 'Computer Science'
SELECT * FROM students 
WHERE id IN (SELECT student_id FROM enrollments WHERE course = 'CS')

Combine data from two tables based on a related column.

SELECT * FROM table1 JOIN table2 ON table1.column = table2.column

Example:

-- Get student names with their course titles
SELECT * FROM students JOIN courses ON students.course_id = courses.id

Result:

id  | name    | course_id | title
----|---------|-----------|------------------
101 | Collins | 1         | Computer Science
102 | John    | 2         | Electrical Eng

7. UPDATE

Modify existing rows in a table.

UPDATE table_name SET column = value WHERE condition_column operator condition_value

Examples:

-- Update student name
UPDATE students SET name = 'Collins Jr.' WHERE id = 101

-- Update course title
UPDATE courses SET title = 'Advanced CS' WHERE id = 1

-- Update with numeric value
UPDATE students SET course_id = 2 WHERE id = 101

8. DELETE

Remove rows from a table.

DELETE FROM table_name WHERE column operator value

Examples:

-- Delete specific student
DELETE FROM students WHERE id = 101

-- Delete all students in course 1
DELETE FROM students WHERE course_id = 1

-- Delete by name
DELETE FROM students WHERE name = 'John'

9. ALTER TABLE

Modify table schema by adding new columns.

ALTER TABLE table_name ADD column_name data_type

Supported Types: INT, STR

Behavior:

Adds column to table schema
Updates all existing rows with default values:
- INT → 0
- STR → '' (empty string)
Changes persist across database restarts

Examples:

-- Add email column
ALTER TABLE users ADD email STR

-- Add age column
ALTER TABLE users ADD age INT

-- Add multiple columns (run separately)
ALTER TABLE products ADD price INT
ALTER TABLE products ADD description STR

After ALTER TABLE:

-- Existing rows have default values
SELECT * FROM users
-- Output: [{'id': 1, 'name': 'Alice', 'email': ''}]

-- Update new column
UPDATE users SET email = 'alice@example.com' WHERE id = 1

-- Insert with new columns
INSERT INTO users VALUES (2, 'Bob', 'bob@example.com')

DROP COLUMN

Remove a column from an existing table:

ALTER TABLE table_name DROP COLUMN column_name

Example:

-- Drop email column
ALTER TABLE users DROP COLUMN email

-- Verify
DESCRIBE users

Safety:

❌ Cannot drop primary key column
✅ Removes column from all rows
✅ Removes associated constraints (UNIQUE, FOREIGN KEY)
✅ Atomic operation with automatic save

RENAME COLUMN

Rename an existing column:

ALTER TABLE table_name RENAME COLUMN old_name TO new_name

Example:

-- Rename column
ALTER TABLE users RENAME COLUMN name TO full_name

-- Can rename primary key
ALTER TABLE users RENAME COLUMN id TO user_id

-- Verify
DESCRIBE users

Features:

✅ Updates all rows automatically
✅ Preserves data
✅ Updates constraints (UNIQUE, FOREIGN KEY, PRIMARY KEY)
✅ Rebuilds indexes
✅ Atomic operation

10. TRANSACTIONS

Group multiple operations together with atomicity guarantees.

BEGIN TRANSACTION

Start a new transaction:

BEGIN TRANSACTION
-- or simply:
BEGIN

COMMIT

Save all changes to disk:

COMMIT

ROLLBACK

Discard all changes:

ROLLBACK

Behavior:

Changes are staged in memory until COMMIT
ROLLBACK discards all staged changes
SELECT sees staged changes during transaction
Auto-commit mode when no transaction active

Example: Money Transfer

-- Start transaction
BEGIN TRANSACTION

-- Transfer $100 from Alice to Bob
UPDATE accounts SET balance = 900 WHERE id = 1
UPDATE accounts SET balance = 600 WHERE id = 2

-- Log the transaction
INSERT INTO transactions VALUES (1, 1, 2, 100)

-- Commit all changes atomically
COMMIT

Example: Rollback on Error

-- Start transaction
BEGIN

-- Make changes
INSERT INTO accounts VALUES (5, 'Eve', 1500)
UPDATE accounts SET balance = -100 WHERE id = 1  -- Invalid!

-- Detect error, rollback
ROLLBACK

-- Changes discarded, database unchanged

Use Cases:

Multi-table updates
Batch operations
Error recovery
Data consistency

🚫 Common Mistakes

Mistake 1: Missing WHERE keyword

❌ SELECT id=102 FROM students
✅ SELECT * FROM students WHERE id = 102

Mistake 2: Wrong DESCRIBE syntax

❌ decribe users (typo)
✅ DESCRIBE users or DESC users

Mistake 3: Case sensitivity in table names

❌ DESCRIBE Users (if table is 'users')
✅ DESCRIBE users

Mistake 4: Missing quotes for strings

❌ INSERT INTO students VALUES (101, Collins, 1)
✅ INSERT INTO students VALUES (101, 'Collins', 1)

Mistake 5: Typos in keywords

❌ fronm instead of from
✅ SELECT * FROM students

💡 Tips

Commands are case-insensitive: SELECT, select, and SeLeCt all work
Table names are case-sensitive: students ≠ Students
Use DESC for quick table inspection: DESC table_name
String values need quotes: Use single quotes 'value' or double quotes "value"
Numbers don't need quotes: 101, 3.14, etc.
WHERE is required for conditions: Always use WHERE column = value

📚 Full Example Session

minidb> SHOW TABLES
['students', 'courses']

minidb> DESC students
{'columns': ['id', 'name', 'course_id'], 'primary_key': 'id', ...}

minidb> SELECT * FROM students
id  | name    | course_id
----|---------|----------
101 | Collins | 1
102 | John    | 2

minidb> SELECT * FROM students WHERE id = 101
id  | name    | course_id
----|---------|----------
101 | Collins | 1

minidb> SELECT * FROM students JOIN courses ON students.course_id = courses.id
id  | name    | course_id | title
----|---------|-----------|------------------
101 | Collins | 1         | Computer Science
102 | John    | 2         | Electrical Eng

minidb> UPDATE students SET name = 'Collins Smith' WHERE id = 101
Updated 1 row(s) in 'students'.

minidb> DELETE FROM students WHERE id = 102
Deleted 1 row(s) from 'students'.

🎯 Need Help?

Type SHOW TABLES to see available tables
Type DESC table_name to see table structure
Type exit or quit to leave the REPL

For more information, see the README.md file.

Provide feedback

Saved searches

Use saved searches to filter your results more quickly

MiniDB SQL Syntax Guide

Quick Reference

✅ Supported Commands

1. SHOW TABLES

2. DESCRIBE / DESC

3. CREATE TABLE

4. INSERT INTO

5. SELECT

Select Specific Columns:

Select with WHERE Clause:

SELECT with LIMIT Clause:

6. SELECT with NESTED SUBQUERY

7. UPDATE

8. DELETE

9. ALTER TABLE

DROP COLUMN

RENAME COLUMN

10. TRANSACTIONS

BEGIN TRANSACTION

COMMIT

ROLLBACK

🚫 Common Mistakes

Mistake 1: Missing WHERE keyword

Mistake 2: Wrong DESCRIBE syntax

Mistake 3: Case sensitivity in table names

Mistake 4: Missing quotes for strings

Mistake 5: Typos in keywords

💡 Tips

📚 Full Example Session

🎯 Need Help?

FilesExpand file tree

SQL_SYNTAX_GUIDE.md

Latest commit

History

SQL_SYNTAX_GUIDE.md

File metadata and controls

MiniDB SQL Syntax Guide

Quick Reference

✅ Supported Commands

1. SHOW TABLES

2. DESCRIBE / DESC

3. CREATE TABLE

4. INSERT INTO

5. SELECT

Select Specific Columns:

Select with WHERE Clause:

SELECT with LIMIT Clause:

6. SELECT with NESTED SUBQUERY

7. UPDATE

8. DELETE

9. ALTER TABLE

DROP COLUMN

RENAME COLUMN

10. TRANSACTIONS

BEGIN TRANSACTION

COMMIT

ROLLBACK

🚫 Common Mistakes

Mistake 1: Missing WHERE keyword

Mistake 2: Wrong DESCRIBE syntax

Mistake 3: Case sensitivity in table names

Mistake 4: Missing quotes for strings

Mistake 5: Typos in keywords

💡 Tips

📚 Full Example Session

🎯 Need Help?