Changeset 101cbe2 for database_scripts/polnenje_v2.sql

Timestamp:

12/27/22 22:45:56 (18 months ago)

Author:

andrejtodorovski <82031894+andrejtodorovski@…>

Branches:

main

Children:

23a2bc5

Parents:

f094f6c

Message:

Added more data

File:

• database_scripts/polnenje_v2.sql (modified) (7 diffs)

database_scripts/polnenje_v2.sql

@@ -3 +3 @@
 ('stefanmileski4','stefanmileski4@gmail.com','Stefan Mileski','stefanpassword'),
 ('hristijansazdovski3','hristijansazdovski3@gmail.com','Hristijan Sazdovski','hristijanpassword'),
-('bojantrpeski2','bojantrpeski2@gmail.com','Bojan Trpeski','bojanpassword'),
-('darkosasanski1','darkosasanski1@gmail.com','Darko Sasanski','darkopassword'),
-('denicakjorvezir','denicakjorvezir@gmail.com','Denica Kjorvezir','denicapassword'),
+('client1','bojantrpeski2@gmail.com','Bojan Trpeski','bojanpassword'),
+('client2','darkosasanski1@gmail.com','Darko Sasanski','darkopassword'),
+('client3','denicakjorvezir@gmail.com','Denica Kjorvezir','denicapassword'),
 ('deliveryman1','deliveryman1@gmail.com','Petko Petkovski','petko123'),
 ('deliveryman2','deliveryman2@gmail.com','Marko Markovski','marko123'),
@@ -11 +11 @@
 ('warehouseman1','warehouseman1@gmail.com','Viktor Petrovski','viktor123'),
 ('warehouseman2','warehouseman2@gmail.com','Vlatko Petrovski','vlatko123'),
-('warehouseman3','warehouseman3@gmail.com','Vido Petrovski','vido123');
-
-insert into project.users_table (username,email,name_user,password_user,phone_number) values
-('stefanmileski5','stefanmileski5@gmail.com','Stefan Mileski','stefanklient','070 123 456');
+('warehouseman3','warehouseman3@gmail.com','Vido Petrovski','vido123'),
+('client4','client4@gmail.com','Client Four','client4'),
+('client5','client5@gmail.com','Client Five','client5'),
+('client6','client6@gmail.com','Client Six','client6'),
+('client7','client7@gmail.com','Client Seven','client7'),
+('client8','client8@gmail.com','Client Eight','client8'),
+('client9','client9@gmail.com','Client Nine','client9'),
+('client10','client10@gmail.com','Client Ten','client10'),
+('client11','client11@gmail.com','Client Eleven','client11'),
+('client12','client12@gmail.com','Client Twelve','client12'),
+('client13','client13@gmail.com','Client Thirteen','client13'),
+('client14','client14@gmail.com','Client Fourteen','client14'),
+('client15','client15@gmail.com','Client Fifteen','client15');
+
+--insert into project.users_table (username,email,name_user,password_user,phone_number) values
+--('stefanmileski5','stefanmileski5@gmail.com','Stefan Mileski','stefanklient','070 123 456');
 
 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');
 
-insert into project.client (id_user) values (13),(4),(5),(6);
+insert into project.client (id_user) values (4),(5),(6),(13),(14),(15),(16),(17),(18),(19),(20),(21),(22),(23),(24);
 
 insert into project.delivery_man (id_user,employed_from) values (7,now()),(8,now()),(9,now());
@@ -29 +41 @@
 ('Bosch','Germany'),
 ('Continental','Belgium'),
-('Mahle','Netherlands');
+('Mahle','Netherlands'),
+('Sachs','Germany'),
+('Brembo','Italy');
 
 insert into project.car_manufacturer (cm_name,cm_country) values
@@ -39 +53 @@
 
 insert into project.repair_shop (rs_name,rs_location,rs_phone_number) values
