					More programs at www.pranavthegreat.cjb.net


CREATE TABLE PCUSTOMER
(CUST_NO INT,
CNAME VARCHAR2(20) NOT NULL,
CITY_CUST VARCHAR(20),
CONSTRAINT TAB2PK1 PRIMARY KEY(CUST_NO));

CREATE TABLE PORDER
(ORDER_NO INT,
ODATE DATE NOT NULL,
CUST_NO INT,
ORDER_AMT INT DEFAULT 100,
CONSTRAINT TAB2PK2 PRIMARY KEY(ORDER_NO),
CONSTRAINT TAB2FK1 FOREIGN KEY(CUST_NO) REFERENCES PCUSTOMER(CUST_NO));

CREATE TABLE ITEM
(ITEM_NO INT,
UNIT_PRICE INT DEFAULT 100,
CONSTRAINT T2PK3 PRIMARY KEY(ITEM_NO));

CREATE TABLE ORDER_ITEM
(ORDER_NO INT,
ITEM_NO INT DEFAULT 1,
QTY INT DEFAULT 1,
CONSTRAINT TAB2PK4 PRIMARY KEY(ORDER_NO),
CONSTRAINT TAB2FK3 FOREIGN KEY(ORDER_NO) REFERENCES PORDER(ORDER_NO),
CONSTRAINT TAB2FK4 FOREIGN KEY(ITEM_NO) REFERENCES ITEM(ITEM_NO));

CREATE TABLE WAREHOUSE
(WAREHOUSE_NO INT,
CITY_WARE VARCHAR2(20) NOT NULL,
CONSTRAINT TAB2PK5 PRIMARY KEY(WAREHOUSE_NO));

CREATE TABLE SHIPMENT
(ORDER_NO INT,
WAREHOUSE_NO INT,
SHIPDATE DATE NOT NULL,
CONSTRAINT TAB2PK6 PRIMARY KEY(ORDER_NO),
CONSTRAINT TAB2FK5 FOREIGN KEY(ORDER_NO) REFERENCES PORDER(ORDER_NO),
CONSTRAINT TAB2FK6 FOREIGN KEY(WAREHOUSE_NO) REFERENCES WAREHOUSE(WAREHOUSE_NO));

==========================================================================
==========================================================================

INSERT INTO PCUSTOMER
VALUES(&CUST_NO,'&ENAME','&CITY_CUST');

INSERT INTO PORDER
VALUES(&ORDER_NO,'&ODATE',&CUST_NO,&ORDER_AMT);

INSERT INTO ITEM
VALUES(&ITEM_NO,&UNIT_PRICE);

INSERT INTO ORDER_ITEM
VALUES(&ORDER_NO,&ITEM_NO,&QTY);

INSERT INTO WAREHOUSE
VALUES(&WAREHOUSE_NO,'&CITY_WARE');

INSERT INTO SHIPMENT
VALUES(&ORDER_NO,&WAREHOUSE_NO,'&SHIPDATE');

==========================================================================
==========================================================================

SELECT PCUSTOMER.CNAME,COUNT(*),AVG(ORDER_AMT)
FROM PCUSTOMER,PORDER
WHERE PCUSTOMER.CUST_NO=PORDER.CUST_NO
GROUP BY CNAME;

SELECT ORDER_NO
FROM WAREHOUSE W,SHIPMENT S
WHERE W.WAREHOUSE_NO=S.WAREHOUSE_NO AND CITY_WARE='CHENNAI';

ALTER TABLE ORDER_ITEM
DROP CONSTRAINT TAB2FK4;
ALTER TABLE ORDER_ITEM ADD CONSTRAINT TAB2FK4 FOREIGN KEY(ITEM_NO) REFERENCES ITEM(ITEM_NO)
ON DELETE SET NULL;

==========================================================================
==========================================================================


					More programs at www.pranavthegreat.cjb.net
