7pCwuk0r

1DROP TABLE IF EXISTS candy_purchase;
2DROP TABLE IF EXISTS candy_customer;
3DROP TABLE IF EXISTS candy_cust_type;
4DROP TABLE IF EXISTS candy_product;
5
6CREATE TABLE candy_cust_type
7(cust_type CHAR(1) PRIMARY KEY,
8cust_type_desc VARCHAR(10));
9
10CREATE TABLE candy_customer
11(cust_id BIGINT IDENTITY PRIMARY KEY,
12cust_name VARCHAR(30),
13cust_type CHAR(1),
14cust_addr VARCHAR(30),
15cust_zip VARCHAR(15),
16cust_phone VARCHAR(15),
17username VARCHAR(30),
18password VARCHAR(8),
19CONSTRAINT candy_customer_cust_type_fk FOREIGN KEY (cust_type) REFERENCES candy_cust_type(cust_type));
20
21CREATE TABLE candy_product(
22  prod_id BIGINT IDENTITY PRIMARY KEY,
23  prod_desc VARCHAR(30),
24  prod_cost DECIMAL(5,2),
25  prod_price DECIMAL(5,2)
26);
27
28CREATE TABLE candy_purchase(
29  purch_id BIGINT,
30  prod_id BIGINT,
31  cust_id BIGINT,
32  purch_date DATE,
33  delivery_date DATE,
34  pounds FLOAT,
35  status VARCHAR(10),
36  CONSTRAINT candy_purchase_prod_id_fk FOREIGN KEY (prod_id) REFERENCES candy_product(prod_id),
37  CONSTRAINT candy_customer_cust_id_fk FOREIGN KEY (cust_id) REFERENCES candy_customer(cust_id),
38  CONSTRAINT candy_purch_purch_prod_id_pk PRIMARY KEY (purch_id, prod_id, cust_id)
39);
40
41-- insert values into candy_cust_type
42-- this must be done prior to inserting into candy_customer
43INSERT INTO candy_cust_type VALUES ('P', 'Private');
44INSERT INTO candy_cust_type VALUES ('R', 'Retail');
45INSERT INTO candy_cust_type VALUES ('W', 'Wholesale');
46
47-- insert values into candy_customer
48INSERT INTO candy_customer (cust_name, cust_type, cust_addr, cust_zip, cust_phone, username, password) VALUES
49('Joe Jones', 'P', '1234 Main Street', '91212', '434-1231', 'jonesj', '1234');
50INSERT INTO candy_customer (cust_name, cust_type, cust_addr, cust_zip, cust_phone, username, password) VALUES
51('Armstrong, Inc.', 'R', '231 Globe Blvd', '91212', '434-7664', 'armstrong', '3333');
52INSERT INTO candy_customer (cust_name, cust_type, cust_addr, cust_zip, cust_phone, username, password) VALUES
53('Swedish Burgers', 'R', '1889 20th N.E.', '91213', '434-9090', 'swedburg', '2353');
54INSERT INTO candy_customer (cust_name, cust_type, cust_addr, cust_zip, cust_phone, username, password) VALUES
55('Pickled Pickles', 'R', '194 CityView', '91289', '324-8909', 'pickpick', '5333');
56INSERT INTO candy_customer (cust_name, cust_type, cust_addr, cust_zip, cust_phone, username, password) VALUES
57('The Candy Kid', 'W', '2121 Main St.', '91212', '583-4545', 'kidcandy', '2351');
58INSERT INTO candy_customer (cust_name, cust_type, cust_addr, cust_zip, cust_phone, username, password) VALUES
59('Waterman, Al', 'P', '23 Yankee Blvd.', '91234', NULL, 'wateral', '8900');
60INSERT INTO candy_customer (cust_name, cust_type, cust_addr, cust_zip, cust_phone, username, password) VALUES
61('Bobby Bon Bons', 'R', '12 NichiCres.', '91212', '434-9045', 'bobbybon', '3011');
62INSERT INTO candy_customer (cust_name, cust_type, cust_addr, cust_zip, cust_phone, username, password) VALUES
63('Crowsh, Elias', 'P', '7 77th Ave.', '91211', '434-0007', 'crowel', '1033');
64INSERT INTO candy_customer (cust_name, cust_type, cust_addr, cust_zip, cust_phone, username, password) VALUES
65('Montag, susie', 'P', '981 Montview', '91213', '456-2091', 'montags', '9633');
66INSERT INTO candy_customer (cust_name, cust_type, cust_addr, cust_zip, cust_phone, username, password) VALUES
67('Columberg Sweets', 'W', '239 East Falls', '91209', '874-9092', 'columswe', '8399');
68
69-- insert values into candy_product
70INSERT INTO candy_product (prod_desc, prod_cost, prod_price) VALUES('Celestial Cashew Crunch', 7.45, 10);
71INSERT INTO candy_product (prod_desc, prod_cost, prod_price) VALUES('Unbrittle Peanut Paradise', 5.75, 9);
72INSERT INTO candy_product (prod_desc, prod_cost, prod_price) VALUES('Mystery Melange', 7.75, 10.50);
73INSERT INTO candy_product (prod_desc, prod_cost, prod_price) VALUES('Millionaire''s Macadamia Mix', 12.50, 16);
74INSERT INTO candy_product (prod_desc, prod_cost, prod_price) VALUES('Nuts Not Nachos', 6.25, 9.50);
75INSERT INTO candy_product (prod_desc, prod_cost, prod_price) VALUES('Chocolate Excess', 15.25, 20.50);
76
77-- insert values into candy_purchase
78INSERT INTO candy_purchase VALUES (1, 1, 5, '2007-9-6', '2007-9-6', 3.5, 'PAID');
79INSERT INTO candy_purchase VALUES (2, 2, 6, '2007-9-6', '2007-9-7', 15, 'PAID');
80INSERT INTO candy_purchase VALUES (3, 1, 9, '2007-9-6', '2007-9-6', 2, 'PAID');
81INSERT INTO candy_purchase VALUES (3, 3, 9, '2007-9-6', '2007-9-7', 3.7, 'PAID');
82INSERT INTO candy_purchase VALUES (4, 3, 2, '2007-9-6', NULL, 3.7, 'PAID');
83INSERT INTO candy_purchase VALUES (5, 1, 7, '2007-9-6', '2007-9-6', 3.7, 'NOT PAID');
84INSERT INTO candy_purchase VALUES (5, 2, 7, '2007-9-6', '2007-9-6', 1.2, 'NOT PAID');
85INSERT INTO candy_purchase VALUES (5, 3, 7, '2007-9-7', '2007-9-7', 4.4, 'NOT PAID');
86INSERT INTO candy_purchase VALUES (6, 2, 7, '2007-9-7', NULL, 3, 'PAID');
87INSERT INTO candy_purchase VALUES (7, 2, 10, '2007-9-7', NULL, 14, 'NOT PAID');
88INSERT INTO candy_purchase VALUES (7, 5, 10, '2007-9-7', NULL, 4.8, 'NOT PAID');
89INSERT INTO candy_purchase VALUES (8, 1, 4, '2007-9-7', '2007-9-8', 1, 'PAID');
90INSERT INTO candy_purchase VALUES (8, 5, 4, '2007-9-7', NULL, 7.6, 'PAID');
91INSERT INTO candy_purchase VALUES (9, 5, 4, '2007-9-7', '2007-9-8', 3.5, 'NOT PAID');