铁路局数据库管理(内附答案).doc

铁路管理系统2任务概述：某铁路局的数据库，其中存放以下三个表：车站(车站名，建设日期，所在城市，最大旅客吞吐量)列车(列车名，起始车站名，终点车站名，载客量)运行记录(记录号，到站时间，到站车次，车站名，上车人数，下车人数) 一、试用SQL语言完成下列功能：1、 用create 语句创建以上表，并根据数据选择合适的数据类型10分2、 用sql语句设置每个表的主外键10分3、 向每个表中插入以下数据10分车站表数据如下：北京西站1967-4-2北京30000北京站1951-3-1北京10000上海站1987-4-2上海20000天津站1967-4-1天津13000浦东站1998-3-1上海4000列车的数据如下：Z1北京站上海站3000Z8天津站北京西站2000Z11北京南站天津站2500Z14上海站北京南站1000运行记录表的数据如下：15：00Z1北京站2000027：31Z1北京南站200300315：21Z1南京站10070048：43Z14上海站500300512：02Z14合肥6501000618：10Z14北京南站240600二、完成以下信息的查询1、统计最大旅客吞吐量的车站15分2、统计载客量在2500以上的列车信息15分3、列车终点站在上海站的列车名以及载客量20分4、统计每个列车名以及此列车行程中上车的总人数20分答案如下：- /* (四)铁路管理系统 */-创建数据库-CREATE DATABASE RailwayManageSystemGOUSE RailwayManageSystemGO-创建车站表-CREATE TABLE RailwayStation(stationID INT PRIMARY KEY,-车站编号stationName VARCHAR(50) NOT NULL,-车站名称buildTime DATETIME,-建站时间ofCity VARCHAR(50),-所在城市maxThruput INT-最大旅客吞吐量)-创建车次表-CREATE TABLE TrainNumber(trainID INT PRIMARY KEY,-列车编号trainName VARCHAR(50) NOT NULL,-列车名originatingStation VARCHAR(50),-起始车站名terminalStation VARCHAR(50),-终点车站名capacityAVG INT-平均载客量)-创建运行记录表-CREATE TABLE TransportRecord(recordID INT PRIMARY KEY,-记录号arrivedTime DATETIME NOT NULL,-到站时间trainID INT FOREIGN KEY-车次编号REFERENCES TrainNumber(trainID),stationName VARCHAR(50),-车站名称getOnTrain INT,-上车人数getOffTrain INT-下车人数)-向车站表中插入数据-INSERT INTO RailwayStation(stationID,stationName,buildTime,ofCity,maxThruput)VALUES (1,北京西站,1967-4-2,北京,30000)INSERT INTO RailwayStation(stationID,stationName,buildTime,ofCity,maxThruput)VALUES(2,北京站,1951-3-1,北京,10000)INSERT INTO RailwayStation(stationID,stationName,buildTime,ofCity,maxThruput)VALUES(3,上海站,1987-4-2,上海,20000)INSERT INTO RailwayStation(stationID,stationName,buildTime,ofCity,maxThruput)VALUES(4,天津站,1967-4-1,天津,13000)INSERT INTO RailwayStation(stationID,stationName,buildTime,ofCity,maxThruput)VALUES(5,浦东站,1998-3-1,上海,4000)-向车次表中插入数据-INSERT INTO TrainNumber(trainID,trainName,originatingStation,terminalStation,capacityAVG)VALUES (1,Z1,北京站,上海站,3000)INSERT INTO TrainNumber(trainID,trainName,originatingStation,terminalStation,capacityAVG)VALUES (2,Z8,天津站,北京西站,2000)INSERT INTO TrainNumber(trainID,trainName,originatingStation,terminalStation,capacityAVG)VALUES (3,Z11,北京南站,天津站,2500)INSERT INTO TrainNumber(trainID,trainName,originatingStation,terminalStation,capacityAVG)VALUES (4,Z14,上海站,北京南站,1000)-向运行记录表中插入数据-INSERT INTO TransportRecord(recordID,arrivedTime,trainID,stationName,getOnTrain,getOffTrain)VALUES (1,5:00,1,北京站,2000,0)INSERT INTO TransportRecord(recordID,arrivedTime,trainID,stationName,getOnTrain,getOffTrain)VALUES (2,7:31,1,北京南站,200,300)INSERT INTO TransportRecord(recordID,arrivedTime,trainID,stationName,getOnTrain,getOffTrain)VALUES (3,15:21,1,南京站,100,700)INSERT INTO TransportRecord(recordID,arrivedTime,trainID,stationName,getOnTrain,getOffTrain)VALUES (4,8:43,4,上海站,500,300)INSERT INTO TransportRecord(recordID,arrivedTime,trainID,stationName,getOnTrain,getOffTrain)VALUES (5,12:02,4,合肥,650,1000)INSERT INTO TransportRecord(recordID,arrivedTime,trainID,stationName,getOnTrain,getOffTrain)VALUES (6,18:10,4,北京南站,240,600)-创建视图统计最大旅客吞吐量的车站-CREATE VIEW StationOfMaxThruputAS(SELECT T1.stationID,stationNameFROM RailwayStation AS T1INNER JOIN( SELECT TOP 1 MAX(maxThruput) AS maxThruput,stationIDFROM RailwayStation GROUP BY stationIDORDER BY maxThruput DESC) AS T2ON T1.stationID = T2.stationID)-创建视图统计平均载客量在2500以上的车次信息-CREATE VIEW TrainNoInfoAS(SELECT trainID,trainName,originatingStation,terminalStation,capacityAVGFROM TrainNumberWHERE capacityAVG = 2500)-创建视图查询列车终点站在上海站的车次以及平均载客量-CREATE VIEW TerminalOfSHAS(SELECT trainID,c