AYXJgtp7

1CREATE DATABASE IF NOT EXISTS Ex1;
2CREATE TABLE IF NOT EXISTS Ex1.Customer
3( CustomerID CHAR(4) NOT NULL,
4DeliveryAddress VARCHAR(50),
5EmailAddress VARCHAR(25),
6PRIMARY KEY (CustomerID),
7UNIQUE INDEX (CustomerID));
8
9CREATE TABLE IF NOT EXISTS Ex1.Item
10( ItemCode CHAR(4) NOT NULL,
11Description VARCHAR(15),
12UnitPrice FLOAT,
13PRIMARY KEY (ItemCode),
14UNIQUE INDEX (ItemCode));
15
16CREATE TABLE IF NOT EXISTS Ex1.OnlineOrder
17( OrderNumber CHAR(6) NOT NULL,
18CustomerID CHAR(4) NOT NULL,
19OrderDate DATE,
20PRIMARY KEY (OrderNumber),
21FOREIGN KEY (CustomerID) REFERENCES Customer(CustomerID),
22INDEX (OrderNumber));
23
24CREATE TABLE IF NOT EXISTS Ex1.ItemOrder
25(OrderNumber CHAR(6) NOT NULL,
26ItemCode CHAR(5) NOT NULL,
27OrderQuantity INTEGER,
28PRIMARY KEY (OrderNumber, ItemCode),
29FOREIGN KEY (ItemCode) REFERENCES Item(ItemCode),
30UNIQUE INDEX (OrderNumber, ItemCode),
31INDEX (OrderNumber));
32
33USE Ex1;
34INSERT INTO Customer (CustomerID,DeliveryAddress,EmailAddress) VALUES
35('BLF1',NULL,'FredBloggs@NT.co.uk'),
36('SMJ2',NULL,'JoeSmith@NT.co,uk');
37
38INSERT INTO item (ItemCode,Description,UnitPrice) VALUES
39('3456','Stapler',2.99),
40('8967','Divider',0.5),
41('1234','Ring Binder',1.5),
42('9684','Scissors',1.99);
43
44INSERT INTO onlineorder (OrderNumber,CustomerID,OrderDate) VALUES
45('034231','SMJ2','2009-05-03'),
46('012367','BLF1','2009-05-01');
47
48INSERT INTO itemorder (OrderNumber,ItemCode,OrderQuantity) VALUES
49('012367','1234',3),	('012367','8967',4),	('012367','3456',1),
50('034231','9684',2),	('034231','3456',4);
51
52Use Ex1;
53
54SET SQL_SAFE_UPDATES = 0;
55
56UPDATE Item SET UnitPrice = ROUND(UnitPrice*1.1,2);
57DELETE FROM item
58Where UnitPrice>10;
59
60SELECT Description 
61FROM item, itemorder
62WHERE OrderNumber = '012367'
63AND itemorder.ItemCode = item.ItemCode
64ORDER BY item asc