Spaces:
Sleeping
Sleeping
File size: 5,032 Bytes
64ccc23 | 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 | -- 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');
|