sql 基础 [一个常见的面试题]

--create database Dom
--on
--(
-- name='Dom_MDF',
-- filename='E:\Dom_Dom.mdf',
-- size=3mb,
-- maxsize=5mb,
-- filegrowth=15%
--)
--log on
--(
-- name='Dom_Log',
-- filename='E:\Dom_Log.log',
-- size=3mb,
-- maxsize=5mb,
-- filegrowth=15%
--)
--go
--
--use Dom
--go
----创建表

--create table city(
--cityCode int primary key identity(1000,1) not null,
--cityName varchar(20) not null,
--)

--create table train(
--startCode int , --(出发城市编号)
--endCode int , --(到达城市编号)
--trainNum varchar(10), --(火车型号)
--startDate datetime , --(出发时间)
--endDate datetime , --(到达时间)
--)
--select * from train
--select * from city
--
--alter table train add constraint fk_startCode foreign key (startCode) references city (cityCode)
--alter table train add constraint fk_endCode foreign key (endCode) references city (cityCode)
--
--insert into city values('成都')
--insert into city values('北京')
--insert into city values('上海')
--insert into city values('武汉')
--insert into city values('香港')
--
--delete train
--insert into train values(1000,1002,'TD-Z241','2010-08-25','2010-08-30')
--insert into train values(1001,1002,'NT-X324','2010-08-29','2010-08-30')
--insert into train values(1003,1002,'MD-Z131','2010-08-30','2010-08-31')
--insert into train values(1002,1001,'TE-S221','2010-08-25','2010-08-30')
--insert into train values(1002,1003,'TR-Z241','2010-08-25','2010-08-30')
--insert into train values(1000,1003,'TW-Z241','2010-08-24','2010-08-30')
--insert into train values(1003,1001,'ZE-Z131','2010-07-30','2010-08-02')
--insert into train values(1002,1003,'DT-Z241','2010-08-10','2010-08-12')
--insert into train values(1002,1004,'DT-Z241',getDate(),'2010-08-30')
--insert into train values(1004,1002,'DT-Z241',getDate(),'2010-08-30')
--insert into train values(1004,1001,'DT-Z241',getDate(),'2010-08-30')

--1)选出-8-10以后出发火车，并按列车的出发时间从早到晚排序。输出内容：出发城市名称、到达城市名称、发车时间
--2)统计-08-10至-08-29每天从“北京”出发的列车的数量
--3)找出今天没有回程的火车。

----1)
--select c1.cityName as startCity ,c2.cityName as endCity, t.startDate,t.endDate from train as t inner join city as c1
--on t.startCode=c1.cityCode inner join city as c2 on t.endCode= c2.cityCode where startDate > cast( '2010-08-10' as datetime)
--order by startDate asc
--
----2)
--select count(*),convert(CHAR(10), t.startDate, 103) as '出发时间' from train as t left join city as c on t.startCode=c.cityCode where
--c.cityName='北京' and t.startDate between cast('2010-08-10' as datetime) and cast('2010-08-29' as datetime) group by t.startDate;
--
----3)
--select c1.cityName as startCity ,c2.cityName as endCity, t.* from train as t inner join city as c1 on t.startCode=c1.cityCode inner join
--city as c2 on t.endCode= c2.cityCode where convert(char(10),t.startDate,120) = convert(char(10),getDate(),120)
--and not exists ( select * from train t1 where convert(char(10),t1.startDate,120) = convert(char(10),getDate(),120)
--and t.startCode = t1.endCode and t.endCode = t1.startCode and t.trainNum = t1.trainNum )