北大數(shù)據(jù)庫原理上機(jī)考題練習(xí)及參考答案 練習(xí)二(商品、顧客、購買)

精選文庫題目：現(xiàn)有一個商店的數(shù)據(jù)庫，記錄顧客及其購物情況，由下面三個表組成： 商品（商品號，商品名，單價，商品類別，供應(yīng)商）； 顧客（顧客號，姓名，住址）； 購買（顧客號，商品號，購買數(shù)量）； 試用SQL語言完成下列功能： 1建表，在定義中要求聲明： (1)每個表的主外碼； (2)顧客的姓名和商品名不能為空值； (3)單價必須大于0，購買數(shù)量必須再0到20之間； 2往表中插入數(shù)據(jù)： 商品（M01，佳潔士，8.00，牙膏，寶潔； M02，高露潔，6.50，牙膏，高露潔； M03，潔諾，5.00，牙膏，聯(lián)合利華； M04，舒膚佳，3.00，香皂，寶潔； M05，夏士蓮，5.00，香皂，聯(lián)合利華； M06，雕牌，2.50，洗衣粉，納愛斯 M07，中華，3.50，牙膏，聯(lián)合利華； 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）求購買了供應(yīng)商寶潔產(chǎn)品的所有顧客； （2）求購買的商品包括了顧客Dennis所購買商品的顧客（姓名）； （3）求牙膏賣出數(shù)量最多的供應(yīng)商。 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,牙膏,聯(lián)合利華) ;insert into product values(M04,舒膚佳,3.00,香皂,寶潔) ;insert into product values(M05,夏士蓮,5.00,香皂,聯(lián)合利華); insert into product values(M06,雕牌,2.50,洗衣粉,納愛斯); insert into product values(M07,中華,3.50,牙膏,聯(lián)合利華) ;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）求購買了供應(yīng)商寶潔產(chǎn)品的所有顧客；select c.customerno,c.customername from product p,customer c, buy b where ductno = ductno 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 ductno = ductno )and x.customerno = c.customerno（3）求牙膏賣出數(shù)量最多的供應(yīng)商。select p.supplier,sum(num) from product p,customer c, buy b where ductno = ductno 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 ductno = ductno and c.customerno = b.customerno and sort =