/* ################### START CREATE AND POPULATE DATABASE ########################## */ DROP DATABASE IF EXISTS mycustomer;

CREATE DATABASE mycustomer;

USE mycustomer;

CREATE TABLE customer( cid varchar(4), cname varchar(20), street varchar(20), city varchar(20), country varchar(20), phone varchar(12),

PRIMARY KEY(cid) );

CREATE TABLE custorder( oid varchar(4), cid varchar(4), orderdate date, shippingdate date,

PRIMARY KEY(oid), FOREIGN KEY(cid) REFERENCES customer(cid) );

CREATE TABLE itemorder( oid varchar(4), iid varchar(4), quantity int, totalprice decimal(9,2),

PRIMARY KEY(oid, iid), FOREIGN KEY(oid) REFERENCES custorder(oid) );

INSERT INTO customer VALUES ('C223','COMPAQ', '3 Barbados Ave', 'Bridgetown','Barbados','809-956-2345'); INSERT INTO customer VALUES ('C224','IBM', '14 Ruthven Rd', 'Kingston','Jamaica','876-927-2654'); INSERT INTO customer VALUES ('C225','IBM', '24 Hart St', 'Port-au-Prince','Trinidad','309-913-2345'); INSERT INTO customer VALUES ('C226','GATEWAY', '30 Austin Ave', 'Kingston','Jamaica','876-956-2458'); INSERT INTO customer VALUES ('C227','IBM', '5 Seville Rd', 'Bridgetown','Barbados','809-756-1345'); INSERT INTO customer VALUES ('C228','GATEWAY', '3 Kings Way', 'Port-au-Prince','Trinidad','309-756-2375');

INSERT INTO custorder VALUES ('O010','C223','2020-09-24','2020-09-26'); INSERT INTO custorder VALUES ('O011','C227','2020-09-15','2020-09-20'); INSERT INTO custorder (oid, cid, orderdate) VALUES ('O012','C224','2021-01-16'); INSERT INTO custorder VALUES ('O013','C225','2020-07-15','2020-07-20'); INSERT INTO custorder (oid, cid, orderdate) VALUES ('O014','C224','2021-02-16');

INSERT INTO itemorder VALUES('O010','i110',10, 600000.00); INSERT INTO itemorder VALUES('O010','i111',5, 300000.00); INSERT INTO itemorder VALUES('O011','i110',8, 540000.00); INSERT INTO itemorder VALUES('O012','i113',20, 1000000.00); INSERT INTO itemorder VALUES('O012','i111',6, 360000.00); INSERT INTO itemorder VALUES('O012','i114',6, 600000.00); INSERT INTO itemorder VALUES('O013','i114',9, 800000.00);

/* ################### END CREATE AND POPULATE DATABASE ########################## */

Question 5

Find the name of the companies that order item# i110.

Question 6

Find the unit cost per item (assume the cost of each unit does not vary for each order).

Question 7

Find the order that has the greatest total cost.

Question 8

Find the order where the cost is greater that the average cost.