-('Toyota Service','Partizanska','070 123 456'),
 ('Volkswagen Service','Ilinenska','075 876 543'),
 ('Fiat Service','Vodnjanska','078 555 666'),
+('Ford Service','Teodosij Gologanov','071 333 444'),
+('Toyota Service','Partizanska','070 123 456'),
 ('Hyndai Service','Boris Trajkovski','075 500 000');
 
 insert into project.car(in_production_since,in_production_till,car_type,id_car_manufacturer) values
 (2001,2004,'Golf 4',1),
-(2006,2009,'Punto',2),
 (2004,2006,'Golf 5',1),
+(2001,2003,'Punto',2),
+(2004,2008,'Punto Evo',2),
+(2009,2012,'Grande Punto',2),
+(2007,2014,'Doblo',2),
 (2000,2008,'Fiesta',3),
+(2005,2015,'Focus',3),
+(2003,2006,'Aygo',4),
+(2007,2015,'Yaris',4),
+(2015,2022,'Corollaa',4),
 (1995,2005,'Coupe',5);
 
 insert into project.car_sample(vin,year_of_production, engine_power, displacement, fuel_type, km_driven, ID_user, ID_car) values
-(1111,2008,65,1200,'Diesel',120000,4,4),
-(2222,2010,80,1400,'Diesel',150000,5,4),
-(3333,2016,65,1200,'Petrol/Gas',65000,6,2),
-(4444,1997,140,2000,'Petrol/Gas',185000,13,5);
+(4444,2003,65,1200,'Diesel',120000,4,1),
+(5555,2005,80,1400,'Diesel',150000,5,2),
+(6666,2002,65,1200,'Petrol/Gas',65000,6,3),
+(1313,2006,140,2000,'Diesel',185000,13,4),
+(1414,2006,120,1900,'Diesel',240000,14,5),
+(1515,2006,95,1600,'Petrol',85000,15,6),
+(1616,2006,80,1400,'Petrol/Gas',125000,16,7),
+(1717,2006,69,1200,'Petrol',113000,17,8),
+(1818,2006,65,1200,'Petrol/Gas',150000,18,9),
+(1919,2006,140,1600,'Diesel',140000,19,10),
+(2020,2017,200,2400,'Diesel',20000,20,11),
+(2121,1996,200,2000,'Petrol/Gas',320000,21,12),
+(2222,2004,120,1600,'Diesel',115000,22,3),
+(2323,2003,140,2000,'Petrol/Gas',105000,23,3),
+(2424,2000,120,1900,'Diesel',150000,24,1);
 
 insert into project.part(part_name, id_part_manufacturer) values
@@ -61 +94 @@
 ('Wheel 20CM',2),
 ('Shift 6GEARS',3),
-('Radio WITH AUX',1),
+('Radio with AUX',1),
 ('Rear Bumper FOR VW GOLF 4',2),
-('Headlight FOR PUNTO',3);
-
-insert into project.category(category_name) values ('Brakes'),('Cooling System'),('Electrical'),('Engine components'),('Exterior'),('Filters');
+('Headlight FOR PUNTO',3),
+('Air Conditioning',4),
+('Hand Brake',5),
+('Siren',4),
+('Electrical window buttons',5),
+('Sport filter',4),
+('Air filter',5),
+('Fuel filter',4),
+('Oil filter',5);
+
+insert into project.category(category_name) values ('Brakes'),('Cooling System'),('Electrical'),('Engine components'),('Exterior'),('Filters'),('Interior'),('Shift');
 
 insert into project.order_table(order_status,order_date,id_user) values 
