现有一个商店的数据库，记录顾客及其购物情况，由下面三个表组成：
商品product（商品号productid，商品名productname，单价unitprice，商品类别category，供应商provider，上架日期startdate）；
create table product(
productid char(3) primary key,
productname char(50) not null,
unitprice number(8,2),
category char(50),
provider char(50),
startdate date,
constraint chk_unitprice check(unitprice>0));
顾客customer（顾客号customerid，姓名name，住址location）；
create table customer(
customerid char(3) primary key,
name char(20),
location char(50));
购买purcase（顾客号customerid，商品号productid，购买数量quantity）；
create table purcase(
customerid char(3),
productid char(3),
quantity number(3),
constraint pk_cus_pro primary key (customerid,productid),
constraint fk_pur_pro foreign key (productid) references product(productid),
constraint fk_pur_cus foreign key (customerid) references customer(customerid),
constraint chk_quantity check(quantity>0 and quantity<=20));
试用SQL语言完成下列功能：
1 建表，在定义中要求声明：
(1)每个表的主外码；
(2)顾客的姓名和商品名不能为空值；
(3)单价必须大于0，购买数量必须再0到20之间；
2 往表中插入数据：
商品（M01，佳洁士，8.00，牙膏，宝洁；
M02，高露洁，6.50，牙膏，高露洁；
M03，洁诺，5.00，牙膏，联合利华；
M04，舒肤佳，3.00，香皂，宝洁；
M05，夏士莲，5.00，香皂，联合利华；
M06，雕牌，2.50，洗衣粉，纳爱斯
M07，中华，3.50，牙膏，联合利华；
M08，汰渍，3.00，洗衣粉，宝洁；
M09，碧浪，4.00，洗衣粉，宝洁；）
insert into product(productid,productname,unitprice,category,provider) values('M01','佳洁士',8.00,'牙膏','宝洁');
insert into product(productid,productname,unitprice,category,provider) values('M02','高露洁',6.50,'牙膏','高露洁');
insert into product(productid,productname,unitprice,category,provider) values('M03','洁诺',5.00,'牙膏','联合利华');
insert into product(productid,productname,unitprice,category,provider) values('M04','舒肤佳',3.00,'香皂','宝洁');
insert into product(productid,productname,unitprice,category,provider) values('M05','夏士莲',5.00,'香皂','联合利华');
insert into product(productid,productname,unitprice,category,provider) values('M06','雕牌',2.50,'洗衣粉','纳爱斯');
insert into product(productid,productname,unitprice,category,provider) values('M07','中华',3.50,'牙膏','联合利华');
insert into product(productid,productname,unitprice,category,provider) values('M08','汰渍',3.00,'洗衣粉','宝洁');
insert into product(productid,productname,unitprice,category,provider) values('M09','碧浪',4.00,'洗衣粉','宝洁');
顾客（C01，Dennis，海淀；
C02，John，朝阳；
C03，Tom，东城；
C04，Jenny，东城；
C05，Rick，西城；）
insert into customer values('C01','Dennis','海淀');
insert into customer values('C02','John','朝阳');
insert into customer values('C03','Tom','东城');
insert into customer values('C04','Jenny','东城');
insert into customer values('C05','Rick','西城');
购买(C01，M01，3；
C01，M05，2；
C01，M08，2；
C02，M02，5；
C02，M06，4；
C03，M01，1；
C03，M05，1；
C03，M06，3；
C03，M08，1；
C04，M03，7；
C04，M04，3；
C05，M06，2；
C05，M07，8；）
insert into purcase(customerid,productid,quantity) values('C01','M01',3);
insert into purcase(customerid,productid,quantity) values('C01','M05',2);
insert into purcase(customerid,productid,quantity) values('C01','M08',2);
insert into purcase(customerid,productid,quantity) values('C02','M02',5);
insert into purcase(customerid,productid,quantity) values('C02','M06',4);
insert into purcase(customerid,productid,quantity) values('C03','M01',1);
insert into purcase(customerid,productid,quantity) values('C03','M05',1);
insert into purcase(customerid,productid,quantity) values('C03','M06',3);
insert into purcase(customerid,productid,quantity) values('C03','M08',1);
insert into purcase(customerid,productid,quantity) values('C04','M03',7);
insert into purcase(customerid,productid,quantity) values('C04','M04',3);
insert into purcase(customerid,productid,quantity) values('C05','M06',2);
insert into purcase(customerid,productid,quantity) values('C05','M07',8);
insert into purcase(customerid,productid,quantity) values('C05','M08',2);
商品有9 条,顾客有5条,购买有13条
3 用SQL语句完成下列查询：
（1）求购买了供应商"宝洁"产品的所有顾客；
select * from customer where customerid in(select distinct customerid from purcase where productid in(select productid from product where provider='宝洁'));
---（2）求购买的商品包含了顾客"Dennis"所购买的所有商品的顾客（姓名）；
select customerid from (select productid from purcase where customerid=(select customerid from customer where name='Dennis')) a,purcase b where b.productid in (a.productid) group by customerid having count(customerid)=3;
（3）求牙膏卖出数量最多的供应商。
select * from(select provider,pu.productid,quantity from purcase pu,product po where pu.productid in(select productid from product where category='牙膏') and po.productid=pu.productid order by quantity desc) where rownum<2;
4 将所有的牙膏商品单价增加10%。
update product set unitprice=unitprice*1.1 where category='牙膏';
5 删除从未被购买的商品记录。
评论0
最新资源