VjmCECw0

1DROP DATABASE IF EXISTS pct_db;
2CREATE DATABASE IF NOT EXISTS pct_db;
3USE pct_db;
4
5CREATE TABLE IF NOT EXISTS customer(
6custNo INT,
7firstName VARCHAR(15),
8lastName VARCHAR(15),
9email VARCHAR(20),
10phone VARCHAR(15),
11zip VARCHAR(15),
12PRIMARY KEY(custNo)
13);
14
15CREATE TABLE IF NOT EXISTS item (
16itemNo INT,
17deptNo SMALLINT,
18ProdName VARCHAR(20),
19Price DECIMAL(5,2),
20Quant INT,
21Sold INT,
22PRIMARY KEY(itemNo)
23);
24
25INSERT INTO item
26(itemNo, deptNo, ProdName, Price, Quant, Sold)
27VALUES
28(500,2,'hammers', 3, 5, 2),
29(501,2,'locks', 5, 6, 3),
30(502,2,'hinges', 4, 7, 2),
31(503,4,'Flower Pots', 5, 3, 2),
32(504,4,'Potting Soil', 3, 12,3),
33(505,4,'Seeds', 5, 8, 6),
34(506,4,'Decorative Plant', 2 ,6, 4),
35(507,4,'Fence', 1, 5, 2),
36(508,4,'Paving Stones', 2, 6, 4),
37(509,10,'Lawn Mowers', 250, 5, 1),
38(510,10,'Soil Mulchers', 100, 8, 2),
39(515,15,'Shirts', 25, 5, 3),
40(516,15,'Jackets', 20, 5, 3),
41(511,8,'Canned Fruit', 3, 8 , 2),
42(512,8,'Bread', 2, 16, 10),
43(513,8,'Milk', 2, 15, 8),
44(514,8,'Cheese', 2, 10, 8);
45
46INSERT INTO customer
47(custNo, firstname,lastname,email,phone,zip)
48VALUES
49(200,'Andy','Harris','aharris@pct.edu','124-4567','19902'),
50(201,'Randy','Harris','randys@pct.edu','123-4567','20147'),
51(202,'Liz','Jackson','ljackson@pct.edu','456-8967','17701'),
52(203,'John','Smith','jsmith@pct.edu','123-4567','199024'),
53(204,'Tony','Evans','tevans@pct.edu','444-4567','20147'),
54(205,'Tim','Henry','thenry@pct.edu','765-8967','17701')
55;
56
57INSERT INTO orders
58(orderKey,itemNo, custNo)
59
60VALUES
61(600,500,203),
62(620,501,203),
63(630,510,201),
64(640,515,205),
65(650,515,202)
66;
67select custno from orders
68where custno=203;