· 7 years ago · Nov 27, 2018, 05:52 AM
1# Drop the tables if they already exist
2DROP TABLE IF EXISTS order_item;
3DROP TABLE IF EXISTS order_;
4DROP TABLE IF EXISTS product;
5DROP TABLE IF EXISTS customer;
6DROP TABLE IF EXISTS employee;
7
8# Create the tables
9CREATE TABLE customer
10(
11 customerID INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
12 firstName VARCHAR(30) NOT NULL,
13 lastName VARCHAR(30) NOT NULL,
14 phone VARCHAR(20) NULL,
15 address1 VARCHAR(30) NULL,
16 address2 VARCHAR(30) NULL,
17 city VARCHAR(30) NULL,
18 state CHAR(2) NULL,
19 zip CHAR(10) NULL
20) TYPE=INNODB;
21
22
23CREATE TABLE employee
24(
25 employeeID INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
26 firstName VARCHAR(30) NOT NULL,
27 lastName VARCHAR(30) NOT NULL
28) TYPE=INNODB;
29
30
31CREATE TABLE product
32(
33 productID INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
34 productName VARCHAR(30) NOT NULL,
35 productVendor VARCHAR(50) NULL,
36 cost FLOAT NOT NULL CHECK (cost > 0), # MySQL ignores constraints
37 price FLOAT NOT NULL CHECK (price > 0) # MySQL ignores constraints
38) TYPE=INNODB;
39
40
41CREATE TABLE order_
42(
43 orderID INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
44 orderDate DATE NOT NULL DEFAULT '2000-01-01',
45 customerID INT NOT NULL,
46 employeeID INT NOT NULL,
47 INDEX (customerID),
48 INDEX (employeeID),
49 FOREIGN KEY (customerID) REFERENCES customer(customerID),
50 FOREIGN KEY (employeeID) REFERENCES employee(employeeID)
51) TYPE=INNODB;
52
53
54CREATE TABLE order_item
55(
56 orderItemID INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
57 orderID INT NOT NULL,
58 productID INT NOT NULL,
59 quantity INT NOT NULL,
60 INDEX (orderID),
61 INDEX (productID),
62 FOREIGN KEY (orderID) REFERENCES order_(orderID),
63 FOREIGN KEY (productID) REFERENCES product(productID)
64) TYPE=INNODB;
65
66
67# Insert data into the customer table
68INSERT INTO customer (firstName, lastName, phone, address1, address2, city,
69 state, zip) VALUES ('Joe', 'Namath', '(205) 648-8169', 'EE 208', NULL,
70 'Tuscaloosa', 'AL', '39762');
71INSERT INTO customer (firstName, lastName, phone, address1, address2, city,
72 state, zip) VALUES ('Ken', 'Stabler', '(987) 456-0515', 'Nash Street',
73 'Apt #1', 'Los Angeles', 'CA', '58746');
74INSERT INTO customer (firstName, lastName, phone, address1, address2, city,
75 state, zip) VALUES ('Bart', 'Star', '(662) 323-4624', 'Box 2345', NULL,
76 'Oxford', 'MS', '39759');
77INSERT INTO customer (firstName, lastName, phone, address1, address2, city,
78 state, zip) VALUES ('John', 'Hannah', '(276) 587-9851', 'Sunset Boulevard',
79 'Number 5', 'Orlando', 'FL', '56984');
80INSERT INTO customer (firstName, lastName, phone, address1, address2, city,
81 state, zip) VALUES ('Don', 'Hutson', '(873) 632-9651', 'Main Street',
82 'Apt B', 'Portland', 'OR', '48751');
83INSERT INTO customer (firstName, lastName, phone, address1, address2, city,
84 state, zip) VALUES ('Lance', 'Cormier', '(759) 695-7541',
85 'University Drive', NULL, 'New York', 'NY', '69354');
86INSERT INTO customer (firstName, lastName, phone, address1, address2, city,
87 state, zip) VALUES ('Joe', 'Sewell', '(456) 325-9654', 'Bourbon Street',
88 'Number 34G', 'Miami', 'FL', '12547');
89INSERT INTO customer (firstName, lastName, phone, address1, address2, city,
90 state, zip) VALUES ('Frank', 'Lary', '(981) 741-8526', '5th Avenue', NULL,
91 'Houston', 'TX', '69541');
92INSERT INTO customer (firstName, lastName, phone, address1, address2, city,
93 state, zip) VALUES ('Del', 'Pratt', '(237) 369-8524', 'Morgan Street',
94 'Apt. 12', 'Minneapolis', 'MN', '78922');
95INSERT INTO customer (firstName, lastName, phone, address1, address2, city,
96 state, zip) VALUES ('Robert', 'Van de Graaff', '(706) 852-9874', 'Box 9874',
97 NULL, 'Reading', 'MA', '96521');
98
99
100# Insert data into the employee table
101INSERT INTO employee (firstName, lastName) VALUES ('Paul', 'Bryant');
102INSERT INTO employee (firstName, lastName) VALUES ('Gene', 'Stallings');
103INSERT INTO employee (firstName, lastName) VALUES ('Mel', 'Allen');
104INSERT INTO employee (firstName, lastName) VALUES ('George', 'Wallace');
105INSERT INTO employee (firstName, lastName) VALUES ('Winton', 'Blount');
106
107
108# Insert data into the order_ table
109INSERT INTO order_ (orderDate, customerID, employeeID) VALUES
110 ('2004-04-12', 1, 1);
111INSERT INTO order_ (orderDate, customerID, employeeID) VALUES
112 ('2005-07-15', 6, 2);
113INSERT INTO order_ (orderDate, customerID, employeeID) VALUES
114 ('2004-02-22', 7, 3);
115INSERT INTO order_ (orderDate, customerID, employeeID) VALUES
116 ('2003-11-09', 5, 1);
117INSERT INTO order_ (orderDate, customerID, employeeID) VALUES
118 ('2004-12-19', 10, 2);
119INSERT INTO order_ (orderDate, customerID, employeeID) VALUES
120 ('2004-06-13', 1, 3);
121INSERT INTO order_ (orderDate, customerID, employeeID) VALUES
122 ('2005-06-15', 10, 5);
123INSERT INTO order_ (orderDate, customerID, employeeID) VALUES
124 ('2005-02-28', 7, 5);
125
126
127# Insert data into the product table
128INSERT INTO product (productName, productVendor, cost, price) VALUES
129 ('Microsoft SQL Sever 2000', 'Microsoft', 1500, 1800);
130INSERT INTO product (productName, productVendor, cost, price) VALUES
131 ('Oracle 9i Server', 'Oracle Corporation', 7000, 9500);
132INSERT INTO product (productName, productVendor, cost, price) VALUES
133 ('Microsoft Windows XP', 'Microsoft', '600', '800');
134INSERT INTO product (productName, productVendor, cost, price) VALUES
135 ('Java Enterprise Edition', 'Sun Microsytems', 100, 120);
136INSERT INTO product (productName, productVendor, cost, price) VALUES
137 ('Paint Shop Pro', 'Jasc', 200, 280);
138INSERT INTO product (productName, productVendor, cost, price) VALUES
139 ('Freelancer', 'Microsoft', 35, 54.95);
140INSERT INTO product (productName, productVendor, cost, price) VALUES
141 ('Jeopardy 2', 'Sony Online Entertainment', 35, 50);
142INSERT INTO product (productName, productVendor, cost, price) VALUES
143 ('Microsoft Visual Studio .NET', 'Microsoft', 1300, 1900);
144INSERT INTO product (productName, productVendor, cost, price) VALUES
145 ('Red Hat Linux', 'Red Hat', 120, 150);
146INSERT INTO product (productName, productVendor, cost, price) VALUES
147 ('Oracle Developer 2003', 'Oracle Corporation', 3500, 5800);
148INSERT INTO product (productName, productVendor, cost, price) VALUES
149 ('Monkey Ball II', 'Sega', 25, 50);
150
151
152# Insert data into the order_item table
153INSERT INTO order_item (orderID, productID, quantity) VALUES (1, 1, 1);
154INSERT INTO order_item (orderID, productID, quantity) VALUES (1, 6, 1);
155INSERT INTO order_item (orderID, productID, quantity) VALUES (2, 1, 15);
156INSERT INTO order_item (orderID, productID, quantity) VALUES (2, 3, 30);
157INSERT INTO order_item (orderID, productID, quantity) VALUES (2, 8, 1);
158INSERT INTO order_item (orderID, productID, quantity) VALUES (2, 10, 25);
159INSERT INTO order_item (orderID, productID, quantity) VALUES (2, 2, 20);
160INSERT INTO order_item (orderID, productID, quantity) VALUES (3, 9, 2);
161INSERT INTO order_item (orderID, productID, quantity) VALUES (3, 10, 2);
162INSERT INTO order_item (orderID, productID, quantity) VALUES (4, 2, 1);
163INSERT INTO order_item (orderID, productID, quantity) VALUES (4, 3, 5);
164INSERT INTO order_item (orderID, productID, quantity) VALUES (4, 5, 1);
165INSERT INTO order_item (orderID, productID, quantity) VALUES (5, 7, 1);
166INSERT INTO order_item (orderID, productID, quantity) VALUES (5, 6, 1);
167INSERT INTO order_item (orderID, productID, quantity) VALUES (6, 7, 1);
168INSERT INTO order_item (orderID, productID, quantity) VALUES (6, 8, 1);
169INSERT INTO order_item (orderID, productID, quantity) VALUES (7, 11, 2);
170INSERT INTO order_item (orderID, productID, quantity) VALUES (7, 7, 2);
171INSERT INTO order_item (orderID, productID, quantity) VALUES (8, 2, 1);
172INSERT INTO order_item (orderID, productID, quantity) VALUES (8, 10, 1);
173INSERT INTO order_item (orderID, productID, quantity) VALUES (4, 6, 2);
174INSERT INTO order_item (orderID, productID, quantity) VALUES (7, 6, 1);