-('created',now(),4),
-('created',now(),5),
-('created',now(),6),
-('created',now(),13);
-
-insert into project.service_book (vin) values (1111),(2222),(3333),(4444);
+('created',now()-interval'12months',4),
+('created',now()-interval'6months',4),
+('created',now()-interval'7months',4),
+('created',now()-interval'1month',4),
+('created',now()-interval'11months',5),
+('created',now()-interval'10months',6),
+('created',now()-interval'2months',6),
+('created',now()-interval'9months',13),
+('created',now()-interval'8months',14),
+('created',now()-interval'7months',15),
+('created',now()-interval'6months',16),
+('created',now()-interval'9months',17),
+('created',now()-interval'5months',17),
+('created',now()-interval'4months',18),
+('created',now()-interval'3months',19),
+('created',now()-interval'2months',20),
+('created',now()-interval'1month',21),
+('created',now()-interval'12months',22),
+('created',now(),23),
+('created',now()-interval'3months',24),
+('created',now(),24);
+
+
+insert into project.service_book (vin) values (4444),(5555),(6666),(1313),(1414),(1515),(1616),(1717),(1818),(1919),(2020),(2121),(2222),(2323),(2424);
 
 insert into project.delivery (delivery_status, delivery_address,id_user,id_order) values 
-('in progress','Aerodrom',7,3),
 ('finished','Pintija',8,1),
-('in progress','Kisela Voda',9,2),
-('in progress','Kozle',8,4);
+('finished','Kisela Voda',9,2),
+('finished','Aerodrom',7,3),
+('in progress','Kozle',8,4),
+('finished', 'Radisani',7,5),
+('finished','Dracevo',8,6),
+('in progress','Gjorce',9,7),
+('finished','Novo lisice',7,8),
+('finished','Lisice',8,9),
+('finished', 'Butel',7,10),
+('finished','Pintija',8,11),
+('finished','Kisela Voda',9,12),
+('finished','Radisani',7,13),
+('finished','Nerezi',8,14),
+('finished', 'Aerodrom',7,15),
+('finished', 'Aerodrom',9,16),
+('in progress', 'Novo Lisice',9,17),
+('finished', 'Butel',7,18),
+('in progress', 'Kozle',8,19),
+('finished', 'Aerodrom',9,20),
+('in progress', 'Gjorce',8,21);
+
+
 
 insert into project.price (amount, price_from, id_part) values 
+(3000,now(),1),
 (1000,now(),2),
 (2000,now(),3),
-(3000,now(),1),
 (1500,now(),4),
 (3500,now(),5),
