drop table if exists ORDERS;
drop table if exists PRODUCTS;
drop table if exists CUSTOMERS;
drop table if exists OFFICES;
drop table if exists SALESREPS;

create table ORDERS(
ORDER_NUM varchar(6),
ORDER_DATA date,
CUST char(4),
REP char(3),		
MFR char(3),
PRODUCT varchar(10),
QTY int, 
TOTAL_AMOUNT decimal (10,2),
constraint pk_orders primary key (ORDER_NUM)
);

create table PRODUCTS(
MFR_ID char(3),
PRODUCT_ID varchar(10),
DESCRIPTION varchar(20),
UNIT_PRICE decimal (10,2),
QTY_ON_HAND int,
constraint pk_products primary key(MFR_ID, PRODUCT_ID)
);

create table CUSTOMERS(
CUST_NUM char(4),
COMPANY varchar(20),
CUST_REP char(3),
CREDIT_LIMIT decimal (10,2),
constraint pk_customers primary key (CUST_NUM)
);

create table OFFICES(
OFFICE_NUM char(2),
CITY varchar(20),
REGION varchar(10),
MGR char(3),
TARGET decimal (10,2),
SALES decimal (10,2),
constraint pk_offices primary key (OFFICE_NUM)
);

create table SALESREPS(
EMP_NUM char(3),
NAME varchar(20),
AGE int,
REP_OFFICE char(2),
TITLE varchar(10),
MANAGER char(3), 
HIRE_DATE date,
QUOTA decimal (10,2),
SALES decimal (10,2),
constraint pk_salesRep primary key (EMP_NUM)
);

INSERT INTO CUSTOMERS VALUES('2101', 'Jones Mfg.', '106', '65000');
INSERT INTO CUSTOMERS VALUES('2102', 'First Corp.', '101', '65000');
INSERT INTO CUSTOMERS VALUES('2103', 'Acme Mfg.', '105', '50000');
INSERT INTO CUSTOMERS VALUES('2105', 'AAA Investments','101', '45000' );
INSERT INTO CUSTOMERS VALUES('2106', 'Fred Lewis Corp.', '102', '65000');
INSERT INTO CUSTOMERS VALUES('2107', 'Ace International', '110', '35000');
INSERT INTO CUSTOMERS VALUES('2108', 'Holm & Landis', '109', '55000');
INSERT INTO CUSTOMERS VALUES('2109', 'Chen Associates', '103', '25000');
INSERT INTO CUSTOMERS VALUES('2111', 'JCP Inc.', '103', '50000');
INSERT INTO CUSTOMERS VALUES('2112', 'Zetacorp', '108', '50000');
INSERT INTO CUSTOMERS VALUES('2113', 'Ian & Schmidt', '104', '20000');
INSERT INTO CUSTOMERS VALUES('2114', 'Orion Corp.', '102', '20000');
INSERT INTO CUSTOMERS VALUES('2115', 'Smithson Corp.', '101', '20000');
INSERT INTO CUSTOMERS VALUES('2117', 'J.P. Sinclair', '106', '35000');
INSERT INTO CUSTOMERS VALUES('2118', 'Miswest Sytems', '108', '60000');
INSERT INTO CUSTOMERS VALUES('2119', 'Solomon Inc.', '109', '25000');
INSERT INTO CUSTOMERS VALUES('2120', 'Rico Enterprises', '102', '50000');
INSERT INTO CUSTOMERS VALUES('2121', 'QMA Assoc.', '103', '54000');
INSERT INTO CUSTOMERS VALUES('2122', 'Three-Way Lines', '105', '30000');
INSERT INTO CUSTOMERS VALUES('2123', 'Carter & sons', '102', '40000');
INSERT INTO CUSTOMERS VALUES('2124', 'Peter Brothers', '107', '40000');

