sql-repair-env / db /fixtures.sql
ashoka0402
clean commit (removed token)
64ccc23
Raw History Blame
5.03 kB
-- db/fixtures.sql
-- =================
-- SQLite seed data for the Interactive SQL Query Repair environment.
-- Tables cover all difficulty levels.
-- This file is executed once per episode reset to produce a clean DB state.
-- ============================================================
-- EASY tasks use: users
-- ============================================================
CREATE TABLE IF NOT EXISTS users (
id INTEGER PRIMARY KEY,
name TEXT NOT NULL,
email TEXT NOT NULL UNIQUE,
age INTEGER NOT NULL,
city TEXT NOT NULL,
active INTEGER NOT NULL DEFAULT 1 -- 0 = inactive, 1 = active
);
INSERT INTO users VALUES
(1, 'Alice Sharma', 'alice@example.com', 34, 'Mumbai', 1),
(2, 'Bob Tran', 'bob@example.com', 28, 'Hanoi', 1),
(3, 'Carol Mendes', 'carol@example.com', 45, 'São Paulo', 0),
(4, 'David Kim', 'david@example.com', 31, 'Seoul', 1),
(5, 'Eva Novak', 'eva@example.com', 22, 'Prague', 1),
(6, 'Frank Osei', 'frank@example.com', 38, 'Accra', 0),
(7, 'Grace Liu', 'grace@example.com', 29, 'Beijing', 1),
(8, 'Hiro Tanaka', 'hiro@example.com', 52, 'Tokyo', 1),
(9, 'Irina Popescu', 'irina@example.com', 41, 'Bucharest', 1),
(10, 'James Okoye', 'james@example.com', 26, 'Lagos', 0);
-- ============================================================
-- MEDIUM tasks use: users + orders + products
-- ============================================================
CREATE TABLE IF NOT EXISTS orders (
id INTEGER PRIMARY KEY,
user_id INTEGER NOT NULL REFERENCES users(id),
product_id INTEGER NOT NULL,
amount REAL NOT NULL,
status TEXT NOT NULL, -- 'pending' | 'shipped' | 'delivered' | 'cancelled'
created_at TEXT NOT NULL -- ISO date string YYYY-MM-DD
);
INSERT INTO orders VALUES
(1, 1, 3, 120.00, 'delivered', '2024-01-10'),
(2, 1, 7, 340.50, 'shipped', '2024-03-22'),
(3, 2, 2, 89.99, 'delivered', '2024-02-14'),
(4, 3, 5, 250.00, 'cancelled', '2024-01-30'),
(5, 4, 1, 45.00, 'delivered', '2024-04-05'),
(6, 4, 4, 199.00, 'pending', '2024-06-01'),
(7, 5, 6, 60.00, 'delivered', '2024-05-18'),
(8, 6, 3, 120.00, 'shipped', '2024-06-10'),
(9, 7, 2, 89.99, 'delivered', '2024-03-03'),
(10, 8, 8, 500.00, 'delivered', '2024-01-25'),
(11, 9, 1, 45.00, 'cancelled', '2024-02-28'),
(12, 10,5, 250.00, 'pending', '2024-06-15'),
(13, 1, 1, 45.00, 'delivered', '2024-07-01'),
(14, 2, 8, 500.00, 'shipped', '2024-07-05'),
(15, 7, 4, 199.00, 'delivered', '2024-07-10');
CREATE TABLE IF NOT EXISTS products (
id INTEGER PRIMARY KEY,
name TEXT NOT NULL,
category TEXT NOT NULL,
price REAL NOT NULL,
stock INTEGER NOT NULL
);
INSERT INTO products VALUES
(1, 'Notebook', 'stationery', 45.00, 200),
(2, 'Wireless Mouse', 'electronics', 89.99, 85),
(3, 'Desk Lamp', 'furniture', 120.00, 60),
(4, 'USB-C Hub', 'electronics', 199.00, 40),
(5, 'Standing Desk', 'furniture', 250.00, 15),
(6, 'Pen Set', 'stationery', 60.00, 300),
(7, 'Monitor Arm', 'furniture', 340.50, 25),
(8, 'Mechanical KB', 'electronics', 500.00, 30);
-- ============================================================
-- HARD tasks use: users + orders + products + reviews + tags
-- ============================================================
CREATE TABLE IF NOT EXISTS reviews (
id INTEGER PRIMARY KEY,
user_id INTEGER NOT NULL REFERENCES users(id),
product_id INTEGER NOT NULL REFERENCES products(id),
rating INTEGER NOT NULL CHECK(rating BETWEEN 1 AND 5),
body TEXT,
created_at TEXT NOT NULL
);
INSERT INTO reviews VALUES
(1, 1, 3, 5, 'Great lamp!', '2024-02-01'),
(2, 2, 2, 4, 'Good mouse.', '2024-03-01'),
(3, 4, 1, 3, 'OK notebook.', '2024-04-10'),
(4, 5, 6, 5, 'Love the pens.', '2024-05-20'),
(5, 7, 2, 4, 'Works well.', '2024-03-15'),
(6, 8, 8, 5, 'Best keyboard ever.', '2024-02-10'),
(7, 9, 1, 2, 'Flimsy.', '2024-03-05'),
(8, 1, 7, 4, 'Solid arm.', '2024-04-01'),
(9, 3, 5, 1, 'Arrived damaged.', '2024-02-20'),
(10, 10,4, 3, 'Decent hub.', '2024-06-18');
CREATE TABLE IF NOT EXISTS tags (
id INTEGER PRIMARY KEY,
product_id INTEGER NOT NULL REFERENCES products(id),
tag TEXT NOT NULL
);
INSERT INTO tags VALUES
(1, 1, 'office'),
(2, 1, 'writing'),
(3, 2, 'office'),
(4, 2, 'computer'),
(5, 3, 'office'),
(6, 3, 'lighting'),
(7, 4, 'computer'),
(8, 4, 'accessories'),
(9, 5, 'office'),
(10, 5, 'ergonomic'),
(11, 6, 'writing'),
(12, 6, 'office'),
(13, 7, 'computer'),
(14, 7, 'ergonomic'),
(15, 8, 'computer'),
(16, 8, 'mechanical');