Table: orderdetail

item_id order_id product_id selling_price discount quantity
1 10001 1 9500 250 1
2 10002 1 9500 400 2
3 10002 2 1900 50 5
4 10003 5 6800 100 1
5 10003 4 990 0 2
6 10003 3 4950 10 1
7 10003 2 1950 25 2
8 10004 3 4960 0 1
9 10005 1 9500 400 5
10 10005 4 990 0 2
11 10005 5 5800 150 1
USE sqlforbi;

CREATE TABLE orderdetail ( item_id int, order_id int, product_id int, selling_price int, discount int, quantity int );

INSERT INTO orderdetail (item_id,order_id,product_id,selling_price,discount,quantity) VALUES ('1', '10001', '1', '9500', '250', '1');
INSERT INTO orderdetail (item_id,order_id,product_id,selling_price,discount,quantity) VALUES ('2', '10002', '1', '9500', '400', '2');
INSERT INTO orderdetail (item_id,order_id,product_id,selling_price,discount,quantity) VALUES ('3', '10002', '2', '1900', '50', '5');
INSERT INTO orderdetail (item_id,order_id,product_id,selling_price,discount,quantity) VALUES ('4', '10003', '5', '6800', '100', '1');
INSERT INTO orderdetail (item_id,order_id,product_id,selling_price,discount,quantity) VALUES ('5', '10003', '4', '990', '0', '2');
INSERT INTO orderdetail (item_id,order_id,product_id,selling_price,discount,quantity) VALUES ('6', '10003', '3', '4950', '10', '1');
INSERT INTO orderdetail (item_id,order_id,product_id,selling_price,discount,quantity) VALUES ('7', '10003', '2', '1950', '25', '2');
INSERT INTO orderdetail (item_id,order_id,product_id,selling_price,discount,quantity) VALUES ('8', '10004', '3', '4960', '0', '1');
INSERT INTO orderdetail (item_id,order_id,product_id,selling_price,discount,quantity) VALUES ('9', '10005', '1', '9500', '400', '5');
INSERT INTO orderdetail (item_id,order_id,product_id,selling_price,discount,quantity) VALUES ('10', '10005', '4', '990', '0', '2');
INSERT INTO orderdetail (item_id,order_id,product_id,selling_price,discount,quantity) VALUES ('11', '10005', '5', '5800', '150', '1');
SELECT * from orderdetail;

SQL statement to display total sales value by order_id?

SELECT a.order_id,sum(a.total_sales) FROM
(SELECT order_id, (selling_price*quantity) AS total_sales FROM orderdetail)a GROUP BY a.order_id;

Write SQL query to display the Top 3 product names by sales

SELECT p.product_name FROM products p JOIN
(SELECT a.product_id,SUM(a.total_sales) as total_sales_value FROM
(SELECT product_id, selling_price*quantity as total_sales FROM orderdetail)a
GROUP BY a.product_id ORDER BY total_sales_value DESC LIMIT 3) b
on p.product_id=b.product_id;
Note: Instead of join we can write IN condition but it is not supported in MySQL due to "Error Code: 1235. This version of MySQL doesn't yet support 'LIMIT & IN/ALL/ANY/SOME subquery' "

Display the customers names and order value in descending order

SELECT c.cust_name,od2.total_sales_value FROM customers c LEFT JOIN orders o ON c.cust_id=o.cust_id
LEFT JOIN (SELECT od1.order_id, sum(od1.total_sales) AS total_sales_value FROM ( SELECT order_id, (selling_price*quantity) AS total_sales FROM orderdetail ) od1 GROUP BY od1.order_id)od2
ON o.order_id=od2.order_id ORDER BY od2.total_sales_value ASC;

Assume there is no item_id field present in the 'orderdetail' table. Write SQL query to display records with highest discount

SELECT od.* FROM orderdetail od
INNER JOIN ( SELECT order_id, MAX(discount) AS highest FROM orderdetail GROUP BY order_id) od1
ON od.discount = od1.highest AND od.order_id = od1.order_id
ORDER BY discount DESC;

Output:

item_id order_id product_id selling_price discount quantity
2 10002 1 9500 400 2
9 10005 1 9500 400 5
1 10001 1 9500 250 1
4 10003 5 6800 100 1
8 10004 3 4960 0 1

Write the SQL query to display best selling product name (highest number of quantity)

SELECT product_name FROM products p
JOIN ( SELECT product_id, SUM(quantity) AS total_quantity FROM orderdetail GROUP BY product_id ORDER BY total_quantity DESC LIMIT 1) od
ON p.product_id=od.product_id;

Output:

laptop
Subscribe
Subscribe