“数据库原理”上机题目汇总(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)