Repository navigation
Expand file tree
/
Copy pathsample_dataset.sql
More file actions
217 lines (206 loc) · 10 KB
/
Copy pathsample_dataset.sql
File metadata and controls
217 lines (206 loc) · 10 KB
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
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
CREATE TABLE IF NOT EXISTS categories (
id INT AUTO_INCREMENT PRIMARY KEY,
name VARCHAR(100) NOT NULL
) ENGINE=InnoDB;
CREATE TABLE IF NOT EXISTS products (
id INT AUTO_INCREMENT PRIMARY KEY,
name VARCHAR(150) NOT NULL,
category_id INT,
price DECIMAL(10,2) NOT NULL,
stock_quantity INT DEFAULT 100,
created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
FOREIGN KEY (category_id) REFERENCES categories(id) ON DELETE SET NULL
) ENGINE=InnoDB;
CREATE TABLE IF NOT EXISTS customers (
id INT AUTO_INCREMENT PRIMARY KEY,
name VARCHAR(100) NOT NULL,
email VARCHAR(150) NOT NULL,
segment VARCHAR(50) DEFAULT 'Standard',
created_at DATETIME DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB;
CREATE TABLE IF NOT EXISTS orders (
id INT AUTO_INCREMENT PRIMARY KEY,
customer_id INT,
status VARCHAR(50) DEFAULT 'completed',
discount_amount DECIMAL(10,2) DEFAULT 0.00,
created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
updated_at DATETIME DEFAULT CURRENT_TIMESTAMP,
FOREIGN KEY (customer_id) REFERENCES customers(id) ON DELETE CASCADE
) ENGINE=InnoDB;
CREATE TABLE IF NOT EXISTS order_items (
id INT AUTO_INCREMENT PRIMARY KEY,
order_id INT NOT NULL,
product_id INT NOT NULL,
quantity INT NOT NULL,
unit_price DECIMAL(10,2) NOT NULL,
FOREIGN KEY (order_id) REFERENCES orders(id) ON DELETE CASCADE,
FOREIGN KEY (product_id) REFERENCES products(id) ON DELETE CASCADE
) ENGINE=InnoDB;
CREATE TABLE IF NOT EXISTS payments (
id INT AUTO_INCREMENT PRIMARY KEY,
order_id INT NOT NULL,
payment_method VARCHAR(50) NOT NULL,
amount DECIMAL(10,2) NOT NULL,
created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
FOREIGN KEY (order_id) REFERENCES orders(id) ON DELETE CASCADE
) ENGINE=InnoDB;
INSERT INTO categories (id, name) VALUES
(1, 'Electronics'),
(2, 'Home & Kitchen'),
(3, 'Apparel'),
(4, 'Books'),
(5, 'Sports & Fitness');
INSERT INTO products (id, name, category_id, price, stock_quantity) VALUES
(1, 'Pro Laptop 15-inch', 1, 1299.99, 50),
(2, 'Wireless Noise-Canceling Headphones', 1, 249.99, 120),
(3, 'Ultra-Wide Monitor 34-inch', 1, 599.99, 45),
(4, 'Mechanical Keyboard RGB', 1, 119.99, 150),
(5, 'Ergonomic Mouse', 1, 79.99, 200),
(6, 'Smartphone Flagship 256GB', 1, 899.99, 80),
(7, 'Smart Watch Series 5', 1, 329.99, 110),
(8, 'Espresso Machine Pro', 2, 499.99, 35),
(9, 'Cast Iron Skillet Set', 2, 89.99, 90),
(10, 'Robot Vacuum Cleaner', 2, 349.99, 60),
(11, 'Air Fryer XL 6-Quart', 2, 129.99, 85),
(12, 'Blender 1200W High-Speed', 2, 109.99, 70),
(13, 'Ceramic Knife Set', 2, 49.99, 130),
(14, 'Performance Running Shoes', 3, 139.99, 140),
(15, 'Waterproof Trail Jacket', 3, 179.99, 65),
(16, 'Classic Cotton Crewneck', 3, 34.99, 300),
(17, 'Slim-Fit Denim Jeans', 3, 69.99, 180),
(18, 'Merino Wool Sweater', 3, 89.99, 95),
(19, 'Data Engineering with Python', 4, 49.99, 150),
(20, 'Designing Data-Intensive Applications', 4, 54.99, 210),
(21, 'Cloud Architecture Blueprint', 4, 44.99, 120),
(22, 'Machine Learning Operations Handbook', 4, 59.99, 90),
(23, 'Business Analytics in Practice', 4, 39.99, 160),
(24, 'Adjustable Dumbbell Set 50lbs', 5, 299.99, 40),
(25, 'Yoga Mat Non-Slip 6mm', 5, 29.99, 250),
(26, 'Resistance Bands 5-Pack', 5, 19.99, 350),
(27, 'Cycling Indoor Smart Trainer', 5, 699.99, 25),
(28, 'Hydration Backpack 2L', 5, 45.99, 110);
INSERT INTO customers (id, name, email, segment, created_at) VALUES
(1, 'Apex Logistics', 'contact@apexlogistics.com', 'Enterprise', '2025-01-15 10:00:00'),
(2, 'Beacon Health', 'contact@beaconhealth.com', 'Enterprise', '2025-02-10 11:30:00'),
(3, 'Crestview Financial', 'contact@crestviewfinancial.com', 'Enterprise', '2025-02-28 09:15:00'),
(4, 'Delta Retail Group', 'contact@deltaretailgroup.com', 'Enterprise', '2025-03-12 14:00:00'),
(5, 'Echo Media Lab', 'contact@echomedialab.com', 'Mid-Market', '2025-03-25 16:45:00'),
(6, 'Frontier Systems', 'contact@frontiersystems.com', 'Mid-Market', '2025-04-05 08:30:00'),
(7, 'Genesis Energy', 'contact@genesisenergy.com', 'Mid-Market', '2025-04-18 12:20:00'),
(8, 'Horizon Tech', 'contact@horizontech.com', 'Mid-Market', '2025-05-02 15:10:00'),
(9, 'Insight Analytics', 'contact@insightanalytics.com', 'Mid-Market', '2025-05-19 11:00:00'),
(10, 'Jupiter Networks', 'contact@jupiternetworks.com', 'Mid-Market', '2025-06-01 13:40:00'),
(11, 'Krypton Design', 'contact@kryptondesign.com', 'SMB', '2025-06-15 10:15:00'),
(12, 'Lighthouse Capital', 'contact@lighthousecapital.com', 'SMB', '2025-07-01 14:50:00'),
(13, 'Matrix Software', 'contact@matrixsoftware.com', 'SMB', '2025-07-20 09:35:00'),
(14, 'Nexus Biotech', 'contact@nexusbiotech.com', 'SMB', '2025-08-04 16:00:00'),
(15, 'Omni Consumer Goods', 'contact@omniconsumergoods.com', 'SMB', '2025-08-22 10:45:00'),
(16, 'Pulse Digital', 'contact@pulsedigital.com', 'SMB', '2025-09-10 11:25:00'),
(17, 'Quantum Labs', 'contact@quantumlabs.com', 'SMB', '2025-09-28 15:30:00'),
(18, 'Radiant Solutions', 'contact@radiantsolutions.com', 'SMB', '2025-10-12 13:10:00'),
(19, 'Summit Partners', 'contact@summitpartners.com', 'SMB', '2025-10-30 08:50:00'),
(20, 'Titan Manufacturing', 'contact@titanmanufacturing.com', 'SMB', '2025-11-15 14:20:00'),
(21, 'Unified Commerce', 'contact@unifiedcommerce.com', 'Standard', '2025-11-28 10:00:00'),
(22, 'Vortex Labs', 'contact@vortexlabs.com', 'Standard', '2025-12-10 16:15:00'),
(23, 'Waveform Audio', 'contact@waveformaudio.com', 'Standard', '2025-12-24 11:30:00'),
(24, 'Xenon Global', 'contact@xenonglobal.com', 'Standard', '2026-01-08 09:40:00'),
(25, 'Yield Metrics', 'contact@yieldmetrics.com', 'Standard', '2026-01-22 15:05:00'),
(26, 'Zenith Dynamics', 'contact@zenithdynamics.com', 'Standard', '2026-02-05 12:50:00'),
(27, 'Alpha Core', 'contact@alphacore.com', 'Standard', '2026-02-18 10:30:00'),
(28, 'Beta Ventures', 'contact@betaventures.com', 'Standard', '2026-03-01 14:10:00');
INSERT INTO orders (id, customer_id, status, discount_amount, created_at, updated_at) VALUES
(1, 1, 'completed', 0.00, '2025-10-01 10:15:00', '2025-10-03 12:00:00'),
(2, 2, 'completed', 25.00, '2025-10-05 14:30:00', '2025-10-07 16:20:00'),
(3, 3, 'completed', 0.00, '2025-10-12 09:45:00', '2025-10-14 11:10:00'),
(4, 4, 'completed', 50.00, '2025-10-20 16:00:00', '2025-10-22 18:00:00'),
(5, 5, 'completed', 0.00, '2025-10-28 11:20:00', '2025-10-30 14:30:00'),
(6, 1, 'completed', 15.00, '2025-11-04 13:10:00', '2025-11-06 15:45:00'),
(7, 6, 'completed', 0.00, '2025-11-10 15:50:00', '2025-11-12 17:00:00'),
(8, 7, 'completed', 0.00, '2025-11-18 08:30:00', '2025-11-20 10:20:00'),
(9, 8, 'completed', 25.00, '2025-11-25 12:00:00', '2025-11-27 14:15:00'),
(10, 2, 'completed', 0.00, '2025-12-02 10:40:00', '2025-12-04 12:50:00'),
(11, 9, 'completed', 0.00, '2025-12-09 14:15:00', '2025-12-11 16:30:00'),
(12, 10, 'completed', 50.00, '2025-12-15 16:50:00', '2025-12-17 19:10:00'),
(13, 11, 'completed', 0.00, '2025-12-22 09:10:00', '2025-12-24 11:00:00'),
(14, 3, 'completed', 15.00, '2026-01-05 11:30:00', '2026-01-07 14:00:00'),
(15, 12, 'completed', 0.00, '2026-01-12 13:45:00', '2026-01-14 15:20:00'),
(16, 13, 'completed', 0.00, '2026-01-19 15:20:00', '2026-01-21 17:40:00'),
(17, 14, 'completed', 25.00, '2026-01-26 10:00:00', '2026-01-28 12:30:00'),
(18, 4, 'completed', 0.00, '2026-02-02 14:10:00', '2026-02-04 16:00:00'),
(19, 15, 'completed', 0.00, '2026-02-09 09:30:00', '2026-02-11 11:45:00'),
(20, 16, 'completed', 50.00, '2026-02-16 16:40:00', '2026-02-18 19:00:00'),
(21, 1, 'completed', 0.00, '2026-02-23 11:15:00', '2026-02-25 13:30:00'),
(22, 17, 'completed', 15.00, '2026-03-01 13:50:00', '2026-03-03 15:10:00'),
(23, 18, 'completed', 0.00, '2026-03-05 10:20:00', '2026-03-07 12:40:00'),
(24, 19, 'pending', 0.00, '2026-03-10 15:30:00', '2026-03-10 15:30:00'),
(25, 20, 'pending', 0.00, '2026-03-12 12:00:00', '2026-03-12 12:00:00'),
(26, 5, 'refunded', 0.00, '2026-02-15 14:00:00', '2026-02-18 10:00:00');
INSERT INTO order_items (order_id, product_id, quantity, unit_price) VALUES
(1, 1, 2, 1299.99),
(1, 4, 2, 119.99),
(2, 3, 1, 599.99),
(2, 5, 2, 79.99),
(3, 8, 1, 499.99),
(3, 12, 1, 109.99),
(4, 1, 3, 1299.99),
(4, 2, 3, 249.99),
(5, 6, 2, 899.99),
(6, 2, 1, 249.99),
(6, 7, 2, 329.99),
(7, 10, 1, 349.99),
(7, 11, 1, 129.99),
(8, 14, 2, 139.99),
(8, 15, 1, 179.99),
(9, 27, 1, 699.99),
(9, 24, 1, 299.99),
(10, 19, 3, 49.99),
(10, 20, 2, 54.99),
(11, 21, 2, 44.99),
(11, 22, 1, 59.99),
(12, 1, 1, 1299.99),
(12, 3, 1, 599.99),
(13, 8, 1, 499.99),
(14, 16, 4, 34.99),
(14, 17, 2, 69.99),
(15, 2, 2, 249.99),
(16, 7, 1, 329.99),
(16, 6, 1, 899.99),
(17, 24, 2, 299.99),
(18, 10, 2, 349.99),
(19, 14, 1, 139.99),
(20, 1, 2, 1299.99),
(20, 2, 2, 249.99),
(21, 3, 2, 599.99),
(22, 18, 2, 89.99),
(23, 25, 3, 29.99),
(23, 26, 2, 19.99),
(24, 11, 1, 129.99),
(25, 4, 1, 119.99),
(26, 9, 1, 89.99);
INSERT INTO payments (order_id, payment_method, amount, created_at) VALUES
(1, 'Wire Transfer', 2839.96, '2025-10-01 10:16:00'),
(2, 'Corporate Card', 734.97, '2025-10-05 14:31:00'),
(3, 'Credit Card', 609.98, '2025-10-12 09:46:00'),
(4, 'Wire Transfer', 4599.94, '2025-10-20 16:01:00'),
(5, 'Corporate Card', 1799.98, '2025-10-28 11:21:00'),
(6, 'Credit Card', 894.97, '2025-11-04 13:11:00'),
(7, 'Credit Card', 479.98, '2025-11-10 15:51:00'),
(8, 'PayPal', 459.97, '2025-11-18 08:31:00'),
(9, 'Credit Card', 974.98, '2025-11-25 12:01:00'),
(10, 'Corporate Card', 259.95, '2025-12-02 10:41:00'),
(11, 'Credit Card', 149.97, '2025-12-09 14:16:00'),
(12, 'Wire Transfer', 1849.98, '2025-12-15 16:51:00'),
(13, 'Credit Card', 499.99, '2025-12-22 09:11:00'),
(14, 'PayPal', 264.94, '2026-01-05 11:31:00'),
(15, 'Credit Card', 499.98, '2026-01-12 13:46:00'),
(16, 'Corporate Card', 1229.98, '2026-01-19 15:21:00'),
(17, 'Credit Card', 574.98, '2026-01-26 10:01:00'),
(18, 'Wire Transfer', 699.98, '2026-02-02 14:11:00'),
(19, 'Credit Card', 139.99, '2026-02-09 09:31:00'),
(20, 'Wire Transfer', 3049.96, '2026-02-16 16:41:00'),
(21, 'Credit Card', 1199.98, '2026-02-23 11:16:00'),
(22, 'PayPal', 164.98, '2026-03-01 13:51:00'),
(23, 'Credit Card', 129.95, '2026-03-05 10:21:00'),
(24, 'Credit Card', 129.99, '2026-03-10 15:31:00'),
(25, 'Credit Card', 119.99, '2026-03-12 12:01:00'),
(26, 'PayPal', 89.99, '2026-02-15 14:01:00');