Komby Online requires a database to manage data related to products, customers, orders, and order details.

Database Structure:-

Products Table:- Contains fields for product ID (unique key), product name, price, and stock quantity.

Customers Table:- Includes fields for customer ID (unique key), first name, last name, email address, and address.

Orders Table:- Features fields for order ID (primary key), customer ID (referencing Customers table), order date, and total amount.

OrderDetails Table:-

Stores information about order details, including order details ID (unique key), order ID (referencing Orders table), product ID (referencing Products table), quantity, and subtotal.

 

Write the necessary MySQL queries to perform the below-mentioned tasks:-

1. Retrieve a list of all customers along with their total order count.

2. Find the top 5 best-selling products based on the total quantity sold.

3. Calculate the total revenue generated by the online store.

4. Update the stock quantity for a specific product after a new order is placed.