NEW.txt

CREATE TABLE SALESMAN(
SALESMAN_ID NUMBER,
NAME VARCHAR(50),
CITY VARCHAR(50),
COMMISSION FLOAT
);
SELECT * FROM SALESMAN;
INSERT INTO SALESMAN(SALESMAN_ID,NAME, CITY, COMMISSION) VALUES (5001,'James Hoog','New York',0.15);
INSERT INTO SALESMAN(SALESMAN_ID,NAME, CITY, COMMISSION) VALUES (5002,'Nail Knite','Paris',0.13);
INSERT INTO SALESMAN(SALESMAN_ID,NAME, CITY, COMMISSION) VALUES (5005,'Pit Alex','London',0.11);
INSERT INTO SALESMAN(SALESMAN_ID,NAME, CITY, COMMISSION) VALUES (5006,'Mc Lyon','Paris',0.14);
INSERT INTO SALESMAN(SALESMAN_ID,NAME, CITY, COMMISSION) VALUES (5007,'Paul Adam','Rome',0.13);
INSERT INTO SALESMAN(SALESMAN_ID,NAME, CITY, COMMISSION) VALUES (5003,'Lauson Hen','San Jose',0.12);

CREATE TABLE CUST_ALL(
CUSTOMER_ID NUMBER,
CUST_NAME VARCHAR(50),
CITY VARCHAR(50),
GRADE NUMBER,
SALESMAN_ID NUMBER
);

INSERT INTO CUST_ALL(CUSTOMER_ID,CUST_NAME, CITY, GRADE, SALESMAN_ID) VALUES (3002,'Nick Rimando','New York',100,5001);
INSERT INTO CUST_ALL(CUSTOMER_ID,CUST_NAME, CITY, GRADE, SALESMAN_ID) VALUES (3007,'Brad Davis','New York',200,5001);
INSERT INTO CUST_ALL(CUSTOMER_ID,CUST_NAME, CITY, GRADE, SALESMAN_ID) VALUES (3005,'Graham Zusi','California',200,5002);
INSERT INTO CUST_ALL(CUSTOMER_ID,CUST_NAME, CITY, GRADE, SALESMAN_ID) VALUES (3008,'Julian Green','London',300,5002);
INSERT INTO CUST_ALL(CUSTOMER_ID,CUST_NAME, CITY, GRADE, SALESMAN_ID) VALUES (3004,'Fabian Johnson','Paris',300,5006);
INSERT INTO CUST_ALL(CUSTOMER_ID,CUST_NAME, CITY, GRADE, SALESMAN_ID) VALUES (3009,'Geoff Cameron','Berlin',100,5003);
INSERT INTO CUST_ALL(CUSTOMER_ID,CUST_NAME, CITY, GRADE, SALESMAN_ID) VALUES (3003,'Jozy Altidor','Moskow',200,5007);
INSERT INTO CUST_ALL(CUSTOMER_ID,CUST_NAME, CITY, GRADE, SALESMAN_ID) VALUES (3001,'Brad Guzan','London','',5005);


SELECT C2.*, C1.NAME,C1.CITY FROM CUST_ALL C2 INNER JOIN SALESMAN C1 ON C1.SALESMAN_ID=C2.SALESMAN_ID;

SELECT * FROM CUST_ALL 
CASE 
WHEN CUST_ALL.GRADE >=100 AND CUST_ALL.GRADE<200 THEN 'MIDDLE'
WHEN CUST_ALL.GRADE>200 THEN 'HIGH'
ELSE 'UNKNOWN'  END AS GRADE_NAMES GROUP BY (SELECT C2.*, C1.NAME,C1.CITY FROM CUST_ALL C2 INNER JOIN SALESMAN C1 ON C1.SALESMAN_ID=C2.SALESMAN_ID);