DBMS Lab Cycle 6 (client_master, product_master, salesman_master, sales_order, sales_order_details)

CREATE THE FOLLOWING TABLES AND INSERT THE VALUES


client_master


product_master


salesman_master


sales_order


sales_order_details



CREATE TABLE client_master(
client_no VARCHAR2(10),
name VARCHAR2(50),
city VARCHAR2(40),
pincode NUMBER(6),
state VARCHAR2(40),
bal_due NUMBER(9,2),
PRIMARY KEY(client_no)
);

INSERT INTO client_master
VALUES ('C00001','Anup Dalal','Mumbai',400054,'Maharashtra',15000);

INSERT INTO client_master  VALUES
('C00002','Vandana Srivastava','Chennai',600018,'Tamil Nadu',0);

INSERT INTO client_master  VALUES
('C00003','Kaustubh Khare','Mumbai',400056,'Maharashtra',5000);

INSERT INTO client_master  VALUES
('C00004','Ashok Agarwal','Mumbai',400057,'Maharashtra',0);

INSERT INTO client_master  VALUES
('C00005','Vivek Misra','Delhi',100001,'Delhi',2000);

INSERT INTO client_master  VALUES
('CC0006','Melvin D''Cruz','Mumbai',400005,'Maharashtra',0);

CREATE TABLE product_master(
product_no VARCHAR2(10),
description VARCHAR2(30),
profit_percent NUMBER,
unit_of_measure VARCHAR2(10),
qty_on_hand NUMBER,
reorder_level NUMBER,
sell_price NUMBER(9,2),
cost_price NUMBER(9,2),
PRIMARY KEY(product_no)
);

INSERT INTO product_master VALUES 
('P00001','1.44 Floppies',5,'piece',100,20,525,500);

INSERT INTO product_master VALUES 
('P03453','Monitors',6,'piece',10,3,12000,11280);

INSERT INTO product_master VALUES 
('P06734','Mouse',5,'piece',20,5,1050,1000);

INSERT INTO product_master VALUES 
('P07865','1.22 Floppies',5,'piece',100,20,525,500);

INSERT INTO product_master VALUES 
('P07868','Keyboards',2,'piece',10,3,3150,3050);

INSERT INTO product_master VALUES 
('P07885','CD Drive',2.5,'piece',10,3,5250,5100);

INSERT INTO product_master VALUES 
('P07965','540HDD',4,'piece',10,3,8400,8000);

INSERT INTO product_master VALUES 
('P07975','1.44 Drive',5,'piece',10,3,1050,1000);

INSERT INTO product_master VALUES 
('P08865','1.22 Drive',5,'piece',2,3,1050,1000);
CREATE TABLE salesman_master(
salesman_no VARCHAR2(10),
name VARCHAR2(50),
address1 VARCHAR2(20),
address2 VARCHAR2(20),
city VARCHAR2(40),
pincode NUMBER(6),
state VARCHAR2(40),
sal_amnt NUMBER(9,2),
trgt_to_get NUMBER,
ytd_sales NUMBER,
remarks VARCHAR2(10),
PRIMARY KEY(salesman_no)
);

INSERT INTO salesman_master VALUES 
('S00001','Hari','A/14','Navi Mumbai','Mumbai',400002,'Maharashtra',3000,100,50,'Good');

INSERT INTO salesman_master VALUES 
('S00002','Manish','65','Nariman','Mumbai',400001,'Maharashtra',3000,200,100,'Good');

INSERT INTO salesman_master VALUES 
('S00003','Ravi','P-7','Santa Cruz','Mumbai',400032,'Maharashtra',3000,200,100,'Good');

INSERT INTO salesman_master VALUES 
('S00004','Ashish','A/5','Andheri','Mumbai',400044,'Maharashtra',3500,200,150,'Good');
CREATE TABLE sales_order(
order_no VARCHAR2(10),
order_date DATE,
client_no VARCHAR2(10) REFERENCES client_master(client_no),
del_type VARCHAR2(2),
billyn VARCHAR2(2),
salesman_no VARCHAR2(10) REFERENCES salesman_master(salesman_no),
dely_date DATE,
order_status VARCHAR2(20),
PRIMARY KEY(order_no)
);

INSERT INTO sales_order
 values ('O19002',to_date('25-JAN-2014'),'C00002','P','N','S00002',to_date('27-JAN-2014'),'Cancelled');

INSERT INTO sales_order 
values ('O19001',to_date('12-JAN-2014'),'C00001','F','N','S00001',to_date('20-JAN-2014'),'In Progress');

INSERT INTO sales_order 
values ('O46865',to_date('18-FEB-2014'),'C00003','F','Y','S00003',to_date('20-FEB-2014'),'Fulfilled');

