Scribd
Upload
5 views5 pages

Prog 2

Uploaded by

FATIMA DEGREE COLLEGE Hubli

Copyright:

© All Rights Reserved
Download as DOCX, PDF, TXT or read online from Scribd
Download as docx, pdf, or txt

Prog 2

Uploaded by

FATIMA DEGREE COLLEGE Hubli
5 views5 pages

Original Title

prog2

Copyright

© © All Rights Reserved

Available Formats

DOCX, PDF, TXT or read online from Scribd

Did you find this document useful?

Is this content inappropriate?

Copyright:

© All Rights Reserved
Download as DOCX, PDF, TXT or read online from Scribd
Download as docx, pdf, or txt
5 views5 pages

Prog 2

Uploaded by

FATIMA DEGREE COLLEGE Hubli

Copyright:

© All Rights Reserved
Download as DOCX, PDF, TXT or read online from Scribd
Download as docx, pdf, or txt
You are on page 1/ 5

COMPANY DATABASE

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)

1)create the tables by properly specifying the primary keys and the forign keys..
2)enter atleast five tuples for each relation.
3)produce a listing:CUSTNAME,#oforders,AVG_ORDER_AMT,where the middle column is the
total number of orders by the customer and the last colum is the average
order amount for that customer.
4)list the order# for orders that were shipped from all the warehouse that the company has in a
specific city.
5)demonstrate how you delete item# 10 fromthe ITEM table and make that field null in the
ORDER_ITEM table.

TABLE CREATION:

1.CUSTOMER TABLE:

SQL>CREATE TABLE CUSTOMER99


(
CUST_ID INT PRIMARY KEY,
CNAME VARCHAR2(15),
CITY VARCHAR(20)
);

2.ORDER TABLE:

SQL>CREATE TABLE ORDER99


(
ORDER_ID INT PRIMARY KEY,
CUST_ID INT,
ORDAMT NUMBER(10),
FOREIGN KEY(CUST_ID) REFERENCES CUSTOMER99(CUST_ID)
);

3.ORD_-ITEM TABLE:
SQL>CREATE TABLE ORDERITEM99
(
ORDER_ID INT,
ITEM_ID NUMBER(5),
QTY NUMBER(5),
PRIMARY KEY(ORDER_ID, ITEM_ID),
FOREIGN KEY(ORDER_ID) REFERENCES ORDER99(ORDER_ID) ,
FOREIGN KEY(ITEM_ID) REFERENCES ITEM99(ITEM_ID)
);

4.ITEM TABLE:

SQL>CREATE TABLE ITEM99


(
ITEM_ID NUMBER(5) PRIMARY KEY,
UNITPRICE NUMBER(6)
);

5.SHIPMENT TABLE:

SQL>CREATE TABLE SHIPMENT99


(
ORDER_ID INTEGER,
WAREHOUSE_ID NUMBER(5),
SHIPDATE DATE,
PRIMARY KEY(ORDER_ID, WAREHOUSE_ID),
FOREIGN KEY(WAREHOUSE_ID) REFERENCES WAREHOUSE99(WAREHOUSE_ID) ,
FOREIGN KEY(ORDER_ID) REFERENCES ORDER99(ORDER_ID)
);

6.WAREHOUSE TABLE:

CREATE TABLE WAREHOUSE99


(
WAREHOUSE_ID NUMBER(5) PRIMARY KEY,
CITY VARCHAR2(15)
);

TABLE FILLING:

1.INSERT TO CUSTOMER

SQL>INSERT INTO CUSTOMER99 VALUES('3','NIRUPAMA','BANGALORE');


SQL>INSERT INTO CUSTOMER99 VALUES('2','SUVARNA','HUBLI');

2.INSERT TO ORDER

SQL>INSERT INTO ORDER99 VALUES(10,1,15000);


SQL>INSERT INTO ORDER99 VALUES(11,2,20000);

3.INSERT INTO ORD-ITEM VALUES

SQL>INSERT INTO ORDERITEM99 VALUES(10,990,6);


SQL>INSERT INTO ORDERITEM99 VALUES(11,991,5);

4.INSERT TO ITEM

SQL>INSERT INTO ITEM99 VALUES(990,500);


SQL>INSERT INTO ITEM99 VALUES(991,990);
5.INSERT INTO SHIPMENT

SQL>INSERT INTO SHIPMENT99 VALUES(10,30,'07-OCT-02');


SQL>INSERT INTO SHIPMENT99 VALUES(11,15,'11-DEC-02');

6.INSERT INTO WAREHOUSE

SQL>INSERT INTO WAREHOUSE99 VALUES(30,'DELHI');


SQL>INSERT INTO WAREHOUSE99 VALUES(15,'AHMADABAD');

QUERIES:

//1.PRODUCE A LISTING

SOLUTION:

SQL>SELECT CNAME,COUNT(*),AVG(ORDAMT)
FROM CUSTOMER99,ORDER99
WHERE CUSTOMER99.CUST_ID=ORDER99.CUST_ID
GROUP BY CNAME;

CNAME COUNT(*) AVG(ORDAMT)


--------- ------------ -------------
APARNA 1 15000
NEELESH 1 15000
NIRUPAMA 1 20000
SUVARNA 1 20000
VANDANA 1 50000

//2.LIST THE ORDERS THAT WERE SHIPPED FROM ALL THE WAREHOUSES THAT
THE COMPANY HAS IN A
//SPECIFIC CITY

SOLUTION:

SQL>SELECT ORDER_ID
FROM SHIPMENT99,WAREHOUSE99
WHERE SHIPMENT99.WAREHOUSE_ID=WAREHOUSE99.WAREHOUSE_ID AND
CITY='DELHI';

ORDER_ID
-------------
10

SQL>SELECT ORDER_ID
FROM SHIPMENT99,WAREHOUSE99
WHERE SHIPMENT99.WAREHOUSE_ID=WAREHOUSE99.WAREHOUSE_ID AND
CITY='CHENNAI';

ORDER_ID
-------------
12

//3.DELETE FROM THE ITEM TABLE AND MAKE THE FIELD NULL IN THE
ORD_ITEM TABLE

SOLUTION:

ITEM_ID UNITPRICE
-------- ----------
991 990
950 700
800 1600
1200 2000
990 500

SQL>DELETE FROM ITEM99 WHERE ITEM_ID=990;

ITEM_ID UNITPRICE
----- ----------
991 990
950 700
800 1600
1200 2000

SQL>SELECT * FROM ORDERITEM99;

ORDER# ITEM# QTY


-------- ---------- ----------
10 6
11 991 5
12 950 9
13 800 3
14 1200 7

You might also like