Table: orders

order_id cust_id order_date ship_date payment_mode created_date
10001 1 2022-01-15 22:15:00 2022-01-16 09:15:00 Credit Card 15/01/22
10002 1 2022-01-24 04:15:43 2022-01-26 15:00:15 Net Banking 24/01/22
10003 4 2022-01-30 08:15:32 2022-01-30 13:15:00 Cash on Delivery 30/01/22
10004 5 2022-02-13 21:20:00 2022-02-17 14:09:10 Credit Card 13/02/22
10005 3 2022-02-14 18:00:14 2022-02-14 18:10:10 Credit Card 14/02/22
USE sqlforbi;

CREATE TABLE orders ( order_id int, cust_id int, order_date datetime, ship_date datetime, payment_mode varchar(50), created_date datetime );

INSERT INTO orders (order_id,cust_id,order_date,ship_date,payment_mode,created_date) VALUES ('10001', '1', '2022-01-15 22:15:00', '2022-01-16 09:15:00', 'Credit Card', '15/01/22');
INSERT INTO orders (order_id,cust_id,order_date,ship_date,payment_mode,created_date) VALUES ('10002', '1', '2022-01-24 04:15:43', '2022-01-26 15:00:15', 'Net Banking', '24/01/22');
INSERT INTO orders (order_id,cust_id,order_date,ship_date,payment_mode,created_date) VALUES ('10003', '4', '2022-01-30 08:15:32', '2022-01-30 13:15:00', 'Cash on Delivery', '30/01/22');
INSERT INTO orders (order_id,cust_id,order_date,ship_date,payment_mode,created_date) VALUES ('10004', '5', '2022-02-13 21:20:00', '2022-02-17 14:09:10', 'Credit Card', '13/02/22');
INSERT INTO orders (order_id,cust_id,order_date,ship_date,payment_mode,created_date) VALUES ('10005', '3', '2022-02-14 18:00:14', '2022-02-14 18:10:10', 'Credit Card', '14/02/22'); select * from orders;

How many payment methods has been used by customers?

SELECT COUNT(DISTINCT (payment_mode)) FROM orders;

How many days it took to ship the order?

SSELECT order_id, DATEDIFF(ship_date,order_date) as days_to_ship FROM orders;

Which customer has placed highest number of orders?

SELECT cust_id,COUNT(*) FROM orders GROUP BY cust_id ORDER BY COUNT(*) DESC LIMIT 1;

Display the customer name name who has placed highest number of orders

SELECT cust_name from customers where cust_id=(SELECT cust_id FROM orders GROUP BY cust_id ORDER BY COUNT(*) DESC LIMIT 1);

Output:

Ram

Write the SQL statement to display all customers who has not placed any order

Medthod 1: SELECT c.cust_name FROM customers c LEFT JOIN orders o ON c.cust_id = o.cust_id WHERE o.cust_id is NULL;
Method 2: SELECT c.cust_name FROM customers c WHERE NOT EXISTS (SELECT 1 FROM orders o WHERE c.cust_id = o.cust_id);
Method 3: SELECT c.cust_name FROM customers c WHERE c.cust_id NOT IN (SELECT o.cust_id FROM orders o);

Subscribe
Subscribe