SELECT GRADE, COUNT (DISTINCT CUSTOMER_ID) AS NO_OF_CUSTOMER
FROM CUSTOMER
GROUP BY GRADE
HAVING GRADE > (SELECT AVG(GRADE)
 FROM CUSTOMER
 WHERE CITY='BANGALORE');


SELECT SALESMAN_ID, NAME
 FROM SALESMAN S
 WHERE ((SELECT COUNT (*)
 FROM CUSTOMER
 WHERE SALESMAN_ID=S.SALESMAN_ID)>1);


SELECT S.SALESMAN_ID, S.CITY
FROM SALESMAN S
WHERE EXISTS (SELECT *
 FROM CUSTOMER
 WHERE S.CITY=CITY AND
 S.SALESMAN_ID=SALESMAN_ID)
 UNION
SELECT SALESMAN_ID,'NO MATCH OF CITIES'
FROM SALESMAN S
WHERE NOT EXISTS (SELECT *
 FROM CUSTOMER
 WHERE S.CITY=CITY AND
 S.SALESMAN_ID=SALESMAN_ID);


CREATE VIEW BB AS
SELECT DISTINCT S.SALESMAN_ID,S.ORD_DATE
FROM ORDERS S
WHERE (SELECT SUM(PURCHASE_AMT)
 FROM ORDERS
 WHERE SALESMAN_ID=S.SALESMAN_ID ORD_DATE=S.ORD_DATE )AND
CUSTOMER_ID=S.CUSTOMER_ID AND
 =(SELECT MAX(SUM(PURCHASE_AMT))
 FROM ORDERS S1
 WHERE S1.ORD_DATE=S.ORD_DATE
 GROUP BY S1.ORD_DATE,S1.SALESMAN_ID);


DELETE FROM SALESMAN
 WHERE SALESMAN_ID=1000;