CorrectedSalesDB

-- Paste the following into https://www.programiz.com/sql/online-compiler/

DROP TABLE Customers;
DROP TABLE Orders;
DROP TABLE Shippings;

create table Client_master (
	Client_no varchar(6) primary key,
	Name varchar(20) not null,
	City varchar(15),
	State varchar(15),
	Pincode decimal(6),
	Bal_due decimale(10, 2),
	check(Client_no like 'C%')
);

create table product_master (
	Product_no varchar(6) primary key,
	Description varchar(15) not null,
	Profit_percent decimal(2, 2) not null,
	Unit_measure varchar(10) not null,
	Qty_on_hand decimal(8) not null,
	Reorder_lvl decimal(8) not null,
	Sell_price decimal(8, 2) not null,
	Cost_price decimal(8, 2) not null,
	check(Sell_price <> 0 AND Cost_price <> 0)
);

create table salesman_master (
	Salesman_no varchar(6) primary key,
	Salseman_name varchar(20) not null,
	Address1 varchar(30) not null,
	Address2 varchar(30),
	City varchar(20),
	Pincode varchar(6),
	State varchar(20),
	Sal_amt decimal(8, 2) not null,
	Tgt_to_get decimal(6, 2) not null,
	Ytd_sales decimal(6, 2) not null,
	Remarks varchar(60),
	check(Sal_amt != 0),
	check(Tgt_to_get != 0)
);

create table sales_order (
	S_order_no varchar(6) primary key,
	S_order_date date,
	Client_no varchar(6),
	Dely_addr varchar(25),
	Salesman_no varchar(6),
	Dely_type char(1) default 'F',
	Biled_yn char(1),
	Dely_date date,
	Order_status varchar(10),
	check(S_order_no like 'O%'),
	check(dely_type in ('P', 'F')),
	check(Order_status in ('In Process', 'Fulfilled', 'BackOrder', 'Canceled'))
	check(Dely_date >= S_order_date),
	foreign key(Client_no) references client_master(Client_no),
	foreign key(Salesman_no) references salesman_master(Salesman_no)
);

create table sales_order_details (
	S_order_no varchar(6),
	Product_no varchar(6),
	Qty_ordered decimal(8),
	Qty_disp decimal(8),
	Product_rate decimal(10, 2),
	primary key(S_order_no, Product_no),
	foreign key(S_order_no) references sales_order(S_order_no),
	foreign key(Product_no) references product_master(Product_no)
);

create table Challan_Header (
	Challan_no varchar(6) PRIMARY key,
	S_order_no varchar(6),
	Challan_date date NOT NULL,
	Billed_yn char(1) default 'N',
	check(Challan_no like 'CH%'),
	check(Billed_yn in ('Y', 'N')),
	FOREIGN key(S_order_no) references sales_order(S_order_no)
);

create table Challan_Details (
	Challan_no varchar(6),
	Product_no varchar(6),
	Qty_disp decimal(4, 2) not null,
	primary key(Challan_no, Product_no),
	foreign key(Challan_no) references Challan_Header(Challan_no),
	foreign key(Product_no) references product_master(Product_no)
);









INSERT INTO Client_master VALUES
('C00001', 'Ivan Bayross', 'Bombay', 400054, 'Maharashtra', 15000),
('C00002', 'Vandana Saitwal', 'Madras', 780001, 'Tamil Nadu', 0),
('C00003', 'Pramada Jaguste', 'Bombay', 400057, 'Maharashtra', 5000),
('C00004', 'Basu Navindgi', 'Bombay', 400056, 'Maharashtra', 0),
('C00006', 'Rukmini', 'Bombay', 400050, 'Maharashtra', 0);


INSERT INTO Client_master (Client_no, Name, City, Pincode, Bal_due)
VALUES ('C00005', 'Ravi Sreedharan', 'Delhi', 100001, 2000);


