2011年6月7日下午 北大自考数据库系统原理实践考试题及答案

/*               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