· 9 years ago · Nov 27, 2016, 07:46 AM
1DROP SCHEMA IF EXISTS OPS;
2CREATE SCHEMA OPS;
3USE OPS;
4
5CREATE TABLE customer (
6 customer_id INT AUTO_INCREMENT NOT NULL,
7 customer_name VARCHAR(45),
8 city VARCHAR(45),
9 PRIMARY KEY (customer_id)
10) ENGINE = INNODB;
11
12
13
14CREATE TABLE item (
15 item_id INT AUTO_INCREMENT NOT NULL,
16 unit_price DECIMAL NOT NULL,
17 PRIMARY KEY (item_id)
18) ENGINE = INNODB;
19
20CREATE TABLE `order` (
21 order_id INT AUTO_INCREMENT NOT NULL,
22 order_date DATE NOT NULL,
23 customer_id INT NOT NULL,
24 PRIMARY KEY (order_id),
25 FOREIGN KEY (customer_id) REFERENCES customer(customer_id)
26) ENGINE = INNODB;
27
28
29CREATE TABLE order_item (
30 order_id INT NOT NULL,
31 item_id INT NOT NULL,
32 quantity INT NOT NULL,
33 FOREIGN KEY (order_id) REFERENCES `order`(order_id),
34 FOREIGN KEY (item_id) REFERENCES item(item_id)
35) ENGINE = INNODB;
36
37CREATE TABLE warehouse (
38 warehouse_id INT AUTO_INCREMENT NOT NULL,
39 warehouse_name VARCHAR(45) NOT NULL,
40 warehouse_city VARCHAR(45) NOT NULL,
41 PRIMARY KEY (warehouse_id)
42
43) ENGINE = INNODB;
44
45CREATE TABLE shipment (
46 order_id INT AUTO_INCREMENT NOT NULL,
47 warehouse_id INT NOT NULL,
48 ship_date DATE NOT NULL,
49 FOREIGN KEY (order_id) REFERENCES `order`(order_id),
50 FOREIGN KEY (warehouse_id) REFERENCES warehouse(warehouse_id)
51) ENGINE = INNODB;
52
53
54INSERT INTO `customer`(`customer_id`, `customer_name`, `city`) VALUES (1,'Mohamed','Alex');
55INSERT INTO `customer`(`customer_id`, `customer_name`, `city`) VALUES (2,'Mazen','Alex');
56INSERT INTO `customer`(`customer_id`, `customer_name`, `city`) VALUES (3,'Mai','Cairo');
57INSERT INTO `customer`(`customer_id`, `customer_name`, `city`) VALUES (4,'Mahmoud','Giza');
58INSERT INTO `customer`(`customer_id`, `customer_name`, `city`) VALUES (5,'Omar','Giza');
59
60INSERT INTO `item`(`item_id`, `unit_price`) VALUES (1,7.5);
61INSERT INTO `item`(`item_id`, `unit_price`) VALUES (2,10.4);
62INSERT INTO `item`(`item_id`, `unit_price`) VALUES (3,8.5);
63INSERT INTO `item`(`item_id`, `unit_price`) VALUES (4,25.5);
64INSERT INTO `item`(`item_id`, `unit_price`) VALUES (5,9.5);
65INSERT INTO `item`(`item_id`, `unit_price`) VALUES (6,9.5);
66INSERT INTO `item`(`item_id`, `unit_price`) VALUES (7,10.5);
67
68INSERT INTO `order`(`order_id`, `order_date`, `customer_id`) VALUES (1,'2014-09-01',1);
69INSERT INTO `order`(`order_id`, `order_date`, `customer_id`) VALUES (2,'2014-10-01',2);
70INSERT INTO `order`(`order_id`, `order_date`, `customer_id`) VALUES (3,'2014-11-01',2);
71INSERT INTO `order`(`order_id`, `order_date`, `customer_id`) VALUES (4,'2014-11-2',3);
72INSERT INTO `order`(`order_id`, `order_date`, `customer_id`) VALUES (5,'2014-11-2',4);
73INSERT INTO `order_item`(`order_id`, `item_id`, `quantity`) VALUES (1,2,2);
74INSERT INTO `order_item`(`order_id`, `item_id`, `quantity`) VALUES (1,1,2);
75INSERT INTO `order_item`(`order_id`, `item_id`, `quantity`) VALUES (2,3,4);
76INSERT INTO `order_item`(`order_id`, `item_id`, `quantity`) VALUES (2,4,2);
77INSERT INTO `order_item`(`order_id`, `item_id`, `quantity`) VALUES (3,5,3);
78INSERT INTO `order_item`(`order_id`, `item_id`, `quantity`) VALUES (3,4,1);
79INSERT INTO `order_item`(`order_id`, `item_id`, `quantity`) VALUES (3,3,1);
80INSERT INTO `order_item`(`order_id`, `item_id`, `quantity`) VALUES (4,1,1);
81INSERT INTO `order_item`(`order_id`, `item_id`, `quantity`) VALUES (5,2,3);
82
83INSERT INTO `warehouse`(`warehouse_id`,`warehouse_name`,`warehouse_city`) VALUES (1,'Warehouse#1','Alex');
84INSERT INTO `warehouse`(`warehouse_id`,`warehouse_name`,`warehouse_city`) VALUES (2,'Warehouse#2','Cairo');
85INSERT INTO `warehouse`(`warehouse_id`,`warehouse_name`,`warehouse_city`) VALUES (3,'Warehouse#3','Giza');
86INSERT INTO `warehouse`(`warehouse_id`,`warehouse_name`,`warehouse_city`) VALUES (4,'Warehouse#4','USA');
87
88INSERT INTO `shipment`(`order_id`, `warehouse_id`, `ship_date`) VALUES (1,2,'2014-11-2');
89INSERT INTO `shipment`(`order_id`, `warehouse_id`, `ship_date`) VALUES (1,3,'2014-11-2');
90INSERT INTO `shipment`(`order_id`, `warehouse_id`, `ship_date`) VALUES (1,1,'2014-11-2');
91INSERT INTO `shipment`(`order_id`, `warehouse_id`, `ship_date`) VALUES (2,3,'2014-11-2');
92INSERT INTO `shipment`(`order_id`, `warehouse_id`, `ship_date`) VALUES (3,2,'2014-11-2');