北大数据库原理上机考题练习及参考答案练习二

1、题目： 现有一个商店的数据库，记录顾客及其购物情况，由下面三个表组成： 商品（商品号，商品名，单价，商品类别，供应商）； 顾客（顾客号，姓名，住址）； 购买（顾客号，商品号，购买数量）； 试用 SQL语言完成下列功能： 1 建表，在定义中要求声明： （1）每个表的主外码； （2）顾客的姓名和商品名不能为空值； （3）单价必须大于 0，购买数量必须再 0到 20之间； 2 往表中插入数据： 商品（ M01，佳洁士，牙膏，宝洁； M02，高露洁，牙膏，高露洁； M03，洁诺，牙膏，联合利华； M04，舒肤佳，香皂，宝洁； M05，夏士莲，香皂，联合利华； M06，雕牌，洗衣粉，纳爱斯 M07，中华

2、，牙膏，联合利华； M08，汰渍，洗衣粉，宝洁； M09，碧浪，洗衣粉，宝洁；） 顾客（ 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条, 购

3、买有 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

4、), 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 (custome

5、rno) references customer (customerno), foreign key (productno) references product (productno), check (num between 0 and 20) insert into product values (M01, insert into product values(M02, insert into product values(M03, insert into product values(M04, insert into product values(M05, insert into pro

6、duct values(M06, insert into product values(M07, insert into product values(M08, insert into product values(M09, 佳洁士 , 牙膏, 宝洁); 高露洁 , 牙膏, 高露洁 ); 洁诺, 牙膏, 联合利华 ) ; 舒肤佳, 香皂, 宝洁) ; 夏士莲, 香皂, 联合利华 ); 雕牌, 洗衣粉, 纳爱斯 ); 中华, 牙膏, 联合利华 ) ; 汰渍, 洗衣粉, 宝洁) ; 碧浪, 洗衣粉, 宝洁) ; insert into customer values (C01,Dennis,海淀

7、) ; 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 bu

8、y 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);

9、 insert into buy values(C05,M07,8); 1)求购买了供应商 宝洁 产品的所有顾客； supplier select , from product p,customer c, buy b where = and = and = 宝洁 ( 2)求购买的商品包括了顾客 Dennis 所购买商品的顾客(姓名) ； 参考书本 p126 select distinct from buy as x , customer c where not exists ( select * from buy as y where in (select customerno from cu

10、stomer where customername = Dennis ) and not exists ( select * from buy as z where = and = ) ) and = (3)求牙膏卖出数量最多的供应商。 select ,sum(num) from product p,customer c, buy b where = and = and sort = 牙膏 group by having sum(num) = all ( select sum(num) from product p,customer c, buy b where = and = and sort = 牙膏