-(300,now(),6);
-
-insert into project.repair (vin, id_repair_shop, id_service_book) values (1111,3,1),(2222,2,2),(3333,1,3),(4444, 2, 4);
-insert into project.repair (vin, id_repair_shop, id_service_book) values (1111,2,1)
-
-insert into project.review (review_rating, id_repair, id_user) values (10, 1, 4), (6, 2, 5), (8, 3, 6), (9, 4, 13);
-insert into project.review (review_rating, id_repair, id_user) values (7, 5, 4)
-
-insert into project.part_is_from_category (id_part, id_category) values (5,5), (1,4), (6,5);
-
-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);
-
-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);
-
-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);
-
-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);
-
-
-
+(300,now(),6),
+(4300,now(),7),
+(900,now(),8),
+(500,now(),9),
+(1300,now(),10),
+(3500,now(),11),
+(3600,now(),12),
+(4500,now(),13),
+(4100,now(),14);
+
+
+insert into project.repair (vin, id_repair_shop, id_service_book) values 
+(4444,1,1),
+(5555,1,2),
+(6666,2,3),
+(1313,2,4),
+(1414,2,5),
+(1515,2,6),
+(1616,3,7),
+(1717,3,8),
+(1818,4,9),
+(1919,4,10),
+(2020,4,11),
+(2121,5,12),
+(2222,2,13),
+(2323,2,14),
+(2424,1,15);
+
+
+insert into project.part_is_from_category (id_part, id_category) values (1,4),(2,7),(3,8),(4,3),(5,5),(6,5),(7,2),(8,1),(9,3),(10,3),(11,6),(12,6),(13,6),(14,6);
+
+insert into project.repair_shop_is_authorized_for_car_make (id_repair_shop, id_car_manufacturer) values (1,1),(2,2),(3,3),(4,4),(5,5);
+
+
+insert into project.review (review_rating, id_repair, id_user) values (10,1,4),(6,2,5),(7,3,6),
+(8,4,13),(9,5,14),(10,6,15),(5,7,16),(3,8,17),(4,9,18),(10,10,19),(9,11,20),(7,12,21),(8,13,22),(9,14,23),(10,15,24);
+
+
+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),
+(7,3,10),(8,2,20),(9,3,50),(10,3,25),(11,1,16),(12,2,24),(13,1,20),(14,2,70);
+
+
+insert into project.part_is_appropriate_for_car (id_part, id_car) values 
+(1,1),(1,2),(2,3),(2,4),(2,5),(2,6),(3,8),(3,11),(3,12),(4,1),(4,2),(4,3),(4,4),(4,5),(4,6),(4,7),(4,8),(5,1),(6,3),(6,4),(7,9),(7,10),
+(7,11),(8,12),(9,1),(9,2),(9,7),(9,8),(10,1),(10,2),(10,12),(11,3),(11,4),(11,5),(12,7),(12,8),(13,6),(14,1),(14,2);
+
+
+
+insert into project.order_contains_part (id_order, id_part, quantity_order) values 
+(1,1,2),(1,4,1),(2,14,1),(3,10,2),(3,5,2),(4,4,3),(4,1,1),(5,1,2),(5,4,1),(5,14,1),(6,11,2),(7,2,2),(7,6,1),(7,11,1),
+(8,2,1),(9,11,2),(10,13,1),(11,9,1),(11,12,1),(12,3,1),(12,4,1),(13,9,2),(14,7,1),(15,7,1),(16,7,1),(17,10,1),(17,8,1),
+(18,2,1),(19,4,1),(19,6,2),(19,11,2),(20,1,2),(21,9,1);
+
+
+
+-- Important Use Cases
 select p.part_name, project.repair.vin,id_repair_shop  from project.repair
 join project.car_sample cs on cs.vin = project.repair.vin
@@ -136 +250 @@
 select part_name from project.part
 
-drop view project.repair_shop_reviews_summary
+-- извештај за сите сервиси и за сите број на reviews и avg рејтинг
+drop view project.repair_shop_reviews_summary;
 
 create or replace view project.repair_shop_reviews_summary as
-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
+select rs.id_repair_shop as rs_id , rs.rs_name as rs_name , count(r2.review_rating) as review_count ,avg(r2.review_rating)::float as review_average
 from project.repair_shop rs 
 left join project.repair r on r.id_repair_shop = rs.id_repair_shop 
@@ -147 +262 @@
 select * from project.repair_shop_reviews_summary
 