INSERT INTO OFFICES VALUES ('11','New York','Eastern','106','575000','692637');
INSERT INTO OFFICES VALUES ('12','Chicago','Eastern','104','800000','735042');
INSERT INTO OFFICES VALUES ('13','Atlanta','Eastern','105','350000','367911');
INSERT INTO OFFICES VALUES ('21','Los Angeles','Western','108','725000','835915');
INSERT INTO OFFICES VALUES ('22','Denver','Western','108','300000','186042');

INSERT INTO ORDERS VALUES ('112961','1999-12-17','2117','106','REI','2A44L',7,'31500');
INSERT INTO ORDERS VALUES ('112963','1999-12-17','2103','105','ACI','41004',28,'3276');
INSERT INTO ORDERS VALUES ('112968','1999-10-12','2102','101','ACI','41004',34,'3978');
INSERT INTO ORDERS VALUES ('112975','1999-10-12','2111','103','REI','2A44G',6,'2100');
INSERT INTO ORDERS VALUES ('112979','1999-10-12','2114','102','ACI','4100Z',6,'15000');
INSERT INTO ORDERS VALUES ('112983','1999-12-27','2103','105','ACI','41004',6,'702');
INSERT INTO ORDERS VALUES ('112987','1999-12-31','2103','105','ACI','4100Y',11,'27500');
INSERT INTO ORDERS VALUES ('112989','2000-01-03','2101','106','FEA','114',6,'1458');
INSERT INTO ORDERS VALUES ('112992','1999-11-04','2118','108','ACI','41002',10,'760');
INSERT INTO ORDERS VALUES ('112993','1999-01-04','2106','102','REI','2A45C',24,'1896');
INSERT INTO ORDERS VALUES ('112997','2000-01-08','2124','107','BIC','41003',1,'652');
INSERT INTO ORDERS VALUES ('113003','2000-01-25','2108','109','IMM','779C',3,'5625');
INSERT INTO ORDERS VALUES ('113007','2000-01-08','2112','108','IMM','773C',3,'2925');
INSERT INTO ORDERS VALUES ('113012','2000-01-11','2111','105','ACI','41003',35,'3745');
INSERT INTO ORDERS VALUES ('113013','2000-01-14','2118','108','BIC','41003',1,'652');
INSERT INTO ORDERS VALUES ('113024','2000-01-20','2114','108','QSA','XK47',20,'7100');
INSERT INTO ORDERS VALUES ('113027','2000-01-22','2103','105','ACI','41002',54,'4104');
INSERT INTO ORDERS VALUES ('113034','2000-01-29','2107','110','REI','2A45C',8,'632');
INSERT INTO ORDERS VALUES ('113036','2000-01-30','2107','110','ACI','4100Z',9,'22500');
INSERT INTO ORDERS VALUES ('113042','2000-02-02','2113','101','REI','2A44R',5,'22500');
INSERT INTO ORDERS VALUES ('113045','2000-02-02','2112','108','REI','2A44R',10,'45000');
INSERT INTO ORDERS VALUES ('113048','2000-02-10','2120','102','IMM','779C',2,'3750');
INSERT INTO ORDERS VALUES ('113049','2000-02-10','2118','108','QSA','XK47',2,'776');
INSERT INTO ORDERS VALUES ('113051','2000-02-10','2118','108','QSA','XK47',4,'1420');
INSERT INTO ORDERS VALUES ('113055','2000-02-15','2108','101','ACI','4100X',6,'150');
INSERT INTO ORDERS VALUES ('113057','2000-02-18','2111','103','ACI','4100X',24,'600');
INSERT INTO ORDERS VALUES ('113058','2000-02-23','2108','109','FEA','112',10,'1480');
INSERT INTO ORDERS VALUES ('113062','2000-02-24','2124','107','FEA','114',10,'2430');
INSERT INTO ORDERS VALUES ('113065','2000-02-27','2106','102','QSA','XK47',6,'2130');
INSERT INTO ORDERS VALUES ('113069','2000-03-02','2109','107','IMM','775C',22,'31350');

