-- create a table
CREATE TABLE salesman (
  salesman_id INTEGER PRIMARY KEY,
  name TEXT NOT NULL,
  city TEXT NOT NULL,
  comission float(4)
);
-- insert some values
INSERT INTO salesman VALUES (5001, 'James Hoog', 'New York', 0.15);
INSERT INTO salesman VALUES (5002, 'Nail Knite', 'Paris' ,0.13);
INSERT INTO salesman VALUES (5005, 'Pit Alex','London', 0.11);
INSERT INTO salesman VALUES (5006,  'Mc Lyon', 'Paris' ,0.14);
INSERT INTO salesman VALUES (5007,  'Paul Adam', 'Rome' , 0.13);
INSERT INTO salesman VALUES (5003,  'Lauson Hen', 'San Jose' ,0.12);

-- create a table
CREATE TABLE customer (
  customer_id INTEGER PRIMARY KEY,
  customer_name TEXT NOT NULL,
  city TEXT NOT NULL,
  grade INTEGER,
  salesman_id INTEGER
);

-- insert some values
INSERT INTO customer VALUES ( 3002, 'Nick Rimando' ,'New York',  100 ,5001);
INSERT INTO customer VALUES (3007, 'Brad Davis',  'New York' , 200, 5001);
INSERT INTO customer VALUES (3005, 'Graham Zusi', 'California' ,200 ,5002);
INSERT INTO customer VALUES (3008 ,'Julian Green', 'London' , 300, 5002);
INSERT INTO customer VALUES (3004, 'Fabian Johnson' ,'Paris' , 300, 5006);
INSERT INTO customer VALUES (3009, 'Geoff Cameron', 'Berlin',  100, 5003);
INSERT INTO customer VALUES (3003 ,'Jozy Altidor', 'Moscow', 200,  5007);
INSERT INTO customer VALUES (3001, 'Brad Guzan', 'London' , ' ' , 5005);

-- create a table
CREATE TABLE orders (
  ord_no INTEGER PRIMARY KEY,
  purch_amt INTEGER,
  ord_date date,
  customer_id INTEGER,
  salesman_id INTEGER
);

-- insert some values
INSERT INTO orders VALUES (70001, 150.5, '2012-10-05', 3005, 5002);
INSERT INTO orders VALUES (70009, 270.65, '2012-09-10', 3001, 5005);
INSERT INTO orders VALUES (70002, 65.26 ,'2012-10-05' ,3002, 5001);
INSERT INTO orders VALUES (70004, 110.5, '2012-08-17', 3009, 5003);
INSERT INTO orders VALUES (70007, 948.5, '2012-09-10', 3005, 5002);
INSERT INTO orders VALUES (70005, 2400.6, '2012-07-27', 3007, 5001);
INSERT INTO orders VALUES (70008, 5760, '2012-09-10', 3002, 5001);
INSERT INTO orders VALUES (70010, 1983.43, '2012-10-10', 3004, 5006);
INSERT INTO orders VALUES (70003, 2480.4, '2012-10-10', 3009, 5003);
INSERT INTO orders VALUES (70012, 250.45, '2012-06-27', 3008, 5002);
INSERT INTO orders VALUES (70011, 75.29, '2012-08-17', 3003, 5007);
INSERT INTO orders VALUES (70013, 3045.6, '2012-04-25', 3002, 5001);

-- 1. From the following tables write a SQL query to find the salesperson and customer who belong to the same city. Return Salesman, cust_name and city.

SELECT s.name AS Salesman, c.cust_name, c.city
FROM salesman s
JOIN customer c ON s.city = c.city;

-- 2. From the following tables write a SQL query to find those orders where order amount exists between 500 and 2000. Return ord_no, purch_amt, cust_name, city.

SELECT o.ord_no, o.purch_amt, c.cust_name, c.city
FROM orders o
JOIN customer c ON o.customer_id = c.customer_id
WHERE o.purch_amt BETWEEN 500 AND 2000;

-- 3. From the following tables write a SQL query to find the salesperson(s) and the customer(s) he handle. Return Customer Name, city, Salesman, commission. 

SELECT c.cust_name, c.city, s.name AS Salesman, s.comission
FROM customer c
JOIN salesman s ON c.salesman_id = s.salesman_id;

-- 4. From the following tables write a SQL query to find those salespersons who received a commission from the company more than 12%. Return Customer Name, customer city, Salesman, commission

SELECT c.cust_name, c.city, s.name AS Salesman, s.comission
FROM customer c
JOIN salesman s ON c.salesman_id = s.salesman_id
WHERE s.comission > 0.12;

-- 5. From the following tables write a SQL query to find those salespersons do not live in the same city where their customers live and received a commission from the company more than 12%. Return Customer Name, customer city, Salesman, salesman city, commission.

