DMLiDDL: DML2.sql

File DML2.sql, 55.6 KB (added by 231004, 4 days ago)
Line 
1SET statement_timeout = 0;
2
3-- =========================================================
4-- FACTORIES
5-- =========================================================
6
7INSERT INTO car_dealership.factory (id, name)
8VALUES (1, 'NovaForge'),
9 (2, 'IronAxis'),
10 (3, 'VelocityWorks'),
11 (4, 'ApexDrive'),
12 (5, 'TitanAssembly'),
13 (6, 'QuantumMotors'),
14 (7, 'BlueCore Industries'),
15 (8, 'FusionLine'),
16 (9, 'PrimeTorque'),
17 (10, 'Vertex Automotive');
18
19
20-- =========================================================
21-- BRANDS
22-- =========================================================
23
24INSERT INTO car_dealership.brand (id, brand)
25VALUES (1, 'BMW'),
26 (2, 'Mercedes-Benz'),
27 (3, 'Audi'),
28 (4, 'Volkswagen'),
29 (5, 'Porsche'),
30 (6, 'Peugeot'),
31 (7, 'Renault'),
32 (8, 'Škoda'),
33 (9, 'Volvo'),
34 (10, 'Toyota'),
35 (11, 'Honda'),
36 (12, 'Nissan'),
37 (13, 'Mazda'),
38 (14, 'Subaru'),
39 (15, 'Mitsubishi'),
40 (16, 'Suzuki'),
41 (17, 'Geely'),
42 (18, 'BYD'),
43 (19, 'Cherry'),
44 (20, 'GWM');
45
46
47-- =========================================================
48-- ENGINE TYPES
49-- =========================================================
50
51INSERT INTO car_dealership.enginetype
52VALUES (1, 'Petrol'),
53 (2, 'Diesel'),
54 (3, 'Hybrid'),
55 (4, 'Electric');
56
57
58-- =========================================================
59-- VEHICLE TYPES
60-- =========================================================
61
62INSERT INTO car_dealership.vehicletype (id, type)
63VALUES (1, 'Sedan'),
64 (2, 'Hatchback'),
65 (3, 'SUV'),
66 (4, 'Coupe'),
67 (5, 'Convertible'),
68 (6, 'Wagon'),
69 (7, 'Pickup'),
70 (8, 'Van'),
71 (9, 'Minivan'),
72 (10, 'Sports Car'),
73 (11, 'Crossover'),
74 (12, 'Electric'),
75 (13, 'Hybrid'),
76 (14, 'Off-Road');
77
78
79-- =========================================================
80-- VEHICLE STATUSES
81-- =========================================================
82
83INSERT INTO car_dealership.status
84VALUES (1, 'In Stock'),
85 (2, 'Ordered'),
86 (3, 'In Production'),
87 (4, 'Reserved'),
88 (5, 'Sold'),
89 (6, 'In Transit');
90
91
92-- =========================================================
93-- EQUIPMENT TYPES
94-- =========================================================
95
96INSERT INTO car_dealership.equipmenttype (id, name)
97VALUES (1, 'Safety'),
98 (2, 'Comfort'),
99 (3, 'Infotainment'),
100 (4, 'Performance'),
101 (5, 'Driver Assistance'),
102 (6, 'Security');
103
104
105-- =========================================================
106-- MODELS
107-- =========================================================
108
109INSERT INTO car_dealership.model (id, model, brand_id, year)
110VALUES (1, '3 Series', 1, 1995),
111 (2, '3 Series', 1, 2000),
112 (3, '3 Series', 1, 2005),
113 (4, '3 Series', 1, 2012),
114 (5, '3 Series', 1, 2019),
115 (6, '3 Series', 1, 2023),
116 (7, '5 Series', 1, 1995),
117 (8, '5 Series', 1, 2004),
118 (9, '5 Series', 1, 2010),
119 (10, '5 Series', 1, 2017),
120 (11, '5 Series', 1, 2024),
121 (12, 'X3', 1, 2004),
122 (13, 'X3', 1, 2011),
123 (14, 'X3', 1, 2018),
124 (15, 'X3', 1, 2024),
125 (16, 'X5', 1, 1999),
126 (17, 'X5', 1, 2007),
127 (18, 'X5', 1, 2014),
128 (19, 'X5', 1, 2023),
129
130 (20, 'A-Class', 2, 1997),
131 (21, 'A-Class', 2, 2005),
132 (22, 'A-Class', 2, 2013),
133 (23, 'A-Class', 2, 2019),
134 (24, 'C-Class', 2, 1995),
135 (25, 'C-Class', 2, 2001),
136 (26, 'C-Class', 2, 2008),
137 (27, 'C-Class', 2, 2015),
138 (28, 'C-Class', 2, 2022),
139 (29, 'E-Class', 2, 1995),
140 (30, 'E-Class', 2, 2003),
141 (31, 'E-Class', 2, 2010),
142 (32, 'E-Class', 2, 2017),
143 (33, 'E-Class', 2, 2024),
144 (34, 'GLE', 2, 2016),
145 (35, 'GLE', 2, 2020),
146 (36, 'GLE', 2, 2024),
147
148 (37, 'A3', 3, 1997),
149 (38, 'A3', 3, 2004),
150 (39, 'A3', 3, 2013),
151 (40, 'A3', 3, 2021),
152 (41, 'A4', 3, 1995),
153 (42, 'A4', 3, 2001),
154 (43, 'A4', 3, 2008),
155 (44, 'A4', 3, 2016),
156 (45, 'A4', 3, 2024),
157 (46, 'A6', 3, 1995),
158 (47, 'A6', 3, 2005),
159 (48, 'A6', 3, 2012),
160 (49, 'A6', 3, 2019),
161 (50, 'Q5', 3, 2009),
162 (51, 'Q5', 3, 2017),
163 (52, 'Q5', 3, 2024),
164
165 (53, 'Golf', 4, 1995),
166 (54, 'Golf', 4, 2004),
167 (55, 'Golf', 4, 2009),
168 (56, 'Golf', 4, 2013),
169 (57, 'Golf', 4, 2020),
170 (58, 'Passat', 4, 1997),
171 (59, 'Passat', 4, 2006),
172 (60, 'Passat', 4, 2015),
173 (61, 'Passat', 4, 2024),
174 (62, 'Tiguan', 4, 2008),
175 (63, 'Tiguan', 4, 2016),
176 (64, 'Tiguan', 4, 2024),
177 (65, 'Polo', 4, 1995),
178 (66, 'Polo', 4, 2002),
179 (67, 'Polo', 4, 2010),
180 (68, 'Polo', 4, 2018),
181
182 (69, '911', 5, 1995),
183 (70, '911', 5, 2005),
184 (71, '911', 5, 2012),
185 (72, '911', 5, 2019),
186 (73, 'Cayenne', 5, 2003),
187 (74, 'Cayenne', 5, 2011),
188 (75, 'Cayenne', 5, 2018),
189 (76, 'Macan', 5, 2015),
190 (77, 'Macan', 5, 2022),
191 (78, 'Panamera', 5, 2010),
192 (79, 'Panamera', 5, 2017),
193 (80, 'Panamera', 5, 2024),
194
195 (81, '206', 6, 1998),
196 (82, '206', 6, 2003),
197 (83, '207', 6, 2006),
198 (84, '208', 6, 2012),
199 (85, '208', 6, 2020),
200 (86, '308', 6, 2008),
201 (87, '308', 6, 2014),
202 (88, '308', 6, 2022),
203 (89, '3008', 6, 2009),
204 (90, '3008', 6, 2017),
205 (91, '3008', 6, 2024),
206 (92, '508', 6, 2011),
207 (93, '508', 6, 2019),
208
209 (94, 'Clio', 7, 1998),
210 (95, 'Clio', 7, 2006),
211 (96, 'Clio', 7, 2013),
212 (97, 'Clio', 7, 2020),
213 (98, 'Megane', 7, 1996),
214 (99, 'Megane', 7, 2003),
215 (100, 'Megane', 7, 2009),
216 (101, 'Megane', 7, 2016),
217 (102, 'Captur', 7, 2013),
218 (103, 'Captur', 7, 2020),
219 (104, 'Kadjar', 7, 2015),
220 (105, 'Kadjar', 7, 2022),
221
222 (106, 'Fabia', 8, 1999),
223 (107, 'Fabia', 8, 2007),
224 (108, 'Fabia', 8, 2015),
225 (109, 'Fabia', 8, 2022),
226 (110, 'Octavia', 8, 1997),
227 (111, 'Octavia', 8, 2004),
228 (112, 'Octavia', 8, 2013),
229 (113, 'Octavia', 8, 2021),
230 (114, 'Superb', 8, 2002),
231 (115, 'Superb', 8, 2009),
232 (116, 'Superb', 8, 2016),
233 (117, 'Superb', 8, 2024),
234 (118, 'Kodiaq', 8, 2017),
235 (119, 'Kodiaq', 8, 2024),
236
237 (120, 'S60', 9, 2001),
238 (121, 'S60', 9, 2011),
239 (122, 'S60', 9, 2019),
240 (123, 'S90', 9, 1997),
241 (124, 'S90', 9, 2017),
242 (125, 'S90', 9, 2024),
243 (126, 'XC40', 9, 2018),
244 (127, 'XC40', 9, 2023),
245 (128, 'XC60', 9, 2009),
246 (129, 'XC60', 9, 2018),
247 (130, 'XC60', 9, 2024),
248
249 (131, 'Corolla', 10, 1995),
250 (132, 'Corolla', 10, 2002),
251 (133, 'Corolla', 10, 2007),
252 (134, 'Corolla', 10, 2014),
253 (135, 'Corolla', 10, 2019),
254 (136, 'Camry', 10, 1997),
255 (137, 'Camry', 10, 2007),
256 (138, 'Camry', 10, 2012),
257 (139, 'Camry', 10, 2018),
258 (140, 'RAV4', 10, 1996),
259 (141, 'RAV4', 10, 2006),
260 (142, 'RAV4', 10, 2013),
261 (143, 'RAV4', 10, 2019),
262 (144, 'Yaris', 10, 1999),
263 (145, 'Yaris', 10, 2006),
264 (146, 'Yaris', 10, 2012),
265 (147, 'Yaris', 10, 2021),
266
267 (148, 'Civic', 11, 1995),
268 (149, 'Civic', 11, 2001),
269 (150, 'Civic', 11, 2006),
270 (151, 'Civic', 11, 2012),
271 (152, 'Civic', 11, 2022),
272 (153, 'Accord', 11, 1998),
273 (154, 'Accord', 11, 2004),
274 (155, 'Accord', 11, 2013),
275 (156, 'Accord', 11, 2018),
276 (157, 'CR-V', 11, 1997),
277 (158, 'CR-V', 11, 2007),
278 (159, 'CR-V', 11, 2013),
279 (160, 'CR-V', 11, 2023),
280 (161, 'Jazz', 11, 2002),
281 (162, 'Jazz', 11, 2009),
282 (163, 'Jazz', 11, 2015),
283 (164, 'Jazz', 11, 2020),
284
285 (165, 'Micra', 12, 1995),
286 (166, 'Micra', 12, 2003),
287 (167, 'Micra', 12, 2010),
288 (168, 'Micra', 12, 2017),
289 (169, 'Qashqai', 12, 2007),
290 (170, 'Qashqai', 12, 2014),
291 (171, 'Qashqai', 12, 2021),
292 (172, 'X-Trail', 12, 2001),
293 (173, 'X-Trail', 12, 2008),
294 (174, 'X-Trail', 12, 2014),
295 (175, 'X-Trail', 12, 2022),
296 (176, 'Juke', 12, 2010),
297 (177, 'Juke', 12, 2020),
298
299 (178, 'Mazda3', 13, 2004),
300 (179, 'Mazda3', 13, 2009),
301 (180, 'Mazda3', 13, 2014),
302 (181, 'Mazda3', 13, 2019),
303 (182, 'Mazda6', 13, 2002),
304 (183, 'Mazda6', 13, 2008),
305 (184, 'Mazda6', 13, 2013),
306 (185, 'Mazda6', 13, 2018),
307 (186, 'CX-5', 13, 2012),
308 (187, 'CX-5', 13, 2017),
309 (188, 'CX-5', 13, 2022),
310 (189, 'CX-30', 13, 2019),
311 (190, 'CX-30', 13, 2024),
312
313 (191, 'Impreza', 14, 1995),
314 (192, 'Impreza', 14, 2001),
315 (193, 'Impreza', 14, 2008),
316 (194, 'Impreza', 14, 2017),
317 (195, 'Outback', 14, 1996),
318 (196, 'Outback', 14, 2004),
319 (197, 'Outback', 14, 2015),
320 (198, 'Outback', 14, 2021),
321 (199, 'Forester', 14, 1997),
322 (200, 'Forester', 14, 2008),
323 (201, 'Forester', 14, 2014),
324 (202, 'Forester', 14, 2019),
325 (203, 'BRZ', 14, 2012),
326 (204, 'BRZ', 14, 2022),
327
328 (205, 'Lancer', 15, 1995),
329 (206, 'Lancer', 15, 2004),
330 (207, 'Lancer', 15, 2008),
331 (208, 'Outlander', 15, 2003),
332 (209, 'Outlander', 15, 2013),
333 (210, 'Outlander', 15, 2022),
334 (211, 'ASX', 15, 2010),
335 (212, 'ASX', 15, 2017),
336 (213, 'ASX', 15, 2023),
337 (214, 'Eclipse Cross', 15, 2018),
338 (215, 'Eclipse Cross', 15, 2022),
339
340 (216, 'Swift', 16, 1995),
341 (217, 'Swift', 16, 2005),
342 (218, 'Swift', 16, 2011),
343 (219, 'Swift', 16, 2017),
344 (220, 'Swift', 16, 2024),
345 (221, 'Vitara', 16, 1995),
346 (222, 'Vitara', 16, 2006),
347 (223, 'Vitara', 16, 2015),
348 (224, 'Vitara', 16, 2022),
349 (225, 'SX4', 16, 2006),
350 (226, 'SX4', 16, 2014),
351 (227, 'SX4', 16, 2021),
352 (228, 'Ignis', 16, 2017),
353 (229, 'Ignis', 16, 2020),
354
355 (230, 'Emgrand', 17, 2009),
356 (231, 'Emgrand', 17, 2016),
357 (232, 'Emgrand', 17, 2022),
358 (233, 'Coolray', 17, 2019),
359 (234, 'Coolray', 17, 2023),
360 (235, 'Atlas', 17, 2017),
361 (236, 'Atlas', 17, 2022),
362 (237, 'Tugella', 17, 2020),
363 (238, 'Tugella', 17, 2024),
364
365 (239, 'Han', 18, 2020),
366 (240, 'Han', 18, 2023),
367 (241, 'Tang', 18, 2018),
368 (242, 'Tang', 18, 2022),
369 (243, 'Seal', 18, 2022),
370 (244, 'Seal', 18, 2025),
371 (245, 'Dolphin', 18, 2021),
372 (246, 'Dolphin', 18, 2024),
373
374 (247, 'Tiggo 4', 19, 2014),
375 (248, 'Tiggo 4', 19, 2021),
376 (249, 'Tiggo 7', 19, 2016),
377 (250, 'Tiggo 7', 19, 2022),
378 (251, 'Tiggo 8', 19, 2018),
379 (252, 'Tiggo 8', 19, 2023),
380 (253, 'Arrizo 5', 19, 2014),
381 (254, 'Arrizo 5', 19, 2021),
382
383 (255, 'Haval H6', 20, 2011),
384 (256, 'Haval H6', 20, 2017),
385 (257, 'Haval H6', 20, 2021),
386 (258, 'Haval Jolion', 20, 2021),
387 (259, 'Haval Jolion', 20, 2024),
388 (260, 'Poer', 20, 2019),
389 (261, 'Poer', 20, 2023),
390 (262, 'Tank 300', 20, 2021),
391 (263, 'Tank 300', 20, 2024);
392
393
394-- =========================================================
395-- CUSTOMERS
396-- =========================================================
397
398WITH first_names AS MATERIALIZED (SELECT val, row_number() OVER () rn
399 FROM unnest(ARRAY [
400 'John','Emma','Liam','Olivia','Noah','Ava','Ethan','Sophia',
401 'Mia','Lucas','Isabella','Mason','Amelia','Logan','Harper',
402 'James','Evelyn','Benjamin','Ella','Alexander',
403 'Daniel','David','Elena','Ivana','Stefan','Marko','Petar',
404 'Ana','Marija','Filip','Nikola','Sara','Teodora','Kristijan'
405 ]) AS t(val)),
406 last_names AS MATERIALIZED (SELECT val, row_number() OVER () rn
407 FROM unnest(ARRAY [
408 'Smith','Brown','Johnson','Wilson','Taylor','Lee','Martin','Anderson',
409 'Thomas','Moore','Jackson','White','Harris','Clark','Lewis',
410 'Walker','Hall','Allen','Young','King',
411 'Petrovski','Stojanov','Trajkovski','Nikolov','Jovanov',
412 'Iliev','Ristov','Georgiev','Kostov','Angelov','Mitrevski'
413 ]) AS t(val)),
414 fn_cnt AS MATERIALIZED (SELECT count(*) c
415 FROM first_names),
416 ln_cnt AS MATERIALIZED (SELECT count(*) c
417 FROM last_names)
418
419INSERT
420INTO car_dealership.customer
421 (id, first_name, last_name, email, phone, created_at)
422SELECT i,
423 fn.val,
424 ln.val,
425 lower(
426 fn.val || '.' || ln.val || i || '@' ||
427 (ARRAY ['gmail','yahoo','outlook'])[(i % 3) + 1] || '.com'
428 ),
429 '+3897' || (i % 8) || lpad((i % 1000000)::text, 6, '0'),
430 CURRENT_DATE - (i % 5000)
431FROM generate_series(1, 1000000) i
432 JOIN first_names fn
433 ON fn.rn = (i % (SELECT c FROM fn_cnt)) + 1
434 JOIN last_names ln
435 ON ln.rn = (i % (SELECT c FROM ln_cnt)) + 1;
436
437
438-- =========================================================
439-- CUSTOMER ADDRESSES
440-- =========================================================
441
442WITH cities AS MATERIALIZED (SELECT *
443 FROM (VALUES (0, 'Skopje', 1000),
444 (1, 'Bitola', 7000),
445 (2, 'Kumanovo', 1300),
446 (3, 'Prilep', 7500),
447 (4, 'Tetovo', 1200),
448 (5, 'Ohrid', 6000),
449 (6, 'Veles', 1400),
450 (7, 'Stip', 2000),
451 (8, 'Strumica', 2400),
452 (9, 'Kavadarci', 1430),
453 (10, 'Gostivar', 1230),
454 (11, 'Kocani', 2300),
455 (12, 'Gevgelija', 1480),
456 (13, 'Debar', 1250),
457 (14, 'Kriva Palanka', 1330)) AS t(idx, city, postal_code)),
458 streets AS MATERIALIZED (SELECT *
459 FROM (VALUES (0, 'Partizanska'),
460 (1, 'Ilindenska'),
461 (2, 'Goce Delchev'),
462 (3, 'Dame Gruev'),
463 (4, 'Marsal Tito'),
464 (5, 'Boris Trajkovski'),
465 (6, 'Jane Sandanski'),
466 (7, 'Nikola Karev'),
467 (8, '11 Oktomvri'),
468 (9, 'Dimitar Vlahov'),
469 (10, 'Makedonija'),
470 (11, 'Vasil Glavinov'),
471 (12, 'Kuzman Josifovski Pitu'),
472 (13, 'Mirche Acev'),
473 (14, 'Pitu Guli'),
474 (15, 'Blagoja Toska'),
475 (16, 'Ruzveltova'),
476 (17, 'Orce Nikolov'),
477 (18, 'Vodno'),
478 (19, 'Pelagonija')) AS t(idx, street))
479
480INSERT
481INTO car_dealership.address
482(id, country, city, postal_code, street,
483 building_number, entry_number, apartment_number,
484 customer_id, factory_id)
485SELECT i,
486 'North Macedonia',
487 c.city,
488 c.postal_code,
489 s.street,
490 (i % 200) + 1,
491 (i % 10) + 1,
492 (i % 50) + 1,
493 i,
494 NULL
495FROM generate_series(1, 1000000) i
496 JOIN cities c ON c.idx = (i % 15)
497 JOIN streets s ON s.idx = (i % 20);
498
499
500-- =========================================================
501-- FACTORY ADDRESSES
502-- =========================================================
503
504WITH cities AS MATERIALIZED (SELECT *
505 FROM (VALUES (0, 'Skopje', 1000),
506 (1, 'Bitola', 7000),
507 (2, 'Kumanovo', 1300),
508 (3, 'Prilep', 7500),
509 (4, 'Tetovo', 1200),
510 (5, 'Ohrid', 6000),
511 (6, 'Veles', 1400),
512 (7, 'Stip', 2000),
513 (8, 'Strumica', 2400),
514 (9, 'Kavadarci', 1430),
515 (10, 'Gostivar', 1230),
516 (11, 'Kocani', 2300),
517 (12, 'Gevgelija', 1480),
518 (13, 'Debar', 1250),
519 (14, 'Kriva Palanka', 1330)) AS t(idx, city, postal_code)),
520 streets AS MATERIALIZED (SELECT *
521 FROM (VALUES (0, 'Partizanska'),
522 (1, 'Ilindenska'),
523 (2, 'Goce Delchev'),
524 (3, 'Dame Gruev'),
525 (4, 'Marsal Tito'),
526 (5, 'Boris Trajkovski'),
527 (6, 'Jane Sandanski'),
528 (7, 'Nikola Karev'),
529 (8, '11 Oktomvri'),
530 (9, 'Dimitar Vlahov'),
531 (10, 'Makedonija'),
532 (11, 'Vasil Glavinov'),
533 (12, 'Kuzman Josifovski Pitu'),
534 (13, 'Mirche Acev'),
535 (14, 'Pitu Guli'),
536 (15, 'Blagoja Toska'),
537 (16, 'Ruzveltova'),
538 (17, 'Orce Nikolov'),
539 (18, 'Vodno'),
540 (19, 'Pelagonija')) AS t(idx, street))
541
542INSERT
543INTO car_dealership.address
544(id, country, city, postal_code, street,
545 building_number, entry_number, apartment_number,
546 customer_id, factory_id)
547SELECT 1000000 + i,
548 'North Macedonia',
549 c.city,
550 c.postal_code,
551 s.street,
552 (i % 200) + 1,
553 NULL,
554 NULL,
555 NULL,
556 i
557FROM generate_series(1, 10) i
558 JOIN cities c ON c.idx = (i % 15)
559 JOIN streets s ON s.idx = (i % 20);
560
561
562-- =========================================================
563-- EQUIPMENT PACKAGES
564-- =========================================================
565
566INSERT INTO car_dealership.equipmentpackage
567 (id, name, price, description, is_default, equipment_type_id)
568VALUES (1, 'Base', 0,
569 'Standard equipment included in every vehicle', true, 1),
570
571 (2, 'Winter', 1200,
572 'Elements that support harsh and cold winter weather, heating, tires', false, 2),
573
574 (3, 'Sport', 2500,
575 'Performance upgrades including suspension and exhaust enhancements', false, 4),
576
577 (4, 'Luxury', 4000,
578 'Premium materials, leather interior, and high-end comfort features', false, 2),
579
580 (5, 'Safety Plus', 1800,
581 'Advanced driver assistance and safety systems', false, 5),
582
583 (6, 'Tech Pack', 2200,
584 'Infotainment, navigation, and smart connectivity features', false, 3),
585
586 (7, 'Comfort Pack', 2000,
587 'Heated seats, climate control upgrades, and interior comfort enhancements', false, 2);
588
589
590INSERT INTO car_dealership.packageelement
591 (id, package_id, name, price)
592VALUES (1, 1, 'Air Conditioning', 0),
593 (2, 1, 'ABS Braking System', 0),
594 (3, 1, 'Front Airbags', 0),
595 (4, 1, 'Power Steering', 0),
596
597 (5, 2, 'Heated Front Seats', 300),
598 (6, 2, 'Heated Steering Wheel', 250),
599 (7, 2, 'Winter Tires Set', 700),
600 (8, 2, 'Windshield Defrost System', 200),
601
602 (9, 3, 'Sport Suspension', 900),
603 (10, 3, 'Performance Exhaust System', 1200),
604 (11, 3, 'Sport Steering Mode', 400),
605 (12, 3, 'Paddle Shifters', 350),
606
607 (13, 4, 'Leather Interior', 1500),
608 (14, 4, 'Premium Sound System', 1200),
609 (15, 4, 'Ambient Interior Lighting', 600),
610 (16, 4, 'Panoramic Sunroof', 1800),
611
612 (17, 5, 'Lane Assist System', 800),
613 (18, 5, 'Adaptive Cruise Control', 1000),
614 (19, 5, 'Blind Spot Monitoring', 700),
615 (20, 5, 'Automatic Emergency Braking', 900),
616
617 (21, 6, 'Digital Dashboard', 900),
618 (22, 6, 'Built-in Navigation System', 600),
619 (23, 6, 'Wireless Charging', 400),
620 (24, 6, 'Smartphone Integration', 500),
621
622 (25, 7, 'Dual Zone Climate Control', 500),
623 (26, 7, 'Massage Seats', 1100),
624 (27, 7, 'Rear Seat Heating', 350),
625 (28, 7, 'Electric Seat Adjustment', 450);
626
627
628-- =========================================================
629-- ENGINES
630-- =========================================================
631
632WITH engine_types AS MATERIALIZED (SELECT MAX(CASE WHEN type = 'Petrol' THEN id END) AS petrol_id,
633 MAX(CASE WHEN type = 'Diesel' THEN id END) AS diesel_id,
634 MAX(CASE WHEN type = 'Hybrid' THEN id END) AS hybrid_id,
635 MAX(CASE WHEN type = 'Electric' THEN id END) AS electric_id
636 FROM car_dealership.enginetype)
637
638INSERT
639INTO car_dealership.engine
640 (enginenumber, horsepower, enginetypeid)
641SELECT UPPER(
642 CASE
643 WHEN i % 100 < 60 THEN 'PTR'
644 WHEN i % 100 < 88 THEN 'DSL'
645 WHEN i % 100 < 96 THEN 'HYB'
646 ELSE 'ELC'
647 END
648 ||
649 CASE
650 WHEN i % 100 < 60 THEN (1000 + (i * 17 % 4001))::text
651 WHEN i % 100 < 88 THEN (1600 + (i * 13 % 1901))::text
652 WHEN i % 100 < 96 THEN (1500 + (i * 11 % 1501))::text
653 ELSE '0000'
654 END
655 || CHR(65 + (i % 26))
656 || lpad(i::text, 7, '0')
657 ),
658
659 CASE
660 WHEN i % 100 < 60 THEN 60 + (i * 13 % 441)
661 WHEN i % 100 < 88 THEN 60 + (i * 11 % 341)
662 WHEN i % 100 < 96 THEN 80 + (i * 9 % 321)
663 ELSE 100 + (i * 7 % 501)
664 END::int,
665
666 CASE
667 WHEN i % 100 < 60 THEN et.petrol_id
668 WHEN i % 100 < 88 THEN et.diesel_id
669 WHEN i % 100 < 96 THEN et.hybrid_id
670 ELSE et.electric_id
671 END
672
673FROM generate_series(1, 10000000) i
674 CROSS JOIN engine_types et;
675
676
677-- =========================================================
678-- VEHICLES
679-- =========================================================
680
681WITH models AS MATERIALIZED (SELECT id,
682 brand_id,
683 model,
684 year,
685 row_number() OVER (ORDER BY id) AS rn
686 FROM car_dealership.model),
687
688 statuses AS MATERIALIZED (SELECT id,
689 row_number() OVER (ORDER BY id) AS rn
690 FROM car_dealership.status),
691
692 vehicle_types AS MATERIALIZED (SELECT id, type
693 FROM car_dealership.vehicletype),
694
695 engines AS MATERIALIZED (SELECT enginenumber,
696 CASE
697 WHEN enginenumber LIKE 'PTR%' THEN 'P'
698 WHEN enginenumber LIKE 'DSL%' THEN 'D'
699 WHEN enginenumber LIKE 'HYB%' THEN 'H'
700 ELSE 'E'
701 END AS engine_code,
702 row_number() OVER (ORDER BY enginenumber) AS rn
703 FROM car_dealership.engine),
704
705 model_cnt AS MATERIALIZED (SELECT count(*) c
706 FROM models),
707
708 status_cnt AS MATERIALIZED (SELECT count(*) c
709 FROM statuses),
710
711 brand_wmi AS MATERIALIZED (SELECT *
712 FROM (VALUES (1, 'WBA'),
713 (2, 'WDB'),
714 (3, 'WAU'),
715 (4, 'WVW'),
716 (5, 'WP0'),
717 (6, 'VF3'),
718 (7, 'VF1'),
719 (8, 'TMB'),
720 (9, 'YV1'),
721 (10, 'JT2'),
722 (11, 'JHM'),
723 (12, 'JN1'),
724 (13, 'JM1'),
725 (14, 'JF1'),
726 (15, 'JA3'),
727 (16, 'JS2'),
728 (17, 'LGX'),
729 (18, 'LBV'),
730 (19, 'LVV'),
731 (20, 'LHG')) AS t(brand_id, wmi)),
732
733 model_specs AS MATERIALIZED (SELECT *
734 FROM (VALUES ('3 Series', 'Sedan', 25000, 55000),
735 ('5 Series', 'Sedan', 35000, 75000),
736 ('X3', 'SUV', 35000, 70000),
737 ('X5', 'SUV', 50000, 95000),
738 ('A-Class', 'Hatchback', 22000, 45000),
739 ('C-Class', 'Sedan', 28000, 60000),
740 ('E-Class', 'Sedan', 40000, 80000),
741 ('GLE', 'SUV', 55000, 95000),
742 ('A3', 'Hatchback', 22000, 45000),
743 ('A4', 'Sedan', 28000, 58000),
744 ('A6', 'Sedan', 40000, 75000),
745 ('Q5', 'SUV', 40000, 72000),
746 ('Golf', 'Hatchback', 18000, 40000),
747 ('Passat', 'Sedan', 25000, 50000),
748 ('Tiguan', 'SUV', 28000, 52000),
749 ('Polo', 'Hatchback', 13000, 28000),
750 ('911', 'Sports Car', 80000, 180000),
751 ('Cayenne', 'SUV', 70000, 150000),
752 ('Macan', 'SUV', 50000, 90000),
753 ('Panamera', 'Sedan', 85000,
754 160000)) AS t(model_name, vehicle_type, price_min, price_max)),
755
756 year_codes AS MATERIALIZED (SELECT *
757 FROM (VALUES (1995, 'S'),
758 (1996, 'T'),
759 (1997, 'V'),
760 (1998, 'W'),
761 (1999, 'X'),
762 (2000, 'Y'),
763 (2001, '1'),
764 (2002, '2'),
765 (2003, '3'),
766 (2004, '4'),
767 (2005, '5'),
768 (2006, '6'),
769 (2007, '7'),
770 (2008, '8'),
771 (2009, '9'),
772 (2010, 'A'),
773 (2011, 'B'),
774 (2012, 'C'),
775 (2013, 'D'),
776 (2014, 'E'),
777 (2015, 'F'),
778 (2016, 'G'),
779 (2017, 'H'),
780 (2018, 'J'),
781 (2019, 'K'),
782 (2020, 'L'),
783 (2021, 'M'),
784 (2022, 'N'),
785 (2023, 'P'),
786 (2024, 'R'),
787 (2025, 'S')) AS t(yr, code)),
788
789 model_full AS MATERIALIZED (SELECT m.rn,
790 m.id AS model_id,
791 m.year,
792 m.brand_id,
793 bw.wmi,
794 yc.code AS year_code,
795 vt.id AS vehicle_type_id,
796
797 CASE vt.type
798 WHEN 'Sedan' THEN 'AA'
799 WHEN 'Hatchback' THEN 'BB'
800 WHEN 'SUV' THEN 'CC'
801 WHEN 'Coupe' THEN 'DD'
802 WHEN 'Convertible' THEN 'EE'
803 WHEN 'Wagon' THEN 'FF'
804 WHEN 'Pickup' THEN 'GG'
805 WHEN 'Sports Car' THEN 'HH'
806 WHEN 'Crossover' THEN 'JJ'
807 ELSE 'XX'
808 END AS body_code,
809
810 COALESCE(ms.price_min, 15000) AS price_min,
811 COALESCE(ms.price_max, 50000) AS price_max,
812
813 (
814 COALESCE(ms.price_min, 15000)
815 +
816 (
817 (COALESCE(ms.price_max, 50000) -
818 COALESCE(ms.price_min, 15000))
819 *
820 (m.year - 1995)::numeric / 31
821 )
822 )::numeric(10, 2) AS base_price
823
824 FROM models m
825 LEFT JOIN model_specs ms
826 ON ms.model_name = m.model
827 JOIN vehicle_types vt
828 ON vt.type = COALESCE(ms.vehicle_type, 'Sedan')
829 JOIN year_codes yc
830 ON yc.yr = m.year
831 JOIN brand_wmi bw
832 ON bw.brand_id = m.brand_id)
833
834INSERT
835INTO car_dealership.vehicle
836(vin, model_id, status_id, vehicle_type_id,
837 color, price, production_year, engine_number)
838SELECT mf.wmi
839 || mf.body_code
840 || e.engine_code
841 || lpad((mf.model_id % 1000)::text, 3, '0')
842 || mf.year_code
843 || CHR(65 + ((i - 1) / 1000000 % 26))
844 || lpad(((i - 1) % 1000000)::text, 6, '0'),
845
846 mf.model_id,
847 s.id,
848 mf.vehicle_type_id,
849
850 (ARRAY ['Black','White','Grey','Silver','Red','Blue'])[(i % 6) + 1],
851
852 mf.base_price,
853 mf.year,
854 e.enginenumber
855
856FROM generate_series(1, 10000000) i
857 JOIN model_full mf
858 ON mf.rn = (i % (SELECT c FROM model_cnt)) + 1
859 JOIN statuses s
860 ON s.rn = (i % (SELECT c FROM status_cnt)) + 1
861 JOIN engines e
862 ON e.rn = i;
863
864
865-- =========================================================
866-- EMPLOYEES: MANAGERS FIRST
867-- =========================================================
868
869WITH first_names AS MATERIALIZED (SELECT val, row_number() OVER () rn
870 FROM unnest(ARRAY [
871 'Anna','Mark','Ivan','Christian','Nicole','Andrew','Peter','Matthew',
872 'Richard','Elena','Stefan','Maria','Daniel','Laura','John','Emma',
873 'Petar','Nikola','Sara','Filip'
874 ]) AS t(val)),
875
876 last_names AS MATERIALIZED (SELECT val, row_number() OVER () rn
877 FROM unnest(ARRAY [
878 'Smith','Brown','Lee','Johnson','White','Peshevski',
879 'Stojanov','Ivanov','Petrov','Georgiev','Trajkovski',
880 'Kostov','Mitrevski','Ristov','Iliev'
881 ]) AS t(val)),
882
883 fn_cnt AS MATERIALIZED (SELECT count(*) c
884 FROM first_names),
885
886 ln_cnt AS MATERIALIZED (SELECT count(*) c
887 FROM last_names)
888
889INSERT
890INTO car_dealership.employee
891(id, position, first_name, last_name,
892 email, phone, hire_date, manager_id)
893SELECT i,
894 'Manager',
895 fn.val,
896 ln.val,
897
898 lower(
899 fn.val || '.' || ln.val || i || '@' ||
900 (ARRAY ['gmail','yahoo','outlook'])[(i % 3) + 1] ||
901 '.com'
902 ),
903
904 '+3897' || (i % 8) ||
905 lpad((i % 1000000)::text, 6, '0'),
906
907 CURRENT_DATE - (i % 7000),
908
909 NULL
910
911FROM generate_series(1, 5000) i
912 JOIN first_names fn
913 ON fn.rn = (i % (SELECT c FROM fn_cnt)) + 1
914 JOIN last_names ln
915 ON ln.rn = (i % (SELECT c FROM ln_cnt)) + 1;
916
917
918-- =========================================================
919-- EMPLOYEES: REMAINING 45,000
920-- =========================================================
921
922WITH first_names AS MATERIALIZED (SELECT val, row_number() OVER () rn
923 FROM unnest(ARRAY [
924 'Anna','Mark','Ivan','Christian','Nicole','Andrew','Peter','Matthew',
925 'Richard','Elena','Stefan','Maria','Daniel','Laura','John','Emma',
926 'Petar','Nikola','Sara','Filip'
927 ]) AS t(val)),
928
929 last_names AS MATERIALIZED (SELECT val, row_number() OVER () rn
930 FROM unnest(ARRAY [
931 'Smith','Brown','Lee','Johnson','White','Peshevski',
932 'Stojanov','Ivanov','Petrov','Georgiev','Trajkovski',
933 'Kostov','Mitrevski','Ristov','Iliev'
934 ]) AS t(val)),
935
936 fn_cnt AS MATERIALIZED (SELECT count(*) c
937 FROM first_names),
938
939 ln_cnt AS MATERIALIZED (SELECT count(*) c
940 FROM last_names)
941
942INSERT
943INTO car_dealership.employee
944(id, position, first_name, last_name,
945 email, phone, hire_date, manager_id)
946SELECT i,
947
948 (ARRAY [
949 'Salesperson',
950 'Manager',
951 'Finance',
952 'Admin'
953 ])[(i % 4) + 1],
954
955 fn.val,
956 ln.val,
957
958 lower(
959 fn.val || '.' || ln.val || i || '@' ||
960 (ARRAY ['gmail','yahoo','outlook'])[(i % 3) + 1] ||
961 '.com'
962 ),
963
964 '+3897' || (i % 8) ||
965 lpad((i % 1000000)::text, 6, '0'),
966
967 CURRENT_DATE - (i % 7000),
968
969 (i % 5000) + 1
970
971FROM generate_series(5001, 50000) i
972 JOIN first_names fn
973 ON fn.rn = (i % (SELECT c FROM fn_cnt)) + 1
974 JOIN last_names ln
975 ON ln.rn = (i % (SELECT c FROM ln_cnt)) + 1;
976
977
978-- =========================================================
979-- CONFIGURATIONS
980-- =========================================================
981
982WITH customers AS MATERIALIZED (SELECT id,
983 row_number() OVER (ORDER BY id) AS rn
984 FROM car_dealership.customer),
985
986 vehicles AS MATERIALIZED (SELECT vin,
987 row_number() OVER (ORDER BY vin) AS rn
988 FROM car_dealership.vehicle),
989
990 vehicle_cnt AS MATERIALIZED (SELECT COUNT(*) AS c
991 FROM vehicles),
992
993 customer_slots AS MATERIALIZED (SELECT id AS customer_id,
994 rn AS customer_rn,
995
996 CASE
997 WHEN rn % 20 = 0 THEN 1
998 WHEN rn % 20 = 1 THEN 2
999 WHEN rn % 20 < 5 THEN 3
1000 WHEN rn % 20 < 9 THEN 4
1001 WHEN rn % 20 < 12 THEN 5
1002 WHEN rn % 20 < 15 THEN 6
1003 WHEN rn % 20 < 18 THEN 7
1004 WHEN rn % 20 = 18 THEN 8
1005 ELSE 9
1006 END AS slot_count
1007
1008 FROM customers),
1009
1010 expanded AS MATERIALIZED (SELECT cs.customer_id,
1011 cs.customer_rn,
1012 gs.slot,
1013
1014 (
1015 SUM(cs.slot_count) OVER (
1016 ORDER BY cs.customer_rn
1017 ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
1018 )
1019 - cs.slot_count
1020 + gs.slot
1021 )::int AS global_pos
1022
1023 FROM customer_slots cs
1024 CROSS JOIN LATERAL
1025 generate_series(1, cs.slot_count) AS gs(slot))
1026
1027INSERT
1028INTO car_dealership.configuration
1029 (vin, description, total_price, created_at, customer_id)
1030SELECT v.vin,
1031
1032 (ARRAY [
1033 'Standard configuration with base equipment',
1034 'Sport-oriented setup with performance packages',
1035 'Comfort-focused build for long-distance driving',
1036 'City commuter spec with economy features',
1037 'Family configuration with safety and comfort packs',
1038 'Executive spec with full luxury options',
1039 'Off-road ready with enhanced suspension package',
1040 'Hybrid-optimised configuration for fuel efficiency',
1041 'Tech-forward setup with full infotainment suite',
1042 'Winter spec with cold-weather equipment'
1043 ])[(e.global_pos % 10) + 1],
1044
1045 ROUND(
1046 (
1047 10000 +
1048 (e.global_pos * 7 % 75001)
1049 )::numeric(10, 2),
1050 2
1051 ),
1052
1053 CURRENT_DATE - (e.global_pos % 2920),
1054
1055 e.customer_id
1056
1057FROM expanded e
1058 JOIN vehicles v
1059 ON v.rn =
1060 ((e.global_pos - 1) %
1061 (SELECT c FROM vehicle_cnt)) + 1;
1062
1063
1064-- =========================================================
1065-- PRODUCTION
1066-- =========================================================
1067
1068WITH brand_factory
1069 (brand_id, factory_id, volume_weight)
1070 AS MATERIALIZED (VALUES (1, 1, 1.6),
1071 (2, 1, 1.4),
1072
1073 (3, 2, 1.9),
1074 (4, 2, 2.1),
1075
1076 (5, 3, 1.0),
1077 (6, 3, 1.2),
1078
1079 (7, 4, 1.1),
1080 (8, 4, 0.9),
1081
1082 (9, 5, 2.0),
1083 (10, 5, 2.2),
1084
1085 (11, 6, 1.8),
1086 (12, 6, 1.6),
1087
1088 (13, 7, 0.8),
1089 (14, 7, 0.7),
1090
1091 (15, 8, 0.6),
1092 (16, 8, 0.7),
1093
1094 (17, 9, 1.3),
1095 (18, 9, 1.5),
1096
1097 (19, 10, 0.8),
1098 (20, 10, 0.7)),
1099
1100 vehicle_factory AS MATERIALIZED (SELECT v.vin,
1101 bf.factory_id,
1102 bf.volume_weight
1103
1104 FROM car_dealership.vehicle v
1105 JOIN car_dealership.model m
1106 ON m.id = v.model_id
1107 JOIN brand_factory bf
1108 ON bf.brand_id = m.brand_id)
1109
1110INSERT
1111INTO car_dealership.production
1112 (factory_id, vin, status)
1113SELECT vf.factory_id,
1114 vf.vin,
1115
1116 CASE
1117 WHEN r < 0.10 THEN 'Scheduled'
1118
1119 WHEN r <
1120 0.10 +
1121 0.25 / vf.volume_weight
1122 THEN 'In Progress'
1123
1124 WHEN r <
1125 0.10 +
1126 0.25 / vf.volume_weight +
1127 0.15 / vf.volume_weight
1128 THEN 'Quality Check'
1129
1130 ELSE 'Completed'
1131 END
1132
1133FROM vehicle_factory vf
1134
1135 CROSS JOIN LATERAL (
1136 SELECT ABS(
1137 (hashtext(vf.vin) # vf.factory_id)::float8 /
1138 2147483647.0
1139 ) AS r
1140 ) rng;
1141
1142
1143-- =========================================================
1144-- ORDERS
1145-- =========================================================
1146
1147WITH configs AS MATERIALIZED (SELECT DISTINCT ON (vin) id,
1148 customer_id,
1149 row_number() OVER (ORDER BY id)::int AS rn
1150
1151 FROM car_dealership.configuration
1152 ORDER BY vin, id),
1153
1154 employees AS MATERIALIZED (SELECT id,
1155 row_number() OVER (ORDER BY id)::int AS rn
1156 FROM car_dealership.employee
1157 WHERE position = 'Salesperson'),
1158
1159 employee_cnt AS MATERIALIZED (SELECT COUNT(*)::int AS c
1160 FROM employees)
1161
1162INSERT
1163INTO car_dealership."Order"
1164 (date, status, customer_id, configuration_id, employee_id)
1165SELECT CURRENT_DATE - (60 + (c.rn % 3000)),
1166
1167 (ARRAY [
1168 'Confirmed',
1169 'Pending',
1170 'Canceled',
1171 'Completed'
1172 ])[(c.rn % 4) + 1],
1173
1174 c.customer_id,
1175 c.id,
1176 e.id
1177
1178FROM configs c
1179 JOIN employees e
1180 ON e.rn =
1181 ((c.rn - 1) %
1182 (SELECT c FROM employee_cnt)) + 1;
1183
1184
1185-- =========================================================
1186-- CONTRACTS
1187-- =========================================================
1188
1189WITH orders AS MATERIALIZED (SELECT DISTINCT ON (cfg.vin) o.id AS order_id,
1190 o.customer_id,
1191 o.employee_id,
1192 o.date AS order_date,
1193 cfg.vin,
1194 row_number() OVER (ORDER BY o.id)::int AS rn
1195
1196 FROM car_dealership."Order" o
1197
1198 JOIN car_dealership.configuration cfg
1199 ON cfg.id = o.configuration_id
1200
1201 WHERE o.status IN ('Confirmed', 'Completed')
1202
1203 ORDER BY cfg.vin, o.id),
1204
1205 employees AS MATERIALIZED (SELECT id,
1206 row_number() OVER (ORDER BY id)::int AS rn
1207
1208 FROM car_dealership.employee
1209 WHERE position = 'Salesperson'),
1210
1211 employee_cnt AS MATERIALIZED (SELECT COUNT(*)::int AS c
1212 FROM employees)
1213
1214INSERT
1215INTO car_dealership.contract
1216(employee_id, notes, date, type,
1217 order_id, customer_id, vin)
1218SELECT e.id,
1219
1220 'Contract ' || o.rn,
1221
1222 o.order_date + (o.rn % 30),
1223
1224 (ARRAY [
1225 'Standard',
1226 'Finance',
1227 'Fleet'
1228 ])[(o.rn % 3) + 1],
1229
1230 o.order_id,
1231 o.customer_id,
1232 o.vin
1233
1234FROM orders o
1235 JOIN employees e
1236 ON e.rn =
1237 ((o.rn - 1) %
1238 (SELECT c FROM employee_cnt)) + 1;
1239
1240
1241-- =========================================================
1242-- TEST DRIVES
1243-- =========================================================
1244
1245WITH customers AS MATERIALIZED (SELECT id,
1246 row_number() OVER (ORDER BY id)::int AS rn
1247 FROM car_dealership.customer),
1248
1249 vehicles AS MATERIALIZED (SELECT vin,
1250 row_number() OVER (ORDER BY vin)::int AS rn
1251 FROM car_dealership.vehicle),
1252
1253 test_vehicles AS MATERIALIZED (SELECT vin, rn
1254 FROM vehicles
1255 WHERE rn <= 1000000),
1256
1257 tv_cnt AS MATERIALIZED (SELECT COUNT(*)::int AS c
1258 FROM test_vehicles)
1259
1260INSERT
1261INTO car_dealership.testdrive
1262(date, time_start, time_end,
1263 customer_id, vin, result)
1264SELECT CURRENT_DATE - (i % 2000),
1265
1266 ts,
1267
1268 ts +
1269 ((30 + (i % 11)) || ' minutes')::interval,
1270
1271 c.id,
1272
1273 v.vin,
1274
1275 (ARRAY [
1276 'Interested',
1277 'Not Interested',
1278 'Follow-Up'
1279 ])[(i % 3) + 1]
1280
1281FROM generate_series(1, 10000000) i
1282
1283 CROSS JOIN LATERAL (
1284 SELECT TIMESTAMP '2024-01-01'
1285 + (i % 365) * INTERVAL '1 day'
1286 + (i % 86400) * INTERVAL '1 second'
1287 AS ts
1288 ) t
1289
1290 JOIN customers c
1291 ON c.rn = ((i - 1) % 1000000) + 1
1292
1293 JOIN test_vehicles v
1294 ON v.rn =
1295 ((i - 1) %
1296 (SELECT c FROM tv_cnt)) + 1;
1297
1298
1299-- =========================================================
1300-- CONFIGURATION PACKAGES
1301-- =========================================================
1302
1303WITH configs AS MATERIALIZED (SELECT id,
1304 row_number() OVER (ORDER BY id) rn
1305 FROM car_dealership.configuration),
1306
1307 packages AS MATERIALIZED (SELECT id,
1308 row_number() OVER (ORDER BY id) rn
1309 FROM car_dealership.equipmentpackage),
1310
1311 package_cnt AS MATERIALIZED (SELECT count(*) c
1312 FROM packages)
1313
1314INSERT
1315INTO car_dealership.configurationpackage
1316 (id, configuration_id, package_id)
1317SELECT (c.rn - 1) * 3 + p_idx,
1318
1319 c.id,
1320
1321 p.id
1322
1323FROM configs c
1324
1325 CROSS JOIN generate_series(1, 3) AS p_idx
1326
1327 JOIN packages p
1328 ON p.rn =
1329 ((c.rn + p_idx - 2) %
1330 (SELECT c FROM package_cnt)) + 1;
1331
1332
1333-- =========================================================
1334-- CONFIGURATION ELEMENTS
1335-- =========================================================
1336
1337WITH cp AS MATERIALIZED (SELECT id,
1338 row_number() OVER (ORDER BY id) rn
1339 FROM car_dealership.configurationpackage),
1340
1341 elements AS MATERIALIZED (SELECT id,
1342 row_number() OVER (ORDER BY id) rn
1343 FROM car_dealership.packageelement),
1344
1345 element_cnt AS MATERIALIZED (SELECT count(*) c
1346 FROM elements)
1347
1348INSERT
1349INTO car_dealership.configurationelement
1350 (id, configuration_package_id, package_element_id)
1351SELECT (cp.rn - 1) * 2 + e_idx,
1352
1353 cp.id,
1354
1355 e.id
1356
1357FROM cp
1358
1359 CROSS JOIN generate_series(1, 2) AS e_idx
1360
1361 JOIN elements e
1362 ON e.rn =
1363 ((cp.rn + e_idx - 2) %
1364 (SELECT c FROM element_cnt)) + 1;
1365
1366
1367-- =========================================================
1368-- SALES
1369-- =========================================================
1370
1371WITH contracts AS MATERIALIZED (SELECT id,
1372 customer_id,
1373 date AS contract_date,
1374 row_number() OVER (ORDER BY id)::int AS rn
1375 FROM car_dealership.contract),
1376
1377 employees AS MATERIALIZED (SELECT id,
1378 row_number() OVER (ORDER BY id)::int AS rn
1379
1380 FROM car_dealership.employee
1381 WHERE position = 'Salesperson'),
1382
1383 employee_cnt AS MATERIALIZED (SELECT COUNT(*)::int AS c
1384 FROM employees)
1385
1386INSERT
1387INTO car_dealership.sale
1388 (date, contract_id, customer_id, employee_id)
1389SELECT c.contract_date + (c.rn % 30),
1390
1391 c.id,
1392
1393 c.customer_id,
1394
1395 e.id
1396
1397FROM contracts c
1398 JOIN employees e
1399 ON e.rn =
1400 ((c.rn - 1) %
1401 (SELECT c FROM employee_cnt)) + 1;
1402
1403
1404-- =========================================================
1405-- MARK SOLD VEHICLES
1406-- =========================================================
1407
1408UPDATE car_dealership.vehicle v
1409SET status_id = (SELECT id
1410 FROM car_dealership.status
1411 WHERE status = 'Sold')
1412WHERE EXISTS (SELECT 1
1413
1414 FROM car_dealership.contract c
1415 JOIN car_dealership.sale s
1416 ON s.contract_id = c.id
1417
1418 WHERE c.vin = v.vin);
1419
1420
1421-- =========================================================
1422-- COMPLETE PRODUCTION FOR SOLD VEHICLES
1423-- =========================================================
1424
1425UPDATE car_dealership.production p
1426SET status = 'Completed'
1427WHERE EXISTS (SELECT 1
1428
1429 FROM car_dealership.contract c
1430 JOIN car_dealership.sale s
1431 ON s.contract_id = c.id
1432
1433 WHERE c.vin = p.vin);
1434
1435
1436-- =========================================================
1437-- PAYMENTS
1438-- =========================================================
1439
1440WITH sales AS MATERIALIZED (SELECT s.id,
1441 row_number() OVER (ORDER BY s.id) AS rn,
1442 conf.total_price
1443
1444 FROM car_dealership.sale s
1445
1446 JOIN car_dealership.contract ct
1447 ON ct.id = s.contract_id
1448
1449 JOIN car_dealership."Order" o
1450 ON o.id = ct.order_id
1451
1452 JOIN car_dealership.configuration conf
1453 ON conf.id = o.configuration_id)
1454
1455INSERT
1456INTO car_dealership.payment
1457 (id, type, amount, sale_id)
1458SELECT rn,
1459
1460 (ARRAY [
1461 'Cash',
1462 'Installment',
1463 'Leasing'
1464 ])[(rn % 3) + 1],
1465
1466 total_price,
1467
1468 id
1469
1470FROM sales;
1471
1472
1473-- =========================================================
1474-- DISCOUNTS
1475-- =========================================================
1476
1477WITH payments AS MATERIALIZED (SELECT id,
1478 row_number() OVER (ORDER BY id)::int AS rn
1479 FROM car_dealership.payment),
1480
1481 employees AS MATERIALIZED (SELECT id,
1482 row_number() OVER (ORDER BY id)::int AS rn
1483
1484 FROM car_dealership.employee
1485
1486 WHERE position IN ('Finance', 'Manager')),
1487
1488 employee_cnt AS MATERIALIZED (SELECT COUNT(*)::int AS c
1489 FROM employees)
1490
1491INSERT
1492INTO car_dealership.discount
1493 (percentage, payment_id, employee_id)
1494SELECT (5 + (p.rn % 21))::smallint,
1495
1496 p.id,
1497
1498 e.id
1499
1500FROM payments p
1501
1502 JOIN employees e
1503 ON e.rn =
1504 ((p.rn - 1) %
1505 (SELECT c FROM employee_cnt)) + 1
1506
1507WHERE p.rn % 10 < 3;
1508
1509
1510-- =========================================================
1511-- UPDATE STATISTICS AFTER BULK LOAD
1512-- =========================================================
1513
1514/*ANALYZE car_dealership.factory;
1515ANALYZE car_dealership.brand;
1516ANALYZE car_dealership.model;
1517ANALYZE car_dealership.customer;
1518ANALYZE car_dealership.address;
1519ANALYZE car_dealership.engine;
1520ANALYZE car_dealership.vehicle;
1521ANALYZE car_dealership.employee;
1522ANALYZE car_dealership.configuration;
1523ANALYZE car_dealership.production;
1524ANALYZE car_dealership."Order";
1525ANALYZE car_dealership.contract;
1526ANALYZE car_dealership.testdrive;
1527ANALYZE car_dealership.configurationpackage;
1528ANALYZE car_dealership.configurationelement;
1529ANALYZE car_dealership.sale;
1530ANALYZE car_dealership.payment;
1531ANALYZE car_dealership.discount;*/
1532
1533SELECT st.status, COUNT(*)
1534FROM car_dealership.Sale s
1535JOIN car_dealership.Contract c ON c.id = s.contract_id
1536JOIN car_dealership.Vehicle v ON v.vin = c.vin
1537JOIN car_dealership.Status st ON st.id = v.status_id
1538GROUP BY st.status;