SALESMAN vjezba

CREATE TABLE salesman (
    salesman_id INT PRIMARY KEY,
    name VARCHAR(50),
    city VARCHAR(50),
    commission DECIMAL(4,2)
    );
    
    INSERT INTO salesman 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),
    (5007, 'Paul Adam', 'Rome', 0.13),
    (5003, 'Lauson Hen', 'San Jose', 0.12);
    


CREATE TABLE customer (
    customer_id INT PRIMARY KEY,
    cust_name VARCHAR(50),
    city VARCHAR(50),
    grade INT,
    salesman_id INT,
    FOREIGN KEY (salesman_id)
    REFERENCES salesman(salesman_id)
    );
    
    INSERT INTO customer VALUES
    (3002, 'Nick Rimando', 'New York', 100, 5001),
    (3007, 'Brad Davis', 'New York', 200, 5001),
    (3005, 'Graham Zusi', 'California', 200, 5002),
    (3008, 'Julian Green', 'London', 300, 5002),
    (3004, 'Fabian Johnson', 'Paris', 300, 5006),
    (3009, 'Geoff Cameron', 'Berlin', 100, NULL);
    

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


SELECT c.cust_name AS "Customer Name",
c.city AS "Customer City",
s.name AS Salesman,
s.commission
FROM salesman s
INNER JOIN customer c
ON s.salesman_id = c.salesman_id
WHERE s.commission > 0.12;



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



SELECT s.name AS Salesman,
c.cust_name,
s.city AS salesman_city,
c.city AS customer_city,
c.grade
FROM salesman s 
CROSS JOIN customer c 
WHERE s.city <> c.city;



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