+--извештај за сите доставувачи, која е најдобра нарачка што ја направил и кој производ е најскап во таа нарачка
+drop view project.deliveryman_summary
+
+create or replace view project.deliveryman_summary as
+        select nar.deliverer, nar.deliverer_username,
+        max(nar.nar_suma) as najdobra_naracka, max(par.par_suma) as najskap_proizvod 
+        from project.delivery_man d 
+        left join 
+        (
+                select d.id_user as deliverer, ut.username as deliverer_username, d2.id_delivery as delivery_num
+                ,sum(p2.amount*ocp.quantity_order) as nar_suma
+                from project.delivery_man d
+                left join project.users_table ut on ut.id_user = d.id_user 
+                left join project.delivery d2 on d2.id_user = d.id_user 
+                left join project.order_contains_part ocp on ocp.id_order = d2.id_order 
+                left join project.part p on p.id_part = ocp.id_part 
+                left join project.price p2 on p2.id_part = p.id_part
+                group by (d.id_user,ut.username,delivery_num)
+        ) as nar on d.id_user = nar.deliverer
+        left join 
+        (
+                select d.id_user as deliverer, ocp.id_order as order_num,d2.id_delivery as delivery_num, p.part_name as part_name,
+                sum(p2.amount*ocp.quantity_order) as par_suma
+                from project.delivery_man d
+                left join project.users_table ut on ut.id_user = d.id_user 
+                left join project.delivery d2 on d2.id_user = d.id_user 
+                left join project.order_contains_part ocp on ocp.id_order = d2.id_order 
+                left join project.part p on p.id_part = ocp.id_part 
+                left join project.price p2 on p2.id_part = p.id_part
+                group by (d.id_user,ocp.id_order,d2.id_delivery,p.part_name)
+        ) as par on nar.deliverer = par.deliverer and nar.delivery_num = par.delivery_num
+        group by nar.deliverer,nar.deliverer_username 
+        order by najdobra_naracka desc
+
+select * from project.deliveryman_summary
+
+--извештај за сите доставувачи, број на нарачки, вкупна вредност на сите нарачки што ги направил
+drop view project.deliveryman_count_sum
+
+create or replace view project.deliveryman_count_sum as
+        select nar.deliverer, nar.deliverer_username, count(distinct(nar.delivery_num)) as vkupno_dostavi,
+        sum(nar.nar_suma) as vkupna_suma
+        from project.delivery_man d 
+        left join 
+        (
+                select d.id_user as deliverer, ut.username as deliverer_username, d2.id_delivery as delivery_num
+                ,sum(p2.amount*ocp.quantity_order) as nar_suma
+                from project.delivery_man d
+                left join project.users_table ut on ut.id_user = d.id_user 
+                left join project.delivery d2 on d2.id_user = d.id_user 
+                left join project.order_contains_part ocp on ocp.id_order = d2.id_order 
+                left join project.part p on p.id_part = ocp.id_part 
+                left join project.price p2 on p2.id_part = p.id_part
+                group by (d.id_user,ut.username,delivery_num)
+        ) as nar on d.id_user = nar.deliverer
+        group by nar.deliverer,nar.deliverer_username 
+        order by vkupna_suma desc
+
+select * from project.deliveryman_count_sum
+
+-- извештај за сите категории и за кој прозиводител на коли биле купени највеќе делови од таа категорија
+select c.id_category, c.category_name, cm.id_car_manufacturer , cm.cm_name ,
+count(ocp.id_part) as category_by_car
+from project.category as c
+left join project.part_is_from_category pifc on pifc.id_category = c.id_category 
+left join project.part p on p.id_part = pifc.id_part 
+left join project.order_contains_part ocp on ocp.id_part = p.id_part 
+left join project.order_table ot on ot.id_order = ocp.id_order 
+left join project.users_table ut on ut.id_user = ot.id_user 
+left join project.car_sample cs on cs.id_user = ut.id_user 
+left join project.car c2 on c2.id_car = cs.id_car 
+left join project.car_manufacturer cm on cm.id_car_manufacturer = c2.id_car_manufacturer 
+group by c.id_category,cm.id_car_manufacturer  
+order by category_by_car desc
+
+
+-- Извештај за најкупуван артикл, во која количина и од која категорија е истиот
+select c.category_name , p.part_name , coalesce(sum(ocp2.quantity_order),0) as maxkol from project.part p
+        left join project.part_is_from_category pifc on pifc.id_part = p.id_part 
+        left join project.category c on c.id_category = pifc.id_category  
+        left join project.order_contains_part ocp2 on ocp2.id_part = p.id_part 
+        group by c.id_category , p.id_part  
+        having coalesce(sum(ocp2.quantity_order), 0)=(
+                select max(maxkol) from
+                (
+                        select coalesce(sum(ocp2.quantity_order),0) as maxkol from project.part p
+                        left join project.part_is_from_category pifc on pifc.id_part = p.id_part 
+                        left join project.category c on c.id_category = pifc.id_category  
+                        left join project.order_contains_part ocp2 on ocp2.id_part = p.id_part 
+                        group by c.id_category , p.id_part 
+                ) q1
+                )
+
+                
+--Извештај за сите делови, со нивни категории и производители, и количината која била нарачана од овие делови во секој од последните 3 месеци. 
+select p.id_part, p.part_name, cat.category_name, pm.pm_name, f.narachani_vo_ovoj_mesec as narachani_ovoj_mesec, s.narachani_vo_prethoden_mesec as narachani_prethoden_mesec, t.narachani_vo_predprethoden_mesec as narachani_predprethoden_mesec
+from project.part as p
+join project.part_manufacturer as pm on p.id_part_manufacturer = pm.id_part_manufacturer
+join project.part_is_from_category as pifc on p.id_part = pifc.id_part
+join project.category as cat on pifc.id_category = cat.id_category
+left join project.order_contains_part as ocp on p.id_part = ocp.id_part
+left join project.order_table as o on ocp.id_order = o.id_order
+left join (
+select p.id_part, p.part_name, cat.category_name, pm.pm_name,
+       count(o.id_order) as narachani_vo_ovoj_mesec
+from project.part as p
+join project.part_manufacturer as pm on p.id_part_manufacturer = pm.id_part_manufacturer
+join project.part_is_from_category as pifc on p.id_part = pifc.id_part
+join project.category as cat on pifc.id_category = cat.id_category
+left join project.order_contains_part as ocp on p.id_part = ocp.id_part
+left join project.order_table as o on ocp.id_order = o.id_order
+                                  and
+                              extract(month from o.order_date) = extract(month from now()) and extract(year from o.order_date) = extract(year from now())
+group by p.id_part, p.part_name, cat.category_name, pm.pm_name
+) as f on p.id_part = f.id_part and pm.pm_name = f.pm_name
+left join (
+select p.id_part, p.part_name, cat.category_name, pm.pm_name,
+       count(o.id_order) as narachani_vo_prethoden_mesec
+from project.part as p
+join project.part_manufacturer as pm on p.id_part_manufacturer = pm.id_part_manufacturer
+join project.part_is_from_category as pifc on p.id_part = pifc.id_part
+join project.category as cat on pifc.id_category = cat.id_category
+left join project.order_contains_part as ocp on p.id_part = ocp.id_part
+left join project.order_table as o on ocp.id_order = o.id_order
+                                  and
+                              extract(month from o.order_date) = extract(month from now()-interval'1 month') and extract(year from o.order_date) = extract(year from now()-interval'1 month')
+group by p.id_part, p.part_name, cat.category_name, pm.pm_name
+) as s on p.id_part = s.id_part and pm.pm_name = s.pm_name
+left join (
+select p.id_part, p.part_name, cat.category_name, pm.pm_name,
+       count(o.id_order) as narachani_vo_predprethoden_mesec
+from project.part as p
+join project.part_manufacturer as pm on p.id_part_manufacturer = pm.id_part_manufacturer
+join project.part_is_from_category as pifc on p.id_part = pifc.id_part
+join project.category as cat on pifc.id_category = cat.id_category
+left join project.order_contains_part as ocp on p.id_part = ocp.id_part
+left join project.order_table as o on ocp.id_order = o.id_order
+                                  and
+                              extract(month from o.order_date) = extract(month from now()-interval'2 month') and extract(year from o.order_date) = extract(year from now()-interval'2 month')
+group by p.id_part, p.part_name, cat.category_name, pm.pm_name
+) as t on p.id_part = t.id_part and pm.pm_name = t.pm_name
+group by p.id_part, p.part_name, cat.category_name, pm.pm_name, f.narachani_vo_ovoj_mesec, s.narachani_vo_prethoden_mesec, t.narachani_vo_predprethoden_mesec
+
+