I have three primary tables:
- Customer
- Product
- Order
Relations:
Many to many between product and order.
CREATE TABLE Customer (
CustomerId int NOT NULL AUTO_INCREMENT,
CustomerName varchar(50) NOT NULL,
CustomerMail varchar(50) NOT NULL,
CONSTRAINT Customer_pk PRIMARY KEY (CustomerId)
);
-- Table `Order`
CREATE TABLE `Order` (
OrderId int NOT NULL AUTO_INCREMENT,
OrderDate date NOT NULL,
CustomerId int NOT NULL,
CONSTRAINT Order_pk PRIMARY KEY (OrderId)
);
-- Table Product
CREATE TABLE Product (
ProductId int NOT NULL AUTO_INCREMENT,
ProductName varchar(50) NOT NULL,
SKU varchar(50) NOT NULL,
Price decimal(8,2) NOT NULL,
CONSTRAINT Product_pk PRIMARY KEY (ProductId)
);
-- Table ProductOrder
CREATE TABLE ProductOrder (
ProductOrderId int NOT NULL AUTO_INCREMENT,
OrderId int NOT NULL,
ProductId int NOT NULL,
Quantity int NOT NULL,
CONSTRAINT ProductOrder_pk PRIMARY KEY (ProductOrderId)
);
ALTER TABLE `Order` ADD CONSTRAINT Order_Customer FOREIGN KEY Order_Customer (CustomerId)
REFERENCES Customer (CustomerId);
-- Reference: ProductOrder_Order (table: ProductOrder)
ALTER TABLE ProductOrder ADD CONSTRAINT ProductOrder_Order FOREIGN KEY ProductOrder_Order (OrderId)
REFERENCES `Order` (OrderId);
-- Reference: ProductOrder_Product (table: ProductOrder)
ALTER TABLE ProductOrder ADD CONSTRAINT ProductOrder_Product FOREIGN KEY ProductOrder_Product (ProductId)
REFERENCES Product (ProductId);
I need to write a query for:
Create a query for getting the total quantity of products
ordered and the total value for all orders made
on the current date.
This is my solution:
-- query for current date
SELECT SUM(quantity) AS TotalQuantity, SUM(quantity * price) AS TotalSaleAmount
FROM productorder, product, `order`
WHERE productorder.ProductId = product.ProductId
AND
`order`.OrderId = productorder.OrderId
AND
`order`.OrderDate = CURDATE()
Can someone help me in optimizing it?