CREATE TABLE CLIENT_MASTER (
CLIENTNO VARCHAR(6),
NAME VARCHAR(20),
ADDRESS1 VARCHAR(30),
ADDRESS2 VARCHAR(30),
CITY VARCHAR(15),
PINCODE INT(8),
STATE VARCHAR(15),
BALDUE DECIMAL(10,2)
);
CREATE TABLE PRODUCT_MASTER (
PRODUCTNO VARCHAR(6),
DESCRIPTION VARCHAR(15),
PROFITPERCENT DECIMAL(4,2),
UNITMEASURE VARCHAR(10),
QTYONHAND INT(8),
REORDERLVL INT(8),
SELLPRICE DECIMAL(8,2),
COSTPRICE DECIMAL(8,3)
);
CREATE TABLE SALESMAN_MASTER (
SALESMANNO VARCHAR(6),
SALESMANNAME VARCHAR(20),
ADDRESS1 VARCHAR(30),
ADDRESS2 VARCHAR(30),
CITY VARCHAR(20),
PINCODE INT(8),
STATE VARCHAR(20),
SALAMT DECIMAL(8,2),
TGTTOGET DECIMAL(6,2),
YTDSALES DECIMAL(6,2),
REMARKS VARCHAR(60)
);
INSERT INTO CLIENT_MASTER (CLIENTNO, NAME, CITY, PINCODE, STATE, BALDUE)
VALUES
('C00001', 'Ivan Bayross', 'Mumbai', 400054, 'Maharashtra', 15000),
('C00002', 'Mamta Muzumdar', 'Madras', 780001, 'Tamil Nadu', 0),
('C00003', 'Chhaya Bankar', 'Mumbai', 400057, 'Maharashtra', 5000),
('C00004', 'Ashwini Joshi', 'Bangalore', 560001, 'Karnataka', 0),
('C00005', 'Hansel Colaco', 'Mumbai', 400060, 'Maharashtra', 2000),
('C00006', 'Deepak Sharma', 'Mangalore', 560050, 'Karnataka', 0);
INSERT INTO PRODUCT_MASTER (PRODUCTNO, DESCRIPTION, PROFITPERCENT, UNITMEASURE, QTYONHAND, REORDERLVL, SELLPRICE, COSTPRICE)
VALUES
('P00001', 'T-Shirts', 5, 'Piece', 200, 50, 350, 250),
('P0345', 'Shirts', 6, 'Piece', 150, 50, 500, 350),
('P06734', 'Cotton Jeans', 5, 'Piece', 100, 20, 600, 450),
('P07865', 'Jeans', 5, 'Piece', 100, 20, 750, 500),
('P07868', 'Trousers', 2, 'Piece', 150, 50, 850, 550),
('P07885', 'Pull Overs', 2.5, 'Piece', 80, 30, 700, 450),
('P07965', 'Denim Shirts', 4, 'Piece', 100, 40, 350, 250),
('P07975', 'Lycra Tops', 5, 'Piece', 70, 30, 300, 175),
('P08865', 'Skirts', 5, 'Piece', 75, 30, 450, 300);
INSERT INTO SALESMAN_MASTER (SALESMANNO, SALESMANNAME, ADDRESS1, ADDRESS2, CITY, PINCODE, STATE, SALAMT, TGTTOGET, YTDSALES, REMARKS)
VALUES
('S00001', 'Aman', 'A/14', 'Worli', 'Mumbai', 400002, 'Maharashtra', 3000, 100, 50, 'Good'),
('S00002', 'Omkar', '65', 'Nariman', 'Mumbai', 400001, 'Maharashtra', 3000, 200, 100, 'Good'),
('S00003', 'Raj', 'P-7', 'Bandra', 'Mumbai', 400032, 'Maharashtra', 3000, 200, 100, 'Good'),
('S00004', 'Ashish', 'A/5', 'Juhu', 'Mumbai', 400044, 'Maharashtra', 3500, 200, 150, 'Good');
-------------------------------------------------------------------------------------------------------------------------
CREATE TABLE CLIENT_MASTER (
CLIENTNO VARCHAR(6) PRIMARY KEY CHECK (CLIENTNO LIKE 'C%'),
NAME VARCHAR(20) NOT NULL,
ADDRESS1 VARCHAR(30),
ADDRESS2 VARCHAR(30),
CITY VARCHAR(15),
PINCODE INT(8),
STATE VARCHAR(15),
BALDUE DECIMAL(10,2)
);
CREATE TABLE PRODUCT_MASTER (
PRODUCTNO VARCHAR(6) PRIMARY KEY CHECK (PRODUCTNO LIKE 'P%'),
DESCRIPTION VARCHAR(15) NOT NULL,
PROFITPERCENT DECIMAL(4,2) NOT NULL,
UNITMEASURE VARCHAR(10) NOT NULL,
QTYONHAND INT(8) NOT NULL,
REORDERLVL INT(8) NOT NULL,
SELLPRICE DECIMAL(8,2) NOT NULL CHECK (SELLPRICE <> 0),
COSTPRICE DECIMAL(8,2) NOT NULL CHECK (COSTPRICE <> 0)
);
CREATE TABLE SALESMAN_MASTER (
SALESMANNO VARCHAR(6) PRIMARY KEY CHECK (SALESMANNO LIKE 'S%'),
SALESMANNAME VARCHAR(20) NOT NULL,
ADDRESS1 VARCHAR(30) NOT NULL,
ADDRESS2 VARCHAR(30),
CITY VARCHAR(20),
PINCODE INT(8),
STATE VARCHAR(20),
SALAMT DECIMAL(8,2) NOT NULL CHECK (SALAMT <> 0),
TGTTOGET DECIMAL(6,2) NOT NULL CHECK (TGTTOGET <> 0),
YTDSALES DECIMAL(6,2) NOT NULL ,
REMARKS VARCHAR(60)
);
CREATE TABLE SALES_ORDER (
ORDERNO VARCHAR(6),
CLIENTNO VARCHAR(6) NOT NULL,
ORDERDATE DATE NOT NULL,
DELYADDR VARCHAR(25),
SALESMANNO VARCHAR(6),
DELYTYPE CHAR(1) DEFAULT 'F',
BILLYN CHAR(1),
DELYDATE DATE,
ORDERSTATUS VARCHAR(10) CHECK (ORDERSTATUS IN ('In Process', 'Fulfilled', 'BackOrder', 'Cancelled')),
PRIMARY KEY (ORDERNO),
FOREIGN KEY (SALESMANNO) REFERENCES SALESMAN_MASTER(SALESMANNO)
);
CREATE TABLE SALES_ORDER_DETAILS (
ORDERNO VARCHAR(6),
PRODUCTNO VARCHAR(6),
QTYORDERED INT(8),
QTYDISP INT(8),
PRODUCTRATE DECIMAL(10,2),
FOREIGN KEY (ORDERNO) REFERENCES SALES_ORDER(ORDERNO),
FOREIGN KEY (PRODUCTNO) REFERENCES PRODUCT_MASTER(PRODUCTNO)
);
INSERT INTO SALES_ORDER_DETAILS (ORDERNO, PRODUCTNO, QTYORDERED, QTYDISP, PRODUCTRATE)
VALUES
('O19001', 'P00001', 4, 4, 525),
('O19001', 'P07965', 2, 1, 8400),
('O19001', 'P07885', 2, 1, 5250),
('O19002', 'P00001', 10, 0, 525),
('O46865', 'P07868', 3, 3, 3150),
('O46865', 'P07885', 3, 1, 5250),
('O46865', 'P00001', 10, 10, 525),
('O46865', 'P0345', 4, 4, 1050),
('O19003', 'P0345', 2, 2, 1050),
('O19003', 'P06734', 1, 1, 12000),
('O46866', 'P07965', 1, 0, 8400),
('O46866', 'P07975', 1, 0, 1050),
('O19008', 'P00001', 10, 5, 525),
('O19008', 'P07975', 5, 3, 1050);
INSERT INTO CLIENT_MASTER (CLIENTNO, NAME, CITY, PINCODE, STATE, BALDUE)
VALUES
('C00001', 'Ivan Bayross', 'Mumbai', 400054, 'Maharashtra', 15000),
('C00002', 'Mamta Muzumdar', 'Madras', 780001, 'Tamil Nadu', 0),
('C00003', 'Chhaya Bankar', 'Mumbai', 400057, 'Maharashtra', 5000),
('C00004', 'Ashwini Joshi', 'Bangalore', 560001, 'Karnataka', 0),
('C00005', 'Hansel Colaco', 'Mumbai', 400060, 'Maharashtra', 2000),
('C00006', 'Deepak Sharma', 'Mangalore', 560050, 'Karnataka', 0);
INSERT INTO PRODUCT_MASTER (PRODUCTNO, DESCRIPTION, PROFITPERCENT, UNITMEASURE, QTYONHAND, REORDERLVL, SELLPRICE, COSTPRICE)
VALUES
('P00001', 'T-Shirts', 5, 'Piece', 200, 50, 350, 250),
('P0345', 'Shirts', 6, 'Piece', 150, 50, 500, 350),
('P06734', 'Cotton Jeans', 5, 'Piece', 100, 20, 600, 450),
('P07865', 'Jeans', 5, 'Piece', 100, 20, 750, 500),
('P07868', 'Trousers', 2, 'Piece', 150, 50, 850, 550),
('P07885', 'Pull Overs', 2.5, 'Piece', 80, 30, 700, 450),
('P07965', 'Denim Shirts', 4, 'Piece', 100, 40, 350, 250),
('P07975', 'Lycra Tops', 5, 'Piece', 70, 30, 300, 175),
('P08865', 'Skirts', 5, 'Piece', 75, 30, 450, 300);
INSERT INTO SALESMAN_MASTER (SALESMANNO, SALESMANNAME, ADDRESS1, ADDRESS2, CITY, PINCODE, STATE, SALAMT, TGTTOGET, YTDSALES, REMARKS)
VALUES
('S00001', 'Aman', 'A/14', 'Worli', 'Mumbai', 400002, 'Maharashtra', 3000, 100, 50, 'Good'),
('S00002', 'Omkar', '65', 'Nariman', 'Mumbai', 400001, 'Maharashtra', 3000, 200, 100, 'Good'),
('S00003', 'Raj', 'P-7', 'Bandra', 'Mumbai', 400032, 'Maharashtra', 3000, 200, 100, 'Good'),
('S00004', 'Ashish', 'A/5', 'Juhu', 'Mumbai', 400044, 'Maharashtra', 3500, 200, 150, 'Good');
INSERT INTO SALES_ORDER (ORDERNO, CLIENTNO, ORDERDATE, SALESMANNO, DELYTYPE, BILLYN, DELYDATE, ORDERSTATUS)
VALUES
('O19001', 'C00001', '2004-06-12', 'S00001', 'F', 'N', '2002-07-20', 'In Process'),
('O19002', 'C00002', '2004-06-25', 'S00002', 'P', 'N', '2002-06-27', 'Cancelled'),
('O46865', 'C00003', '2004-02-18', 'S00003', 'F', 'Y', '2002-02-20', 'Fulfilled'),
('O19003', 'C00001', '2004-04-03', 'S00001', 'F', 'Y', '2002-04-07', 'Fulfilled'),
('O46866', 'C00004', '2004-05-20', 'S00002', 'P', 'N', '2002-05-22', 'Cancelled'),
('O19008', 'C00005', '2004-05-24', 'S00004', 'F', 'N', '2002-07-26', 'In Process');
Comments
Post a Comment