2. ORDER DATABASE VTU DBMS LAB | Direct SQL Code
VTU DBMS LAB Program 2
SALESMAN(SALESMAN_ID,NAME,CITY,COMISSION)
CUSTOMER(CUSTOMER_ID,CUST_NAME,CITY,GRADE,SALESMAN_ID)
ORDERS(ORD_NO,PURCHASE_AMT,ORD_DATE,CUSTOMER_ID,SALESMAN_ID)
VTU DBMS LAB -Create Table SQL Commands
Create the tables for all of the above attributes
Create Table for SALESMAN
CREATE TABLE SALESMAN(SALESMAN_ID NUMBER(4),
NAME VARCHAR(15),
CITY VARCHAR(15),
COMISSION NUMBER(7,2),
CONSTRAINT PK_A PRIMARY KEY(SALESMAN_ID)
);
Table created.
DESC SALESMAN;
Name Null? Type ---------------- -------- ---------------------- SALESMAN_ID NOT NULL NUMBER(4) NAME VARCHAR2(15) CITY VARCHAR2(15) COMISSION NUMBER(7,2)
Create Table for CUSTOMER
CREATE TABLE CUSTOMER(CUSTOMER_ID NUMBER(2),
CUST_NAME VARCHAR(15),
CITY VARCHAR(15),
GRADE NUMBER(3),
SALESMAN_ID NUMBER(4),
CONSTRAINT PK_B PRIMARY KEY(CUSTOMER_ID),
CONSTRAINT FK_D FOREIGN KEY(SALESMAN_ID) REFERENCES SALESMAN(SALESMAN_ID) ON DELETE SET NULL );
Table created
DESC CUSTOMER;
Name Null? Type ------------------- -------- ---------------------------- CUSTOMER_ID NOT NULL NUMBER(2) CUST_NAME VARCHAR2(15) CITY VARCHAR2(15) GRADE NUMBER(3) SALESMAN_ID NUMBER(4)
Create Tabel for ORDERS
CREATE TABLE ORDERS(ORD_NO NUMBER(4),
PURCHASE_AMT NUMBER(10,2),
ORD_DATE DATE,
CUSTOMER_ID NUMBER(2),
SALESMAN_ID NUMBER(4),
CONSTRAINT PK_E PRIMARY KEY(ORD_NO),
CONSTRAINT FK_G FOREIGN KEY(CUSTOMER_ID) REFERENCES CUSTOMER(CUSTOMER_ID) ON DELETE SET NULL,
CONSTRAINT FK_H FOREIGN KEY(SALESMAN_ID) REFERENCES SALESMAN(SALESMAN_ID) ON DELETE SET NULL );
Table created
DESC ORDERS;
Name Null? Type -------------------------- - -------- -------------------- ORD_NO NOT NULL NUMBER(4) PURCHASE_AMT NUMBER(10,2) ORD_DATE DATE CUSTOMER_ID NUMBER(2) SALESMAN_ID NUMBER(4)
VTU DBMS LAB -SQL Insertion Commands for ORDER
Enter ‘ / ‘ to continue to insert the values
VTU DBMS LAB -Insert Values into SALESMAN
Insert Command
INSERT INTO SALESMAN VALUES(&SALESMAN_ID,'&NAME','&CITY',&COMISSION);
Enter the input values
Enter value for salesman_id: 1000 Enter value for name: RAMA Enter value for city: BANGALORE Enter value for comission: 10000.75 old 1: INSERT INTO SALESMAN VALUES(&SALESMAN_ID,'&NAME','&CITY',&COMISSION) new 1: INSERT INTO SALESMAN VALUES(1000,'RAMA','BANGALORE',10000.75)
row-1 created
Command to continue insertion of the values into next row
/
Enter the input values
Enter value for salesman_id: 2000 Enter value for name: KRISHNA Enter value for city: MATHURA Enter value for comission: 20000.25 old 1: INSERT INTO SALESMAN VALUES(&SALESMAN_ID,'&NAME','&CITY',&COMISSION) new 1: INSERT INTO SALESMAN VALUES(2000,'KRISHNA','MATHURA',20000.25)
row-2 created
Command to continue insertion of the values into next row
/
Enter the input values
Enter value for salesman_id: 3000 Enter value for name: SHIVA Enter value for city: AMARNATH Enter value for comission: 30000.30 old 1: INSERT INTO SALESMAN VALUES(&SALESMAN_ID,'&NAME','&CITY',&COMISSION) new 1: INSERT INTO SALESMAN VALUES(3000,'SHIVA','AMARNATH',30000.30)
row-3 created
Command to continue insertion of the values into next row
/
Enter the input values
Enter value for salesman_id: 4000 Enter value for name: GOVINDA Enter value for city: TIRUPATHI Enter value for comission: 4000.40 old 1: INSERT INTO SALESMAN VALUES(&SALESMAN_ID,'&NAME','&CITY',&COMISSION) new 1: INSERT INTO SALESMAN VALUES(4000,'GOVINDA','TIRUPATHI',4000.40)
row-4 created
Command to continue insertion of the values into next row
/
Enter the input values
Enter value for salesman_id: 5000 Enter value for name: NARAYANA Enter value for city: VELLORE Enter value for comission: 50000.50 old 1: INSERT INTO SALESMAN VALUES(&SALESMAN_ID,'&NAME','&CITY',&COMISSION) new 1: INSERT INTO SALESMAN VALUES(5000,'NARAYANA','VELLORE',50000.50)
row-5 created
Command to select all 5 rows of SALESMAN
SELECT * FROM SALESMAN;
SALESMAN_ID NAME CITY COMISSION ----------- --------------- ----------- ---------- 1000 RAMA BANGALORE 10000.75 2000 KRISHNA MATHURA 20000.25 3000 SHIVA AMARNATH 30000.3 4000 GOVINDA TIRUPATHI 4000.4 5000 NARAYANA VELLORE 50000.5
6-rows selected
Insert Values into CUSTOMER
Insert Command
INSERT INTO CUSTOMER VALUES(&CUSTOMER_ID,'&CUST_NAME','&CITY',&GRADE,&SALESMAN_ID);
Enter the input values
Enter value for customer_id: 10 Enter value for cust_name: GANESH Enter value for city: BANGALORE Enter value for grade: 100 Enter value for salesman_id: 1000 old 1: INSERT INTO CUSTOMER VALUES(&CUSTOMER_ID,'&CUST_NAME','&CITY',&GRADE,&SALESMAN_ID) new 1: INSERT INTO CUSTOMER VALUES(10,'GANESH','BANGALORE',100,1000)
row-1 created
Command to continue insertion of the values into next row
/
Enter the input values
Enter value for customer_id: 20 Enter value for cust_name: SUDHEEP Enter value for city: BANGALORE Enter value for grade: 200 Enter value for salesman_id: 2000 old 1: INSERT INTO CUSTOMER VALUES(&CUSTOMER_ID,'&CUST_NAME','&CITY',&GRADE,&SALESMAN_ID) new 1: INSERT INTO CUSTOMER VALUES(20,'SUDHEEP','BANGALORE',200,2000)
row-2 created
Command to continue insertion of the values into next row
/
Enter the input values
Enter value for customer_id: 30 Enter value for cust_name: PRABHAS Enter value for city: HYDERABAD Enter value for grade: 300 Enter value for salesman_id: 3000 old 1: INSERT INTO CUSTOMER VALUES(&CUSTOMER_ID,'&CUST_NAME','&CITY',&GRADE,&SALESMAN_ID) new 1: INSERT INTO CUSTOMER VALUES(30,'PRABHAS','HYDERABAD',300,3000)
row-3 created
Command to continue insertion of the values into next row
/
Enter the input values
Enter value for customer_id: 40 Enter value for cust_name: ARVIND Enter value for city: CHENNAI Enter value for grade: 400 Enter value for salesman_id: 4000 old 1: INSERT INTO CUSTOMER VALUES(&CUSTOMER_ID,'&CUST_NAME','&CITY',&GRADE,&SALESMAN_ID) new 1: INSERT INTO CUSTOMER VALUES(40,'ARVIND','CHENNAI',400,4000)
row-4 created
Command to continue insertion of the values into next row
/
Enter the input values
Enter value for customer_id: 50 Enter value for cust_name: DARSHAN Enter value for city: BANGALORE Enter value for grade: 500 Enter value for salesman_id: 2000 old 1: INSERT INTO CUSTOMER VALUES(&CUSTOMER_ID,'&CUST_NAME','&CITY',&GRADE,&SALESMAN_ID) new 1: INSERT INTO CUSTOMER VALUES(50,'DARSHAN','BANGALORE',500,2000)
row-5 created
Command to continue insertion of the values into next row
/
Enter the input values
Enter value for customer_id: 60 Enter value for cust_name: YASH Enter value for city: BANGALORE Enter value for grade: 600 Enter value for salesman_id: 1000 old 1: INSERT INTO CUSTOMER VALUES(&CUSTOMER_ID,'&CUST_NAME','&CITY',&GRADE,&SALESMAN_ID) new 1: INSERT INTO CUSTOMER VALUES(60,'YASH','BANGALORE',600,1000)
row-6 created
Command to select all 6 rows of CUSTOMER
SELECT * FROM CUSTOMER;
CUSTOMER_ID CUST_NAME CITY GRADE SALESMAN_ID ----------- ------------ -- -------- ------ ----------- 10 GANESH BANGALORE 100 1000 20 SUDHEEP BANGALORE 200 2000 30 PRABHAS HYDERABAD 300 3000 40 ARVIND CHENNAI 400 4000 50 DARSHAN BANGALORE 500 2000 60 YASH BANGALORE 600 1000
6-rows selected
Insert Values into ORDERS
Insert Command
INSERT INTO ORDERS VALUES(&ORD_NO,&PURCHASE_AMT,'&ORD_DATE',&CUSTOMER_ID,&SALESMAN_ID);
Enter the input values
Enter value for ord_no: 1111 Enter value for purchase_amt: 100000.00 Enter value for ord_date: 01-JAN-17 Enter value for customer_id: 10 Enter value for salesman_id: 2000 old 1: INSERT INTO ORDERS VALUES(&ORD_NO,&PURCHASE_AMT,'&ORD_DATE',&CUSTOMER_ID,&SALESMAN_ID) new 1: INSERT INTO ORDERS VALUES(1111,100000.00,'01-JAN-17',10,2000)
row-1 created
Command to continue insertion of the values into next row
/
Enter the input values
Enter value for ord_no: 2222 Enter value for purchase_amt: 200000.00 Enter value for ord_date: 21-FEB-17 Enter value for customer_id: 20 Enter value for salesman_id: 3000 old 1: INSERT INTO ORDERS VALUES(&ORD_NO,&PURCHASE_AMT,'&ORD_DATE',&CUSTOMER_ID,&SALESMAN_ID) new 1: INSERT INTO ORDERS VALUES(2222,200000.00,'21-FEB-17',20,3000)
row-2 created
Command to continue insertion of the values into next row
/
Enter the input values
Enter value for ord_no: 3333 Enter value for purchase_amt: 300000.00 Enter value for ord_date: 15-MAR-17 Enter value for customer_id: 30 Enter value for salesman_id: 4000 old 1: INSERT INTO ORDERS VALUES(&ORD_NO,&PURCHASE_AMT,'&ORD_DATE',&CUSTOMER_ID,&SALESMAN_ID) new 1: INSERT INTO ORDERS VALUES(3333,300000.00,'15-MAR-17',30,4000)
row-3 created
Command to continue insertion of the values into next row
/
Enter the input values
Enter value for ord_no: 4444 Enter value for purchase_amt: 400000.00 Enter value for ord_date: 18-APR-17 Enter value for customer_id: 40 Enter value for salesman_id: 5000 old 1: INSERT INTO ORDERS VALUES(&ORD_NO,&PURCHASE_AMT,'&ORD_DATE',&CUSTOMER_ID,&SALESMAN_ID) new 1: INSERT INTO ORDERS VALUES(4444,400000.00,'18-APR-17',40,5000)
row-4 created
Command to continue insertion of the values into next row
/
Enter the input values
Enter value for ord_no: 5555 Enter value for purchase_amt: 500000.00 Enter value for ord_date: 12-MAY-17 Enter value for customer_id: 10 Enter value for salesman_id: 1000 old 1: INSERT INTO ORDERS VALUES(&ORD_NO,&PURCHASE_AMT,'&ORD_DATE',&CUSTOMER_ID,&SALESMAN_ID) new 1: INSERT INTO ORDERS VALUES(5555,500000.00,'12-MAY-17',10,1000)
row-5 created
Command to continue insertion of the values into next row
/
Enter the input values
Enter value for ord_no: 6666 Enter value for purchase_amt: 600000.00 Enter value for ord_date: 12-MAY-17 Enter value for customer_id: 10 Enter value for salesman_id: 1000 old 1: INSERT INTO ORDERS VALUES(&ORD_NO,&PURCHASE_AMT,'&ORD_DATE',&CUSTOMER_ID,&SALESMAN_ID) new 1: INSERT INTO ORDERS VALUES(6666,600000.00,'12-MAY-17',10,1000)
row-6 created
Command to select all 6 rows of PUBLISHER
SELECT * FROM ORDERS;
ORD_NO PURCHASE_AMT ORD_DATE CUSTOMER_ID SALESMAN_ID ------- ------------ --------- ----------- ----------- 1111 100000 01-JAN-17 10 2000 2222 200000 21-FEB-17 20 3000 3333 300000 15-MAR-17 30 4000 4444 400000 18-APR-17 40 5000 5555 500000 12-MAY-17 10 1000 6666 600000 12-MAY-17 10 1000
6-rows selected
VTU DBMS LAB – SQL Queries for ORDER DATABASE
1] Count the customers with grades above Banglore’s average
SELECT COUNT(CUSTOMER_ID)
FROM CUSTOMER
WHERE GRADE>( SELECT AVG(GRADE)
FROM CUSTOMER
WHERE CITY='BANGALORE' );
Output
COUNT(CUSTOMER_ID)
------------------
3
2] Find the names and numbers of all salesman who had more than one customer.
SELECT S.NAME,S.SALESMAN_ID FROM SALESMAN S,CUSTOMER C WHERE S.SALESMAN_ID=C.SALESMAN_ID GROUP BY S.NAME,S.SALESMAN_ID HAVING COUNT(C.CUSTOMER_ID)>1;
Output
NAME SALESMAN_ID ------------ ----------- RAMA 1000 KRISHNA 2000
3]List all salesman and indicate those who have and don’t have customers in their cities. Use union operation
(SELECT S.SALESMAN_ID,S.NAME,C.CUST_NAME FROM SALESMAN S,CUSTOMER C WHERE S.CITY=C.CITY AND S.SALESMAN_ID=C.SALESMAN_ID) UNION (SELECT S1.SALESMAN_ID,S1.NAME,'NO CUSTOMER' FROM SALESMAN S1,CUSTOMER C1 WHERE S1.CITY!=C1.CITY AND S1.SALESMAN_ID=C1.SALESMAN_ID );
Output
SALESMAN_ID NAME CUST_NAME ----------- ---------- --------------- 1000 RAMA GANESH 1000 RAMA YASH 2000 KRISHNA NO CUSTOMER 3000 SHIVA NO CUSTOMER 4000 GOVINDA NO CUSTOMER
4]Create a view that finds the salesman who has the customer with the highest order of a day
CONNECT SYSTEM/username;
Connected
GRANT CREATE VIEW TO B2;
Grant succeeded
CONNECT B2; Enter password: **
Connected
CREATE VIEW HIGH_ORDER_DAY AS SELECT O.ORD_DATE,S.SALESMAN_ID,S.NAME,C.CUST_NAME,O.PURCHASE_AMT FROM ORDERS O,SALESMAN S,CUSTOMER C WHERE O.SALESMAN_ID=S.SALESMAN_ID AND C.CUSTOMER_ID=O.CUSTOMER_ID;
View created.
SELECT *
FROM HIGH_ORDER_DAY H
WHERE H.PURCHASE_AMT=(SELECT MAX(H1.PURCHASE_AMT)
FROM HIGH_ORDER_DAY H1
WHERE H1.ORD_DATE=H.ORDER_DATE);
Output
PURCHASE_AMT ORD_DATE CUST_NAME SALESMAN_ID NAME ------------ --------- --------- ----------- -------- 100000 01-JAN-17 GANESH 2000 KRISHNA 200000 21-FEB-17 SUDHEEP 3000 SHIVA 300000 15-MAR-17 PRABHAS 4000 GOVINDA 400000 18-APR-17 ARVIND 5000 NARAYANA 600000 12-MAY-17 GANESH 1000 RAMA
5] Demonstrate the delete operation by removing salesman with ID 1000, all their orders must also be deleted
DELETE FROM SALESMAN WHERE SALESMAN_ID=1000;
1 row deleted
Output
SELECT * FROM CUSTOMER;
CUSTOMER_ID CUST_NAME CITY GRADE SALESMAN_ID ----------- ---------- ------------ ------- ----------- 10 GANESH BANGALORE 100 20 SUDHEEP BANGALORE 200 2000 30 PRABHAS HYDERABAD 300 3000 40 ARVIND CHENNAI 400 4000 50 DARSHAN BANGALORE 500 2000 60 YASH BANGALORE 600
6 rows selected
SELECT * FROM ORDERS;
ORD_NO PURCHASE_AMT ORD_DATE CUSTOMER_ID SALESMAN_ID ------ ------------ --------- ----------- ----------- 1111 100000 01-JAN-17 10 2000 2222 200000 21-FEB-17 20 3000 3333 300000 15-MAR-17 30 4000 4444 400000 18-APR-17 40 5000 5555 500000 12-MAY-17 10
