1
\$\begingroup\$

I have three primary tables:

  1. Customer
  2. Product
  3. 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?

\$\endgroup\$
  • 1
    \$\begingroup\$ "Have I written the correct query"? Did you test it out? Did you get the results you expected? \$\endgroup\$ – Mat Mar 22 '16 at 12:31
  • \$\begingroup\$ I am getting the output but I am not sure if this is correct. I saw one or two solution using joins. So I am confused that whether the query I have written is correct or not \$\endgroup\$ – Unbreakable Mar 22 '16 at 16:46
1
\$\begingroup\$

I am getting the output but I am not sure if this is correct. I saw one or two solution using joins So I am confused that whether the query I have written is correct or not

It is a good practice to use JOIN : Explicit vs implicit SQL joins.

Second you could use aliases so you do not have to type entire table name.

SELECT  SUM(po.quantity)         AS TotalQuantity
       ,SUM(po.quantity*p.price) AS TotalSaleAmount 
FROM productorder po
JOIN product p
  ON po.ProductId = p.ProductId 
JOIN `order` o
  ON o.OrderId = po.OrderId 
WHERE o.OrderDate = CURDATE();

SqlFiddleDemo

| improve this answer | |
\$\endgroup\$

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.