北大数据库原理上机考题练习及参考答案 练习七(代理、顾客、产品、订单).docx

题目：某公司产品的分销管理系统有如下四个表项： Agent(AID, ANAME,SALARY)注：对应含义为：代理商(代理商编号，姓名，薪水)要求： AID 为主码，所有字段不为空Customer（CID,CNAME）注：对应含义为：顾客（顾客编号，姓名）要求：编号为主码，所有字段不为空 Product ( PID,PNAME, PRICE)注：对应含义为：产品信息（编号，名称，价格） 要求：编号为主码，所有字段不为空Orders（OID，BUY_DATE，CID，AID，PID ，QTY ，DOLLARS）注：对应含义为：订单（订单号，购买日期，顾客号，产品号，代理商号，订购数量，订金）要求：订单号为主码，顾客号、产品号、代理商号为外码，分别参照Customer中的CID, Agent 中的AID，和Product 中的PID，订购数量大于，所有字段不为空。 题目：1 按要求完成该四个表的创建，请使用原英文表名和属性名(满足上述表的每个约束要求）。2 完成每个表中的数据的插入Agent (01,Smith,10000);(02,Jones,7000);(03, Brown,5000);(04, Gray,7200);(05,Otasi,4800);(06, Jack,5500);insert into Agent9527 values(01,Smith,10000)insert into Agent9527 values(02,Jones,7000)insert into Agent9527 values(03, Brown,5000)insert into Agent9527 values(04, Gray,7200)insert into Agent9527 values(05,Otasi,4800)insert into Agent9527 values (06, Jack,5500)Customer (001,TipTop);(002,Basics);(003,Allied);(004, ACME);(005, ACME);insert into Customer9527 values (001,TipTop)insert into Customer9527 values(002,Basics)insert into Customer9527 values(003,Allied)insert into Customer9527 values(004, ACME)insert into Customer9527 values(005, ACME)Product (01,comb,0.5)(02,brush,0.5);(03,razor,1)(04,pen,1)(05, pencil,1)insert into Product9527 values(01,comb,0.5)insert into Product9527 values(02,brush,0.5)insert into Product9527 values(03,razor,1)insert into Product9527 values(04,pen,1)insert into Product9527 values (05, pencil,1)Orders (1011, 2002-4-8,001, 01, 01, 1000, 450)(1012,2001-4-1,001, 02, 02, 400, 180)(1013,2002-1-1,002, 03, 03, 1000, 880)(1014,2001-5-1,002, 05, 03, 800, 704)(1015,2002-1-1,003, 03, 05, 1200, 1104)(1016,2001-8-1,004, 06, 01, 1000, 460)(1017,2002-9-1,005, 01, 04, 1000, 500)(1018,2001-3-6,005, 01, 01, 800, 400)insert into Orders9527 values (1011, 2002-4-8,001, 01, 01, 1000, 450)insert into Orders9527 values (1012,2001-4-1,001, 02, 02, 400, 180)insert into Orders9527 values (1013,2002-1-1,002, 03, 03, 1000, 880)insert into Orders9527 values (1014,2001-5-1,002, 05, 03, 800, 704)insert into Orders9527 values (1015,2002-1-1,003, 03, 05, 1200, 1104)insert into Orders9527 values (1016,2001-8-1,004, 06, 01, 1000, 460)insert into Orders9527 values (1017,2002-9-1,005, 01, 04, 1000, 500)insert into Orders9527 values (1018,2001-3-6,005, 01, 01, 800, 400)3用SQL语句完成下列查询：（1）列出产品订购数量超过1000的订单号。select OID from Orders9527 where QTY1000（2）列出每个代理商经手的订单数和总的订金以及该代理商的编号，姓名select Agent9527.AID,ANAME,count(OID)as 订单数, sum(DOLLARS)as 总订金 from Agent9527,Orders9527 where Agent9527.AID=Orders9527.AID group by Agent9527.AID,ANAME（3）找出同时通过代理商01和02购买产品的顾客编号，姓名。4完成如下更新 将薪水不高于5000的代理商的薪水提高10%，高于5000的代理商的薪水提高5%。update Agent9527 set SALARY=SALARY*1.05 where SALARY5000update Agent9527 set SALARY=SALARY*1.1 where SALARY1000open c1declare x intdeclare y varchar(8)fetch next from c1 into x,ywhile fetch_status=0beginupdate Agent9527 set SALARY=SALARY*1.05 where SALARY=x and ANAME=yfetch next from c1 into x,yendclose c1deallocate c16列出在通过代理商01购买产品数量最多的顾客的编号。 （以下做的题目明显是错误的。）select Customer9527.CID from Customer9527 ,Orders9527 where Customer9527.CID=Orders9527.CID and PID=01 group by Customer9527.CID having count(QTY)=all(select count(QTY) from Orders9527 where PID=01group by Orders9527.CID)参考答案：create table Agent9527(AID varchar(8),ANAME varchar(8)not null,SALARY INT NOT NULL,primary key(AID)create table Customer9527(CID varchar(8),CNAME varchar(8) not null,primary key(CID)CREATE table Product9527(PID varchar(8),PNAME varchar(8)not null,PRICE real not null,primary key(PID)自己做的答案：create table agent( aid char(10) not null, aname char(10) not null, salary money not null, （这里必须用money，因为后面涉及涨工资都不是整数了） primary key (aid)create table customer( cid char(10) not null, cname char(10) not null, primary key (cid)create table product( pid char(10) not null, pname char(10) not null, price money not null, primary key (pid)create table orders( oid char(10) not null, buy_date datetime not null, cid char(10) not null, aid char(10) not null, pid char(10) not null, qty int not null, dollars int not null, primary key (oid), foreign key (cid) references customer(cid), foreign key (aid) references agent(aid), foreign key (pid) references product(pid), check(qty 0)insert into agent values(01,Smith,10000)insert into agent values(02,Jones,7000)insert into agent values(03, Brown,5000)insert into agent values(04, Gray,7200)insert into agent values(05,Otasi,4800)insert into agent values (06, Jack,5500)insert into customer values (001,TipTop)insert into customer values(002,Basics)insert into customer values(003,Allied)insert into customer values(004, ACME)insert into customer values(005, ACME)insert into product values(01,comb,0.5)insert into product values(02,brush,0.5)insert into product values(03,razor,1)insert into product values(04,pen,1)insert into product values (05, pencil,1)insert into orders values (1011, 2002-4-8,001, 01, 01, 1000, 450)insert into orders values (1012,2001-4-1,001, 02, 02, 400, 180)insert into orders values (1013,2002-1-1,002, 03, 03, 1000, 880)insert into orders values (1014,2001-5-1,002, 05, 03, 800, 704)insert into orders values (1015,2002-1-1,003, 03, 05, 1200, 1104)insert into orders values (1016,2001-8-1,004, 06, 01, 1000, 460)insert into orders values (1017,2002-9-1,005, 01, 04, 1000, 500)insert into orders values (1018,2001-3-6,005, 01, 01, 800, 400)3用SQL语句完成下列查询：（1）列出产品订购数量超过1000的订单号。select oid from orders where qty 1000（2）列出每个代理商经手的订单数和总的订金以及该代理商的编号，姓名select o.aid,a.aname,count(o.aid),sum(dollars) from orders o, agent awhere o.aid = a.aidgroup by o.aid,a.aname（3）找出同时通过代理商01和02购买产品的顾客编号，姓名。select cid,cname from customer where cid in (select o1.cid from orders o1, orders o2 wher