Changes between Initial Version and Version 1 of SQL

Timestamp:

09/14/21 15:28:44 (4 years ago)

Author:

161029

Comment:

--

SQL

@@ +1 @@
+= Напредни извештаи од базата (SQL и складирани процедури) =
+
+Поглед за наоѓање на најнарачуваниот прозвод од категоријата Витамини
+//
+
+//
+
+{{{
+#!python
+    create view najnaracuvan_proizvod_od_vitamini as 
+    select order_item.product_id as "product id", product.name as "product name", sum(quantity) as "product quantity"
+    from order_item
+    join product on order_item.product_id = product.id
+    join product_category on product.category_id = product_category.id 
+    where product_category.category_name = 'Vitamins'
+    group by order_item.product_id, name
+    order by "product quantity" desc
+    limit 1
+}}}
+
+//
+
+Поглед за наоѓање на најпрофитабилниот клиент во аптеката
+//
+
+//
+
+{{{
+#!python
+    create view najprofitabilen_klient as 
+    select customer.id, customer.first_name, customer.last_name, sum(product.unit_price * order_item.quantity) as "total"
+    from orders
+    join order_item on orders.id = order_item.order_id 
+    join product on order_item.product_id = product.id
+    join customer on customer.id = orders.customer_id
+    group by customer.id, customer.first_name, customer.last_name
+    order by total desc
+    limit 1
+}}}
+//
+