2010年12月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

D19,天津站,上海站,500)

运行记录(

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,9:09,Z11,北京南站,800,0

9,9:45，Z11,天津站,0,800

10,1:02，Z14,上海站,1200,0

11,2:03,Z14,南京站,300,500

12,15:11,Z14,北京南站,0,1000

13,12:20,D19,天津站,300,0

14,17:10,D19,上海站,0,300

)

use station
go
create table Station001
(
StationName varchar(20) primary key,
CreateDate  datetime,
City varchar(20),
MaxCounts int check(MaxCounts >=0)
)

create table Train001
(
TrainName varchar(20) primary key,
StartSName varchar(20) not null references Station001(StationName) ,
EndSName   varchar(20) not null references Station001(StationName),
CarryCounts int check(CarryCounts >=0)
)

create table RunLog001
(
LogID int primary key,
ArriveTime datetime,
ArriveNumber varchar(20) not null,
StationName varchar(20) not null references Station001(StationName),
upNumber int check(upNumber >=0),
downNumber int check(downNumber >=0)
)

--2
insert into Station001 values('北京南站','2008-7-2','北京',50000)
insert into Station001 values('北京西站','1967-4-2','北京',30000)
insert into Station001 values('北京站','1951-3-1','北京',10000)
insert into Station001 values('上海站','1987-4-2','上海',20000)
insert into Station001 values('天津站','1967-4-1','天津',13000)
insert into Station001 values('浦东站','1998-3-1','上海',4000)
insert into Station001 values('南京站','1968-5-2','南京' ,10000)

--select* from Station001
insert into Train001 values('Z1','北京站','上海站',3000)
insert into Train001 values('Z8','天津站','北京西站',2000)
insert into Train001 values('Z11','北京南站','天津站',2500)
insert into Train001 values('Z14','上海站','北京南站',1000)
insert into Train001 values('D19','天津站','上海站',500)


insert into RunLog001 values(1,'5:00','Z1','北京站',2000,0)
insert into RunLog001 values(2,'7:31','Z1','北京南站',200,300)
insert into RunLog001 values(3,'15:21','Z1','南京站',100,700)
insert into RunLog001 values(4,'17:43','Z1','浦东站',0,300)
insert into RunLog001 values(5,'18:02','Z1','上海站',0,1000)
insert into RunLog001 values(6,'18:09','Z8','天津站',500,0)
insert into RunLog001 values(7,'5:23','Z8','北京西站',0,500)
insert into RunLog001 values(8,'9:09','Z11','北京南站',800,0)
insert into RunLog001 values(9,'9:45','Z11','天津站',0,800)
insert into RunLog001 values(10,'1:02','Z14','上海站',1200,0)
insert into RunLog001 values(11,'2:03','Z14','南京站',300,500)
insert into RunLog001 values(12,'15:11','Z14','北京南站',0,1000)
insert into RunLog001 values(13,'12:20','D19','天津站',300,0)
insert into RunLog001 values(14,'17:10','D19','上海站',0,300)

3、用SQL语句完成下列查询（10分*3=30分）

（1）找出拥有火车站最多的城市（注意：转入转出都算）

select  top 1 city from Station001
group by city
order by count(city) desc

（2）找出经过城市第二多的列车车次和其经过的火车站名

create function GetTable()
returns table
as
return (
select ArriveNumber,city from RunLog001 r,Station001 s
where r.StationName = s.StationName
group by ArriveNumber,city
--order by count(ArriveNumber) desc
)
go
create function GetNumber()
returns table
return(
select arrivenumber,count(city) as number from dbo.GetTable()
group by arrivenumber
)
-- 2
--找出经过城市第二多的列车车次和其经过的火车站名
declare @number as int
select @number = (
select top 1 * from(
select distinct top 2 number from dbo.GetNumber()
order by number desc
) as c 
where number not in(select top 1* from (
select distinct top 2 number from dbo.GetNumber()
order by number desc
) as v)
)

select arrivenumber,stationName from runlog001
where arrivenumber in
(
select arrivenumber from dbo.GetNumber() where number = @number
)

（3）找出从北京出发到上海的所有的至多一次换乘的乘车方案

create function f_GetSName(@City as varchar(20))
returns table
return
(
select stationname from station001 s
    where s.city = @City
)   
--select * from dbo.f_GetSName('北京')
select * from 
(  select trainname as 起始车次,startsname as 起始站, endsname as 到达站 from train001 t,station001 s
   where t.startsname = s.stationname
   and s.city = '北京'
) as x 
left outer join (
select * from
(
   select trainname as 换乘, startsname as 起始站, endsname as 到达站 from train001 t,station001 s
   where t.startsname = s.stationname
   and city in
 (
   select city from station001
   where stationname in
   (
   -- 天津站
   select /*trainname,startsname,*/ endsname from train001 t,station001 s
   where t.startsname = s.stationname
   and startsname in(select * from dbo.f_GetSName('北京'))
   and endsname not in(select * from dbo.f_GetSName('上海'))-- 并且不是直达上海
  )and endsname in (select * from dbo.f_GetSName('上海')))-- 到达上海
)
as y) as t
on x.到达站 = t.起始站