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

题目：

现有一个商店的数据库，记录顾客及其购物情况，由下面三个表组成：商品（商品号，商品名，单价，商品类别，供应商）；

顾客（顾客号，姓名，住址）；

购买（顾客号，商品号，购买数量）；

试用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"所购买商品的顾客（姓名）；

参考书本p126

select 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 b

where p.productno = b.productno and c.customerno = b.customerno and sort = '牙膏'

group by p.supplier

having sum(num) > = all

(

select sum(num) from product p,customer c, buy b

where 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 );