Version 6 (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 )
Извештај за сите продукти кои се нарачани во последните 6 месеци, бројот на критики и нивната просечна оценка
select category.category_name as category, product.product_name as product_name, count(review.review_rating) as number_of_reviews, avg(review.review_rating) as average_rating 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 join order_table on order_table_contains_product.order_id = order_table.order_id join review on product.product_id = review.product_id where order_table.order_date between now() - interval '6 months' and now() group by category.category_name, product.product_name order by category, product_name,number_of_reviews, average_rating;
Извештај за 10те најактивни корисници со најголем број нарачки
select customer.user_id as customer_id, user_table.name_user as customer_name, count(order_table.order_id) as number_of_orders from customer join order_table on customer.user_id = order_table.customer_id join user_table on customer.user_id = user_table.user_id group by customer.user_id, user_table.name_user order by number_of_orders desc limit 10;
Note:
See TracWiki
for help on using the wiki.