删除数据表
DROP TABLE purchase PURGE;
DROP TABLE product PURGE;
DROP TABLE customer PURGE;
--数据表创建
CREATE TABLE product(
productid VARCHAR2(5),
productname VARCHAR2(20) NOT NULL ,
unitprice NUMBER,
category VARCHAR2(50),
provider VARCHAR2(50),
CONSTRAINT pk_productid PRIMARY KEY(productid),
CONSTRAINT ck_unitprice CHECK(unitprice>0)
);
CREATE TABLE customer(
customerid VARCHAR2(5),
name VARCHAR2(20) NOT NULL,
location VARCHAR2(50),
CONSTRAINT pk_customerid PRIMARY KEY(customerid)
);
CREATE TABLE purchase(
customerid VARCHAR2(5),
productid VARCHAR2(5),
quantity NUMBER,
CONSTRAINT fk_customerid FOREIGN KEY(customerid) REFERENCES customer(customerid) ON DELETE CASCADE,
CONSTRAINT fk_productid FOREIGN KEY(productid) REFERENCES product(productid) ON DELETE CASCADE,
CONSTRAINT ck_quantity CHECK(quantity BETWEEN 0 AND 20)
);
INSERT INTO product(productid,productname,unitprice,category,provider)
VALUES('M01','jiajieshi',8.00,'yagao','baojie');
INSERT INTO product(productid,productname,unitprice,category,provider)
VALUES('M02','jiajieshi',10.00,'yagao','lianhelihua');
INSERT INTO product(productid,productname,unitprice,category,provider)
VALUES('M03','gaolujie',12.00,'yagao','baojie');
INSERT INTO product(productid,productname,unitprice,category,provider)
VALUES('M04','shufujia',12.00,'xiangzao','baojie');
INSERT INTO customer(customerid,name,location)
VALUES('C01','郑','江西');
INSERT INTO customer(customerid,name,location)
VALUES('C02','郑题','广州');
INSERT INTO customer(customerid,name,location)
VALUES('C03','冠以','北京');
INSERT INTO customer(customerid,name,location)
VALUES('C04','一一','上海');
INSERT INTO purchase(customerid ,productid,quantity)
VALUES('C03','M04',2);
INSERT INTO purchase(customerid ,productid,quantity)
VALUES('C01','M01',2);
INSERT INTO purchase(customerid ,productid,quantity)
VALUES('C04','M04',3);
INSERT INTO purchase(customerid ,productid,quantity)
VALUES('C02','M03',2);
INSERT INTO purchase(customerid ,productid,quantity)
VALUES('C03','M02',5);
--求购买了供应商‘baojie’产品的所有顾客；
--数据表：
costomer表：顾客
product表：供应商‘baojie’信息
purchase表：购买了供应商‘baojie’产品
--关联关系：
product.productid=purchase.productid;
costomer.customerid=purchase.customerid;
--找出供应商baojie的产品
SELECT productid
FROM product
WHERE provider='baojie';
--找出购买了baojie产品的顾客信息
SELECT *
FROM costomer
WHERE customerid IN(
SELECT customerid
FROM purchase
WHERE productid IN (
SELECT productid
FROM product
WHERE provider='baojie'));
--求购买的商品包含了顾客'冠以'所购买的所有商品的顾客（姓名）；
SELECT customerid
FROM customer
WHERE name='冠以';
SELECT *
FROM customer ca
WHERE NOT EXISTS(
SELECT p1.productid
FROM purchase p1
WHERE customerid =(
SELECT customerid
FROM customer
WHERE name='冠以')
MINUS
SELECT p2.productid
FROM purchase p2
WHERE customerid=ca.customerid)
AND ca.name<>'冠以';
--求牙膏卖出数量最多的供应商；
--先找出牙膏对应的商品编号productid，然后从购买表中找出牙膏商品编号的总的数量，根据商品编号分组可以得出每一个商品编号对应的数量；再求出数量最多对应的商品编号；
SELECT PRODUCER
FROM product
WHERE productid=(
SELECT productid,SUM(QUANTITY)
FROM PURCHASE
WHERE productid IN(
SELECT productid FROM product WHERE category='yagao')
GROUP BY productid
HAVING SUM(QUANTITY)=(
SELECT MAX(SUM(QUANTITY))
FROM PURCHASE
WHERE productid IN(
SELECT productid FROM product WHERE category='yagao')
GROUP BY productid ));
