“數(shù)據(jù)庫原理”上機題目匯總（2）

　　某公司產(chǎn)品的分銷管理系統(tǒng)有如下四個表項：

　　Agent（AID， ANAME， SALARY）

　　注：對應(yīng)含義為：代理商（代理商編號，姓名，薪水）

　　要求： AID 為主碼，所有字段不為空

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

　　Customer（CID，CNAME）

　　注：對應(yīng)含義為：顧客（顧客編號，姓名）

　　要求：編號為主碼，所有字段不為空

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

　　Product （ PID，PNAME， PRICE）

　　注：對應(yīng)含義為：產(chǎn)品信息（編號，名稱，價格）

　　要求：編號為主碼，所有字段不為空

　　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）

　　注：對應(yīng)含義為：訂單（訂單號，購買日期，顧客號，產(chǎn)品號，代理商號，訂購數(shù)量，訂金）

　　要求：訂單號為主碼，顧客號、產(chǎn)品號、代理商號為外碼，分別參照Customer中的CID， Agent 中的AID，和Product 中的PID，訂購數(shù)量大于0，所有字段不為空。

　　題目：

　　1 按要求完成該四個表的創(chuàng)建，請使用原英文表名和屬性名（滿足上述表的每個約束要求）。

　　2 完成每個表中的數(shù)據(jù)的插入

　　Agent （01，'Smith'，10000）；

　?。?2，'Jones'，7000）；

　?。?3， 'Brown'，5000）；

　?。?4， 'Gray'，7200）；

　?。?5，'Otasi'，4800）；

　?。?6， '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'）；

　?。?03，'Allied'）；

　　（004， 'ACME'）；

　?。?05， '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）

　?。?2，'brush'，0.5）；

　　（03，'razor'，1）

　?。?4，'pen'，1）

　?。?5， '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）

　?。?012，'2001-4-1'，001， 02， 02， 400， 180）

　?。?013，'2002-1-1'，002， 03， 03， 1000， 880）

　?。?014，'2001-5-1'，002， 05， 03， 800， 704）

　?。?015，'2002-1-1'，003， 03， 05， 1200， 1104）

　　（1016，'2001-8-1'，004， 06， 01， 1000， 460）

　?。?017，'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語句完成下列查詢：

　?。?）列出產(chǎn)品訂購數(shù)量超過1000的訂單號。

　　select OID from Orders9527 where QTY>1000

　?。?）列出每個代理商經(jīng)手的訂單數(shù)和總的訂金以及該代理商的編號，姓名

　　select Agent9527.AID，ANAME，count（OID）as 訂單數(shù)， sum（DOLLARS）as 總訂金 from Agent9527，

　　Orders9527 where Agent9527.AID=Orders9527.AID group by Agent9527.AID，ANAME

　?。?）找出同時通過代理商01和02購買產(chǎn)品的顧客編號，姓名。

　　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使用游標(biāo)完成如下操作：

　　使用游標(biāo)找出經(jīng)手辦理的訂單訂金總額大于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購買產(chǎn)品數(shù)量最多的顧客的編號。

　　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）