#!/usr/bin/env python3
"""
Generate comprehensive SQL training data for Dev-AI.
Covers: query optimization, indexing, joins, transactions, normalization, security.
Target: 100-130 high-quality entries.
"""
import json
from pathlib import Path
BASE = Path(__file__).parent.parent / "data" / "processed"
def g(issue, bad, good):
return {
"messages": [
{"role": "user", "content": f"Review this SQL code:\n\n```sql\n{bad}\n```"},
{"role": "assistant", "content": f"**Issue: {issue}**\n\n**Fix:**\n```sql\n{good}\n```"}
],
"source": "sql_training",
"tags": ["sql", issue.lower().replace(" ", "_")]
}
entries = []
# =============================================================================
# SQL INJECTION & SECURITY (15 entries)
# =============================================================================
entries.append(g(
"String concatenation in query",
"query = \"SELECT * FROM users WHERE name = '\" + name + \"'\"\ncursor.execute(query)",
"# Use parameterized queries\ncursor.execute(\"SELECT * FROM users WHERE name = ?\", (name,))\n# Or with named parameters:\ncursor.execute(\"SELECT * FROM users WHERE name = :name\", {\"name\": name})"
))
entries.append(g(
"f-string in SQL query",
"cursor.execute(f\"SELECT * FROM orders WHERE user_id = {user_id}\")",
"cursor.execute(\"SELECT * FROM orders WHERE user_id = ?\", (user_id,))\n# Never interpolate values into SQL strings"
))
entries.append(g(
"format() in SQL query",
"cursor.execute(\"SELECT * FROM users WHERE email = '{}'\".format(email))",
"cursor.execute(\"SELECT * FROM users WHERE email = ?\", (email,))\n# .format() is just as vulnerable as f-strings"
))
entries.append(g(
"Dynamic ORDER BY injection",
"column = request.args.get('sort')\ncursor.execute(f\"SELECT * FROM products ORDER BY {column}\")",
"ALLOWED_COLUMNS = {'name', 'price', 'created_at'}\ncolumn = request.args.get('sort', 'name')\nif column not in ALLOWED_COLUMNS:\n column = 'name'\ncursor.execute(f\"SELECT * FROM products ORDER BY {column}\")\n# Allowlist column names for dynamic SQL parts"
))
entries.append(g(
"Dynamic table name injection",
"table = request.args.get('table')\ncursor.execute(f\"SELECT * FROM {table}\")",
"ALLOWED_TABLES = {'users', 'products', 'orders'}\ntable = request.args.get('table', 'users')\nif table not in ALLOWED_TABLES:\n raise ValueError('Invalid table')\ncursor.execute(f\"SELECT * FROM {table}\")\n# Allowlist identifiers that can't be parameterized"
))
entries.append(g(
"Storing passwords in plain text",
"INSERT INTO users (username, password) VALUES ('alice', 'password123')",
"import bcrypt\nhash = bcrypt.hashpw(b'password123', bcrypt.gensalt())\ncursor.execute(\"INSERT INTO users (username, password_hash) VALUES (?, ?)\", ('alice', hash.decode()))\n# Always hash passwords before storing"
))
entries.append(g(
"UNION injection via search",
"term = request.args.get('q')\ncursor.execute(f\"SELECT name FROM products WHERE name LIKE '%{term}%'\")",
"term = request.args.get('q', '')\ncursor.execute(\"SELECT name FROM products WHERE name LIKE ?\", ('%' + term + '%',))\n# Parameterized LIKE queries prevent UNION injection"
))
entries.append(g(
"Second-order injection",
"# User profile stored in DB, then used later\ncursor.execute(f\"INSERT INTO settings (value) VALUES ('{user_input}')\")\n# Later...\ncursor.execute(f\"UPDATE config SET val = (SELECT value FROM settings WHERE id=1)\")",
"cursor.execute(\"INSERT INTO settings (value) VALUES (?)\", (user_input,))\n# Second-order: stored data used in SQL later\n# Defense: parameterize ALL queries, even with stored data"
))
entries.append(g(
"Excessive database privileges",
"# App connection\nDB_USER = app\nDB_PASS = supersecret\n# Connected as root/superuser",
"# Principle of least privilege\nCREATE USER app_user WITH PASSWORD 'strong_password';\nGRANT SELECT, INSERT, UPDATE ON TABLE users TO app_user;\nGRANT SELECT ON TABLE orders TO app_user;\n-- Never connect as superuser from application code"
))
entries.append(g(
"Sensitive data in logs",
"query = f\"SELECT * FROM users WHERE ssn = '{ssn}'\"\nlogger.info(f\"Running query: {query}\")\ncursor.execute(query)",
"cursor.execute(\"SELECT * FROM users WHERE ssn = ?\", (ssn,))\nlogger.info(\"User lookup completed\") # No sensitive data in logs\n# Never log query strings with parameters"
))
entries.append(g(
"SQL dump in error messages",
"try:\n cursor.execute(query)\nexcept psycopg2.Error as e:\n return f\"Database error: {e}\", 500\n# Exposes query structure and schema",
"try:\n cursor.execute(query)\nexcept psycopg2.Error as e:\n logger.error(\"DB error: %s\", e, exc_info=True)\n return \"Internal server error\", 500\n# Log details server-side, show generic message to user"
))
entries.append(g(
"Missing row-level security",
"# Multi-tenant app - no tenant isolation\nSELECT * FROM orders WHERE status = 'pending'\n# Returns ALL tenants' orders",
"SELECT * FROM orders WHERE tenant_id = current_setting('app.tenant_id')::int AND status = 'pending'\n# Or use PostgreSQL Row Level Security:\nALTER TABLE orders ENABLE ROW LEVEL SECURITY;\nCREATE POLICY tenant_isolation ON orders USING (tenant_id = current_setting('app.tenant_id')::int);"
))
entries.append(g(
"Batch insert without parameterization",
"rows = [(name, email) for name, email in users]\nqueries = [f\"INSERT INTO users (name, email) VALUES ('{n}', '{e}')\" for n, e in rows]\nfor q in queries:\n cursor.execute(q)",
"cursor.executemany(\"INSERT INTO users (name, email) VALUES (?, ?)\", users)\n# executemany is safe and efficient"
))
entries.append(g(
"DROP/DELETE without WHERE",
"DELETE FROM sessions\n# Missing WHERE - deletes all sessions",
"DELETE FROM sessions WHERE expires_at < NOW()\n# Always include WHERE on destructive operations\n# In migrations: wrap in transaction so you can rollback"
))
entries.append(g(
"Trusting client-side SQL",
"# Client sends raw SQL for custom reports\ndef run_report(sql):\n cursor.execute(sql)\n return cursor.fetchall()",
"# Never execute client-provided SQL\n# Instead: build queries from validated parameters\ndef run_report(filters):\n query = \"SELECT * FROM reports\"\n conditions = []\n if filters.get('date_from'):\n conditions.append(\"created_at >= ?\")\n params = [f for f in filters.values() if f]\n if conditions:\n query += \" WHERE \" + \" AND \".join(conditions)\n cursor.execute(query, params)"
))
# =============================================================================
# QUERY OPTIMIZATION (18 entries)
# =============================================================================
entries.append(g(
"SELECT * instead of specific columns",
"SELECT * FROM users WHERE status = 'active'",
"SELECT id, username, email FROM users WHERE status = 'active'\n# Only fetch what you need:\n# - Less data transfer\n# - Enables covering index optimization\n# - Breaking changes when schema changes"
))
entries.append(g(
"Missing LIMIT on unbounded query",
"SELECT * FROM logs ORDER BY created_at DESC\n# Table has 10M+ rows",
"SELECT id, message, created_at FROM logs ORDER BY created_at DESC LIMIT 100\n# Always bound result sets for large tables"
))
entries.append(g(
"Function on indexed column in WHERE",
"SELECT * FROM orders WHERE YEAR(created_at) = 2024\n# created_at is indexed but YEAR() prevents index use",
"SELECT * FROM orders WHERE created_at >= '2024-01-01' AND created_at < '2025-01-01'\n# Range on raw column uses the index\n# Functions on columns prevent index usage"
))
entries.append(g(
"Function on indexed column (LEFT)",
"SELECT * FROM users WHERE LEFT(email, 3) = 'abc'\n# email has an index but LEFT() prevents its use",
"SELECT * FROM users WHERE email LIKE 'abc%'\n# LIKE with leading wildcard-free prefix uses index\n# Avoid wrapping indexed columns in functions"
))
entries.append(g(
"Implicit type conversion",
"SELECT * FROM users WHERE phone = 1234567890\n-- phone is VARCHAR but compared to integer\n-- Causes full table scan due to type conversion",
"SELECT * FROM users WHERE phone = '1234567890'\n-- Match the column type in your comparison\n-- Implicit casts prevent index usage"
))
entries.append(g(
"LIKE with leading wildcard",
"SELECT * FROM products WHERE name LIKE '%widget%'\n# Cannot use index - leading % prevents seek",
"SELECT * FROM products WHERE name LIKE 'widget%'\n-- Leading wildcard prevents index use\n-- For full-text search, use: FULLTEXT index, pg_trgm, or dedicated search engine"
))
entries.append(g(
"N+1 query pattern",
"-- In application code:\nusers = db.query(\"SELECT * FROM users\")\nfor user in users:\n orders = db.query(f\"SELECT * FROM orders WHERE user_id = {user.id}\")",
"-- Single query with JOIN:\nSELECT u.id, u.name, o.id as order_id, o.total\nFROM users u\nLEFT JOIN orders o ON o.user_id = u.id\n-- Or fetch all needed orders in one query:\nSELECT * FROM orders WHERE user_id IN (1, 2, 3, ...)"
))
entries.append(g(
"Subquery in SELECT (correlated)",
"SELECT u.name,\n (SELECT COUNT(*) FROM orders o WHERE o.user_id = u.id)\nFROM users u\n-- Runs COUNT(*) once per user row",
"SELECT u.name, COUNT(o.id) as order_count\nFROM users u\nLEFT JOIN orders o ON o.user_id = u.id\nGROUP BY u.id, u.name\n-- JOIN + GROUP BY is one pass instead of N queries"
))
entries.append(g(
"HAVING instead of WHERE for filtering",
"SELECT department, AVG(salary) as avg_sal\nFROM employees\nGROUP BY department\nHAVING AVG(salary) > 50000\n-- HAVING filters after grouping\n-- If filtering rows before aggregation, use WHERE",
"SELECT department, AVG(salary) as avg_sal\nFROM employees\nWHERE hire_date >= '2020-01-01'\nGROUP BY department\nHAVING AVG(salary) > 50000\n-- WHERE filters rows before aggregation (cheaper)\n-- HAVING only for post-aggregation filters"
))
entries.append(g(
"OR conditions preventing index use",
"SELECT * FROM users WHERE email = 'a@b.com' OR phone = '123'\n-- May cause full table scan if both columns indexed separately",
"SELECT * FROM users WHERE email = 'a@b.com'\nUNION\nSELECT * FROM users WHERE phone = '123'\n-- UNION of two index seeks > OR causing full scan\n-- Check EXPLAIN to verify query plan"
))
entries.append(g(
"COUNT(*) with GROUP BY on large table",
"SELECT user_id, COUNT(*) FROM events GROUP BY user_id\n-- Full table scan + sort on 10M+ row table",
"SELECT user_id, COUNT(*) FROM events GROUP BY user_id\n-- Consider:\n-- 1. Partial materialized view refreshed periodically\nREFRESH MATERIALIZED VIEW CONCURRENTLY user_event_counts;\n-- 2. Approximate with COUNT(DISTINCT) on sampled data\n-- 3. Time-bound: WHERE created_at >= '2024-01-01'"
))
entries.append(g(
"Using DISTINCT unnecessarily",
"SELECT DISTINCT u.name FROM users u JOIN orders o ON o.user_id = u.id\n-- DISTINCT adds sort/dedup step",
"SELECT u.name FROM users u INNER JOIN orders o ON o.user_id = u.id\n-- Or: SELECT DISTINCT ON to deduplicate intentionally\n-- Check if JOIN already guarantees uniqueness\n-- DISTINCT = extra sort pass in the query plan"
))
entries.append(g(
"Nested subqueries instead of JOIN",
"SELECT * FROM users\nWHERE id IN (SELECT user_id FROM orders\n WHERE id IN (SELECT order_id FROM payments WHERE amount > 100))",
"SELECT DISTINCT u.*\nFROM users u\nJOIN orders o ON o.user_id = u.id\nJOIN payments p ON p.order_id = o.id\nWHERE p.amount > 100\n-- JOINs are more efficient than nested IN subqueries\n-- Optimizer can choose better join strategies"
))
entries.append(g(
"NOT IN with NULL values",
"SELECT * FROM users WHERE id NOT IN (SELECT user_id FROM banned)\n-- If banned.user_id contains NULL, entire NOT IN returns empty",
"SELECT * FROM users WHERE id NOT IN (SELECT user_id FROM banned WHERE user_id IS NOT NULL)\n-- Or better: NOT EXISTS\nSELECT * FROM users u\nWHERE NOT EXISTS (SELECT 1 FROM banned b WHERE b.user_id = u.id)"
))
entries.append(g(
"Excessive JOINs in single query",
"SELECT * FROM orders o\nJOIN users u ON u.id = o.user_id\nJOIN addresses a ON a.user_id = u.id\nJOIN payments p ON p.order_id = o.id\nJOIN items i ON i.order_id = o.id\nJOIN products pr ON pr.id = i.product_id\nJOIN categories c ON c.id = pr.category_id\nJOIN suppliers s ON s.id = pr.supplier_id\n-- 7 JOINs - likely too wide",
"-- Break into focused queries:\n-- Query 1: orders + users (list view)\n-- Query 2: order details (on-demand)\n-- Or use a denormalized view:\nCREATE VIEW order_summary AS\nSELECT o.id, o.total, u.name, u.email\nFROM orders o JOIN users u ON u.id = o.user_id"
))
entries.append(g(
"Missing query result caching",
"SELECT * FROM config WHERE key = 'site_name'\n-- Run on every request, rarely changes",
"-- Cache infrequently-changing data in application:\n# Python example\nfrom functools import lru_cache\n\n@lru_cache(maxsize=256)\ndef get_config(key):\n return db.query(\"SELECT value FROM config WHERE key = ?\", (key,))\n\n-- Or use Redis/memcached for distributed caching"
))
entries.append(g(
"Fetching data then filtering in application",
"rows = cursor.execute(\"SELECT * FROM transactions WHERE status != 'cancelled'\").fetchall()\n# Fetches cancelled rows too, filters in Python\n-- Actually bad: no WHERE clause at all\nrows = cursor.execute(\"SELECT * FROM transactions\").fetchall()\nfiltered = [r for r in rows if r.status != 'cancelled']",
"rows = cursor.execute(\"SELECT id, amount, date FROM transactions WHERE status != 'cancelled'\").fetchall()\n-- Filter in database, not application\n-- Database has indexes, your app doesn't"
))
entries.append(g(
"Pagination with OFFSET on large tables",
"SELECT * FROM products ORDER BY created_at DESC LIMIT 20 OFFSET 10000\n-- OFFSET 10000 scans and discards 10000 rows",
"SELECT * FROM products\nWHERE created_at < '2024-01-15T10:30:00' -- last seen timestamp\nORDER BY created_at DESC\nLIMIT 20\n-- Keyset pagination: use last-seen value instead of OFFSET\n-- O(1) vs O(n) for deep pagination"
))
# =============================================================================
# INDEXING (15 entries)
# =============================================================================
entries.append(g(
"Missing index on foreign key",
"CREATE TABLE orders (\n id SERIAL PRIMARY KEY,\n user_id INT REFERENCES users(id)\n);\n-- user_id has no index",
"CREATE TABLE orders (\n id SERIAL PRIMARY KEY,\n user_id INT REFERENCES users(id)\n);\nCREATE INDEX idx_orders_user_id ON orders(user_id);\n-- Foreign keys are commonly queried/joined\n-- Always index them"
))
entries.append(g(
"Single-column index when composite needed",
"CREATE INDEX idx_users_email ON users(email);\nCREATE INDEX idx_users_status ON users(status);\n-- Query: SELECT * FROM users WHERE status = 'active' AND email LIKE 'a%'",
"CREATE INDEX idx_users_status_email ON users(status, email);\n-- Composite index: most selective filter first\n-- Single index covers both conditions\n-- Leading column (status) used for equality, second for range"
))
entries.append(g(
"Index with wrong column order",
"CREATE INDEX idx_events ON events(user_id, created_at);\n-- Most queries filter by date range, then group by user\nSELECT * FROM events WHERE created_at > '2024-01-01' GROUP BY user_id",
"CREATE INDEX idx_events_date_user ON events(created_at, user_id);\n-- Equality/equivalence columns first, then range\n-- For range queries: the ranged column should lead\n-- Columns after a range column aren't used for index seeks"
))
entries.append(g(
"Over-indexing (too many indexes)",
"CREATE INDEX idx1 ON users(first_name);\nCREATE INDEX idx2 ON users(last_name);\nCREATE INDEX idx3 ON users(email);\nCREATE INDEX idx4 ON users(phone);\nCREATE INDEX idx5 ON users(city);\nCREATE INDEX idx6 ON users(zip);\nCREATE INDEX idx7 ON users(created_at);\nCREATE INDEX idx8 ON users(updated_at);\n-- Every INSERT/UPDATE must maintain all indexes",
"CREATE INDEX idx_users_email ON users(email);\nCREATE INDEX idx_users_name ON users(last_name, first_name);\nCREATE INDEX idx_users_created ON users(created_at);\n-- Only index columns actually used in WHERE/JOIN/ORDER BY\n-- Each unused index slows writes and wastes space"
))
entries.append(g(
"Index on low-cardinality column",
"CREATE INDEX idx_users_gender ON users(gender);\n-- gender: M/F/other - very few distinct values",
"-- Low-cardinality columns rarely benefit from indexes\n-- Optimizer often chooses sequential scan\n-- Exception: when combined in composite index or with high selectivity filter\n-- Check: SELECT COUNT(DISTINCT gender)::float / COUNT(*) FROM users; -- ratio near 0"
))
entries.append(g(
"Missing covering index",
"CREATE INDEX idx_orders_user ON orders(user_id);\n-- Query does index lookup then table lookup for each row\nSELECT user_id, total, status FROM orders WHERE user_id = 42",
"CREATE INDEX idx_orders_covering ON orders(user_id) INCLUDE (total, status);\n-- Covering index: all needed columns in the index\n-- No table lookup needed (index-only scan)\n-- PostgreSQL: INCLUDE, MySQL: (user_id, total, status)"
))
entries.append(g(
"Index on expression not used in query",
"CREATE INDEX idx_lower_email ON users(LOWER(email));\n-- Query uses: WHERE email = 'A@B.COM' (case-sensitive)",
"CREATE INDEX idx_lower_email ON users(LOWER(email));\n-- Query must match the expression:\nSELECT * FROM users WHERE LOWER(email) = LOWER('A@B.COM')\n-- Or use a case-insensitive collation:\nCREATE TABLE users (email CITEXT); -- PostgreSQL extension"
))
entries.append(g(
"Partial index opportunity missed",
"CREATE INDEX idx_orders_status ON orders(status);\n-- 95% of orders are 'completed', only filter active ones\nSELECT * FROM orders WHERE status = 'active'",
"CREATE INDEX idx_orders_active ON orders(status) WHERE status = 'active';\n-- Partial index: only indexes rows where status = 'active'\n-- Smaller index, faster queries, less write overhead"
))
entries.append(g(
"Unique constraint without index",
"CREATE TABLE users (\n id SERIAL PRIMARY KEY,\n email VARCHAR(255),\n CONSTRAINT unique_email UNIQUE (email)\n);\n-- UNIQUE creates an index implicitly\n-- But sometimes people forget UNIQUE entirely",
"CREATE TABLE users (\n id SERIAL PRIMARY KEY,\n email VARCHAR(255) UNIQUE NOT NULL\n);\n-- UNIQUE constraint automatically creates a unique index\n-- Provides both data integrity and query optimization"
))
entries.append(g(
"Index fragmentation",
"-- After millions of inserts and deletes, index is fragmented\n-- Performance degrades over time\nEXPLAIN ANALYZE SELECT * FROM orders WHERE user_id = 42",
"-- PostgreSQL:\nREINDEX INDEX CONCURRENTLY idx_orders_user_id;\n-- MySQL:\nALTER TABLE orders FORCE;\n-- Or schedule periodic maintenance:\nVACUUM ANALYZE orders;\n-- Monitor: pg_stat_user_indexes, sys.schema_index_statistics"
))
entries.append(g(
"JSON column query without GIN index",
"SELECT * FROM products WHERE attributes->>'color' = 'red'\n-- attributes is JSONB, full scan on every query",
"CREATE INDEX idx_products_attrs ON products USING GIN (attributes);\n-- Or for specific key:\nCREATE INDEX idx_products_color ON products ((attributes->>'color'));\n-- GIN: good for containment (@>), key existence (?|)\n-- B-tree expression index: good for equality on specific key"
))
entries.append(g(
"Full-text search without index",
"SELECT * FROM articles WHERE to_tsvector('english', body) @@ to_tsquery('english', 'database & optimization')",
"CREATE INDEX idx_articles_search ON articles USING GIN (to_tsvector('english', body));\n-- GIN index on tsvector for fast full-text search\n-- Or generated column for cleaner syntax:\nALTER TABLE articles ADD COLUMN search_vector TSVECTOR GENERATED ALWAYS AS (to_tsvector('english', body)) STORED;\nCREATE INDEX idx_articles_search ON articles USING GIN (search_vector);"
))
entries.append(g(
"Composite index with NULLs",
"CREATE INDEX idx_orders ON orders(user_id, status);\n-- Query filters user_id but status is NULL for new orders\nSELECT * FROM orders WHERE user_id = 42 AND status IS NULL",
"CREATE INDEX idx_orders ON orders(user_id, status);\n-- Index works for IS NULL (PostgreSQL)\n-- MySQL: NULL values are indexed normally in B-tree\n-- For high NULL ratio, consider partial index:\nCREATE INDEX idx_orders_new ON orders(user_id) WHERE status IS NULL;"
))
entries.append(g(
"Unused index consuming write overhead",
"-- Index created for a one-off report, never used\nCREATE INDEX idx_users_last_login ON users(last_login);\n-- Every INSERT/UPDATE on users pays the cost",
"-- Check for unused indexes before removing:\nSELECT indexrelid::regclass, idx_scan, idx_tup_read, idx_tup_fetch\nFROM pg_stat_user_indexes\nWHERE idx_scan = 0\nORDER BY pg_relation_size(indexrelid) DESC;\n\n-- If truly unused:\nDROP INDEX idx_users_last_login;\n-- Rule: if an index hasn't been scanned in 90 days, drop it"
))
entries.append(g(
"Wrong index type for the query",
"CREATE INDEX idx_geo ON locations(lat, lon);\n-- Query: find all locations within radius\nSELECT * FROM locations WHERE sqrt((lat - 40.7)::2 + (lon - -74)::2) < 0.1",
"CREATE EXTENSION IF NOT EXISTS postgis;\nALTER TABLE locations ADD COLUMN geom GEOMETRY(Point, 4326) GENERATED ALWAYS AS (ST_SetSRID(ST_MakePoint(lon, lat), 4326)) STORED;\nCREATE INDEX idx_locations_geo ON locations USING GIST (geom);\n-- Spatial queries need spatial indexes (GiST, SP-GiST)\n-- B-tree indexes can't handle geometric operations"
))
# =============================================================================
# JOIN PATTERNS (12 entries)
# =============================================================================
entries.append(g(
"Cross join instead of inner join",
"SELECT u.name, o.total\nFROM users u, orders o\nWHERE u.id = o.user_id\n-- Implicit join syntax - easy to miss missing condition",
"SELECT u.name, o.total\nFROM users u\nINNER JOIN orders o ON u.id = o.user_id\n-- Explicit JOIN syntax:\n-- Clearer intent, harder to accidentally omit ON clause\n-- Implicit syntax can cause accidental cross joins"
))
entries.append(g(
"Cartesian product (missing JOIN condition)",
"SELECT u.name, o.total\nFROM users u\nINNER JOIN orders o\n-- Missing ON clause - returns all combinations",
"SELECT u.name, o.total\nFROM users u\nINNER JOIN orders o ON u.id = o.user_id\n-- Always verify JOIN has an ON condition\n-- Cartesian products: rows(users) * rows(orders)"
))
entries.append(g(
"LEFT JOIN used when INNER JOIN is correct",
"SELECT u.name, o.total\nFROM users u\nLEFT JOIN orders o ON o.user_id = u.id\n-- Only need users who have orders",
"SELECT u.name, o.total\nFROM users u\nINNER JOIN orders o ON o.user_id = u.id\n-- INNER JOIN is clearer and may be faster\n-- LEFT JOIN preserves non-matching rows with NULLs"
))
entries.append(g(
"Self-join for hierarchical data",
"CREATE TABLE employees (\n id INT PRIMARY KEY,\n name VARCHAR(100),\n manager_id INT REFERENCES employees(id)\n);\n-- Find manager names\ne.name as employee, m.name as manager",
"SELECT e.name as employee, m.name as manager\nFROM employees e\nLEFT JOIN employees m ON e.manager_id = m.id\n-- Self-join: same table aliased differently\n-- LEFT JOIN: top-level managers have NULL manager"
))
entries.append(g(
"JOIN with OR in ON clause",
"SELECT * FROM orders o\nJOIN users u ON u.id = o.user_id OR u.email = o.guest_email\n-- OR in ON clause can cause performance issues",
"SELECT * FROM orders o\nJOIN users u ON u.id = o.user_id\nWHERE o.guest_email IS NULL\nUNION ALL\nSELECT * FROM orders o\nJOIN users u ON u.email = o.guest_email\nWHERE o.guest_email IS NOT NULL\n-- Split OR into separate queries with UNION\n-- Each can use its own index efficiently"
))
entries.append(g(
"Multiple JOINs creating duplicate rows",
"SELECT u.name, COUNT(o.id)\nFROM users u\nJOIN orders o ON o.user_id = u.id\nJOIN payments p ON p.order_id = o.id\nGROUP BY u.id, u.name\n-- If multiple payments per order, orders are counted wrong",
"SELECT u.name, COUNT(DISTINCT o.id)\nFROM users u\nJOIN orders o ON o.user_id = u.id\nJOIN payments p ON p.order_id = o.id\nGROUP BY u.id, u.name\n-- Or pre-aggregate:\nSELECT u.name, sub.order_count\nFROM users u\nJOIN (SELECT user_id, COUNT(DISTINCT id) as order_count FROM orders GROUP BY user_id) sub\n ON sub.user_id = u.id"
))
entries.append(g(
"Using EXISTS instead of IN for existence check",
"SELECT * FROM users WHERE id IN (SELECT user_id FROM orders WHERE total > 100)\n-- IN subquery returns all matching IDs",
"SELECT * FROM users u\nWHERE EXISTS (\n SELECT 1 FROM orders o WHERE o.user_id = u.id AND o.total > 100\n)\n-- EXISTS short-circuits on first match\n-- More efficient for large result sets"
))
entries.append(g(
"ANTI-JOIN pattern (NOT IN vs NOT EXISTS)",
"SELECT * FROM users WHERE id NOT IN (SELECT user_id FROM orders)\n-- Returns users with no orders\n-- Fails silently if orders.user_id contains NULL",
"SELECT * FROM users u\nWHERE NOT EXISTS (\n SELECT 1 FROM orders o WHERE o.user_id = u.id\n)\n-- NOT EXISTS handles NULLs correctly\n-- Also: LEFT JOIN ... WHERE right_side IS NULL"
))
entries.append(g(
"Semi-join for filtering with duplicates",
"SELECT DISTINCT u.*\nFROM users u\nJOIN orders o ON o.user_id = u.id\nWHERE o.total > 100\n-- DISTINCT needed because multiple orders per user",
"SELECT * FROM users u\nWHERE EXISTS (\n SELECT 1 FROM orders o WHERE o.user_id = u.id AND o.total > 100\n)\n-- Semi-join: returns each user once\n-- No DISTINCT needed, often faster"
))
entries.append(g(
"Lateral join for per-row subquery",
"-- Get top order per user\nSELECT u.name, sub.total\nFROM users u\nCROSS JOIN LATERAL (\n SELECT total FROM orders WHERE user_id = u.id ORDER BY total DESC LIMIT 1\n) sub",
"-- PostgreSQL: LATERAL is the right tool here\n-- Alternatively with window function:\nSELECT name, total FROM (\n SELECT u.name, o.total,\n ROW_NUMBER() OVER (PARTITION BY u.id ORDER BY o.total DESC) as rn\n FROM users u JOIN orders o ON o.user_id = u.id\n) sub WHERE rn = 1\n-- Window function avoids correlated subquery per row"
))
entries.append(g(
"JOIN on non-indexed columns",
"SELECT * FROM orders o\nJOIN order_items i ON i.order_id = o.id\nJOIN products p ON p.slug = i.product_slug\n-- product_slug has no index",
"-- Add index on the join column:\nCREATE INDEX idx_items_product_slug ON order_items(product_slug);\nCREATE INDEX idx_products_slug ON products(slug);\n-- JOINs without indexes = nested loop with full scan\n-- Each row in the outer table scans the entire inner table"
))
entries.append(g(
"Natural join (implicit column matching)",
"SELECT * FROM users NATURAL JOIN orders\n-- Joins on all same-named columns\n-- Fragile: schema changes break silently",
"SELECT u.name, o.total, o.created_at\nFROM users u\nINNER JOIN orders o ON o.user_id = u.id\n-- Always specify JOIN conditions explicitly\n-- NATURAL JOIN = hidden coupling between schemas"
))
# =============================================================================
# TRANSACTIONS & CONCURRENCY (12 entries)
# =============================================================================
entries.append(g(
"Missing transaction for multi-step operation",
"-- Transfer money without transaction\nUPDATE accounts SET balance = balance - 100 WHERE id = 1;\nUPDATE accounts SET balance = balance + 100 WHERE id = 2;\n-- If second fails, money disappears",
"BEGIN;\nUPDATE accounts SET balance = balance - 100 WHERE id = 1;\nUPDATE accounts SET balance = balance + 100 WHERE id = 2;\nCOMMIT;\n-- Transaction ensures atomicity:\n-- Either both succeed or both rollback"
))
entries.append(g(
"Lost update race condition",
"-- Two sessions read balance simultaneously\nSELECT balance FROM accounts WHERE id = 1; -- Both see 1000\nUPDATE accounts SET balance = 900 WHERE id = 1; -- Session A deducts 100\nUPDATE accounts SET balance = 800 WHERE id = 1; -- Session B deducts 200, overwrites A\n-- Result: 800 instead of 700",
"BEGIN;\nSELECT balance FROM accounts WHERE id = 1 FOR UPDATE;\n-- Read balance (locked)\n-- Calculate new balance\nUPDATE accounts SET balance = balance - 200 WHERE id = 1;\nCOMMIT;\n-- FOR UPDATE locks the row\n-- Session B waits for Session A to commit"
))
entries.append(g(
"Dirty read (uncommitted data)",
"-- Session A:\nBEGIN;\nUPDATE accounts SET balance = -500 WHERE id = 1;\n-- Session B (READ UNCOMMITTED):\nSELECT balance FROM accounts WHERE id = 1; -- Sees -500\n-- Session A: ROLLBACK;\n-- Session B saw data that never existed",
"SET TRANSACTION ISOLATION LEVEL READ COMMITTED;\n-- Default in PostgreSQL and MySQL\n-- Only reads committed data\n-- For stronger guarantees use REPEATABLE READ or SERIALIZABLE"
))
entries.append(g(
"Non-repeatable read",
"-- Session A:\nBEGIN;\nSELECT balance FROM accounts WHERE id = 1; -- 1000\n-- Session B:\nUPDATE accounts SET balance = 500 WHERE id = 1;\nCOMMIT;\n-- Session A:\nSELECT balance FROM accounts WHERE id = 1; -- 500 (changed!)\n-- Same query, different result within transaction",
"SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;\n-- Guarantees same result for repeated reads\n-- Uses multiversion concurrency control (MVCC)\n-- Trade-off: more locking, potential for serialization failures"
))
entries.append(g(
"Phantom read",
"-- Session A:\nBEGIN;\nSELECT COUNT(*) FROM orders WHERE user_id = 42; -- 5\n-- Session B:\nINSERT INTO orders (user_id, total) VALUES (42, 99.99);\nCOMMIT;\n-- Session A:\nSELECT COUNT(*) FROM orders WHERE user_id = 42; -- 6 (phantom!)",
"SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;\n-- Prevents phantom reads entirely\n-- Or use SELECT ... FOR UPDATE/SKIP LOCKED\n-- Or application-level idempotency to handle duplicates"
))
entries.append(g(
"Long-running transaction holding locks",
"BEGIN;\nUPDATE users SET status = 'processing' WHERE id = 42;\n-- ... complex processing that takes 30 seconds ...\nUPDATE users SET status = 'done' WHERE id = 42;\nCOMMIT;\n-- Row locked for 30 seconds, blocking other sessions",
"BEGIN;\nUPDATE users SET status = 'processing' WHERE id = 42;\nCOMMIT;\n-- Do processing outside transaction\nBEGIN;\nUPDATE users SET status = 'done' WHERE id = 42;\nCOMMIT;\n-- Keep transactions short\n-- Do CPU work between transactions, not inside them"
))
entries.append(g(
"Deadlock",
"-- Session A:\nBEGIN;\nUPDATE accounts SET balance = balance - 100 WHERE id = 1;\n-- Session B:\nBEGIN;\nUPDATE accounts SET balance = balance - 50 WHERE id = 2;\n-- Session A:\nUPDATE accounts SET balance = balance + 100 WHERE id = 2; -- Waits\n-- Session B:\nUPDATE accounts SET balance = balance + 50 WHERE id = 1; -- Deadlock!",
"-- Consistent lock ordering prevents deadlocks:\n-- Always lock lower ID first\nUPDATE accounts SET balance = balance - 100 WHERE id = LEAST(1, 2);\nUPDATE accounts SET balance = balance + 100 WHERE id = GREATEST(1, 2);\n-- Or let the database handle it: catch deadlock error and retry"
))
entries.append(g(
"Missing savepoint for partial rollback",
"BEGIN;\nINSERT INTO orders (user_id, total) VALUES (42, 99.99);\nINSERT INTO order_items (order_id, product_id) VALUES (LASTVAL(), 1);\n-- If order_items fails, entire transaction rolls back\n-- Including the order insert that was fine",
"BEGIN;\nSAVEPOINT sp_order;\nINSERT INTO orders (user_id, total) VALUES (42, 99.99);\nSAVEPOINT sp_item;\nINSERT INTO order_items (order_id, product_id) VALUES (LASTVAL(), 1);\n-- If item insert fails:\nROLLBACK TO sp_item;\n-- Order is still there, retry item insert\nCOMMIT;"
))
entries.append(g(
"Race condition on unique constraint",
"INSERT INTO users (email) VALUES ('alice@example.com');\n-- Concurrent inserts can both pass application check\n-- Then one fails with duplicate key error",
"INSERT INTO users (email) VALUES ('alice@example.com')\nON CONFLICT (email) DO NOTHING\nRETURNING id;\n-- Or handle the error:\ntry:\n cursor.execute(\"INSERT INTO users (email) VALUES (?)\", (email,))\nexcept IntegrityError:\n pass # Already exists\n-- Database constraint is the source of truth"
))
entries.append(g(
"Skipped lock for queue pattern",
"-- Worker picks next job\nSELECT * FROM jobs WHERE status = 'pending' LIMIT 1;\nUPDATE jobs SET status = 'running' WHERE id = selected_id;\n-- Race: multiple workers can pick the same job",
"-- PostgreSQL: SKIP LOCKED\nSELECT * FROM jobs\nWHERE status = 'pending'\nORDER BY created_at\nLIMIT 1\nFOR UPDATE SKIP LOCKED;\n-- Atomically locks and retrieves\n-- Skips rows locked by other workers\n-- No race condition"
))
entries.append(g(
"UPDATE without WHERE on transaction",
"BEGIN;\nUPDATE accounts SET balance = 0;\n-- Updates ALL accounts\n-- Meant to update one",
"BEGIN;\nUPDATE accounts SET balance = 0 WHERE id = 42;\n-- Always include WHERE on UPDATE/DELETE\n-- Consider: SELECT count first to verify scope\nSELECT COUNT(*) FROM accounts WHERE id = 42;\n-- Then UPDATE"
))
entries.append(g(
"Optimistic concurrency control",
"UPDATE accounts SET balance = 500 WHERE id = 1;\n-- Overwrites concurrent changes",
"UPDATE accounts\nSET balance = 500, version = version + 1\nWHERE id = 1 AND version = 3;\n-- Check affected rows:\n-- 0 rows = concurrent modification, retry\n-- 1 row = success\n-- Version column detects concurrent updates"
))
# =============================================================================
# NORMALIZATION (10 entries)
# =============================================================================
entries.append(g(
"Repeating groups in a single table",
"CREATE TABLE orders (\n id INT PRIMARY KEY,\n user_id INT,\n item1 VARCHAR(100),\n item2 VARCHAR(100),\n item3 VARCHAR(100)\n);\n-- Fixed number of items, repeating pattern",
"CREATE TABLE orders (\n id INT PRIMARY KEY,\n user_id INT\n);\nCREATE TABLE order_items (\n id INT PRIMARY KEY,\n order_id INT REFERENCES orders(id),\n product_name VARCHAR(100)\n);\n-- Normalize: separate repeating data into a child table\n-- No arbitrary limit on items per order"
))
entries.append(g(
"Storing computed data as column",
"CREATE TABLE orders (\n id INT PRIMARY KEY,\n subtotal DECIMAL(10,2),\n tax DECIMAL(10,2),\n total DECIMAL(10,2),\n -- total = subtotal + tax, stored redundantly\n);",
"CREATE TABLE orders (\n id INT PRIMARY KEY,\n subtotal DECIMAL(10,2),\n tax_rate DECIMAL(5,4)\n);\n-- Compute total when needed:\nSELECT id, subtotal, subtotal * tax_rate as tax, subtotal * (1 + tax_rate) as total\nFROM orders;\n-- Derived data = update anomalies\n-- Exception: denormalize for performance if justified"
))
entries.append(g(
"Transitive dependency (2NF violation)",
"CREATE TABLE order_details (\n order_id INT,\n user_id INT,\n user_email VARCHAR(255),\n user_city VARCHAR(100),\n -- user_email and user_city depend on user_id, not order_id\n PRIMARY KEY (order_id)\n);",
"CREATE TABLE users (\n id INT PRIMARY KEY,\n email VARCHAR(255),\n city VARCHAR(100)\n);\nCREATE TABLE orders (\n id INT PRIMARY KEY,\n user_id INT REFERENCES users(id)\n);\n-- 3NF: every non-key column depends on the whole key\n-- User attributes belong in users table"
))
entries.append(g(
"JSON/array column for relational data",
"CREATE TABLE orders (\n id INT PRIMARY KEY,\n items JSONB -- [{product_id: 1, qty: 2}, ...]\n);\n-- Relational data stored as JSON in a column",
"CREATE TABLE orders (\n id INT PRIMARY KEY\n);\nCREATE TABLE order_items (\n order_id INT REFERENCES orders(id),\n product_id INT REFERENCES products(id),\n quantity INT,\n PRIMARY KEY (order_id, product_id)\n);\n-- Normalize relational data into proper tables\n-- Use JSON for truly semi-structured data"
))
entries.append(g(
"Composite key as surrogate primary key",
"CREATE TABLE order_items (\n order_id INT,\n product_id INT,\n quantity INT,\n price DECIMAL(10,2),\n PRIMARY KEY (order_id, product_id)\n);",
"CREATE TABLE order_items (\n id SERIAL PRIMARY KEY,\n order_id INT REFERENCES orders(id),\n product_id INT REFERENCES products(id),\n quantity INT,\n price DECIMAL(10,2),\n UNIQUE (order_id, product_id)\n);\n-- Surrogate PK simplifies foreign key references\n-- Unique constraint maintains data integrity\n-- Composite keys are fine if nothing references them"
))
entries.append(g(
"Denormalized user data in orders",
"CREATE TABLE orders (\n id INT PRIMARY KEY,\n user_id INT,\n user_name VARCHAR(100),\n user_email VARCHAR(255),\n user_address TEXT\n -- User data duplicated in every order\n);",
"CREATE TABLE orders (\n id INT PRIMARY KEY,\n user_id INT REFERENCES users(id)\n);\n-- Get user data via JOIN when needed\n-- If performance requires denormalization:\n-- Document it explicitly as intentional"
))
entries.append(g(
"Multi-value attribute in single column",
"CREATE TABLE users (\n id INT PRIMARY KEY,\n phone_numbers VARCHAR(255) -- '555-1111,555-2222'\n);",
"CREATE TABLE users (\n id INT PRIMARY KEY\n);\nCREATE TABLE user_phones (\n user_id INT REFERENCES users(id),\n number VARCHAR(20),\n type VARCHAR(20), -- 'mobile', 'home'\n PRIMARY KEY (user_id, number)\n);\n-- One value per cell = 1NF"
))
entries.append(g(
"Category hierarchy flat table",
"CREATE TABLE categories (\n id INT PRIMARY KEY,\n name VARCHAR(100),\n parent_id INT,\n grandparent_id INT -- Hardcoded hierarchy depth\n);",
"CREATE TABLE categories (\n id INT PRIMARY KEY,\n name VARCHAR(100),\n parent_id INT REFERENCES categories(id)\n);\n-- Adjacency list: any depth hierarchy\n-- For queries use recursive CTE:\nWITH RECURSIVE tree AS (\n SELECT id, name, parent_id, 0 as depth FROM categories WHERE parent_id IS NULL\n UNION ALL\n SELECT c.id, c.name, c.parent_id, t.depth + 1\n FROM categories c JOIN tree t ON c.parent_id = t.id\n)\nSELECT * FROM tree;"
))
entries.append(g(
"Polymorphic association (bad pattern)",
"CREATE TABLE images (\n id INT PRIMARY KEY,\n imageable_id INT,\n imageable_type VARCHAR(50) -- 'user' or 'product'\n);\n-- Single FK references multiple tables",
"CREATE TABLE user_avatars (\n user_id INT PRIMARY KEY REFERENCES users(id)\n);\nCREATE TABLE product_images (\n product_id INT REFERENCES products(id),\n image_url VARCHAR(255)\n);\n-- Separate junction tables per relationship\n-- Polymorphic associations: no FK constraint, hard to query"
))
entries.append(g(
"Wide table with sparse columns",
"CREATE TABLE products (\n id INT PRIMARY KEY,\n name VARCHAR(100),\n -- Electronics-specific:\n cpu VARCHAR(50),\n ram_gb INT,\n -- Clothing-specific:\n size VARCHAR(10),\n material VARCHAR(50),\n -- Food-specific:\n expiry_date DATE,\n calories INT\n -- Most rows have NULLs in most columns\n);",
"CREATE TABLE products (\n id INT PRIMARY KEY,\n name VARCHAR(100)\n);\nCREATE TABLE product_attributes (\n product_id INT REFERENCES products(id),\n attribute_key VARCHAR(50),\n attribute_value TEXT,\n PRIMARY KEY (product_id, attribute_key)\n);\n-- EAV pattern for truly variable attributes\n-- Or use JSONB column for flexible attributes"
))
# =============================================================================
# WINDOW FUNCTIONS & CTEs (10 entries)
# =============================================================================
entries.append(g(
"Self-join for running total",
"SELECT a.id, a.date, a.amount,\n (SELECT SUM(b.amount) FROM daily_sales b WHERE b.date <= a.date) as running_total\nFROM daily_sales a\nORDER BY a.date\n-- Correlated subquery for each row",
"SELECT id, date, amount,\n SUM(amount) OVER (ORDER BY date) as running_total\nFROM daily_sales\nORDER BY date\n-- Window function: single pass\n-- Much faster than correlated subquery"
))
entries.append(g(
"Ranking with self-join",
"SELECT a.name, a.score,\n (SELECT COUNT(*) FROM students b WHERE b.score > a.score) + 1 as rank\nFROM students a\nORDER BY a.score DESC\n-- O(n^2) self-join",
"SELECT name, score,\n RANK() OVER (ORDER BY score DESC) as rank\nFROM students\nORDER BY score DESC\n-- Window function: O(n log n) sort"
))
entries.append(g(
"Missing PARTITION BY in window function",
"SELECT name, department, salary,\n RANK() OVER (ORDER BY salary DESC) as salary_rank\nFROM employees\n-- Ranks across entire company, not per department",
"SELECT name, department, salary,\n RANK() OVER (PARTITION BY department ORDER BY salary DESC) as dept_salary_rank\nFROM employees\n-- PARTITION BY creates separate windows per group\n-- Without it, window spans all rows"
))
entries.append(g(
"CTE for reusability",
"-- Repeated subquery\nSELECT * FROM (\n SELECT user_id, COUNT(*) as order_count FROM orders WHERE status = 'completed' GROUP BY user_id\n) sub\nWHERE order_count > 5\nORDER BY order_count DESC",
"WITH completed_orders AS (\n SELECT user_id, COUNT(*) as order_count\n FROM orders WHERE status = 'completed'\n GROUP BY user_id\n)\nSELECT * FROM completed_orders\nWHERE order_count > 5\nORDER BY order_count DESC\n-- CTE: readable, reusable\n-- Can reference CTE multiple times"
))
entries.append(g(
"Recursive CTE for tree traversal",
"-- Find all descendants of a category\n-- Requires application-level recursive query building",
"WITH RECURSIVE descendants AS (\n SELECT id, name, parent_id, 0 as depth\n FROM categories WHERE id = 1\n UNION ALL\n SELECT c.id, c.name, c.parent_id, d.depth + 1\n FROM categories c JOIN descendants d ON c.parent_id = d.id\n)\nSELECT * FROM descendants\n-- Recursive CTE handles arbitrary depth\n-- No application-level recursion needed"
))
entries.append(g(
"ROW_NUMBER for deduplication",
"DELETE FROM users\nWHERE id NOT IN (\n SELECT MIN(id) FROM users GROUP BY email\n);\n-- Keep one user per email, delete duplicates",
"WITH numbered AS (\n SELECT id, ROW_NUMBER() OVER (PARTITION BY email ORDER BY id) as rn\n FROM users\n)\nDELETE FROM users WHERE id IN (\n SELECT id FROM numbered WHERE rn > 1\n);\n-- ROW_NUMBER: deterministic dedup\n-- ORDER BY id keeps the earliest record"
))
entries.append(g(
"LAG/LEAD for row comparison",
"SELECT a.date, a.revenue,\n a.revenue - (\n SELECT b.revenue FROM daily_revenue b\n WHERE b.date = (SELECT MAX(date) FROM daily_revenue WHERE date < a.date)\n ) as day_over_day_change\nFROM daily_revenue a",
"SELECT date, revenue,\n revenue - LAG(revenue) OVER (ORDER BY date) as day_over_day_change,\n revenue - LAG(revenue, 7) OVER (ORDER BY date) as week_over_week_change\nFROM daily_revenue\n-- LAG/LEAD: access previous/next rows\n-- Second arg = offset (7 = last week)"
))
entries.append(g(
"Multiple aggregations with GROUP BY vs window",
"SELECT department, COUNT(*) as dept_count\nFROM employees GROUP BY department\n-- Separate query for total count\nSELECT COUNT(*) FROM employees",
"SELECT department,\n COUNT(*) as dept_count,\n COUNT(*) OVER () as total_count,\n ROUND(COUNT(*) * 100.0 / COUNT(*) OVER (), 1) as pct_of_total\nFROM employees\nGROUP BY department\n-- Window function on aggregated result\n-- Single query, no self-join needed"
))
entries.append(g(
"Frame clause in window function",
"SELECT date, amount,\n SUM(amount) OVER (ORDER BY date) as running_total\nFROM daily_sales\n-- Default frame: ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW\n-- But be explicit about frame boundaries",
"SELECT date, amount,\n SUM(amount) OVER (\n ORDER BY date\n ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW\n ) as running_total,\n SUM(amount) OVER (\n ORDER BY date\n ROWS BETWEEN 6 PRECEDING AND CURRENT ROW\n ) as rolling_7day\nFROM daily_sales\n-- Be explicit about frame boundaries\n-- ROWS vs RANGE matters with duplicate dates"
))
entries.append(g(
"Materialized CTE for large intermediate result",
"WITH stats AS (\n SELECT user_id, COUNT(*) as cnt, AVG(total) as avg_total\n FROM orders GROUP BY user_id\n)\nSELECT * FROM stats WHERE cnt > 10\n-- CTE may be inlined and recomputed\n-- PostgreSQL: non-materialized CTEs are optimization fences",
"WITH stats AS MATERIALIZED (\n SELECT user_id, COUNT(*) as cnt, AVG(total) as avg_total\n FROM orders GROUP BY user_id\n)\nSELECT * FROM stats WHERE cnt > 10\n-- MATERIALIZED hint (PostgreSQL 12+)\n-- Forces materialization when beneficial\n-- Let optimizer decide: omit hint for small CTEs"
))
# =============================================================================
# DATA TYPES & CONSTRAINTS (8 entries)
# =============================================================================
entries.append(g(
"VARCHAR(n) with arbitrary length",
"CREATE TABLE users (\n email VARCHAR(255),\n phone VARCHAR(500) -- Phone doesn't need 500 chars\n bio VARCHAR(10000) -- Use TEXT for long content\n);",
"CREATE TABLE users (\n email VARCHAR(255) NOT NULL,\n phone VARCHAR(20), -- E.164 format: +1234567890\n bio TEXT -- TEXT has no length limit\n);\n-- Match column size to actual data\n-- TEXT and VARCHAR have no performance difference in PostgreSQL"
))
entries.append(g(
"Storing dates as strings",
"CREATE TABLE events (\n id INT PRIMARY KEY,\n event_date VARCHAR(10) -- '2024-01-15'\n);",
"CREATE TABLE events (\n id INT PRIMARY KEY,\n event_date DATE NOT NULL\n);\n-- Use proper types:\n-- DATE for dates, TIMESTAMP for timestamps\n-- Enables date arithmetic, indexing, validation"
))
entries.append(g(
"NULL where NOT NULL is appropriate",
"CREATE TABLE users (\n id INT PRIMARY KEY,\n email VARCHAR(255), -- Can be NULL?\n created_at TIMESTAMP, -- Every row has a creation time\n status VARCHAR(20), -- Can be NULL?\n);",
"CREATE TABLE users (\n id SERIAL PRIMARY KEY,\n email VARCHAR(255) NOT NULL UNIQUE,\n created_at TIMESTAMP NOT NULL DEFAULT NOW(),\n status VARCHAR(20) NOT NULL DEFAULT 'active'\n);\n-- NOT NULL + DEFAULT: enforce constraints at DB level\n-- Application-level validation is not enough"
))
entries.append(g(
"Using TINYINT for boolean",
"CREATE TABLE users (\n id INT PRIMARY KEY,\n is_active TINYINT -- 0 or 1\n);",
"CREATE TABLE users (\n id INT PRIMARY KEY,\n is_active BOOLEAN NOT NULL DEFAULT TRUE\n);\n-- BOOLEAN: clear intent, proper type\n-- TINYINT: ambiguous (what about values > 1?)"
))
entries.append(g(
"Check constraint for business rules",
"CREATE TABLE products (\n id INT PRIMARY KEY,\n price DECIMAL(10,2),\n quantity INT\n -- Negative prices or quantities possible\n);",
"CREATE TABLE products (\n id SERIAL PRIMARY KEY,\n price DECIMAL(10,2) NOT NULL CHECK (price >= 0),\n quantity INT NOT NULL DEFAULT 0 CHECK (quantity >= 0)\n);\n-- CHECK constraints enforce business rules at DB level\n-- Cannot be bypassed by application bugs"
))
entries.append(g(
"Using INTEGER for large IDs (overflow)",
"CREATE TABLE events (\n id INT PRIMARY KEY,\n -- INT max: 2,147,483,647\n -- High-traffic event log will overflow\n);",
"CREATE TABLE events (\n id BIGSERIAL PRIMARY KEY,\n -- BIGINT max: 9,223,372,036,854,775,807\n -- Won't overflow at reasonable insertion rates\n);\n-- Use BIGSERIAL/BIGINT for high-volume tables\n-- INT is fine for bounded datasets"
))
entries.append(g(
"Enum stored as string without constraint",
"CREATE TABLE orders (\n id INT PRIMARY KEY,\n status VARCHAR(20) -- 'pending', 'shipped', 'delivered'\n -- No enforcement - garbage values possible\n);",
"CREATE TYPE order_status AS ENUM ('pending', 'shipped', 'delivered', 'cancelled');\nCREATE TABLE orders (\n id SERIAL PRIMARY KEY,\n status order_status NOT NULL DEFAULT 'pending'\n);\n-- PostgreSQL ENUM type enforces valid values\n-- Or CHECK constraint: CHECK (status IN ('pending', 'shipped', 'delivered'))"
))
entries.append(g(
"Timestamp without timezone",
"CREATE TABLE events (\n id INT PRIMARY KEY,\n created_at TIMESTAMP -- No timezone info\n);",
"CREATE TABLE events (\n id SERIAL PRIMARY KEY,\n created_at TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT NOW()\n);\n-- TIMESTAMPTZ stores UTC internally\n-- Converts on read based on session timezone\n-- Never use TIMESTAMP without timezone for application data"
))
# =============================================================================
# SCHEMA DESIGN & MIGRATIONS (8 entries)
# =============================================================================
entries.append(g(
"Adding column with no default to existing table",
"ALTER TABLE users ADD COLUMN phone VARCHAR(20);\n-- Existing rows get NULL\n-- If NOT NULL needed, this fails on existing data",
"ALTER TABLE users ADD COLUMN phone VARCHAR(20) DEFAULT '';\nUPDATE users SET phone = 'unknown' WHERE phone = '';\nALTER TABLE users ALTER COLUMN phone SET NOT NULL;\n-- Add with default, backfill, then enforce NOT NULL\n-- Or: ALTER TABLE users ADD COLUMN phone VARCHAR(20);\n-- (accept NULLs temporarily, migrate in phases)"
))
entries.append(g(
"Dropping column directly",
"ALTER TABLE users DROP COLUMN phone;\n-- Data lost immediately, no rollback",
"-- Phase 1: Mark as deprecated, stop writing\n-- Phase 2: Migrate data if needed\n-- Phase 3: Drop in separate migration\nALTER TABLE users DROP COLUMN IF EXISTS phone;\n-- Or use a migration framework with rollback:\n# migrations/003_drop_phone.sql\n# up:\nALTER TABLE users DROP COLUMN phone;\n# down:\nALTER TABLE users ADD COLUMN phone VARCHAR(20);"
))
entries.append(g(
"Missing soft delete",
"DELETE FROM users WHERE id = 42;\n-- Hard delete: data gone, orphaned foreign keys",
"ALTER TABLE users ADD COLUMN deleted_at TIMESTAMP;\n-- Soft delete:\nUPDATE users SET deleted_at = NOW() WHERE id = 42;\n-- Query excludes deleted:\nSELECT * FROM users WHERE deleted_at IS NULL;\n-- Or partial index:\nCREATE INDEX idx_users_active ON users(id) WHERE deleted_at IS NULL;"
))
entries.append(g(
"No audit trail",
"UPDATE users SET email = 'new@email.com' WHERE id = 42;\n-- Previous email lost, no history",
"CREATE TABLE user_audit (\n id BIGSERIAL PRIMARY KEY,\n user_id INT REFERENCES users(id),\n action VARCHAR(20),\n old_values JSONB,\n new_values JSONB,\n changed_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),\n changed_by INT\n);\n-- Log changes via triggers or application code\n-- Essential for compliance and debugging"
))
entries.append(g(
"Online schema change on large table",
"ALTER TABLE orders ADD COLUMN metadata JSONB;\n-- PostgreSQL: locks table for exclusive access\n-- Blocks all reads and writes during ALTER",
"-- PostgreSQL 11+: ADD COLUMN with DEFAULT is instant (no lock)\nALTER TABLE orders ADD COLUMN metadata JSONB;\n-- For operations that DO lock:\n-- Use pg_online_schema_change or similar tools\n-- Or: add column, backfill in batches, verify\n-- MySQL: use pt-online-schema-change or gh-ost"
))
entries.append(g(
"Missing NOT NULL on foreign key",
"CREATE TABLE order_items (\n id SERIAL PRIMARY KEY,\n order_id INT REFERENCES orders(id)\n -- order_id can be NULL - orphan items possible\n);",
"CREATE TABLE order_items (\n id SERIAL PRIMARY KEY,\n order_id INT NOT NULL REFERENCES orders(id) ON DELETE CASCADE\n);\n-- NOT NULL on FK: every item must belong to an order\n-- ON DELETE CASCADE: cleaning up automatically"
))
entries.append(g(
"Using SERIAL instead of IDENTITY",
"CREATE TABLE users (\n id SERIAL PRIMARY KEY\n);\n-- SERIAL is PostgreSQL-specific, uses sequences",
"CREATE TABLE users (\n id INT GENERATED ALWAYS AS IDENTITY PRIMARY KEY\n);\n-- SQL standard IDENTITY column\n-- Better portability, safer (cannot be inserted manually)\n-- SERIAL is fine for PostgreSQL-only projects"
))
entries.append(g(
"Table without primary key",
"CREATE TABLE logs (\n message TEXT,\n level VARCHAR(10),\n created_at TIMESTAMP\n);\n-- No primary key: cannot update/delete specific rows\n-- PostgreSQL heap-organized: no clustering",
"CREATE TABLE logs (\n id BIGSERIAL PRIMARY KEY,\n message TEXT NOT NULL,\n level VARCHAR(10) NOT NULL,\n created_at TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT NOW()\n);\n-- Primary key enables:\n-- Targeted updates/deletes\n-- Foreign key references\n-- Row identification"
))
# =============================================================================
# AGGREGATION & GROUPING (8 entries)
# =============================================================================
entries.append(g(
"GROUP BY without aggregation",
"SELECT user_id, email\nFROM users\nGROUP BY user_id\n-- GROUP BY without aggregate = confusing\n-- Same as DISTINCT on user_id",
"SELECT DISTINCT ON (user_id) user_id, email\nFROM users\nORDER BY user_id, created_at DESC\n-- If you want one row per user: DISTINCT ON or window function\n-- GROUP BY should have aggregate functions"
))
entries.append(g(
"String aggregation with concatenation loop",
"-- Application code builds comma-separated list\nSELECT id FROM tags WHERE post_id = 42\n-- Then joins in Python: ','.join(rows)",
"SELECT STRING_AGG(tag, ', ' ORDER BY tag) as tags\nFROM tags WHERE post_id = 42\n-- STRING_AGG: database-level aggregation\n-- GROUP_CONCAT in MySQL, LISTAGG in Oracle"
))
entries.append(g(
"PIVOT with CASE WHEN",
"SELECT department,\n SUM(CASE WHEN quarter = 'Q1' THEN revenue END) as q1,\n SUM(CASE WHEN quarter = 'Q2' THEN revenue END) as q2,\n SUM(CASE WHEN quarter = 'Q3' THEN revenue END) as q3,\n SUM(CASE WHEN quarter = 'Q4' THEN revenue END) as q4\nFROM quarterly_revenue\nGROUP BY department",
"-- PostgreSQL 14+: native PIVOT via tablefunc\nSELECT * FROM crosstab(\n 'SELECT department, quarter, revenue FROM quarterly_revenue ORDER BY 1,2'\n) AS ct(department text, q1 int, q2 int, q3 int, q4 int);\n-- Or stick with CASE WHEN for compatibility\n-- Both are correct, CASE is more portable"
))
entries.append(g(
"Running count with self-join",
"SELECT a.date, a.orders,\n (SELECT COUNT(*) FROM daily_stats b WHERE b.date <= a.date) as cumulative\nFROM daily_stats a\nORDER BY a.date",
"SELECT date, orders,\n COUNT(*) OVER (ORDER BY date) as cumulative,\n SUM(orders) OVER (ORDER BY date) as cumulative_orders\nFROM daily_stats\nORDER BY date\n-- Window functions replace correlated subqueries"
))
entries.append(g(
"Grouping sets for multi-level totals",
"SELECT department, SUM(salary) FROM employees GROUP BY department\nUNION ALL\nSELECT NULL, SUM(salary) FROM employees\n-- Manual subtotal/total",
"SELECT department, SUM(salary) as total_salary\nFROM employees\nGROUP BY GROUPING SETS ((department), ())\n-- GROUPING SETS: multiple groupings in one pass\n-- ROLLUP: hierarchical totals\n-- CUBE: all combinations"
))
entries.append(g(
"Conditional aggregation",
"SELECT department, COUNT(*) as active_count\nFROM employees WHERE status = 'active'\nGROUP BY department\n-- Separate query for inactive\nSELECT department, COUNT(*) as inactive_count\nFROM employees WHERE status = 'inactive'\nGROUP BY department",
"SELECT department,\n COUNT(*) FILTER (WHERE status = 'active') as active_count,\n COUNT(*) FILTER (WHERE status = 'inactive') as inactive_count,\n COUNT(*) as total\nFROM employees\nGROUP BY department\n-- FILTER clause: conditional aggregation in one pass\n-- MySQL: COUNT(CASE WHEN status = 'active' THEN 1 END)"
))
entries.append(g(
"Percentile calculation",
"SELECT * FROM salaries\nORDER BY amount\n-- Manual percentile calculation in application code",
"SELECT\n PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY amount) as median,\n PERCENTILE_CONT(0.9) WITHIN GROUP (ORDER BY amount) as p90,\n PERCENTILE_CONT(0.95) WITHIN GROUP (ORDER BY amount) as p95\nFROM salaries\n-- ORDERED-SET aggregate functions\n-- PERCENTILE_CONT: continuous interpolation\n-- PERCENTILE_DISC: nearest rank"
))
entries.append(g(
"JSON aggregation",
"-- Application collects rows into JSON array\nrows = cursor.execute(\"SELECT id, name FROM tags WHERE post_id = 42\").fetchall()\ntags = json.dumps([{\"id\": r[0], \"name\": r[1]} for r in rows])",
"SELECT json_agg(to_jsonb(t)) as tags\nFROM (SELECT id, name FROM tags WHERE post_id = 42) t\n-- Or JSON_OBJECTAGG for key-value:\nSELECT json_object_agg(id, name) as tag_map\nFROM tags WHERE post_id = 42\n-- Database builds JSON, application consumes it"
))
# =============================================================================
# VIEWS & MATERIALIZED VIEWS (4 entries)
# =============================================================================
entries.append(g(
"Complex repeated query logic",
"SELECT u.name, COUNT(o.id), SUM(o.total)\nFROM users u JOIN orders o ON o.user_id = u.id\nWHERE o.created_at >= NOW() - INTERVAL '30 days'\nGROUP BY u.id, u.name\n-- This query appears in 5 different places",
"CREATE VIEW user_monthly_stats AS\nSELECT u.id, u.name,\n COUNT(o.id) as order_count,\n SUM(o.total) as total_spent\nFROM users u\nLEFT JOIN orders o ON o.user_id = u.id\n AND o.created_at >= NOW() - INTERVAL '30 days'\nGROUP BY u.id, u.name;\n\n-- Now: SELECT * FROM user_monthly_stats;\n-- Centralized logic, single source of truth"
))
entries.append(g(
"Slow dashboard query on large tables",
"SELECT department, COUNT(*), AVG(salary), MAX(salary)\nFROM employees\nWHERE hire_date >= '2020-01-01'\nGROUP BY department\n-- Runs on every page load against 500K rows",
"CREATE MATERIALIZED VIEW dept_stats AS\nSELECT department, COUNT(*) as emp_count,\n AVG(salary) as avg_salary, MAX(salary) as max_salary\nFROM employees\nGROUP BY department;\n\nCREATE INDEX idx_dept_stats ON dept_stats(department);\n\n-- Refresh periodically:\nREFRESH MATERIALIZED VIEW CONCURRENTLY dept_stats;\n-- CONCURRENTLY: no read locks during refresh"
))
entries.append(g(
"View with no refresh schedule",
"CREATE MATERIALIZED VIEW order_summary AS\nSELECT user_id, COUNT(*), SUM(total)\nFROM orders GROUP BY user_id;\n-- Stale data after inserts",
"-- Option 1: Scheduled refresh\n-- pg_cron extension:\nSELECT cron.schedule('refresh_order_summary', '*/5 * * * *',\n 'REFRESH MATERIALIZED VIEW CONCURRENTLY order_summary');\n\n-- Option 2: Trigger-based refresh\nCREATE OR REPLACE FUNCTION refresh_order_summary()\nRETURNS TRIGGER AS $$\nBEGIN\n REFRESH MATERIALIZED VIEW CONCURRENTLY order_summary;\n RETURN NULL;\nEND;\n$$ LANGUAGE plpgsql;"
))
entries.append(g(
"View for security filtering",
"SELECT * FROM employees\n-- Application filters: WHERE department = user_department\n-- Risk: admin sees all data",
"CREATE VIEW my_department AS\nSELECT id, name, role\nFROM employees\nWHERE department = current_setting('app.department');\n\n-- Grant access to view, not table:\nGRANT SELECT ON my_department TO app_user;\nREVOKE SELECT ON employees FROM app_user;\n-- Row-level security via view"
))
# =============================================================================
# PERFORMANCE ANTI-PATTERNS (6 entries)
# =============================================================================
entries.append(g(
"SELECT in a loop (N+1)",
"user_ids = [1, 2, 3, 4, 5]\nfor uid in user_ids:\n user = db.query(f\"SELECT * FROM users WHERE id = {uid}\")",
"users = db.query(\"SELECT * FROM users WHERE id IN (?, ?, ?, ?, ?)\", user_ids)\n-- Single query with IN clause\n-- Or use a dict to map results:\nusers_by_id = {u['id']: u for u in users}"
))
entries.append(g(
"ORDER BY on non-indexed column",
"SELECT * FROM products ORDER BY popularity\n-- popularity has no index, full table scan + sort",
"CREATE INDEX idx_products_popularity ON products(popularity DESC);\nSELECT * FROM products ORDER BY popularity DESC LIMIT 20\n-- Index on ORDER BY column avoids sort step\n-- Match index sort direction to query"
))
entries.append(g(
"COUNT(*) with GROUP BY on large table",
"SELECT status, COUNT(*) FROM events GROUP BY status\n-- Full table scan on 100M+ row table",
"-- Option 1: Summary table\nCREATE TABLE event_counts (\n status VARCHAR(20) PRIMARY KEY,\n count BIGINT DEFAULT 0\n);\n-- Maintain via triggers or periodic job\n\n-- Option 2: Approximate\nSELECT status, COUNT(*) FROM TABLESAMPLE (1) events GROUP BY status\n-- TABLESAMPLE: statistical estimate"
))
entries.append(g(
"Wide row scanning",
"SELECT * FROM large_table WHERE id = 42\n-- large_table has 50 columns, only need 3",
"SELECT id, name, created_at FROM large_table WHERE id = 42\n-- Only select needed columns:\n-- Less I/O, fits in buffer cache better\n-- Enables covering index"
))
entries.append(g(
"Unnecessary subquery",
"SELECT * FROM (\n SELECT id, name, email FROM users WHERE status = 'active'\n) sub\nWHERE sub.name LIKE 'A%'\n-- Extra layer of subquery",
"SELECT id, name, email FROM users\nWHERE status = 'active' AND name LIKE 'A%'\n-- Flatten: no subquery needed\n-- Subquery adds a materialization step"
))
entries.append(g(
"Coalesce in ORDER BY",
"SELECT * FROM products ORDER BY COALESCE(sort_order, id)\n-- COALESCE prevents index use on sort_order",
"SELECT * FROM products\nORDER BY CASE WHEN sort_order IS NOT NULL THEN 0 ELSE 1 END,\n COALESCE(sort_order, id)\n-- Or: partial indexes\nCREATE INDEX idx_products_sorted ON products(sort_order) WHERE sort_order IS NOT NULL;\nCREATE INDEX idx_products_unsorted ON products(id) WHERE sort_order IS NULL;"
))
# =============================================================================
# OUTPUT & FILE WRITING
# =============================================================================
BASE.mkdir(parents=True, exist_ok=True)
output_path = BASE / "dev_finetuning_sql.json"
with open(output_path, "w") as f:
json.dump(entries, f, indent=2)
print(f"Generated {len(entries)} SQL training entries")
print(f"Output: {output_path}")
print(f"File size: {output_path.stat().st_size / 1024:.1f} KB")