INSERT INTO product_master (Product_no, Description, Profit_percent, Unit_measure, Qty_on_hand, Reorder_lvl, Sell_price, Cost_price)
VALUES ('P00001', '1.44 Floppies', 5, 'Piece', 100, 20, 525, 500),
       ('P03453', 'Monitors', 6, 'Piece', 10, 3, 12000, 11280),
       ('P06734', 'Mouse', 5, 'Piece', 20, 5, 1050, 1000),
       ('P07865', '1.22 Floppies', 5, 'Piece', 100, 20, 525, 500),
       ('P07868', 'Keyboards', 2, 'Piece', 10, 3, 3150, 3050),
       ('P07885', 'CD Drive', 2.5, 'Piece', 10, 3, 5250, 5100),
       ('P07965', '540 HDD', 4, 'Piece', 10, 3, 8400, 8000),
       ('P07975', '1.44 Drive', 5, 'Piece', 10, 3, 1050, 1000),
       ('P08865', '1.22 Drive', 5, 'Piece', 2, 3, 1050, 1000);


INSERT INTO salesman_master (Salesman_no, Salseman_name, Address1, City, Pincode, State, Sal_amt, Tgt_to_get, Ytd_sales, Remarks)
VALUES 
('S00001', 'Kiran', 'A/14, Worli', 'Bombay', 400002, 'MAH', 3000, 100, 50, 'Good'),
('S00002', 'Manish', '65. Nariman', 'Bombay', 400001, 'MAH', 3000, 200, 100, 'Good'),
('S00003', 'Ravi', 'P-7, Bandra', 'Bombay', 400032, 'MAH', 3000, 200, 100, 'Good'),
('S00004', 'Ashish', 'A/5, Juhu', 'Bombay', 400044, 'MAH', 3000, 200, 150, 'Good');

INSERT INTO sales_order 
VALUES 
('O19001', '1996-01-12', 'C00001', '', 'S00001', 'F', 'N', '1996-01-20', 'In Process'),
('O19002', '1996-01-25', 'C00002', '', 'S00002', 'P', 'N', '1996-01-27', 'Canceled'),
('O46865', '1996-02-18', 'C00003', '', 'S00003', 'F', 'Y', '1996-02-20', 'Fulfilled'),
('O19003', '1996-04-03', 'C00001', '', 'S00001', 'F', 'Y', '1996-04-07', 'Fulfilled'),
('O46866', '1996-05-20', 'C00004', '', 'S00002', 'P', 'N', '1996-05-22', 'Canceled'),
('O10008', '1996-05-24', 'C00005', '', 'S00004', 'F', 'N', '1996-05-26', 'In Process');

insert into sales_order_details values
('O19001', 'P00001', 4, 4, 525),
('O19001', 'P07965', 2, 1, 8400),
('O19001', 'P07885', 2, 15, 250),
('O19002', 'P00001', 10, 0, 525),
('O46865', 'P07965', 3, 3, 3150),
('O46865', 'P07885', 3, 1, 5250),
('O46865', 'P00001', 10, 10, 525),
('O46865', 'P03453', 4, 4, 1050),
('O19003', 'P03453', 2, 2, 1050),
('O19003', 'P06734', 1, 1, 12000),
('O46866', 'P07965', 1, 0, 8400),
('O46866', 'P07975', 1, 0, 1050),
('O10008', 'P00001', 10, 5, 525),
('O10008', 'P07975', 5, 3, 1050);



insert into Challan_Header (Challan_no, S_order_no, Challan_date, Billed_yn) values
('CH9001', 'O19001', 1995-12-12, 'Y'),
('CH6865', 'O46865', 1995-11-12, 'Y'),
('CH3965', 'O10008', 1995-10-12, 'Y');

insert into Challan_Details values
('CH9001', 'P00001', 4),
('CH9001', 'P07965', 1),
('CH9001', 'P07885', 1),
('CH6865', 'P07868', 3),
('CH6865', 'P03453', 4),
('CH6865', 'P00001', 10),
('CH3965', 'P00001', 5),
('CH3965', 'P07975', 2);