“数据库原理”上机题目汇总（2）

　　某公司产品的分销管理系统有如下四个表项：

　　Agent（AID， ANAME， SALARY）

　　注：对应含义为：代理商（代理商编号，姓名，薪水）

　　要求： AID 为主码，所有字段不为空

　　create table Agent9527（AID varchar（8），ANAME varchar（8）not null，SALARY INT NOT NULL，primary key（AID））

　　Customer（CID，CNAME）

　　注：对应含义为：顾客（顾客编号，姓名）

　　要求：编号为主码，所有字段不为空

　　create table Customer9527（CID varchar（8），CNAME varchar（8） not null，primary key（CID））

　　Product （ PID，PNAME， PRICE）

　　注：对应含义为：产品信息（编号，名称，价格）

　　要求：编号为主码，所有字段不为空

　　CREATE table Product9527（PID varchar（8），PNAME varchar（8）not null，PRICE real not null，primary key（PID））

　　Orders（OID，BUY_DATE，CID，AID，PID ，QTY ，DOLLARS）

　　注：对应含义为：订单（订单号，购买日期，顾客号，产品号，代理商号，订购数量，订金）

　　要求：订单号为主码，顾客号、产品号、代理商号为外码，分别参照Customer中的CID， Agent 中的AID，和Product 中的PID，订购数量大于0，所有字段不为空。

　　题目：

　　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 QTY>1000

　　（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 SALARY>5000

　　update Agent9527 set SALARY=SALARY*1.1 where SALARY<=5000

　　5使用游标完成如下操作：

　　使用游标找出经手办理的订单订金总额大于1000的代理商，将其薪水提高5%.

　　declare c1 cursor for

　　select Agent9527.AID，ANAME from Agent9527，Orders9527 where Agent9527.AID=Orders9527.AID

　　group by Agent9527.AID，ANAME having sum（DOLLARS）>1000

　　open c1

　　declare @x int

　　declare @y varchar（8）

　　fetch next from c1 into @x，@y

　　while @@fetch_status=0

　　begin

　　update Agent9527 set SALARY=SALARY*1.05 where SALARY=@x and ANAME=@y

　　fetch next from c1 into @x，@y

　　end

　　close c1

　　deallocate c1

　　6列出在通过代理商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=01

　　group by Orders9527.CID）