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 )
LEFT JOIN ( Basic Query) 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.

  • Video Tutorial on all SQL JOINS


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
LEFT JOIN ( Basic Query) You can download SQL Dump of these three tables here. SELECT Query LEFT JOIN using Multiple Tables
Exercise : Sales - Agent using table JOIN and Date functions

Subscribe

* indicates required
Subscribe to plus2net

    plus2net.com






    Post your comments , suggestion , error , requirements etc here





    SQL Video Tutorials










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