text: create database orders;
  use orders;
  
  CREATE TABLE SALESMAN(
  SALESMAN_ID INT PRIMARY KEY, 
  NAME VARCHAR(10),
  CITY VARCHAR (15), COMMISSION INT);
  
  CREATE TABLE CUSTOMER (
  CUSTOMER_ID INT PRIMARY KEY, 
  CUST_NAME VARCHAR (10), 
  CITY VARCHAR (10),
  GRADE INT,
  SALESMAN_ID INT,
  FOREIGN KEY(SALESMAN_ID) REFERENCES SALESMAN(SALESMAN_ID) ON DELETE 
  SET NULL);
  
  CREATE TABLE ORDERS (
  ORD_NO INT PRIMARY KEY, 
  PURCHASE_AMT INT,
  ORD_DATE DATE,
  CUSTOMER_ID INT,
  SALESMAN_ID INT,
  FOREIGN KEY(CUSTOMER_ID) REFERENCES CUSTOMER (CUSTOMER_ID) 
  ON DELETE CASCADE,
  FOREIGN KEY(SALESMAN_ID) REFERENCES SALESMAN(SALESMAN_ID) 
  ON DELETE CASCADE);
  
  INSERT INTO SALESMAN VALUES(1000,'RAJ','BENGALURU',50);
  INSERT INTO SALESMAN VALUES(2000,'ASHWIN','TUMKUR',30);
  INSERT INTO SALESMAN VALUES(3000,'BINDU','MUMBAI',40);
  INSERT INTO SALESMAN VALUES(4000,'LAVANYA','BENGALURU',40);
  INSERT INTO SALESMAN VALUES(5000,'ROHIT','MYSORE',60);
  
  INSERT INTO CUSTOMER VALUES(11,'INFOSYS','BENGALURU','5',1000);
  INSERT INTO CUSTOMER VALUES(22,'TCS','BENGALURU','4',2000);
  INSERT INTO CUSTOMER VALUES(33,'WIPRO','MYSORE','7',1000);
  INSERT INTO CUSTOMER VALUES(44,'TCS','MYSORE','6',2000);
  INSERT INTO CUSTOMER VALUES(55,'ORACLE','TUMKUR','3',3000);
  
  INSERT INTO ORDERS VALUES(1,200000,'12-04-16',11,1000);
  INSERT INTO ORDERS VALUES(2,300000,'12-04-16',11,2000);
  INSERT INTO ORDERS VALUES(3,500000,'15-04-16',22,1000);
  
  SELECT*FROM SALESMAN;
  
  SELECT*FROM CUSTOMER;
  
  SELECT*FROM ORDERS;
  
  SELECT COUNT(CUSTOMER_ID)FROM CUSTOMER WHERE GRADE>(SELECT AVG(GRADE) FROM CUSTOMER WHERE CITY ='BENGALURU');
  
  SELECT S.NAME,COUNT(CUSTOMER_ID)FROM SALESMAN S, CUSTOMER C WHERE S.SALESMAN_ID=C.SALESMAN_ID GROUP BY S.NAME HAVING COUNT(CUSTOMER_ID)>1;
  
  SELECT S.NAME, C.CUST_NAME FROM SALESMAN as S, CUSTOMER as C, ORDERS as O WHERE S.SALESMAN_ID=O.SALESMAN_ID AND C.CUSTOMER_ID=O.CUSTOMER_ID AND O.PURCHASE_AMT>4000;
  
  DELETE from salesman WHERE salesman_id = 1000;
  
  
  