Count the customers with grades above Bangalore’s average:
SELECT GRADE, COUNT(DISTINCT CUSTOMER_ID)
FROM SCUSTOMER1
GROUP BY GRADE
HAVING GRADE > (SELECT AVG(GRADE) FROM SCUSTOMER1 WHERE CITY = ‘BANGALORE’);
Find the name and numbers of all salesmen who had more than one customer:
SELECT SALESMAN_ID, NAME
FROM SSALESMAN A
WHERE 1 < (SELECT COUNT(*) FROM SCUSTOMER1 WHERE SALESMAN_ID = A.SALESMAN_ID);
List all salesmen and indicate those who have and don’t have customers in their cities (Use UNION operation):
SELECT S.SALESMAN_ID, NAME, CUST_NAME, COMMISSION
FROM SSALESMAN S, SCUSTOMER1 C
WHERE S.CITY = C.CITY
UNION
SELECT SALESMAN_ID, NAME, ‘NO MATCH’, COMMISSION
FROM SSALESMAN
WHERE NOT CITY = ANY (SELECT CITY FROM SCUSTOMER1)
ORDER BY 2 DESC;
Create a view that finds the salesman who has the customer with the highest order of a day:
CREATE VIEW ELITSALESMAN AS
SELECT B.ORD_DATE, A.SALESMAN_ID, A.NAME
FROM SSALESMAN A, SORDERS B
WHERE A.SALESMAN_ID = B.SALESMAN_ID
AND B.PURCHASE_AMT = (SELECT MAX(PURCHASE_AMT) FROM SORDERS C WHERE C.ORD_DATE = B.ORD_DATE);
Demonstrate the DELETE operation by removing salesman with id 1000. All his orders must also be deleted:
DELETE FROM SSALESMAN WHERE SALESMAN_ID = 1000;
