Changes between Version 3 and Version 4 of Advanced database reports (SQL and Stored procedures)

Timestamp:

12/21/22 16:54:12 (2 years ago)

Author:

201101

Comment:

--

Advanced database reports (SQL and Stored procedures)

@@ -17 +17 @@
 == Вкупен број на настани во последните 6 месеци, вкупен број на гости на настаните, просечна цена на настан по гостин и вкупен промет од сите настани
 
+{{{#!sql
 select   count(e.event_id) as num_events,  
          sum(e.number_of_interested_guests) as total_guests, 
@@ -28 +29 @@
 join events e on
 rfe.event_id  = e.event_id 
+}}}
 
 == Вкупен број на активности во последната година, вкупен број на гости на активностите, просечна цена на активност по гостин и вкупен промет од сите активности
 
+{{{#!sql
+select   count(a.activity_id) as num_activities,  
+         sum(a.number_of_interested_guests) as total_guests, 
+         avg(a.price) as avg_price,
+         sum(a.price * a.number_of_interested_guests) as sales
+from reservation_for_activity rfa 
+join reservation r on
+rfa.reservation_id = r.reservation_id
+and 
+(start_date between now() - interval '1 year' and now())
+join activities a  on
+rfa.activity_id  = a.activity_id
+}}}
+
 == Просечна плата на секој тип на вработени во Вила Дихово
+
+{{{#!sql
+select 
+        (
+          select count(w.user_id)
+          from waiter w
+        )as waiters_in_villa_dihovo,
+        (
+          select avg(w.salary)
+          from waiter w
+        )as average_salary_waiter,
+        (
+          select count(r.user_id) 
+          from receptionist r
+        )as receptionists_in_villa_dihovo,
+        (
+          select avg(r.salary)
+          from receptionist r 
+        )as average_salary_receptionist,
+        (
+          select count(c.user_id)
+          from chef c
+        ) as chefs_in_villa_dihovo,
+        (
+          select avg(c.salary)
+          from chef c
+        )as average_salary_chef
+
+from user_table ut
+group by 1,2,3;
+}}}
 
 == Број на соби во секоја вила
 
+{{{#!sql
+select v.name as villa_name ,
+       count(r.room_id) as number_of_rooms
+    from villa v
+    join rooms r on
+    r.villa_id  = v.villa_id 
+    group by villa_name 
+    order by villa_name
+}}}
+
 == Нарачки за храна кој ги има направено еден корисник и вкупна сума на плаќање 
+
+{{{#!sql
+select pm.user_id,
+       ut.full_name,
+       count(ut.full_name) as broj_naracki_hrana, 
+       sum(rfpm.quantity * m.price) as total_sum
+from prepared_meal pm 
+join user_table ut on
+pm.user_id  = ut.user_id 
+join meal m on
+pm.meal_id  = m.meal_id 
+join reservation_for_prepared_meal rfpm on
+rfpm.prepared_meal_id  = pm.prepared_meal_id 
+join reservation r on
+rfpm.reservation_id  = r.reservation_id 
+group by 1,2
+}}}