Order Database

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 grade above Bangalores average.
2. Find the name and number of all salesman who had more had one customer.
3. 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_NAME_NN NOT NULL ,
COMMISSION NUMBER(5));

Table created..

CUSTOMER

CREATE TABLE CUSTOMER (
CUSTOMER_ID NUMBER(5) PRIMARY KEY,
CUST_NAME VARCHAR(10) NOT NULL,
CITY VARCHAR(10) NOT NULL,
GRADE NUMBER(5) NOT NULL,
SALESMAN_ID NUMBER(5) REFERENCES SALESMAN(SALESMAN_ID) ON DELETE SET NULL );

Table created..

ORDER

CREATE TABLE ORDERS (
ORD_NO NUMBER(5) PRIMARY KEY,
PURCHASE_AMT INTEGER NOT NULL,
ORD_DATE DATE NOT NULL,
CUSTOMER_ID NUMBER(5) REFERENCES CUSTOMER(CUSTOMER_ID),
SALESMAN_ID NUMBER(5) 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 CUTOMER VALUES (&CUSTOMER,’&CUST_NAME’,’&CITY’,’&GRADE’,&SALESMAN_ID);

SQL>INSERT INTO ORDERS VALUES (&ORD_NO,&PURCHASE_AMT,’&ORD_DATE’,&CUSTOMER_ID,&SALESMAN_ID);

SELECT * FROM SALESMAN; [ Here SPACE means (…….) ]

SALESMAN_ID……NAME …….CITY ……………COMMISSION
— — — — — — — — — — — — — — — — — — — — — — — — — — — — —
1000………………….RAJ………..BENGALURU……50
2000…………………ASHWIN….MUMBAI…………60
3000…………………INDU………HYDRABAD………30
4000…………………HEMA…….BENGALURU……..40
5000…………………ANIL………MYSORE…………..50

SELECT * FROM CUSTOMERS;

CUSTOMER_ID……NAME …….CITY………….GRADE …SALESMAN_ID
— — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — —
11…………………….INFOSYS…..BENGALURU….5………..1000
12…………………….TCS………….MUMBAI………4………..2000
13……………………WIPRO………HYDRABAD……3……….3000
14……………………ORACLE…….BENGALURU…..4……….4000
15……………………TCS…………MYSORE………….5………5000

SELECT * FROM ORDERS;

ORD_NO..PURCHASE_AMT…ORD_DATE …CUSTOMER_ID..SALESMAN_ID— — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — 1……………200000………………12-APR-16…….11……………….1000
2……………300000………………19-APR-16…….12……………….2000
3……………500000………………22-APR-17…….13……………….1000

Queries : [ Here SPACE means ( — — — ) ]

1. Count the customers with grade above Bangalores average.

SELECT COUNT(CUSTOMER_ID)
FROM CUSTOMER
WHERE GARDE>(SELECT AVG(GRADE)
……………………..FROM CUSTOMER
……………………..WHERE CITY LIKE ‘%BENGALURU’);

Result :

COUNT(CUSTOMER_ID)
— — — — — — — — — — — —
3

2. Find the name and number of all salesman who had more had 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;

Result :

NAME……..COUNT(CUSTOMER_ID)
— — — — — — — — — — — — — — — — —
ASHWIN….2
RAJ…………2

3. Demonstrate the DELETE operation by removing salesman with id 1000. All his orders must also be deleted.

SELECT from salesman
WHERE salesman_id = 1000;

1 row deleted…

SELECT * FROM SALESMAN;

SALESMAN_ID……NAME …….CITY ……………COMMISSION
— — — — — — — — — — — — — — — — — — — — — — — — — —
2000…………………ASHWIN….MUMBAI…………60
3000…………………INDU………HYDRABAD………30
4000…………………HEMA…….BENGALURU……..40
5000…………………ANIL………MYSORE…………..50

SELECT * FROM CUSTOMERS;

CUSTOMER_ID……NAME …….CITY………….GRADE …SALESMAN_ID
— — — — — — — — — — — — — — — — — — — — — — — — — — — — — — —-
11…………………….INFOSYS…..BENGALURU….5
12…………………….TCS………….MUMBAI………4………..2000
13……………………WIPRO………HYDRABAD……3……….3000
14……………………ORACLE…….BENGALURU…..4……….4000
15……………………TCS…………MYSORE………….5………5000

SELECT * FROM ORDERS;

ORD_NO..PURCHASE_AMT…ORD_DATE …CUSTOMER_ID..SALESMAN_ID — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — —
2……………300000………………19-APR-16…….12……………….2000

Thanks….

— — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — —

Visit for

1.Basic Concepts of PostgreSQL : https://medium.com/@haripriyapawar006/basic-concepts-of-postgresql-808db2606dd9

2.Stored Procedures in PostgreSQL : https://medium.com/@haripriyapawar006/stored-procedures-in-postgresql-eb451aa6ec46