Exercise using SQL left join

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 )

You can download SQL Dump of these three tables here.

From the above data we will produce following reports.
  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
productp_id
CPU4
Monitor3
RAM2

2.List of quantity sold against each product.

SELECT product,p_id,sum(qty)  FROM `sales` group by product
productp_idsum(qty)
CPU41
Monitor312
RAM27

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
productp_idsum(qty)sum(qty*price)
CPU4155
Monitor312900
RAM27630

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

SELECT product , store , sum(qty)   FROM sales group by product, store
productstoresum(qty)
CPUDEF1
MonitorABC10
MonitorDEF2
RAMABC3
RAMDEF4

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
storetotal_qtytotal_price
ABC131020
DEF7565

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
productp_id
Hard Disk1
Keyboard5
Mouse6
Motherboard7
Power supply8

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
customerc_id
King5
Ronn7
Jem8
Tom9

You can download SQL Dump of these three tables here.

Your Rating




Post your comments , suggestion , error , requirements etc here .








We use cookies to improve your browsing experience. . Learn more
HTML MySQL PHP JavaScript ASP Photoshop Articles FORUM . Contact us
©2000-2019 plus2net.com All rights reserved worldwide Privacy Policy Disclaimer