Example of SQL left join by using Product sales and customer tables

There are three tables in total

customers
c_id ,
customer
( 8 records)

products
p_id,
product and price
( 8 records )

sales
sale_id,
c_id ( customer id ), p_id (product_id), qty ( quantity sold) ,store ( name )

LEFT JOIN ( Basic Query) You can download SQL Dump of these three tables here.
From the above data we will produce following reports.

Exercise on LEFT JOIN
List of products sold
List of quantity sold against each product.
List of quantity and total sales against each product
List of quantity sold against each product and against each store.
List of quantity sold against each Store with total turnover of the store.
List of products which are not sold
List of customers who have not purchased any product.

Joining tables Using SQL LEFT Join, RIGHT Join and Inner Join

1.List of products sold

SELECT product,p_id  FROM `sales` group by product

product	p_id
CPU	4
Monitor	3
RAM	2

2.List of quantity sold against each product.

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

product	p_id	sum(qty)
CPU	4	1
Monitor	3	12
RAM	2	7

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

product	p_id	sum(qty)	sum(qty*price)
CPU	4	1	55
Monitor	3	12	900
RAM	2	7	630

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

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

product	store	sum(qty)
CPU	DEF	1
Monitor	ABC	10
Monitor	DEF	2
RAM	ABC	3
RAM	DEF	4

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

store	total_qty	total_price
ABC	13	1020
DEF	7	565

Using MIN Query with JOIN

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;

store	min_qty	product	total_price
ABC	2	Monitor	150
DEF	1	CPU	55

Using MAX Query with JOIN

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;

store	max_qty	product	total_price
ABC	3	Monitor	225
DEF	2	CPU	110

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

product	p_id
Hard Disk	1
Keyboard	5
Mouse	6
Motherboard	7
Power supply	8

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

customer	c_id
King	5
Ronn	7
Jem	8
Tom	9

LEFT JOIN ( Basic Query) Download SQL Dump of these three tables here. Exercise II : Stock report from Purchase and sales tables .
SELECT Query LEFT JOIN using Multiple Tables
Exercise : Sales - Agent using table JOIN and Date functions

Subscribe to our YouTube Channel here

Exercise using SQL left join

1.List of products sold

2.List of quantity sold against each product.

3. List of quantity and total sales against each product

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

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

6. List of products which are not sold

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

Subscribe