//这是一个问题，如果订单金额大于客户的平均销售额，则查找客户名称、订单号和订单金额，并按客户名称对记录排序[提示：使用where子句]。

SELECT 
name, orderno, amount
FROM
customerinfo,
orderdetails
WHERE
amount > (SELECT 
        AVG(amount)
    FROM
        orderdetails)

ORDER BY name;

//这个结果不是我想要的结果
这是表格编码

CREATE TABLE customerinfo (
customercode VARCHAR(10) NOT NULL UNIQUE,
name VARCHAR(60) NOT NULL,
address VARCHAR(60),
poscode INT(5),
city VARCHAR(30),
state VARCHAR(30),
hpno VARCHAR(20),
email VARCHAR(50),
salesmanid VARCHAR(10),
PRIMARY KEY (customercode),
FOREIGN KEY (salesmanid)
    REFERENCES salesman (salesmanid)
);

CREATE TABLE salesman (
salesmanid VARCHAR(10) NOT NULL UNIQUE,
salesmanname VARCHAR(60) NOT NULL,
PRIMARY KEY (salesmaniD)
);

CREATE TABLE orderinfo (
orderno INT(10) NOT NULL,
ordertype VARCHAR(50),
orderdate DATE,
deliverydate DATE,
PRIMARY KEY (orderno)
);

CREATE TABLE orderdetails (
orderno INT(10) NOT NULL,
itemcode VARCHAR(10) NOT NULL,
quantity INT(5),
amount DECIMAL(9 , 2 ),
PRIMARY KEY (orderno , itemcode),
FOREIGN KEY (orderno)
    REFERENCES orderinfo (orderno),
FOREIGN KEY (itemcode)
    REFERENCES item (itemcode)
);

CREATE TABLE item (
itemcode VARCHAR(10) NOT NULL UNIQUE,
itemdesc VARCHAR(60),
unit VARCHAR(10),
sellingprice DECIMAL(9 , 2 ) NOT NULL,
PRIMARY KEY (itemcode)
);

CREATE TABLE ordersalesmaninfo (
salesmanid VARCHAR(10) NOT NULL,
orderno INT(10) NOT NULL,
PRIMARY KEY (salesmanid),
FOREIGN KEY (salesmanid)
    REFERENCES salesman(salesmanid),
FOREIGN KEY (orderno)
    REFERENCES orderinfo (orderno)
);

我可能对正常化进程有问题，但我认为不会对这个问题造成任何影响。