Aim: Perform the following Create tables in database.
1. Customer Table
CREATE TABLE customer (
custno VARCHAR(10) NOT NULL,
cname VARCHAR(30) NOT NULL,
addr VARCHAR(30) NOT NULL,
city VARCHAR(20) NOT NULL,
creditlimit INT,
PRIMARY KEY(custno));
2. Product Table
CREATE TABLE product (
productnc VARCHAR(10) NOT NULL,
description VARCHAR(20) NOT NULL,
unitmeasure INT,
qtyonhand INT,
sellprice DECIMAL(7,2),
costprice DECIMAL(7,2),
PRIMARY KEY (productno));
3.Salesmanmaster Table
CREATE TABLE salesmanmaster (
salesmanno VARCHAR(10) NOT NULL,
sname VARCHAR(20) NOT NULL,
address VARCHAR(30),
city VARCHAR(20),
salesamt DECIMAL(10,2),
target DECIMAL(10,2),
PRIMARY KEY (salesmanno));
4.Salesorder table
CREATE TABLE salesorder
(orderno VARCHAR(10) NOT NULL,
custno VARCHAR(10) NOT NULL,
orderdt DATE,
salesmanno VARCHAR(10) NOT NULL,
orderstatus VARCHAR(25),
PRIMARY KEY (orderno),
FOREIGN KEY (custno) REFERENCES customer(custno),
FOREIGN KEY (salesmanno) REFERENCES
salesmanmaster(salesmanno));
5.Salesorderdetails table
CREATE TABLE salesorderdetails
(orderno VARCHAR(10) NOT NULL,
productno VARCHAR(10) NOT NULL,
qtyordered INT,
qtydisp INT,
FOREIGN KEY (orderno) REFERENCES sales order(orderno),
FOREIGN KEY (productno) REFERENCES product(productno));
INSERTION OF DATA IN TABLES
1. Customer Table
INSERT INTO customer (custno,cname, addr,city,creditlimit)
VALUES("C1", "Akash Singh", "Main Street", "Delhi",50000);
INSERT INTO customer (custno,cname,addr,city,creditlimit)
VALUES("C2", "Mayur Rai", "Ranade Road","Mumbai",45000);
INSERT INTO customer (custno,cname, addr,city,creditlimit)
VALUES("C3", "Sagar Mehta", "Karol Baug","Delhi",70000);
INSERT INTO customer (custno,cname,addr,city,creditlimit)
VALUES ("C4", "Kunal Thakare", "Link
Road","Mumbai",20000);
INSERT INTO customer
(custno,cname, addr,city,creditlimit)
VALUES("C5", "Pankaj Akre", "Savarkar
Marg","Bangalore",55000);
select * from customer;
2. Product Table
INSERT INTO product (productno,description,
unitmeasure,qtyonhand,sellprice,costprice)
VALUES ("P1","T-Shirts", 1,50,350,300);
INSERT INTO product
(productno,description,unitmeasure,qtyonhand,sellprice,costprice)
VALUE("P2","Jeans", 1,45,1000,900);
INSERT INTO product
(productno,description,unitmeasure,qtyonhand,sellprice,costprice)
VALUES ("P3", "Trousers", 1,60,670,550);
INSERT INTO product
(productno,description,unitmeasure,qtyonhand,sellprice,costprice)
VALUES("P4", "Skirts", 1,80,500,400);
INSERT INTO product
(productno,description,unitmeasure,qtyonhand,sellprice,costprice)
VALUES("F5","Shirts",1,65,2000,1500);
select * from product;
3. Salesmanmaster Table
INSERT INTO salesmanmaster
(salesmanno,sname, address,city,salesamt,target)
VALUES("S1","Taral Jain",
"R.J.Campus","Mumbai",95000.00,100000.00);
INSERT INTO salesmanmaster,
(salesmanno,sname, address,city,salesamt,target)
VALUES("S2", "Eshan Singh","S.V.Road","Mumbai",200000.00,
175000.00);
INSERT INTO salesmanmaster
(salesmanno,sname, address,city,salesamt,target)
VALUES ("S3","Parswa Mehta","Karol Baug",
"Delhi",90000.00,75000.00);
INSERT INTO salesmanmaster
(salesmanno,sname, address,city,salesamt,target)
VALUES("S4","Raj Malhotra", "N.V.Road",
"Bangalore",50000.00,40000.00);
INSERT INTO salesmanmaster
(salesmanno,sname, address,city,salesamt,target)
VALUES("S5","Aniruddha Marathe", "Link Road","Mumbai",
100000.00,85000.00);
select * from salesmanmaster;
4. Salesorder Table
INSERT INTO salesorder
(orderno,custno,orderdt,salesmanno,orderstatus)
VALUES ("01","C1","2015-03-12","S1", "In Process");
INSERT INTO salesorder
(orderno,custno,orderdt,salesmanno,orderstatus)
VALUES ("02", "C1","2015-07-06","S2","Fulfilled");
INSERT INTO salesorder
(orderno,custno,orderdt,salesmanno,orderstatus)
VALUES("03", "C2","2015-8-10","S2","Fulfilled");
INSERT INTO salesorder
(orderno,custno,orderdt,salesmanno,orderstatus)
VALUES("04","C3","2015-12-23","S3","Cancelled");
INSERT INTO salesorder
(orderno,custno,orderdt,salesmanno,orderstatus)
VALUES("05","C4", "2015-09-04","S1","Fulfilled");
INSERT INTO salesorder
(orderno,custno,orderdt,salesmanno,orderstatus)
VALUES ("06","C1","2015-11-12","S2","Fulfilled");
select * from salesorder;
5. Salesorderdetails Table
INSERT INTO salesorderdetails
(orderno,productno,atyordered,qtydisp)
VALUES("01","P1",100,10);
INSERT INTO sales orderdetails
(orderno,productno,qtyordered,qtydisp) VALUES
("01","P2",120,60);
INSERT INTO sales orderdetails
(orderno,productno,qtyordered,qtydisp)
VALUES("02","P3",300,300);
INSERT INTO salesorderdetails
(orderno,productno,qtyordered,qtydisp)
VALUES ("02","P1",100,100);
INSERT INTO salesorderdetails
(orderno,productno,qtyordered,qtydisp)
VALUES("03","P2",220,220);
INSERT INTO salesorderdetails
(orderno,productno,qtyordered,qtydisp)
VALUES("04","P1",120,0);
INSERT INTO sales orderdetails
(orderno,productno,qtyordered,qtydisp)
VALUES("05","P3",400,400);
INSERT INTO salesorderdetails
(orderno,productno,qtyordered,qtydisp) VALUES
("06","P2",300,300);
