Exercise using SQL left join

1. Exercise on LEFT JOIN
2. List of products sold
3. List of quantity sold against each product.
4. List of quantity and total sales against each product
5. List of quantity sold against each product and against each store.
6. List of quantity sold against each Store with total turnover of the store.
7. List of products which are not sold
8. List of customers who have not purchased any product.

1.List of products sold

SELECT product,p_id  FROM `sales` group by product

2.List of quantity sold against each product.

SELECT product,p_id,sum(qty)  FROM `sales` group by product

3. List of quantity and total sales against each product

SELECT a.product,a.p_id,sum(qty), sum(qty*price)  FROM `sales` 
 a LEFT JOIN products b on a.p_id = b.p_id group by product

4. List of quantity sold against each product and against each store.

SELECT product , store , sum(qty)   FROM sales group by product, store

5.List of quantity sold against each Store with total turnover of the store.

SELECT  a.store, sum(qty) total_qty,
sum(b.price * ( a.qty )) AS total_price  FROM sales a
 LEFT JOIN products b ON a.p_id = b.p_id GROUP BY store

SELECT  a.store, MIN(qty) min_qty, b.product,
b.price * MIN(qty) AS total_price  FROM sales a
 LEFT JOIN products b ON a.p_id = b.p_id GROUP BY store;

SELECT  a.store, MAX(qty) max_qty, b.product,
b.price * MAX(qty) AS total_price  FROM sales a
 LEFT JOIN products b ON a.p_id = b.p_id GROUP BY store;

6. List of products which are not sold

SELECT a.product , a.p_id   FROM products  a 
LEFT JOIN sales  b on a.p_id=b.p_id  WHERE b.sale_id is null

7. List of customers who have not purchased any product.

SELECT a.customer, a.c_id from customers a 
LEFT JOIN sales b  on a.c_id=b.c_id WHERE b.sale_id IS NULL