CREATE TABLE Salesman (
    salesman_id INT PRIMARY KEY,
    name VARCHAR(100),
    city VARCHAR(50),
    commission DECIMAL(4, 2)  -- 2 decimal places to represent the commission percentage
);

INSERT INTO Salesman (salesman_id, name, city, commission) VALUES
(5001, 'James Hoog', 'New York', 0.15),
(5002, 'Nail Knite', 'Paris', 0.13),
(5005, 'Pit Alex', 'London', 0.11),
(5006, 'Mc Lyon', 'Paris', 0.14),
(5003, 'Lauson Hen', 'San Jose', 0.12),
(5007, 'Paul Adam', 'Rome', 0.13);

select * from salesman;

CREATE TABLE Orders (
    ord_no INT PRIMARY KEY,
    purch_amt DECIMAL(10, 2),
    ord_date DATE,
    customer_id INT,
    salesman_id INT
);

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

select * from orders;

--1. From the following tables, write a SQL query to find all the orders issued by the salesman 'Paul Adam'. 
-- Return ord_no, purch_amt, ord_date, customer_id and salesman_id.
select purch_amt, ord_date, customer_id, salesman_id from Orders o 
where EXISTS (select salesman_id, name from Salesman s where s.salesman_id = o.salesman_id and name = "Paul Adam");

select * from Orders where salesman_id = (select salesman_id from Salesman where name = "Paul Adam");

select purch_amt, ord_date, customer_id, salesman_id from Orders 
where salesman_id = (select salesman_id from Salesman where name = "Paul Adam");

--2. From the following tables write a SQL query to find all orders generated by London-based 
-- salespeople. Return ord_no, purch_amt, ord_date, customer_id, salesman_id.
select * from Orders where salesman_id IN (select salesman_id from Salesman where city = "London");

--3. From the following tables write a SQL query to find all orders generated by the salespeople who 
-- may work for customers whose id is 3007. Return ord_no, purch_amt, ord_date, customer_id, salesman_id.
select * from Orders where salesman_id IN (select DISTINCT salesman_id from orders where customer_id = 3007);

--4. From the following tables write a SQL query to find the order values greater than the average order 
--value of 10th October 2012. Return ord_no, purch_amt, ord_date, customer_id, salesman_id.
SELECT ord_no, purch_amt, ord_date, customer_id, salesman_id FROM Orders WHERE purch_amt > (SELECT 
avg(purch_amt) FROM Orders WHERE ord_date = '2012-10-10');

-- 5. From the following tables, write a SQL query to find all the orders generated in New York city. 
-- Return ord_no, purch_amt, ord_date, customer_id and salesman_id.
select * from Orders where salesman_id IN (select salesman_id from Salesman where city = "New York");
-- error- Only one expression can be specified in the select list when the subquery is not introduced with EXISTS.
--select * from Orders o where salesman_id IN (select salesman_id, city from Salesman s 
--where s.salesman_id = o.salesman_id and city = "New York");
--correct
select * from Orders o where EXISTS (select salesman_id, city from Salesman s 
where s.salesman_id = o.salesman_id and city = "New York");

--Using full outer join
SELECT o.ord_no, o.purch_amt, o.ord_date, o.customer_id, o.salesman_id, s.city from Orders o JOIN Salesman s 
ON o.salesman_id = s.salesman_id Where city = "New York";

CREATE TABLE Customer (
    customer_id INT PRIMARY KEY,
    cust_name VARCHAR(100),
    city VARCHAR(50),
    grade INT,
    salesman_id INT
);

INSERT INTO Customer (customer_id, cust_name, city, grade, salesman_id) VALUES
(3002, 'Nick Rimando', 'New York', 100, 5001),
(3005, 'Graham Zusi', 'California', 200, 5002),
(3001, 'Brad Guzan', 'London', 100, 5005),
(3004, 'Fabian Johns', 'Paris', 300, 5006),
(3007, 'Brad Davis', 'New York', 200, 5001),
(3009, 'Geoff Camero', 'Berlin', 100, 5003),
(3008, 'Julian Green', 'London', 300, 5002),
(3003, 'Jozy Altidor', 'Moncow', 200, 5007);

select * from customer;
-- 6. From the following tables write a SQL query to determine the commission of the salespeople in 
--Paris.Return commission.
SELECT commission from Salesman WHERE salesman_id IN 
(SELECT salesman_id from Customer where city = 'Paris');

--7. Write a query to display all the customers whose ID is 2001 below the 
--salesperson ID of Mc Lyon.
select customer_id, cust_name from customer where customer_id = (select salesman_id-2001 from salesman where name="Mc Lyon");

--14. Write a query to find the sums of the amounts from the orders table, grouped by date, and eliminate all dates 
--where the sum was not at least 1000.00 above the maximum order amount for that date.

SELECT SUM(purch_amt) AS Total FROM Orders Group by ord_date having purch_amt >= 1000+Max(purch_amt);
by