INSERT INTO PRODUCTS VALUES ('ACI','41002','Size 2 Widget', '76',167);
INSERT INTO PRODUCTS VALUES ('ACI','41003','Size 3 Widget','107',207);
INSERT INTO PRODUCTS VALUES ('ACI','41004','Size 4 Widget','117',139);
INSERT INTO PRODUCTS VALUES ('ACI','4100X','Widget Adjuster','25',37);
INSERT INTO PRODUCTS VALUES ('ACI','4100Y','Widget Remover','2750',25);
INSERT INTO PRODUCTS VALUES ('ACI','4100Z','Size 1 Widget','55',277);
INSERT INTO PRODUCTS VALUES ('ACI','4101','Widget Intaller','2500',28);
INSERT INTO PRODUCTS VALUES ('BIC','41003','Handle','652',3);
INSERT INTO PRODUCTS VALUES ('BIC','41089','Retainer','225',78);
INSERT INTO PRODUCTS VALUES ('BIC','41675','Plate','180',0);
INSERT INTO PRODUCTS VALUES ('FEA','112','Housing','148',115);
INSERT INTO PRODUCTS VALUES ('FEA','114','Motor Mount','243',5);
INSERT INTO PRODUCTS VALUES ('IMM','773C','300-lb Brace','975',28);
INSERT INTO PRODUCTS VALUES ('IMM','775C','500 -lb Brace','1425',5);
INSERT INTO PRODUCTS VALUES ('IMM','779C','900 -lb Brace','1875',9);
INSERT INTO PRODUCTS VALUES ('IMM','887H','Brace Holder','54',223);
INSERT INTO PRODUCTS VALUES ('IMM','887P','Brace Pin','250',24);
INSERT INTO PRODUCTS VALUES ('IMM','887X','Brace Retainer','475',32);
INSERT INTO PRODUCTS VALUES ('QSA','XK47','Reducer','355',15);
INSERT INTO PRODUCTS VALUES ('QSA','XK48','Reducer','134',203);
INSERT INTO PRODUCTS VALUES ('QSA','XK48A','Reducer','177',37);
INSERT INTO PRODUCTS VALUES ('REI','2A44G','Hinge Pin','350',14);
INSERT INTO PRODUCTS VALUES ('REI','2A44L','Left Hinge','4500',12);
INSERT INTO PRODUCTS VALUES ('REI','2A44R','Right Hinge','4500',12);
INSERT INTO PRODUCTS VALUES ('REI','2A45C','Ratchet Link','79',210);

INSERT INTO SALESREPS VALUES ('101','Dan Roberts',45,'12','Sales Rep', '104', '1996-10-20','300000','305673');
INSERT INTO SALESREPS VALUES ('102','Sue Smith',48,'21','Sales Rep','108','1996-12-10', '350000','474050');
INSERT INTO SALESREPS VALUES ('103','Paul Cruz',29,'12','Sales Rep','104','1997-03-01', '275000','286775');
INSERT INTO SALESREPS VALUES ('104','Bob Smith',33,'12','Sales Mrg','106','1997-05-19', '200000','142594');
INSERT INTO SALESREPS VALUES ('105','Bill Adams',37,'13','Sales Rep','104','1996-02-12', '350000','367911');
INSERT INTO SALESREPS VALUES ('106','Sam Clark',52,'11','Vp Sales',  null,'1998-06-14', '275000','299912');
INSERT INTO SALESREPS VALUES ('107','Nancy Angelli',49,'22','Sales Rep','108','1998-11-14','300000','186042');
INSERT INTO SALESREPS VALUES ('108','Larry Fitch',62,'21','Sales Mrg',  '106','1999-10-12', '350000','361865');
INSERT INTO SALESREPS VALUES ('109','Mary Jones',31,'11','Sales Rep',   '106', '1999-10-12','300000','392725');
INSERT INTO SALESREPS VALUES ('110','Tom Snyder',41,NULL,'Sales Rep',   '101','2000-01-13', NULL,'75985');

select * from SALESREPS;