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');