-- write a SQL query to find the salespersons and customers who live in same city. Return customer name, salesperson name and salesperson city.
SELECT customer.cust_name,salesman.name,salesman.city 
FROM salesman,customer
WHERE salesman.city = customer.city;

-- write a SQL query to find all the customers along with the salesperson who works for them. Return customer name, and salesperson name.
SELECT customer.cust_name,salesman.name
FROM customer,salesman
WHERE salesman.salesman_id =custmer.salesman_id;

-- write a SQL query to find those salespeople who generated orders for their customers but not located in the same city. Return ord_no, cust_name, customer_id (orders table), salesman_id (orders table).
SELECT cust_name,orders.salesman_id,ord_no,orders.customer_id
FROM salesman,customer,orders
WHERE salesman.city <> customer.city
AND orders.salesman_id = salesman.salesman_id
AND orders.customer_id = customer.customer_id;

-- write a SQL query to find those orders made by customers. Return order number, customer name.

SELECT orders.ord_no,customer.cust_name
FROM orders,customer
WHERE orders.customer_id=customer.customer_id;

-- From the following tables, write a SQL query to find those customers where each customer has a grade and served by at least a salesperson who belongs to a city. Return cust_name as "Customer", grade as "Grade" and order_no as "Order No.".
SELECT customer.cust_name AS "Customer",customer.grade AS "Grade"
FROM customer,salesman, orders
WHERE orders.salesman_id=salesman.salesman_id
AND orders.customer_id = customer.customer_id
AND customer.city IS NOT NULL
AND salesman.city IS NOT NULL;

-- From the following table, write a SQL query to find those customers who served by a salesperson and the salesperson works at the commission in the range 12% to 14% (Begin and end values are included.). Return cust_name AS "Customer", city AS "City".
SELECT customer.cust_name as "Customer",Customer.city AS "CITY",salesman.name AS "Salesman",salesman.commission
FROM salesman,customer 
WHERE customer.salesman_id= salesman.salesman_id
AND salesman.commission BETWEEN 0.12 AND 0.14;

-- write a SQL query to find those orders executed by the salesperson, ordered by the customer whose grade is greater than or equal to 200. Compute purch_amt*commission as "Commission". Return customer name, commission as "Commission%" and Commission.
SELECT ord_no, cust_name, commission AS "Commission%", purch_amt*commission AS "Commission" FROM salesman,orders,customer WHERE orders.customer_id = customer.customer_id AND orders.salesman_id = salesman.salesman_id AND customer.grade>=200;

-- write a SQL query to find those customers who made orders on October 5, 2012. Return customer_id, cust_name, city, grade, salesman_id, ord_no, purch_amt, ord_date, customer_id and salesman_id.
SELECT *
FROM customer a,orders  b 
WHERE a.customer_id=b.customer_id 
AND b.ord_date='2012-10-05';

--