北大数据库原理上机考题练习及参考答案 练习二(商品、顾客、购买)

题目：现有一个商店的数据库，记录顾客及其购物情况，由下面三个表组成：商品（商品号，商品名，单价，商品类别，供应商）；顾客（顾客号，姓名，住址）；购买（顾客号，商品号，购买数量）；试用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，洗衣粉，宝洁；）顾客（C01，Dennis，海淀；C02，John，朝阳；C03，Tom，东城；C04，Jenny，东城；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；）商品有9 条,顾客有5条,购买有5条3 用SQL语句完成下列查询：（1）求购买了供应商"宝洁"产品的所有顾客；（2）求购买的商品包括了顾客"Dennis"所购买商品的顾客（姓名）；（3）求牙膏卖出数量最多的供应商。

4 将所有的牙膏商品单价增加10%。

5 删除从未被购买的商品记录。

参考答案：create table product(productno char(10) not null,productname char(15) not null,price float(15),sort char(10),supplier char(20),primary key (productno),check (price > 0))create table customer(customerno char(10) not null,customername char(15) not null,address char(10),primary key (customerno))create table buy(customerno char(10) not null,productno char(10) not null,num smallint,primary key (customerno,productno),foreign key (customerno) references customer (customerno), foreign key (productno) references product (productno),check (num between 0 and 20))insert into product values ('M01','佳洁士',8.00,'牙膏','宝洁');insert into product values('M02','高露洁',6.50,'牙膏','高露洁'); insert into product values('M03','洁诺',5.00,'牙膏','联合利华') ; insert into product values('M04','舒肤佳',3.00,'香皂','宝洁') ;insert into product values('M05','夏士莲',5.00,'香皂','联合利华'); insert into product values('M06','雕牌',2.50,'洗衣粉','纳爱斯'); insert into product values('M07','中华',3.50,'牙膏','联合利华') ; insert into product values('M08','汰渍',3.00,'洗衣粉','宝洁') ;insert into product values('M09','碧浪',4.00,'洗衣粉','宝洁') ;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','西城') ;insert into buy values ('C01','M01',3);insert into buy values('C01','M05',2);insert into buy values('C01','M08',2);insert into buy values('C02','M02',5);insert into buy values('C02','M06',4);insert into buy values('C03','M01',1);insert into buy values('C03','M05',1);insert into buy values('C03','M06',3);insert into buy values('C03','M08',1);insert into buy values('C04','M03',7);insert into buy values('C04','M04',3);insert into buy values('C05','M06',2);insert into buy values('C05','M07',8);（1）求购买了供应商"宝洁"产品的所有顾客；select c.customerno,c.customername from product p,customer c, buy b where p.productno = b.productno and c.customerno = b.customerno and supplier = '宝洁'（2）求购买的商品包括了顾客"Dennis"所购买商品的顾客（姓名）；参考书本p126select distinct c.customername from buy as x , customer c where not exists (select * from buy as y where y.customerno in (select customerno from customer where customername = 'Dennis' )and not exists (select * from buy as z where z.customerno = x.customerno and z.productno = y.productno))and x.customerno = c.customerno（3）求牙膏卖出数量最多的供应商。

select p.supplier,sum(num) from product p,customer c, buy bwhere p.productno = b.productno and c.customerno = b.customerno and sort = '牙膏'group by p.supplierhaving sum(num) > = all(select sum(num) from product p,customer c, buy bwhere p.productno = b.productno and c.customerno = b.customerno and sort = '牙膏'group by p.supplier)（4）update product set price = price * (1+0.1) where sort = '牙膏';（5）delete from product where productno not in (select distinct productno from buy );（素材和资料部分来自网络，供参考。

可复制、编制，期待您的好评与关注）。