Version 3 (modified by 8 months ago) ( diff ) | ,
---|
Напредни извештаи од базата (SQL и складирани процедури)
Извештај за сите продукти како и нивното име кои се доставени во последните 3 месеци
select count(order_table.order_id), product.product_name from order_table left join order_table_contains_product on order_table_contains_product.order_id = order_table.order_id left join product on product.product_id = order_table_contains_product.product_id where order_table.order_status = 'Delivered' and order_table.order_date between now() - interval '3 months' and now() group by product.product_name
Извештај за сите категории, нивните најмногу нарачани продукти и бројот на нарачки за продуктот
select category.category_name as Category, product.product_name as Product_Name, count(order_table_contains_product.product_id) as Number_of_orders from category join product_is_in_category on category.category_id = product_is_in_category.category_id join product on product_is_in_category.product_id = product.product_id join order_table_contains_product on product.product_id = order_table_contains_product.product_id group by category.category_id, category.category_name, product.product_name having count(order_table_contains_product.product_id) = ( select count(*) from order_table_contains_product join product on order_table_contains_product.product_id = product.product_id join product_is_in_category on product.product_id = product_is_in_category.product_id where product_is_in_category.category_id = category.category_id group by order_table_contains_product.product_id )
Note:
See TracWiki
for help on using the wiki.