SELECT c.cust_name, c.city AS customer_city, s.name AS Salesman, s.city AS salesman_city, s.comission
FROM customer c
JOIN salesman s ON c.salesman_id = s.salesman_id
WHERE s.city <> c.city AND s.comission > 0.12;

-- 6. From the following tables write a SQL query to find the details of an order. Return ord_no, ord_date, purch_amt, Customer Name, grade, Salesman, commission

SELECT o.ord_no, o.ord_date, o.purch_amt, c.cust_name, c.grade, s.name AS Salesman, s.comission
FROM orders o
JOIN customer c ON o.customer_id = c.customer_id
JOIN salesman s ON o.salesman_id = s.salesman_id;

-- 8. From the following tables write a SQL query to display the cust_name, customer city, grade, Salesman, salesman city. The result should be ordered by ascending on customer_id.  

SELECT c.cust_name, c.city AS customer_city, c.grade, s.name AS Salesman, s.city AS salesman_city
FROM customer c
JOIN salesman s ON c.salesman_id = s.salesman_id
ORDER BY c.customer_id ASC;

-- 9. From the following tables write a SQL query to find those customers whose grade less than 300. Return cust_name, customer city, grade, Salesman, saleman city. The result should be ordered by ascending customer_id

SELECT c.cust_name, c.city AS customer_city, c.grade, s.name AS Salesman, s.city AS salesman_city
FROM customer c
JOIN salesman s ON c.salesman_id = s.salesman_id
WHERE c.grade < 300
ORDER BY c.customer_id ASC;

-- 10. Write a SQL statement to make a report with customer name, city, order number, order date, and order amount in ascending order according to the order date to find that either any of the existing customers have placed no order or placed one or more orders.

SELECT c.cust_name, c.city AS customer_city, o.ord_no, o.ord_date, o.purch_amt
FROM customer c
LEFT JOIN orders o ON c.customer_id = o.customer_id
ORDER BY o.ord_date ASC;

-- 11. Write a SQL statement to make a report with customer name, city, order number, order date, order amount salesman name and commission to find that either any of the existing customers have placed no order or placed one or more orders by their salesman or by own.

SELECT c.cust_name, c.city AS customer_city, o.ord_no, o.ord_date, o.purch_amt, s.name AS Salesman, s.comission
FROM customer c
LEFT JOIN orders o ON c.customer_id = o.customer_id
LEFT JOIN salesman s ON c.salesman_id = s.salesman_id
ORDER BY o.ord_date ASC;

-- 12.Write a SQL statement to make a list in ascending order for the salesmen who works either for one or more customer or not yet join under any of the customers.

SELECT s.name AS Salesman
FROM salesman s
LEFT JOIN customer c ON s.salesman_id = c.salesman_id
GROUP BY s.salesman_id
ORDER BY s.name ASC;

-- 13. From the following tables write a SQL query to list all salespersons along with customer name, city, grade, order number, date, and amount.

SELECT s.name AS Salesman, c.cust_name, c.city AS customer_city, c.grade, o.ord_no, o.ord_date, o.purch_amt
FROM salesman s
LEFT JOIN customer c ON s.salesman_id = c.salesman_id
LEFT JOIN orders o ON c.customer_id = o.customer_id;

-- 14. Write a SQL statement to make a list for the salesmen who either work for one or more customers or yet to join any of the customer. The customer may have placed, either one or more orders on or above order amount 2000 and must have a grade, or he may not have placed any order to the associated supplier.

SELECT s.name AS Salesman
FROM salesman s
LEFT JOIN customer c ON s.salesman_id = c.salesman_id
LEFT JOIN orders o ON c.customer_id = o.customer_id WHERE o.purch_amt >= 2000 AND c.grade IS NOT NULL
GROUP BY s.salesman_id;

-- 15. Write a SQL statement to make a report with customer name, city, order no. order date, purchase amount for those customers from the existing list who placed one or more orders or which order(s) have been placed by the customer who is not on the list.

SELECT c.cust_name, c.city AS customer_city, o.ord_no, o.ord_date, o.purch_amt
FROM customer c
LEFT JOIN orders o ON c.customer_id = o.customer_id
WHERE o.ord_no IS NOT NULL OR c.customer_id NOT IN (SELECT customer_id FROM orders);

-- 16. Write a SQL statement to make a report with customer name, city, order no. order date, purchase amount for only those customers on the list who must have a grade and placed one or more orders or which order(s) have been placed by the customer who is neither in the list nor have a grade.

SELECT c.cust_name, c.city AS customer_city, o.ord_no, o.ord_date, o.purch_amt
FROM customer c
LEFT JOIN orders o ON c.customer_id = o.customer_id
WHERE (c.grade IS NOT NULL AND o.ord_no IS NOT NULL) OR (c.customer_id NOT IN (SELECT customer_id FROM customer WHERE grade IS NOT NULL));