A Database application program for an order processing database application in a company

By | December 3, 2011

Consider the following relations for an order processing database  application in a company:

CUSTOMER (cust #: int , cname: string, city: string)

ORDER (order #: int, odate: date, cust #: int, ord-Amt: int)

ORDER ITEM (order #: int, item #: int, qty: int)

ITEM (item # : int, unit price: int)

SHIPMENT (order #: int, warehouse#: int, ship-date: date)

WAREHOUSE (warehouse #: int, city: string)

(i)         Create the above tables by properly specifying the primary keys and the foreign keys.

(ii)       Enter at least five tuples for each relation.

(iii)      Produce a listing: CUSTNAME, #oforders, AVG_ORDER_AMT, where the middle column is the total numbers of orders by the customer and the last column is the average order amount for that customer.

(iv)     List the order# for orders that were shipped from all the warehouses that the company has in a specific city.

(v)       Demonstrate the deletion of an item from the ITEM table and demonstrate a method of handling the rows in the ORDER_ITEM table that contain this particular item.

 

CREATE TABLE CUSTOMER
(
 Cust_id INTEGER NOT NULL,
 Cust_name VARCHAR2(10),
 City VARCHAR2(10),
 PRIMARY KEY(Cust_id)
);

INSERT INTO CUSTOMER VALUES(&Cust_id,'&Cust_name','&City');

SELECT * FROM CUSTOMER;


CUST_ID CUST_NAME CITY
------- ---------- ----------
 101 SAHIL BELGAUM
 102 NEHA PUNE
 103 PREM BELGAUM
 104 MAYUR HUBLI
 105 AKSHAY KOLKATTA



CREATE TABLE ORDER_1
(
 Order_id INTEGER NOT NULL,
 Order_date DATE,
 Cust_id INTEGER,
 Order_amt INTEGER,
 PRIMARY KEY(Order_id),
 FOREIGN KEY(Cust_id) REFERENCES CUSTOMER(Cust_id)
);

INSERT INTO ORDER_1 values(&Order_id,'&Order_date',&Cust_id,&Order_amt);

SELECT * FROM ORDER_1;

 ORDER_ID ORDER_DAT CUST_ID ORDER_AMT
--------- --------- ---------- ----------
 201 01-OCT-04 101 50000
 202 15-JAN-05 102 30000
 203 20-JUN-06 101 60000
 204 01-FEB-04 104 15000
 205 19-MAR-05 104 40000




CREATE TABLE ITEM
(
 Item_no INTEGER NOT NULL,
 Unit_price INTEGER,
 PRIMARY KEY(Item_no)
);

INSERT INTO ITEM values(&Item_no,&Unit_price);

SELECT * FROM ITEM;

 ITEM_NO UNIT_PRICE
-------- ----------
 1 5000
 2 3000
 3 2500
 4 8000
 5 7000


CREATE TABLE ORDER_ITEM
(
 Order_id INTEGER NOT NULL,
 Item_no INTEGER,
 Quantity INTEGER,
 PRIMARY KEY(Order_id,Item_no),
 FOREIGN KEY(Item_no) REFERENCES ITEM(Item_no),
 FOREIGN KEY(order_id) REFERENCES order_1(order_id)
);


INSERT INTO ORDER_ITEM values(&Order_id,&Item_no,&Quantity);

SELECT * FROM ORDER_ITEM;

 ORDER_ID ITEM_NO QUANTITY
---------- ---------- ----------
 201 1 20
 202 2 10
 203 3 40
 204 4 3
 205 5 5


CREATE TABLE WAREHOUSE
(
 Warehouse1 INTEGER NOT NULL,
 City VARCHAR2(10),
 PRIMARY KEY(Warehouse1)
);

INSERT INTO WAREHOUSE values(&Warehouse1,'&City');

SELECT * FROM WAREHOUSE;

WAREHOUSE1 CITY
---------- ----------
 100 BANGALORE
 200 KOLKATTA
 300 PUNE
 400 BELGAUM
 500 MYSORE

CREATE TABLE SHIPMENT
(
 Order_id INTEGER NOT NULL,
 Warehouse1 INTEGER,
 Ship_date DATE,
 PRIMARY KEY(Order_id,Warehouse1),
 FOREIGN KEY(Order_id) REFERENCES ORDER_1(Order_id),
 FOREIGN KEY(Warehouse1) REFERENCES WAREHOUSE(Warehouse1)
);


INSERT INTO SHIPMENT values(&Order_id,&Warehouse1,'&Ship_date');

SELECT * FROM SHIPMENT;

 ORDER_ID WAREHOUSE1 SHIP_DATE
---------- ---------- ---------
 201 100 03-JAN-06
 202 200 28-MAR-06
 203 300 13-JUN-07
 204 400 11-SEP-06
 205 500 13-JAN-07


QUERIES : 
3)
SELECT Cust_name as Customer_Name,COUNT(Order_id) AS NO_Of_Orders,AVG(Order_amt) AS Avg_Order_amt
FROM CUSTOMER c,ORDER_1 o
WHERE C.Cust_id=o.Cust_id
GROUP BY o.Cust_id,c.Cust_name;

CUSTOMER_N NO_OF_ORDERS AVG_ORDER_AMT
---------- ------------ -------------
SAHIL 2 55000
NEHA 1 30000
MAYUR 2 27500




4)
SELECT Order_id FROM SHIPMENT
WHERE Warehouse1 IN
(SELECT Warehouse1 FROM WAREHOUSE
 WHERE City='df');

Enter value for city: BANGALORE
old 4: WHERE City='&City')
new 4: WHERE City='BANGALORE')

 ORDER_ID
-----------------
 201


5)
SQL> DELETE FROM ITEM WHERE ITEM_NO=4;

1 row deleted.

SQL> SELECT * FROM ITEM;

 ITEM_NO UNIT_PRICE
---------- ----------
 1 5000
 2 3000
 3 2500
 5 7000

Please Share: Tweet about this on TwitterShare on FacebookShare on Google+Share on RedditPin on PinterestShare on LinkedInDigg thisShare on StumbleUponShare on TumblrBuffer this pageShare on VKEmail this to someone

Leave a Reply

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