INSERT INTO sales_order 
values ('O19003',to_date('03-APR-2014'),'C00001','F','Y','S00001',to_date('07-APR-2014'),'Fulfilled');

INSERT INTO sales_order 
values ('O46866',to_date('20-MAY-2014'),'C00004','P','N','S00002',to_date('22-MAY-2014'),'Cancelled');

INSERT INTO sales_order 
values ('O19008',to_date('24-MAY-2014'),'C00005','F','N','S00004',to_date('26-MAY-2014'),'In Progress');
CREATE TABLE sales_order_details(
order_no VARCHAR2(10) REFERENCES sales_order(order_no),
product_no VARCHAR2(10) REFERENCES product_master(product_no),
qty_ordered NUMBER,
qty_disp NUMBER,
product_rate NUMBER(9,2)
);

INSERT INTO sales_order_details 
values ('O19001','P00001',4,4,525);

INSERT INTO sales_order_details 
values ('O19001','P07965',2,1,8400);

INSERT INTO sales_order_details 
values ('O19001','P07885',2,1,5250);

INSERT INTO sales_order_details 
values ('O19002','P00001',10,0,525);

INSERT INTO sales_order_details 
values ('O46865','P07868',3,3,3150);

INSERT INTO sales_order_details 
values ('O46865','P07885',3,1,5250);

INSERT INTO sales_order_details 
values ('O46865','P00001',10,10,525);

INSERT INTO sales_order_details 
values ('O46865','P03453',4,4,1050);

INSERT INTO sales_order_details 
values ('O19003','P03453',2,2,1050);

INSERT INTO sales_order_details 
values ('O19003','P06734',1,1,12000);

INSERT INTO sales_order_details 
values ('O46866','P07965',1,0,8400);

INSERT INTO sales_order_details 
values ('O46866','P07975',1,0,1050);

INSERT INTO sales_order_details 
values ('O19008','P00001',10,5,525);

INSERT INTO sales_order_details 
values ('O19008','P07975',5,3,1050);

--> Display the sales order details of all orders placed by ‘Anup Dalal’.

SELECT * FROM sales_order_details 
WHERE order_no IN
(SELECT order_no FROM sales_order WHERE client_no IN
(SELECT client_no FROM client_master WHERE name LIKE 'Anup Dalal'));
--> Display the products which were ordered by ‘Anup Dalal’ including the quantity.

SELECT pm.description,sod.qty_ordered
FROM client_master cm
LEFT OUTER JOIN sales_order so ON so.client_no = cm.client_no
LEFT OUTER JOIN sales_order_details sod ON sod.order_no = so.order_no
LEFT OUTER JOIN product_master pm ON pm.product_no = sod.product_no
WHERE cm.name LIKE 'Anup Dalal';
--> Display the products which have been sold to ‘Anup Dalal’ including the quantity.

SELECT pm.description,sod.qty_disp
FROM client_master cm
LEFT OUTER JOIN sales_order so ON so.client_no = cm.client_no
LEFT OUTER JOIN sales_order_details sod ON sod.order_no = so.order_no
LEFT OUTER JOIN product_master pm ON pm.product_no = sod.product_no
WHERE cm.name LIKE 'Anup Dalal';
--> Display the customer name, address1, address2, city and pincode for the client who has placed order no ‘O19001’.

SELECT name,city,pincode 
FROM client_master WHERE client_no IN
(SELECT client_no FROM sales_order WHERE order_no LIKE 'O19001');
--> Display the products and their quantities that will have to be delivered in the month of May.

SELECT pm.description,sod.qty_ordered,so.dely_date
FROM sales_order_details sod
LEFT OUTER JOIN product_master pm ON pm.product_no = sod.product_no
LEFT OUTER JOIN sales_order so ON so.order_no = sod.order_no
WHERE EXTRACT(month FROM so.dely_date) = 4;
--> Display the names of clients who have placed orders in Jan 2014.

SELECT name FROM client_master WHERE client_no IN
(SELECT unique client_no FROM sales_order WHERE order_date BETWEEN to_date('01-January-2014') AND to_date('31-January-2014'));
-->Display the names and city of clients who have placed orders with products at the rate 525.

SELECT cm.name,cm.city
FROM sales_order_details sod
LEFT OUTER JOIN sales_order so ON sod.order_no = so.order_no
LEFT OUTER JOIN client_master cm ON cm.client_no = so.client_no
WHERE sod.product_rate = 525;
--> Display the names of clients who have purchased ‘CD Drive’.

