1 | insert into project.users_table (username,email,name_user,password_user) values
|
---|
2 | ('andrejtodorovski5','andrejtodorovski5@gmail.com','Andrej Todorovski','andrejpassword'),
|
---|
3 | ('stefanmileski4','stefanmileski4@gmail.com','Stefan Mileski','stefanpassword'),
|
---|
4 | ('hristijansazdovski3','hristijansazdovski3@gmail.com','Hristijan Sazdovski','hristijanpassword'),
|
---|
5 | ('bojantrpeski2','bojantrpeski2@gmail.com','Bojan Trpeski','bojanpassword'),
|
---|
6 | ('darkosasanski1','darkosasanski1@gmail.com','Darko Sasanski','darkopassword'),
|
---|
7 | ('denicakjorvezir','denicakjorvezir@gmail.com','Denica Kjorvezir','denicapassword'),
|
---|
8 | ('deliveryman1','deliveryman1@gmail.com','Petko Petkovski','petko123'),
|
---|
9 | ('deliveryman2','deliveryman2@gmail.com','Marko Markovski','marko123'),
|
---|
10 | ('deliveryman3','deliveryman3@gmail.com','Petar Petrovski','petar123'),
|
---|
11 | ('warehouseman1','warehouseman1@gmail.com','Viktor Petrovski','viktor123'),
|
---|
12 | ('warehouseman2','warehouseman2@gmail.com','Vlatko Petrovski','vlatko123'),
|
---|
13 | ('warehouseman3','warehouseman3@gmail.com','Vido Petrovski','vido123');
|
---|
14 |
|
---|
15 | insert into project.users_table (username,email,name_user,password_user,phone_number) values
|
---|
16 | ('stefanmileski5','stefanmileski5@gmail.com','Stefan Mileski','stefanklient','070 123 456');
|
---|
17 |
|
---|
18 | insert into project.administrator (id_user,authorized_from, authorized_till) values (1,'2022-01-01','2024-01-01'),(2,'2022-01-01','2025-01-01'),(3,'2022-01-01','2026-01-01');
|
---|
19 |
|
---|
20 | insert into project.client (id_user) values (13),(4),(5),(6);
|
---|
21 |
|
---|
22 | insert into project.delivery_man (id_user,employed_from) values (7,now()),(8,now()),(9,now());
|
---|
23 |
|
---|
24 | insert into project.warehouse (warehouse_location) values ('Radishani'),('Kozle'),('Vizbegovo');
|
---|
25 |
|
---|
26 | insert into project.warehouseman (id_user,employed_from,id_warehouse) values (10,now(),1),(11,now(),2),(12,now(),3);
|
---|
27 |
|
---|
28 | insert into project.part_manufacturer (pm_name,pm_location) values
|
---|
29 | ('Bosch','Germany'),
|
---|
30 | ('Continental','Belgium'),
|
---|
31 | ('Mahle','Netherlands');
|
---|
32 |
|
---|
33 | insert into project.car_manufacturer (cm_name,cm_country) values
|
---|
34 | ('Volkswagen','Germany'),
|
---|
35 | ('Fiat','Italy'),
|
---|
36 | ('Ford','USA'),
|
---|
37 | ('Toyota','Japan'),
|
---|
38 | ('Hyndai','Korea');
|
---|
39 |
|
---|
40 | insert into project.repair_shop (rs_name,rs_location,rs_phone_number) values
|
---|
41 | ('Toyota Service','Partizanska','070 123 456'),
|
---|
42 | ('Volkswagen Service','Ilinenska','075 876 543'),
|
---|
43 | ('Fiat Service','Vodnjanska','078 555 666'),
|
---|
44 | ('Hyndai Service','Boris Trajkovski','075 500 000');
|
---|
45 |
|
---|
46 | insert into project.car(in_production_since,in_production_till,car_type,id_car_manufacturer) values
|
---|
47 | (2001,2004,'Golf 4',1),
|
---|
48 | (2006,2009,'Punto',2),
|
---|
49 | (2004,2006,'Golf 5',1),
|
---|
50 | (2000,2008,'Fiesta',3),
|
---|
51 | (1995,2005,'Coupe',5);
|
---|
52 |
|
---|
53 | insert into project.car_sample(vin,year_of_production, engine_power, displacement, fuel_type, km_driven, ID_user, ID_car) values
|
---|
54 | (1111,2008,65,1200,'Diesel',120000,4,4),
|
---|
55 | (2222,2010,80,1400,'Diesel',150000,5,4),
|
---|
56 | (3333,2016,65,1200,'Petrol/Gas',65000,6,2),
|
---|
57 | (4444,1997,140,2000,'Petrol/Gas',185000,13,5);
|
---|
58 |
|
---|
59 | insert into project.part(part_name, id_part_manufacturer) values
|
---|
60 | ('Engine G4GF',1),
|
---|
61 | ('Wheel 20CM',2),
|
---|
62 | ('Shift 6GEARS',3),
|
---|
63 | ('Radio WITH AUX',1),
|
---|
64 | ('Rear Bumper FOR VW GOLF 4',2),
|
---|
65 | ('Headlight FOR PUNTO',3);
|
---|
66 |
|
---|
67 | insert into project.category(category_name) values ('Brakes'),('Cooling System'),('Electrical'),('Engine components'),('Exterior'),('Filters');
|
---|
68 |
|
---|
69 | insert into project.order_table(order_status,order_date,id_user) values
|
---|
70 | ('created',now(),4),
|
---|
71 | ('created',now(),5),
|
---|
72 | ('created',now(),6),
|
---|
73 | ('created',now(),13);
|
---|
74 |
|
---|
75 | insert into project.service_book (vin) values (1111),(2222),(3333),(4444);
|
---|
76 |
|
---|
77 | insert into project.delivery (delivery_status, delivery_address,id_user,id_order) values
|
---|
78 | ('in progress','Aerodrom',7,3),
|
---|
79 | ('finished','Pintija',8,1),
|
---|
80 | ('in progress','Kisela Voda',9,2),
|
---|
81 | ('in progress','Kozle',8,4);
|
---|
82 |
|
---|
83 | insert into project.price (amount, price_from, id_part) values
|
---|
84 | (1000,now(),2),
|
---|
85 | (2000,now(),3),
|
---|
86 | (3000,now(),1),
|
---|
87 | (1500,now(),4),
|
---|
88 | (3500,now(),5),
|
---|
89 | (300,now(),6);
|
---|
90 |
|
---|
91 | insert into project.repair (vin, id_repair_shop, id_service_book) values (1111,3,1),(2222,2,2),(3333,1,3),(4444, 2, 4);
|
---|
92 | insert into project.repair (vin, id_repair_shop, id_service_book) values (1111,2,1)
|
---|
93 |
|
---|
94 | insert into project.review (review_rating, id_repair, id_user) values (10, 1, 4), (6, 2, 5), (8, 3, 6), (9, 4, 13);
|
---|
95 | insert into project.review (review_rating, id_repair, id_user) values (7, 5, 4)
|
---|
96 |
|
---|
97 | insert into project.part_is_from_category (id_part, id_category) values (5,5), (1,4), (6,5);
|
---|
98 |
|
---|
99 | insert into project.repair_shop_is_authorized_for_car_make (id_repair_shop, id_car_manufacturer) values (1,4),(2,1),(3,2),(4,5);
|
---|
100 |
|
---|
101 | insert into project.order_contains_part (id_order, id_part, quantity_order) values (1,3,1),(2,4,2),(3,1,2),(4,6,1);
|
---|
102 |
|
---|
103 | insert into project.part_is_in_stock_in_warehouse (id_part,id_warehouse,quantity_warehouse) values (1,2,100), (2,1,150), (3,1,50), (4,3,75),(5,1,30),(6,2,70);
|
---|
104 |
|
---|
105 | insert into project.part_is_appropriate_for_car (id_part, id_car) values (1,1),(1,2),(1,3),(2,1),(2,4),(2,5),(5,1),(4,1),(4,2),(4,3),(4,4),(4,5),(3,5),(6,2);
|
---|
106 |
|
---|
107 |
|
---|
108 |
|
---|
109 | select p.part_name, project.repair.vin,id_repair_shop from project.repair
|
---|
110 | join project.car_sample cs on cs.vin = project.repair.vin
|
---|
111 | join project.order_table ot on ot.id_user = cs.id_user
|
---|
112 | join project.order_contains_part ocp on ocp.id_order = ot.id_order
|
---|
113 | join project.part p on p.id_part = ocp.id_part
|
---|
114 | where cs.id_user = 4
|
---|
115 |
|
---|
116 | select p.part_name, c.car_type from project.part p
|
---|
117 | join project.part_is_appropriate_for_car piafc on piafc.id_part = p.id_part
|
---|
118 | join project.car c on c.id_car = piafc.id_car
|
---|
119 | join project.part_is_from_category pifc on pifc.id_part = p.id_part
|
---|
120 | join project.category c2 on c2.id_category = pifc.id_category
|
---|
121 | where c.car_type = 'Punto'
|
---|
122 | and
|
---|
123 | c2.category_name = 'Exterior'
|
---|
124 |
|
---|
125 | select ot.order_date , p.part_name , p2.amount , ocp.quantity_order , ot.order_status from project.order_table ot
|
---|
126 | join project.order_contains_part ocp on ocp.id_order = ot.id_order
|
---|
127 | join project.part p on p.id_part = ocp.id_part
|
---|
128 | join project.price p2 on p2.id_part = p.id_part
|
---|
129 | where ot.id_user = 4
|
---|
130 |
|
---|
131 | select r.review_rating, r.review_comment, rs.rs_name, rs.rs_location from project.review r
|
---|
132 | join project.repair r2 on r2.id_repair = r.id_repair
|
---|
133 | join project.repair_shop rs on rs.id_repair_shop = r2.id_repair_shop
|
---|
134 | where r.id_user = 4
|
---|
135 |
|
---|
136 | select part_name from project.part
|
---|
137 |
|
---|
138 | drop view project.repair_shop_reviews_summary
|
---|
139 |
|
---|
140 | create or replace view project.repair_shop_reviews_summary as
|
---|
141 | select rs.id_repair_shop as rsid , rs.rs_name as rsname , count(r2.review_rating) as reviewcount ,avg(r2.review_rating)::float as reviewaverage
|
---|
142 | from project.repair_shop rs
|
---|
143 | left join project.repair r on r.id_repair_shop = rs.id_repair_shop
|
---|
144 | left join project.review r2 on r2.id_repair = r.id_repair
|
---|
145 | group by rs.id_repair_shop
|
---|
146 |
|
---|
147 | select * from project.repair_shop_reviews_summary
|
---|
148 |
|
---|