--create database SQLHW

use SQLHW

--select * from Customer 
--select * from Orders
--select * from Salesman

--Q1 From the following tables write a SQL query to find the salesperson and customer who reside in the same city. Return Salesman, cust_name and city.
select Salesman.[name], Customer.[cust_name], Salesman.[city] 
from Salesman
inner JOIN Customer ON [Customer].[city] = [Salesman].[city];

--Same Salesman_ID?
select  Salesman.[name], Customer.[cust_name], Salesman.[city] 
from Salesman
inner JOIN Customer ON [Customer].[salesman_id] = [Salesman].[salesman_id]
where [Customer].[city] = [Salesman].[city];



--New York: Customers = Brad Davis, Nick Rimando
--New York: Salesman = James Hoog

--Paris: Customers = Fabian Johnson
--Paris: Salesman = Mc Lyon, Nail Knite

--London: Customers = Brad Guzan, Julian Green
--London: Salesman = Pit Alex


--Q2 From the customer and order tables write a SQL query to find those orders where the order amount exists between 500 and 2000. Return ord_no, purch_amt, cust_name, city.
select Orders.[ord_no], Orders.[purch_amt], Customer.[cust_name],Customer.[city] from Customer
inner JOIN Orders ON [Orders].[customer_id] = [customer].[customer_id]
Where Orders.[purch_amt] > 500 AND Orders.[purch_amt] < 2000;

--ord_no	purch_amt	cust_name	city
--70007	948.5	Graham Zusi	California
--70010	1983.43	Fabian Johnson	Paris

--Q3 From the following 3 tables write a SQL query to find the salesperson(s) and the customer(s) he represents. Return Customer Name, city, Salesman, commission.
select distinct Salesman.[name], Customer.[cust_name], Customer.[city], Salesman.[commission] from Salesman
inner JOIN Customer ON [dbo].[Customer].[salesman_id] = [dbo].[Salesman].[salesman_id];

--name	cust_name	city	commission	
--James Hoog	Brad Davis	New York	0.15	
--James Hoog	Nick Rimando	New York	0.15	
--Lauson Hen	Geoff Cameron	Berlin	0.12	
--Mc Lyon   	Fabian Johnson	Paris	0.14	
--Nail Knite	Graham Zusi	California	0.13	
--Nail Knite	Julian Green	London	0.13	
--Paul Adam 	Jozy Altidor	Moscow	0.13	
--Pit Alex  	Brad Guzan	London	0.11	

--Q4 From the following tables write a SQL query to find salespeople who received commissions of more than 12 percent from the company. Return Customer Name, customer city, Salesman, commission.  
select  Salesman.[name], Customer.[cust_name], Customer.[city], Salesman.[commission] from Salesman
inner JOIN Customer ON [dbo].[Customer].[salesman_id] = [dbo].[Salesman].[salesman_id]
where Salesman.[commission] > 0.12;

--name	cust_name	city	commission
--James Hoog	Nick Rimando	New York	0.15
--James Hoog	Brad Davis	New York	0.15
--Nail Knite	Graham Zusi	California	0.13
--Nail Knite	Julian Green	London	0.13
--Mc Lyon   	Fabian Johnson	Paris	0.14
--Paul Adam 	Jozy Altidor	Moscow	0.13


--Q5 From the following tables write a SQL query to locate those salespeople who do not live in the same city where their customers live and have received a commission of more than 12% from the company. Return Customer Name, customer city, Salesman, salesman city, commission.  
select  Salesman.[name],Salesman.[city] ,Salesman.[commission],Customer.[cust_name], Customer.[city]  from Salesman
inner JOIN Customer ON  [Customer].[salesman_id] = [Salesman].[salesman_id]
where Salesman.[commission] > 0.12 AND Salesman.[City] <> Customer.[city];

--name	city	commission	cust_name	city
--Nail Knite	Paris	0.13	Graham Zusi	California
--Nail Knite	Paris	0.13	Julian Green	London
--Paul Adam 	Rome	0.13	Jozy Altidor	Moscow