北大數(shù)據(jù)庫原理上機(jī)考題練習(xí)及參考答案練習(xí)二

1、題目： 現(xiàn)有一個(gè)商店的數(shù)據(jù)庫，記錄顧客及其購物情況，由下面三個(gè)表組成： 商品（商品號，商品名，單價(jià)，商品類別，供應(yīng)商）； 顧客（顧客號，姓名，住址）； 購買（顧客號，商品號，購買數(shù)量）； 試用 SQL語言完成下列功能： 1 建表，在定義中要求聲明： （1）每個(gè)表的主外碼； （2）顧客的姓名和商品名不能為空值； （3）單價(jià)必須大于 0，購買數(shù)量必須再 0到 20之間； 2 往表中插入數(shù)據(jù)： 商品（ M01，佳潔士，牙膏，寶潔； M02，高露潔，牙膏，高露潔； M03，潔諾，牙膏，聯(lián)合利華； M04，舒膚佳，香皂，寶潔； M05，夏士蓮，香皂，聯(lián)合利華； M06，雕牌，洗衣粉，納愛斯 M07，中華

2、，牙膏，聯(lián)合利華； 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）求購買了供應(yīng)商 寶潔 產(chǎn)品的所有顧客； （ 2）求購買的商品包括了顧客 Dennis 所購買商品的顧客（姓名）； （3）求牙膏賣出數(shù)量最多的供應(yīng)商。 4 將所有的牙膏商品單價(jià)增加 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, 佳潔士 , 牙膏, 寶潔); 高露潔 , 牙膏, 高露潔 ); 潔諾, 牙膏, 聯(lián)合利華 ) ; 舒膚佳, 香皂, 寶潔) ; 夏士蓮, 香皂, 聯(lián)合利華 ); 雕牌, 洗衣粉, 納愛斯 ); 中華, 牙膏, 聯(lián)合利華 ) ; 汰漬, 洗衣粉, 寶潔) ; 碧浪, 洗衣粉, 寶潔) ; 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)求購買了供應(yīng)商 寶潔 產(chǎn)品的所有顧客； 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)求牙膏賣出數(shù)量最多的供應(yīng)商。 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 = 牙膏