Write SQL statements to find the following items based on sa

Write SQL statements to find the following items based on salesman/customer/order tables:

List of salesmen and customers assigned to them along with their cities for the salesmen and customers who belongs to same city.

List of customer names and cities along with their orders (order no, purchase amount) for those orders with purchase amounts between 500 and 2000.

List of customers assigned to a salesman whose commission is more than 12%.

Sample table: salesman salesman _id name city Commission 50 01 50 02 James Hoog Nail Knite Pit Alex New York Paris 0.15 0.13 5005 London 0.11 50 06 50 03 MC Lyon Lawson Hen Paris 0.14 0.12 50 07 Paul Adam Rome 0.13 Sample table : customer customer name city grade salesman.As 30 02 Nick Rimando New York 100 5001 3005 3001 Graham Zusi Brad Guzan California 200 50 02 30 04 30 07 Fabian Johns Paris Brad Davi New York 300 200 50 06 5001 3009 3008 Geoff Camero Berlin Julian Green London 100 300 5002 30 03 Jozy Altidor Moncow 200 50 07 Sample table : order order no purchamtOrder date customer id salesman id 70001 150.5 3005 2016-10-05 2016-09-10 2016-10-05 5002 70009 70002 70004 70007 70005 70008 70010 70003 70012 70011 270.65 65.26 3001 3002 5001 110.5 2016-08-17 3009 948.5 2400.6 2016-09-10 3005 2016-07-27 3007 2016-09-10 3002 2016-10-10 3004 2016-10-10 3009 2016-06-27 3008 2016-08-17 3003 5002 5001 5760 1983.43 2480. 4 250.45 75.29 5001 5006 5002 5007

Solution

1.

    SELECT salesman.name AS \"Salesman\",
    customer.cust_name, customer.city
    FROM salesman,customer
    WHERE salesman.city=customer.city;
  
2.

    SELECT a.ord_no,a.purch_amt,
    b.cust_name,b.city
    FROM orders a,customer b
    WHERE a.customer_id=b.customer_id
    AND a.purch_amt BETWEEN 500 AND 2000;
  
3.

    SELECT a.cust_name AS \"Customer Name\",
    a.city, b.name AS \"Salesman\", b.commission
    FROM customer a
    INNER JOIN salesman b
    ON a.salesman_id=b.salesman_id
    WHERE b.commission>.12;