/* 2011年6月7日下午 北大自考数据库系统原理实践考试题及答案
【要求:不允许使用临时表。第5题必须使用游标,其它各题不允许使用游标,数据库表名前面务必添加自己帐号】
某铁路局的数据库,其中存放以下三个表:
车站(车站名,建设日期,所在城市,最大旅客吞吐量)
列车(列车名,起始车站名,终点车站名,载客量)
运行记录(记录号,到站时间,到站车次,车站名,上车人数,下车人数)
试用SQL语言完成下列功能:
1建表,在定义中要求声明:(5分)
(1)每个表的主外码;
(2) 到站时间,建设日期的数据类型是datetime。
(3) 载客量,各种人数大于等于0,到站车次,车站名不能为空。
2往表中插入数据(5分)
车站(
北京南站,2008-7-2,北京,50000
北京西站,1967-4-2,北京,30000
北京站,1951-3-1,北京,10000
上海站,1987-4-2,上海,20000
天津站,1967-4-1,天津,13000
浦东站,1998-3-1,上海,4000
南京站,1968-5-2,南京,10000
)
列车(
Z1,北京站,上海站,3000
Z8,天津站,北京西站,2000
Z11,北京南站,天津站,2500
Z14,上海站,北京南站,1000
)
运行记录(
1,5:00,Z1,北京站,2000,0
2,7:31,Z1,北京南站,200,300
3,15:21,Z1,南京站,100,700
4,17:43,Z1,浦东站,0,300
5,18:02,Z1,上海站,0,1000
6,18:09,Z8,天津站,500,0
7,5:23,Z8,北京西站,0,500
8,3:09,Z11,北京南站,800,0
9,3:45,Z11,天津站,0,800
10,1:02,Z14,上海站,1200,0
11,2:30,Z14,南京站,300,500
12,15:11,Z14,北京南站,0,1000
)
3.用SQL语句完成下列查询:(10分*3=30分)
(1)找出拥有火车站最多的城市。
(2)找出经过城市第二多的列车车次和其经过的火车站站名。
(3)列出到达旅客最多的火车站。
4.铁道部增加一辆火车T3从北京站直达上海站,8:41出发,19:21到达,载客量1000,实际载客人数400,请根据上述情况更新数据库。(15分)
5.使用游标完成以下操作:
铁道部列车编号规则:
如果列车Z1是从北京出发到上海,那么Z1对应的Z2就应该是上海出发沿原路停靠相同的站到北京。CONVERT(int,substring(列车名,2,LEN(列车名)-1))可以获取列车名中的数字
(1)使用游标修改列车表,补充每辆列车对应的车次。(10分)
(2)假设每辆火车的速度固定,且某次列车到达终点后休息一小时后,即开始作为对应的返程列车,请补充本题第一问中增加的对应车次的运行记录表。(15分)
例如:Z1 18:02到达终点站上海站,那么Z2就应该19:02从上海出发,Z1从浦东站到上海站花费19分钟,那么Z2从上海站到浦东站也应该话费19分钟,所以Z2应该在19:21到达浦东站。以此类推,直到到达终点
6.找出从北京出发到上海的所有的至多一次换乘的乘车方案。(20分)
*/
use study
drop table remember
drop table chezhan
drop table lieche
create table chezhan(
cname char(20) not null,
cdate datetime,
city char(20),
maxtotal int
primary key(cname)
);
create table lieche(
lname char(20) not null,
startcity char(20),
reachcity char(20),
total int,
check(total>0),
primary key(lname)
);
create table remember(
rid int,
reachtime datetime,
lname char(20) not null,
cname char(20) not null,
up int,
down int,
primary key(rid),
foreign key(lname) references lieche(lname),
foreign key(cname) references chezhan(cname)
);
--车站
insert chezhan values('北京南站','2008-7-2','北京','50000');
insert chezhan values('北京西站','1967-4-2','北京','30000');
insert chezhan values('北京站','1951-3-1','北京','10000');
insert chezhan values('上海站','1987-4-2','上海','20000');
insert chezhan values('天津站','1967-4-1','天津','13000');
insert chezhan values('浦东站','1998-3-1','上海','4000');
insert chezhan values('南京站','1968-5-2','南京','10000');
--列车
insert lieche values('Z1','北京站','上海站','3000');
insert lieche values('Z8','天津站','北京西站','2000');
insert lieche values('Z11','北京南站','天津站','2500');
insert lieche values('Z14','上海站','北京南站','1000');
--运行记录
insert remember values('1','5:00','Z1','北京站','2000','0');
insert remember values('2','7:31','Z1','北京南站','200','300');
insert remember values('3','15:21','Z1','南京站','100','700');
insert remember values('4','17:43','Z1','浦东站','0','300');
insert remember values('5','18:02','Z1','上海站','0','1000');
insert remember values('6','18:09','Z8','天津站','500','0');
insert remember values('7','5:23','Z8','北京西站','0','500');
insert remember values('8','3:09','Z11','北京南站','800','0');
insert remember values('9','3:45','Z11','天津站','0','800');
insert remember values('10','1:02','Z14','上海站','1200','0');
insert remember values('11','2:30','Z14','南京站','300','500');
insert remember values('12','15:11','Z14','北京南站','0','1000');
--3.(1)找出拥有火车站最多的城市
select city as'城市',count(city)as'车站数'
from chezhan
group by city
having count(city) >= ALL (select count(city)
from chezhan
group by city)
--3.(2)找出经过城市第二多的列车车次和其经过的火车站站名
/*本问的答案是我自己理解做的,希望能与大家一起交流解题答案
我的 QQ:296729560 E-Mail:zhaojun112@126.com
*/
select remember.lname as'经过城市第二多的列车车次',remember.cname as'其经过的火车站站名'
from(select lname
from (select lname,count(lname) as total--*(2)*这里选出的是去掉了经过城市最多的列车名,那么在导出表x中选出最大count(lname)的就是经过城市第二多的列车名
from (select distinct chezhan.city,remember.lname
from remember,chezhan
where chezhan.cname=remember.cname)as a
where lname not in(select lname--*(1)*这里选出的是所有经过城市做多的列车名并导出表a中在去掉
from(select distinct chezhan.city,remember.lname from remember,chezhan
where chezhan.cname=remember.cname)as b
group by lname
having count(lname)>=all(select count(lname)
from(select distinct chezhan.city,remember.lname
from remember,chezhan
where chezhan.cname=remember.cname)as c
group by lname)
)
group by lname)as x
where total>=all(select count(lname) as total--*(3)*这里选出的就是经过城市第二多的列车
from (select distinct chezhan.city,remember.lname
from remember,chezhan
where chezhan.cname=remember.cname)as a
where lname not in(select lname
from(select distinct chezhan.city,remember.lname from remember,chezhan
where chezhan.cname=remember.cname)as b
group by lname
having count(lname)>=all(select count(lname)
from(select distinct chezhan.city,remember.lname
from remember,chezhan
where chezhan.cname=remember.cname)as c
group by lname)
)
group by lname))as x,remember
where x.lname=remember.lname
--3.(3)列出到达旅客最多的火车站
select cname as '到达车站名',down as'到站人数'
from remember
where down = (
select max(down)
from remember
)
--4.铁道部增加一辆火车T3从北京站直达上海站
insert into lieche
values('T3','北京站','上海站','1000')
insert into remember
values('13','8:41','T3','北京站','400','0')
insert into remember
values('14','19:21','T3','上海站','0','400')
/*5.使用游标完成以下操作:
铁道部列车编号规则:
如果列车Z1是从北京出发到上海,那么Z1对应的Z2就应该是上海出发沿原路停靠相同的站到北京。
CONVERT(int,substring(列车名,2,LEN(列车名)-1))可以获取列车名中的数字*/
--5.(1)使用游标修改列车表,补充每辆列车对应的车次
declare @flag int
declare @name char(20)
declare @lcname char(20)
declare @startname char(20)
declare @reachname char(20)
declare @total int
declare @shi table(
lname char(20) not null,
startcity char(20),
reachcity char(20),
total int,
check(total>0),
primary key(lname)
)
set @flag=0
declare cur_update cursor for
select lname,reachcity,startcity,total
from lieche
open cur_update
fetch next from cur_update
into @lcname,@startname,@reachname,@total
while @@fetch_status =0
begin
set @name=substring(@lcname,1,1)+convert(char(20),convert(int,substring(@lcname,2,len(@lcname)-1))+1)
insert @shi values(@name,@startname,@reachname,@total)
fetch next from cur_update
into @lcname,@startname,@reachname,@total
end
declare cur_insert cursor for
select lname,startcity,reachcity,total
from @shi
open cur_insert
fetch next from cur_insert
into @lcname,@startname,@reachname,@total
while @@fetch_status=0
begin
insert lieche values(@lcname,@startname,@reachname,@total)
fetch next from cur_insert
into @lcname,@startname,@reachname,@total
end
close cur_insert
close cur_update
deallocate cur_insert
deallocate cur_update
print'数据更新成功!'
--5.(2)(2)假设每辆火车的速度固定,且某次列车到达终点后休息一小
--时后,即开始作为对应的返程列车,请补充本题第一问中增加的对应
--车次的运行记录表(例如:Z1 18:02到达终点站上海站,那么Z2就应
--该19:02从上海出发,Z1从浦东站到上海站花费19分钟,那么Z2从上
--海站到浦东站也应该话费19分钟,所以Z2应该在19:21到达浦东站。
--以此类推,直到到达终点)
/**/
declare @top int
declare @id char(20)
declare @lid char(20)
declare @count int
declare @time datetime
declare @lname char(20)
declare @cname char(20)
declare @up int
declare @down int
set @top=0
declare @table table(
rid int,
reachtime datetime,
lname char(20) not null,
cname char(20) not null,
up int,
down int,
primary key(rid)
)
select @count= count(rid) from remember
declare cur_data cursor for
select distinct lname
from remember
open cur_data
fetch next from cur_data
into @lid
while @@fetch_status=0
begin
set @id=substring(@lid,1,1)+convert(char(20),convert(int,substring(@lid,2,len(@lid)-1))+1)
declare cur_updata cursor for
select reachtime,cname,down,up
from remember
where lname=@lid
open cur_updata
fetch next from cur_updata
into @time,@cname,@up,@down
while @@fetch_status=0
begin
set @top=@top+1
select @time=dateadd(hour,1,@time)
insert @table values(@top,@time,@id,@cname,@up,@down)
fetch next from cur_updata
into @time,@cname,@up,@down
end
close cur_updata
deallocate cur_updata
fetch next from cur_data
into @lid
end
declare cur_up cursor for
select reachtime,lname,cname,up,down
from @table
open cur_up
fetch next from cur_up
into @time,@lname,@cname,@up,@down
while @@fetch_status=0
begin
insert remember values((@count+@top),@time,@lname,@cname,@up,@down)
set @top=@top-1
fetch next from cur_up
into @time,@lname,@cname,@up,@down
end
close cur_up
close cur_data
deallocate cur_up
deallocate cur_data
--6.找出从北京出发到上海的所有的至多一次换乘的乘车方案
select x.city as '出发城市',x.startcity as'始发站',x.lname as'始发列车',x.cname as'换乘车站',y.lname as'换乘列车',y.reachcity as'终点站',y.city as'终点城市'
from (select lieche.startcity,lieche.lname,remember.cname,chezhan.city
from lieche,chezhan,remember
where lieche.startcity=chezhan.cname and chezhan.city='北京' and remember.lname=lieche.lname) as x,
(select lieche.reachcity,lieche.lname,remember.cname,chezhan.city
from lieche,chezhan,remember
where lieche.reachcity=chezhan.cname and chezhan.city='上海' and remember.lname=lieche.lname) as y
where x.cname=y.cname and x.lname<>y.lname and x.startcity<>y.reachcity and x.cname<>x.startcity and y.cname<>y.reachcity