[f094f6c] | 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 |
|
---|