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 )