Database Management Systems (DBMS) Lab Program
Question 2
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;
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;
explain the each wards of query so we can easily understand
ReplyDelete