Database Management Systems (DBMS) Lab Program 2

Database Management Systems (DBMS) Lab Program

Question 2

 

 

CREATE TABLE customer (
cust# NUMBER(10) CONSTRAINT cc1 PRIMARY KEY,
cname VARCHAR(20) CONSTRAINT cc2 NOT NULL,
city VARCHAR(30)
);

CREATE TABLE ord (
order# NUMBER(10) CONSTRAINT o1 PRIMARY KEY,
odate DATE CONSTRAINT o2 NOT NULL,
cust# NUMBER(10) CONSTRAINT o3 REFERENCES customer(cust#),
ord_amt NUMBER(10,3) CONSTRAINT o4 CHECK(ord_amt>0)
);

CREATE TABLE item (
item# NUMBER(10) CONSTRAINT i1 PRIMARY KEY,
unitPrice NUMBER(10,3) CONSTRAINT i2 CHECK(unitPrice>0)
);

CREATE TABLE order_item (
order# NUMBER(10) CONSTRAINT oi1 REFERENCES ord(order#) ON DELETE CASCADE,
item# NUMBER(10) CONSTRAINT oi2 REFERENCES item(item#) ON DELETE CASCADE,
qty NUMBER(6) CONSTRAINT oi3 CHECK(qty>0),
CONSTRAINT oi4 PRIMARY KEY(order#,item#)
);

CREATE TABLE warehouse (
warehouse# NUMBER(10) CONSTRAINT w1 PRIMARY KEY,
city VARCHAR(30)
);

CREATE TABLE shippment (
ORDER# NUMBER(10) CONSTRAINT s1 REFERENCES ord(order#) ON DELETE CASCADE,
warehouse# NUMBER(10) CONSTRAINT s2 REFERENCES warehouse(warehouse#) ON DELETE CASCADE,
ship_date DATE CONSTRAINT s3 NOT NULL,
CONSTRAINT s4 PRIMARY KEY(warehouse#,order#)
);

commit;

INSERT INTO customer  VALUES(1,'Adarsha','Bangalore');
INSERT INTO customer  VALUES(2,'Ashish','Bangalore');
INSERT INTO customer  VALUES(3,'Bhavesh','Bangalore');
INSERT INTO customer  VALUES(4,'Anirudh','Bangalore');
INSERT INTO customer  VALUES(5,'Abhishek','Bangalore');


INSERT INTO ord  VALUES(1,'1-Jan-1990',1,2500);
INSERT INTO ord  VALUES(2,'2-Jan-1990',1,4500);
INSERT INTO ord  VALUES(3,'3-Jan-1990',2,6955);
INSERT INTO ord  VALUES(4,'4-Jan-1990',4,1778);
INSERT INTO ord  VALUES(5,'5-Jan-1990',5,2345);


INSERT INTO item  VALUES(1,89);
INSERT INTO item  VALUES(2,87);
INSERT INTO item  VALUES(3,45);
INSERT INTO item  VALUES(4,47);
INSERT INTO item  VALUES(5,56);


INSERT INTO order_item  VALUES(1,2,5);
INSERT INTO order_item  VALUES(1,5,4);
INSERT INTO order_item  VALUES(2,5,3);
INSERT INTO order_item  VALUES(3,1,4);
INSERT INTO order_item  VALUES(4,2,8);
INSERT INTO order_item  VALUES(5,3,2);

INSERT INTO warehouse  VALUES(1,'Bangalore');
INSERT INTO warehouse  VALUES(2,'Bagalkot');
INSERT INTO warehouse  VALUES(3,'Bijapur');
INSERT INTO warehouse  VALUES(4,'Bidar');
INSERT INTO warehouse  VALUES(5,'Bannerghatta');


INSERT INTO shippment  VALUES(1,2,'2-Jan-1990');
INSERT INTO shippment  VALUES(1,4,'2-Jan-1990');
INSERT INTO shippment  VALUES(2,1,'3-Jan-1990');
INSERT INTO shippment  VALUES(3,5,'4-Jan-1990');
INSERT INTO shippment  VALUES(4,2,'5-Jan-1990');
INSERT INTO shippment  VALUES(5,4,'6-Jan-1990');



SELECT * FROM customer;
SELECT * FROM ord;
SELECT * FROM item;
SELECT * FROM order_item;
SELECT * FROM warehouse;
SELECT * FROM shippment;

DESC customer;
DESC ord;

 
DESC item;
DESC order_item;
DESC warehouse;
DESC shippment;

commit;
-----------------------------------------------------------------------------------------------------------
a:
SELECT * FROM customer;
SELECT * FROM ord;

SELECT c.cname as customer_name ,count(o.order#) as No_of_Orders ,avg(ord_amt) as average_amount
FROM customer c,ord o
WHERE c.cust#=o.cust#
GROUP BY c.cust#, c.cname;
------------------------------------------------------------------------------------------------------------
b:
SELECT order#
FROM

(SELECT count(warehouse#) as tot1
FROM warehouse w
WHERE w.city LIKE ‘Bidar’) A,
(SELECT count(s.order#) as tot2
FROM shippment s
WHERE s.warehouse# IN
(SELECT w.warehouse#
FROM warehouse w
WHERE w.city LIKE ‘Bidar’)
GROPU BY s.order#) B
WHERE A.tot1=B.tot2;
------------------------------------------------------------------------------------------------------------
c:
DELETE FROM item WHERE item#=2;