2nd text

CREATE TABLE SALESMAN (
    SALESMAN_ID NUMBER(4) PRIMARY KEY,
    NAME VARCHAR(20),
    CITY VARCHAR(20),
    COMMISSION VARCHAR(20)
);

CREATE TABLE CUSTOMER (
    CUSTOMER_ID NUMBER(4) PRIMARY KEY,
    CUST_NAME VARCHAR(20),
    CITY VARCHAR(20),
    GRADE NUMBER(3),
    SALESMAN_ID NUMBER(4) REFERENCES SALESMAN(SALESMAN_ID) ON DELETE SET NULL
);

CREATE TABLE ORDERS (
    ORD_NO NUMBER(5) PRIMARY KEY,
    PURCHASE_AMT NUMBER(10, 2),
    ORD_DATE DATE,
    CUSTOMER_ID NUMBER(4) REFERENCES CUSTOMER(CUSTOMER_ID) ON DELETE CASCADE,
    SALESMAN_ID NUMBER(4) REFERENCES SALESMAN(SALESMAN_ID) ON DELETE CASCADE
);

INSERT INTO SALESMAN VALUES (1000, 'JOHN', 'BANGALORE', '25%');
INSERT INTO SALESMAN VALUES (2000, 'RAVI', 'BANGALORE', '20%');
INSERT INTO SALESMAN VALUES (3000, 'KUMAR', 'MYSORE', '15%');
INSERT INTO SALESMAN VALUES (4000, 'SMITH', 'DELHI', '30%');
INSERT INTO SALESMAN VALUES (5000, 'HARSHA', 'HYDERABAD', '15%');

INSERT INTO CUSTOMER VALUES (10, 'PREETHI', 'BANGALORE', 100, 1000);
INSERT INTO CUSTOMER VALUES (11, 'VIVEK', 'MANGALORE', 300, 1000);
INSERT INTO CUSTOMER VALUES (12, 'BHASKAR', 'CHENNAI', 400, 2000);
INSERT INTO CUSTOMER VALUES (13, 'CHETHAN', 'BANGALORE', 200, 2000);
INSERT INTO CUSTOMER VALUES (14, 'MAMATHA', 'BANGALORE', 400, 3000);

INSERT INTO ORDERS VALUES (50, 5000, '2017-05-04', 10, 1000);
INSERT INTO ORDERS VALUES (51, 450, '2017-01-20', 10, 2000);
INSERT INTO ORDERS VALUES (52, 1000, '2017-02-24', 13, 2000);
INSERT INTO ORDERS VALUES (53, 3500, '2017-04-13', 14, 3000);
INSERT INTO ORDERS VALUES (54, 550, '2017-03-09', 12, 2000);


1. COUNT the customers WITH grades above Bangalore’s average.
SELECT GRADE, COUNT (DISTINCT CUSTOMER_ID)
FROM CUSTOMER1
GROUP BY GRADE
HAVING GRADE > (SELECT AVG(GRADE)
FROM CUSTOMER1
WHERE CITY='BANGALORE');
18CSL58:DBMS Lab Manual 2022-23

2. Find the name AND numbers OF ALL salesmen who had more than one customer.
SELECT SALESMAN_ID, NAME
FROM SALESMAN A
WHERE 1 < (SELECT COUNT (*)
FROM CUSTOMER1
WHERE SALESMAN_ID=A.SALESMAN_ID);

3. List ALL salesmen AND indicate those who have AND don’t have customers IN their
cities (USE UNION operation.)
SELECT SALESMAN.SALESMAN_ID, NAME, CUST_NAME, COMMISSION
FROM SALESMAN, CUSTOMER1
WHERE SALESMAN.CITY = CUSTOMER1.CITY
UNION
SELECT SALESMAN_ID, NAME, 'NO MATCH', COMMISSION
FROM SALESMAN
WHERE NOT CITY = ANY
(SELECT CITY
FROM CUSTOMER1)
ORDER BY 2 DESC;

4. CREATE a VIEW that finds the salesman who has the customer WITH the highest ORDER
OF a DAY.
CREATE VIEW ELITSALESMANAS
SELECT B.ORD_DATE, A.SALESMAN_ID, A.NAME
FROM SALESMAN A, ORDERS B
18CSL58:DBMS Lab Manual 2022-23
RNSIT, Bangalore Page 31
WHERE A.SALESMAN_ID = B.SALESMAN_ID
AND B.PURCHASE_AMT=(SELECT MAX (PURCHASE_AMT)
FROM ORDERS C
WHERE C.ORD_DATE = B.ORD_DATE);


5. Demonstrate the DELETE operation BY removing salesman WITH id 1000. ALL his orders
must also be deleted.
USE ON DELETE CASCADE at the END OF FOREIGN KEY definitions while creating child TABLE
orders AND THEN EXECUTE the following:
USE ON DELETE SET NULL at the END OF FOREIGN KEY definitions while creating child TABLE
customers AND THEN executes the following:

DELETE FROM SALESMAN
WHERE SALESMAN_ID=1000;