LAB PROGRAM 2

[realcr7goat]

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

CREATE TABLE CUSTOMER ( CUSTOMER_ID NUMBER(4) PRIMARY KEY, CUST_NAME VARCHAR2(20), CITY VARCHAR2(20), GRADE NUMBER(3), SALESMAN_ID NUMBER(4), FOREIGN KEY (SALESMAN_ID) 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), FOREIGN KEY (CUSTOMER_ID) REFERENCES CUSTOMER (CUSTOMER_ID) ON DELETE CASCADE, SALESMAN_ID NUMBER(4), FOREIGN KEY (SALESMAN_ID) REFERENCES SALESMAN (SALESMAN_ID) ON DELETE CASCADE );

INSERT INTO SALESMAN VALUES (1001, 'Rahul', 'Mumbai', 0.25); INSERT INTO SALESMAN VALUES (1002, 'Priya', 'Delhi', 0.20); INSERT INTO SALESMAN VALUES (1003, 'Amit', 'Bangalore', 0.15); INSERT INTO SALESMAN VALUES (1004, 'Neha', 'Chennai', 0.30); INSERT INTO SALESMAN VALUES (1005, 'Suresh', 'Hyderabad', 0.15);

INSERT INTO CUSTOMER VALUES (2001, 'Aisha', 'Pune', 100, 1001); INSERT INTO CUSTOMER VALUES (2002, 'Virat', 'Kolkata', 300, 1002); INSERT INTO CUSTOMER VALUES (2003, 'Kavya', 'Jaipur', 400, 1003); INSERT INTO CUSTOMER VALUES (2004, 'Dhruv', 'Lucknow', 200, 1004); INSERT INTO CUSTOMER VALUES (2005, 'Isha', 'Bhopal', 400, 1005);

INSERT INTO ORDERS VALUES (3001, 5000, '2024-05-04', 2001, 1001); INSERT INTO ORDERS VALUES (3002, 450, '2024-01-20', 2002, 1002); INSERT INTO ORDERS VALUES (3003, 1000, '2024-02-24', 2003, 1003); INSERT INTO ORDERS VALUES (3004, 3500, '2024-04-13', 2004, 1004); INSERT INTO ORDERS VALUES (3005, 550, '2024-03-09', 2005, 1005);

1. Countthe customers with grades above Bangalore’s average SELECT GRADE, COUNT(DISTINCT CUSTOMER_ID) AS customer_count FROM CUSTOMER GROUP BY GRADE HAVING GRADE > (SELECT AVG(GRADE) FROM CUSTOMER WHERE CITY = 'BANGALORE');

2. Find the name and numbers of allsalesmen who had more than one custom SELECT salesman_id, name FROM salesman a WHERE (SELECT COUNT(*) FROM customer WHERE salesman_id = a.salesman_id) > 1;

3.List all salesmen and indicate those who have and don’t have customers in their cities (Use

SELECT salesman.salesman_id, name, cust_name, commission FROM salesman, customer WHERE salesman.city = customer.city UNION SELECT salesman_id, name, 'NO MATCH', commission FROM salesman WHERE NOT city = ANY ORDER BY 2 DESC

4. Find all orders with above average amount. Return ord_no, purch_amt, ord_date, customer_id and salesman_id. SELECT * FROM orders a WHERE purch_amt > (SELECT AVG(purch_amt) FROM orders b WHERE b.customer_id = a.customer_id);

5.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 SALESMAN A, ORDERS B 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);

6. Demonstrate the DELETE operation by removing salesman with id is 1000. All his orders must also be deleted DELETE FROM SALESMAN WHERE SALESMAN_ID=1000

[Varshith00]

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

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

CREATE TABLE ORDERS (ORD_NO NUMBER (5), PURCHASE_AMT NUMBER (10, 2), ORD_DATE DATE, PRIMARY KEY (ORD_NO),CUSTOMER_ID INTEGER, FOREIGN KEY(CUSTOMER_ID) REFERENCES CUSTOMER (CUSTOMER_ID) ON DELETE CASCADE, SALESMAN_ID INTEGER, FOREIGN KEY(SALESMAN_ID) 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’,’HYDRABAD’,’15 %’);

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

INSERT INTO ORDERS VALUES (50, 5000, ‘04-MAY-24’, 10, 1000); INSERT INTO ORDERS VALUES (51, 450, ‘20-JAN-24’, 10, 2000); INSERT INTO ORDERS VALUES (52, 1000, ‘24-FEB-24’, 13, 2000); INSERT INTO ORDERS VALUES (53, 3500, ‘13-APR-24’, 14, 3000); INSERT INTO ORDERS VALUES (54, 550, ‘09-MAR-24’, 12, 2000);

-1

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

-2

SELECT salesman_id, name FROM salesman a WHERE 1 < (SELECT COUNT(*) FROM customer WHERE salesman_id = a.salesman_id);

-3

SELECT salesman.salesman_id, name, cust_name, commission FROM salesman, customer WHERE salesman.city = customer.city UNION SELECT salesman_id, name, 'NO MATCH', commission FROM salesman WHERE NOT city = ANY ORDER BY 2 DESC

-4

SELECT * FROM orders a WHERE purch_amt > (SELECT AVG(purch_amt) FROM orders b WHERE b.customer_id = a.customer_id);

-view

CREATE VIEW ELITSALESMAN AS SELECT B.ORD_DATE, A.SALESMAN_ID, A.NAME FROM SALESMAN A, ORDERS B 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);

-del

DELETE FROM SALESMAN WHERE SALESMAN_ID=1000