XwMq8Hmr

1DROP DATABASE IF EXISTS test;
2CREATE DATABASE test;
3USE test;
4CREATE TABLE customer
5
6(
7    customer_id INT AUTO_INCREMENT NOT NULL PRIMARY KEY,
8    title char(4),
9    fname varchar(32),
10    lname varchar(32) NOT NULL,
11    addressline varchar(64),
12    town varchar(32),
13    zipcode char(10) NOT NULL,
14    phone varchar(16)
15);
16
17CREATE TABLE item
18(
19    item_id INT AUTO_INCREMENT NOT NULL PRIMARY KEY,
20    description varchar(64) NOT NULL,
21    cost_price numeric(7,2),
22    sell_price numeric(7,2)
23);
24CREATE TABLE orderinfo
25(
26    orderinfo_id INT AUTO_INCREMENT NOT NULL PRIMARY KEY,
27    customer_id integer NOT NULL,
28    date_placed date NOT NULL,
29    date_shipped date,
30    shipping numeric(7,2),
31    INDEX (customer_id),
32    CONSTRAINT orderinfo_customer_id_fk FOREIGN KEY(customer_id) REFERENCES customer(customer_id)
33);
34CREATE TABLE stock
35(
36    item_id integer NOT NULL AUTO_INCREMENT PRIMARY KEY,
37    quantity integer NOT NULL,
38    CONSTRAINT stock_item_id_fk FOREIGN KEY(item_id) REFERENCES item(item_id)
39);
40CREATE TABLE orderline
41(
42    orderinfo_id integer NOT NULL,
43    item_id integer NOT NULL,
44    quantity integer NOT NULL,
45    PRIMARY KEY(orderinfo_id, item_id),
46    INDEX(item_id),
47    CONSTRAINT orderline_orderlinfo_id_fk FOREIGN KEY (orderinfo_id) REFERENCES orderinfo(orderinfo_id),
48    CONSTRAINT orderline_item_id_fk FOREIGN KEY (item_id) REFERENCES item(item_id)
49);
50CREATE TABLE barcode
51(
52    barcode_ean char(13) NOT NULL PRIMARY KEY,
53    item_id integer NOT NULL,
54    INDEX(item_id),
55    CONSTRAINT barcode_item_id_fk FOREIGN KEY(item_id) REFERENCES item(item_id)
56);
57INSERT INTO customer(title, fname, lname, addressline, town, zipcode, phone) VALUES('Miss','Jenny','Stones','27 Rowan Avenue','Hightown','NT2 1AQ','023 9876');
58INSERT INTO customer(title, fname, lname, addressline, town, zipcode, phone) VALUES('Mr','Andrew','Stones','52 The Willows','Lowtown','LT5 7RA','876 3527');
59INSERT INTO customer(title, fname, lname, addressline, town, zipcode, phone) VALUES('Miss','Alex','Matthew','4 The Street','Nicetown','NT2 2TX','010 4567');
60INSERT INTO customer(title, fname, lname, addressline, town, zipcode, phone) VALUES('Mr','Adrian','Matthew','The Barn','Yuleville','YV67 2WR','487 3871');
61INSERT INTO customer(title, fname, lname, addressline, town, zipcode, phone) VALUES('Mr','Simon','Cozens','7 Shady Lane','Oahenham','OA3 6QW','514 5926');
62INSERT INTO customer(title, fname, lname, addressline, town, zipcode, phone) VALUES('Mr','Neil','Matthew','5 Pasture Lane','Nicetown','NT3 7RT','267 1232');
63INSERT INTO customer(title, fname, lname, addressline, town, zipcode, phone) VALUES('Mr','Richard','Stones','34 Holly Way','Bingham','BG4 2WE','342 5982');
64INSERT INTO customer(title, fname, lname, addressline, town, zipcode, phone) VALUES('Mrs','Anna','Stones','34 Holly Way','Bingham','BG4 2WE','342 5982');
65INSERT INTO customer(title, fname, lname, addressline, town, zipcode, phone) VALUES('Mrs','Christine','Hickman','36 Queen Street','Histon','HT3 5EM','342 5432');
66INSERT INTO customer(title, fname, lname, addressline, town, zipcode, phone) VALUES('Mr','Mike','Howard','86 Dysart Street','Tibsville','TB3 7FG','505 5482');
67INSERT INTO customer(title, fname, lname, addressline, town, zipcode, phone) VALUES('Mr','Dave','Jones','54 Vale Rise','Bingham','BG3 8GD','342 8264');
68INSERT INTO customer(title, fname, lname, addressline, town, zipcode, phone) VALUES('Mr','Richard','Neill','42 Thached way','Winersby','WB3 6GQ','505 6482');
69INSERT INTO customer(title, fname, lname, addressline, town, zipcode, phone) VALUES('Mrs','Laura','Hendy','73 Margeritta Way','Oxbridge','OX2 3HX','821 2335');
70INSERT INTO customer(title, fname, lname, addressline, town, zipcode, phone) VALUES('Mr','Bill','Neill','2 Beamer Street','Welltown','WT3 8GM','435 1234');
71INSERT INTO customer(title, fname, lname, addressline, town, zipcode, phone) VALUES('Mr','David','Hudson','4 The Square','Milltown','MT2 6RT','961 4526');
72INSERT INTO customer(lname, zipcode) VALUES('Wolski','MX2 6RV');
73
74INSERT INTO item(description, cost_price, sell_price) VALUES('Wood Puzzle', 15.23, 21.95);
75INSERT INTO item(description, cost_price, sell_price) VALUES('Rubik Cube', 7.45, 11.49);
76INSERT INTO item(description, cost_price, sell_price) VALUES('Linux CD', 1.99, 2.49);
77INSERT INTO item(description, cost_price, sell_price) VALUES('Tissues', 2.11, 3.99);
78INSERT INTO item(description, cost_price, sell_price) VALUES('Picture Frame', 7.54, 9.95);
79INSERT INTO item(description, cost_price, sell_price) VALUES('Fan Small', 9.23, 15.75);
80INSERT INTO item(description, cost_price, sell_price) VALUES('Fan Large', 13.36, 19.95);
81INSERT INTO item(description, cost_price, sell_price) VALUES('Toothbrush', 0.75, 1.45);
82INSERT INTO item(description, cost_price, sell_price) VALUES('Roman Coin', 2.34, 2.45);
83INSERT INTO item(description, cost_price, sell_price) VALUES('Carrier Bag', 0.01, 0.0);
84INSERT INTO item(description, cost_price, sell_price) VALUES('Speakers', 19.73, 25.32);
85INSERT INTO item (description) VALUES ('SQL Server 2005');