SELECT cm.name
FROM sales_order_details sod
LEFT OUTER JOIN sales_order so ON so.order_no = sod.order_no
LEFT OUTER JOIN product_master pm ON pm.product_no = sod.product_no
LEFT OUTER JOIN client_master cm ON cm.client_no = so.client_no
WHERE pm.description LIKE 'CD Drive';
-->Display the names of clients whose orders are Full filled

SELECT name 
FROM client_master WHERE client_no IN
(SELECT client_no FROM sales_order WHERE order_status LIKE 'Fulfilled');
--> Display the products and their quantities for the orders which are cancelled

SELECT pm.description,sod.qty_ordered
FROM sales_order so
LEFT OUTER JOIN sales_order_details sod ON sod.order_no = so.order_no
LEFT OUTER JOIN product_master pm ON pm.product_no = sod.product_no
WHERE so.order_status LIKE 'Cancelled';
-->Display the products and their quantities for the orders placed by ‘Ashok Agarwal’ and ‘Vivek Mishra.

SELECT pm.description,sod.qty_ordered
FROM sales_order so
LEFT OUTER JOIN sales_order_details sod ON sod.order_no = so.order_no
LEFT OUTER JOIN product_master pm ON pm.product_no = sod.product_no
LEFT OUTER JOIN client_master cm ON cm.client_no = so.client_no
WHERE cm.name LIKE 'Vivek Misra' OR cm.name LIKE 'Ashok Agarwal';
-->Display the products and their quantities for the orders placed by client_no ‘C00001’ and ‘C00002’.

SELECT pm.description,sod.qty_ordered
FROM sales_order so
LEFT OUTER JOIN sales_order_details sod ON sod.order_no = so.order_no
LEFT OUTER JOIN product_master pm ON pm.product_no = sod.product_no
LEFT OUTER JOIN client_master cm ON cm.client_no = so.client_no
WHERE cm.client_no LIKE 'C00001' OR cm.client_no LIKE 'C00002';
--> Display the names of clients who have placed orders before the month of May 2014.

SELECT name 
FROM client_master WHERE client_no IN
(SELECT UNIQUE client_no FROM sales_order WHERE order_date < to_date('01-May-2014'));



Comments

Post a Comment

Popular posts from this blog

DBMS Lab Cycle 4 (sailor,boat,reserve)

Image
Create the following tables and execute the queries given below sailor boat reserve CREATE TABLE sailors( sid NUMBER(3), sname varchar2(100), rating NUMBER(2), age NUMBER(3), PRIMARY KEY(sid) ); INSERT INTO sailors VALUES(22,'Dustin',7,45); INSERT INTO sailors VALUES(29,'Brutas',1,33); INSERT INTO sailors VALUES(31,'Luber',8,55); INSERT INTO sailors VALUES(32,'Andy',8,25); INSERT INTO sailors VALUES(58,'Rusty',10,35); INSERT INTO sailors VALUES(64,'Horatio',9,35); INSERT INTO sailors VALUES(71,'Zorba',10,16); INSERT INTO sailors VALUES(74,'Horatio',9,35); INSERT INTO sailors VALUES(85,'Art',3,25.5); INSERT INTO sailors VALUES(95,'Bob',3,63.5); CREATE TABLE boats( bid NUMBER(3), bname VARCHAR2(50), color VARCHAR2(25), PRIMARY KEY(bid) ); INSERT INTO boats VALUES(101,'Interlake','Blue'); INSERT INTO boats VALUES(102,'Inter...
Read more

DBMS Lab Cycle 2 (student_info)

Image
1.  Create the table STUDENT_INFO with Columns: Sid, Stud_name & stude_score . create table STUDENT_INFO( sid NUMBER(3) NOT NULL, stud_name VARCHAR2(100) NOT NULL, stud_score NUMBER(3) DEFAULT 20, primary key(sid) ); -->   Insert  values into STUDENT_INFO with the following constraints: Stud name NOT NULL and stude_score DEFAULT value of 20 . INSERT INTO STUDENT_INFO (sid,stud_name) VALUES(9,'Adharsh Sunny'); INSERT INTO STUDENT_INFO VALUES(2'Dimal Mathew',200); INSERT INTO STUDENT_INFO VALUES(3,'Arun Joji',170); INSERT INTO STUDENT_INFO VALUES(4,'MAnuel Cherian',180); INSERT INTO STUDENT_INFO VALUES(5,'Christin Chacko',90); INSERT INTO STUDENT_INFO VALUES(6,'Jithu Sebastian',110); INSERT INTO STUDENT_INFO VALUES(7,'Fazil Habeeb',160); INSERT INTO STUDENT_INFO VALUES(8,'Visakh harikumar',101); -->   Update stude_score by adding a value of 5 to stude_score in the table STUDENT_...
Read more