CHAPTER – 3
ORDER DATABASE
2) Consider the following schema for Order Database:
SALESMAN (Salesman_id, Name, City, Commission)
CUSTOMER (Customer_id, Cust_Name, City, Grade, Salesman_id)
ORDERS (Ord_No, Purchase_Amt, Ord_Date, Customer_id, Salesman_id)
Write SQL queries to
1. Count the customers with grades above Bangalore’s average.
2. Find the name and numbers of all salesmen who had more than one customer.
3. List all salesmen and indicate those who have and don’t have customers in their cities
(Use UNION operation.)
4. Create a view that finds the salesman who has the customer with the highest order of a
day.
5. Demonstrate the DELETE operation by removing salesman with id 1000. All his orders
must also be deleted.
ER-Diagram:
SCHEMA:
Table Creation:
SALESMAN
CREATE TABLE SALESMAN(
SALESMAN_ID NUMBER(5) CONSTRAINT SALESMAN_SALID PRIMARY KEY,
NAME VARCHAR(10) CONSTRAINT SALESMAN_NAME_NN NOT NULL,
CITY VARCHAR(15) CONSTRAINT SALESMAN_CITY_NN NOT NULL,
COMMISSION NUMBER(5));
Table created.
CUSTOMER
CREATE TABLE CUSTOMER(
CUSTOMER_ID NUMBER(5) CONSTRAINT CUSTOMER_CUSTID_PK PRIMARY KEY,
CUST_NAME VARCHAR(10) CONSTRAINT CUSTOMER_CUSTNAME_NN NOT NULL,
CITY VARCHAR(10) CONSTRAINT CUSTOMER_CITY_NN NOT NULL,
GRADE NUMBER(5) CONSTRAINT CUSTOMER_GRADE_NN NOT NULL,
SALESMAN_ID NUMBER(5) CONSTRAINT CUSTOMER_SALEID_FK REFERENCES
SALESMAN(SALESMAN_ID) ON DELETE SET NULL);
Table created.
ORDERS
CREATE TABLE ORDERS(
ORD_NO NUMBER(5) CONSTRAINT ORDERS_ODNO_PK PRIMARY KEY,
PURCHASE_AMT INTEGER CONSTRAINT ORDERS_PAMT_NN NOT NULL,
ORD_DATE DATE CONSTRAINT ORDERS_ODATE_NN NOT NULL,
CUSTOMER_ID NUMBER(5) CONSTRAINT ORDERS_CUSTID_FK REFERENCES
CUSTOMER(CUSTOMER_ID),
SALESMAN_ID NUMBER(5) CONSTRAINT ORDERS_SALEID_FK REFERENCES
SALESMAN(SALESMAN_ID) ON DELETE CASCADE);
Table created.
Values for tables
SQL> INSERT INTO SALESMAN VALUES(&SALESMAN_ID,'&NAME','&CITY',&COMMISSION);
SQL> INSERT INTO CUSTOMER
VALUES(&CUSTOMER_ID,'&CUST_NAME','&CITY','&GRADE',&SALESMAN_ID);
SQL> INSERT INTO ORDERS
VALUES(&ORD_NO,&PURCHASE_AMT,'&ORD_DATE',&CUSTOMER_ID,&SALESMAN_ID);
SELECT * FROM SALESMAN;
SALESMAN_ID NAME CITY COMMISSION
----------- ---------- --------------- ----------
1000 RAJ BENGALURU 50
2000 ASHWIN TUMKUR 30
3000 BINDU MUMBAI 40
4000 LAVANYA BENGALURU 40
5000 ROHIT MYSORE 60
SELECT * FROM CUSTOMER;
CUSTOMER_ID CUST_NAME CITY GRADE SALESMAN_ID
----------- ---------- ---------- --------- -----------
11 INFOSYS BENGALURU 5 1000
22 TCS BENGALURU 4 2000
33 WIPRO MYSORE 7 1000
44 TCS MYSORE 6 2000
55 ORACLE TUMKUR 3 3000
SELECT * FROM ORDERS;
ORD_NO PURCHASE_AMT ORD_DATE CUSTOMER_ID SALESMAN_ID
--------- ------------ --------- ----------- -----------
1 200000 12-APR-16 11 1000
2 300000 12-APR-16 11 2000
3 400000 15-APR-17 22 1000
1. Count the customers with grades above Bangalore’s average.
SELECT COUNT(CUSTOMER_ID)
FROM CUSTOMER
WHERE GRADE>(SELECT AVG(GRADE)
FROM CUSTOMER
WHERE CITY LIKE '%BENGALURU');
COUNT(CUSTOMER_ID)
------------------
3
2. Find the name and numbers of all salesmen who had more than one customer.
SELECT NAME, COUNT(CUSTOMER_ID)
FROM SALESMAN S, CUSTOMER C
WHERE S.SALESMAN_ID=C.SALESMAN_ID
GROUP BY NAME
HAVING COUNT(CUSTOMER_ID)>1;
NAME COUNT(CUSTOMER_ID)
---------- ------------------
ASHWIN 2
RAJ 2
3. List all salesmen and indicate those who have and don’t have customers in their cities
(Use UNION operation.)
(SELECT NAME
FROM SALESMAN S, CUSTOMER C
WHERE S.SALESMAN_ID=C.SALESMAN_ID AND
S.CITY=C.CITY)
UNION
(SELECT NAME
FROM SALESMAN
WHERE SALESMAN_ID NOT IN(SELECT S1.SALESMAN_ID
FROM SALESMAN S1, CUSTOMER C1
WHERE S1.SALESMAN_ID=C1.SALESMAN_ID AND
S1.CITY=C1.CITY));
NAME
----------
ASHWIN
BINDU
LAVANYA
RAJ
ROHIT
4. Create a view that finds the salesman who has the customer with the highest order of a
day.
CREATE VIEW SALES_HIGHERODER AS
SELECT SALESMAN_ID, PURCHASE_AMT
FROM ORDERS
WHERE PURCHASE_AMT=(SELECT MAX(O.PURCHASE_AMT)
FROM ORDERS O
WHERE O.ORD_DATE='12-APR-16');
View created.
SELECT * FROM SALES_HIGHERODER;
SALESMAN_ID PURCHASE_AMT
----------- ------------
2000 300000
5. Demonstrate the DELETE operation by removing salesman with id 1000. All his orders
must also be deleted.
DELETE from salesman
WHERE salesman_id = 1000;
1 row deleted.
SELECT * FROM SALESMAN;
SALESMAN_ID NAME CITY COMMISSION
----------- ---------- --------------- ----------
2000 ASHWIN TUMKUR 30
3000 BINDU MUMBAI 40
4000 LAVANYA BENGALURU 40
5000 ROHIT MYSORE 60
SELECT * FROM CUSTOMER;
CUSTOMER_ID CUST_NAME CITY GRADE SALESMAN_ID
----------- ---------- ---------- --------- -----------
11 INFOSYS BENGALURU 5
22 TCS BENGALURU 4 2000
33 WIPRO MYSORE 7
44 TCS MYSORE 6 2000
55 ORACLE TUMKUR 3 3000
SELECT * FROM ORDERS;
ORD_NO PURCHASE_AMT ORD_DATE CUSTOMER_ID SALESMAN_ID
--------- ------------ --------- ----------- -----------
2 300000 12-APR-16 11 2000
