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

Leave a Reply

Your email address will not be published. Required fields are marked *

WhatsApp Icon Join For Job Alerts