14增删改查综合运用

--删除数据表
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 ));