mysql复杂查询练习题_数据库练习题(DDL&DML&复杂查询部分) -----mysql数据库实现（商品查询练习）...

A.求购买了供应商"宝洁"产品的所有顾客

B.求购买的商品包含了顾客"Dennis"所购买的所有商品的顾客姓名

C.求牙膏卖出数量最多的供应商

D.将所有的牙膏商品单价增加10%

E.删除从未被购买的商品记录

CREATE TABLE PRODUCT(

PRODUCTID VARCHAR(4) PRIMARY KEY,

PRODUCTNAME VARCHAR(20) NOT NULL,

UNITPRICE INT(7),

CATEGORY VARCHAR(10),

PROVIDER VARCHAR(30),

CONSTRAINT FK_UNITPRICE CHECK (UNITPRICR > 0)

);

CREATE TABLE CUSTOMER(

CUSTOMERID VARCHAR(4) PRIMARY KEY,

CNAME VARCHAR(20) NOT NULL,

LOCATION VARCHAR(60)

);

CREATE TABLE PURCASE(

CUSTOMERID VARCHAR(4),

PRODUCTID VARCHAR(4),

QUANTITY INT(22),

CONSTRAINT FK_QUANTITY CHECK (QUANTITY BETWEEN 0 AND 20),

CONSTRAINT FK_CUSTOMERID FOREIGN KEY(CUSTOMERID) REFERENCES CUSTOMER(CUSTOMERID),

CONSTRAINT FK_PRODUCTID FOREIGN KEY(PRODUCTID) REFERENCES PRODUCT(PRODUCTID)

)</