Changes between Version 5 and Version 6 of Advanced database reports (SQL and Stored procedures)

Timestamp:

12/28/22 13:02:13 (2 years ago)

Author:

201101

Comment:

--

Advanced database reports (SQL and Stored procedures)

@@ -80 +80 @@
 }}}
 
-== Број на соби во секоја вила
-
-{{{#!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
-}}}
 
 == Нарачки за храна кој ги има направено еден корисник и вкупна сума на плаќање 
@@ -127 +116 @@
 limit 1;
 }}}
+
+== Извештај за преглед на популарен тип на соба
+
+{{{#!sql
+select distinct r.room_type,
+ (
+        select count(r2.reservation_id)
+        where r.room_type = 'Single'
+ ) as single_room_reservation,
+ (
+        select count(r2.reservation_id)
+        where r.room_type = 'Double'
+ )as double_room_reservation
+from rooms r
+join reservation r2 
+on r.room_id  = r2.room_id 
+group by 1;
+}}}
+
+== Преглед на вкупниот број резервации кои ги има направено секој од корисниците
+
+{{{#!sql
+select ut.full_name , sum(gmr.reservation_id) as number_reservations
+from user_table ut
+join guests g 
+on ut.user_id  = g.user_id 
+join guests_make_reservation gmr 
+on g.user_id  = gmr.user_id 
+join reservation r
+on gmr.reservation_id = r.reservation_id
+group by 1;
+}}}
+
+== Број на соби во секоја вила
+
+{{{#!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
+}}}