1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
|
drop table ORDERS;
drop table PRODUCTS;
drop table CUSTOMERS;
drop table OFFICES;
drop table 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,
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),
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 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)
);
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-12-10','2102','101','ACI','41004',34,'3978');
INSERT INTO ORDERS VALUES ('112975','1999-12-10','2111','103','REI','2A44G',6,'2100');
INSERT INTO ORDERS VALUES ('112979','1999-12-10','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','1900-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-24','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-10-02','2120','102','IMM','779C',2,'3750');
INSERT INTO ORDERS VALUES ('113049','2000-10-02','2118','108','QSA','XK47',2,'776');
INSERT INTO ORDERS VALUES ('113051','2000-10-02','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-10-12', '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;
|
