city(城市信息)...train(火车信息表)

表1：train(火车信息表)  [下面是字段]               
        startCode(出发城市编号) fk,                         
        endCode(到达城市编号) fk,       
        trainNum(火车型号),
        startDate(出发时间),
        endDate(到达时间)

 表2：city(城市信息)  [下面字段]                                                    
 cityCode(城市编号) pk,
cityName(城市名称),
[注]：startCode和endCode 引用cityCode。

1)选出2010-5-10以后出发火车，并按起飞时间从早到晚排序。输出内容：出发城市名称、到达城市名称、发车时间
2)统计2010-4-10至2009-5-10每天从“北京”出发的列车的数量
3)找出今天没有回程的火车。(比如，2010-1-2 北京-天津的火车，  可以是上午去的天津，中午到了；下午的时候，该型号的列车又从天津回北京。这就是回程)

 

-- Create table
create table SYSTEM.TRAIN
(
  STARTCODE NUMBER(16),
  ENDCODE   NUMBER(16),
  TRAINNUM  VARCHAR2(100),
  STARTDATE DATE,
  ENDDATE   DATE
)
tablespace SYSTEM
  pctfree 10
  pctused 40
  initrans 1
  maxtrans 255
  storage
  (
    initial 64K
    minextents 1
    maxextents unlimited
  );
-- Add comments to the table
comment on table SYSTEM.TRAIN
  is '火车信息表';
-- Create/Recreate primary, unique and foreign key constraints
alter table SYSTEM.TRAIN
  add constraint FK_ENDCODE foreign key (ENDCODE)
  references SYSTEM.CITY (CITYCODE);
alter table SYSTEM.TRAIN
  add constraint FK_STARTCODE foreign key (STARTCODE)
  references SYSTEM.CITY (CITYCODE);

 

-- Create table
create table SYSTEM.TRAIN
(
  STARTCODE NUMBER(16),
  ENDCODE   NUMBER(16),
  TRAINNUM  VARCHAR2(100),
  STARTDATE DATE,
  ENDDATE   DATE
)
tablespace SYSTEM
  pctfree 10
  pctused 40
  initrans 1
  maxtrans 255
  storage
  (
    initial 64K
    minextents 1
    maxextents unlimited
  );
-- Add comments to the table
comment on table SYSTEM.TRAIN
  is '火车信息表';
-- Create/Recreate primary, unique and foreign key constraints
alter table SYSTEM.TRAIN
  add constraint FK_ENDCODE foreign key (ENDCODE)
  references SYSTEM.CITY (CITYCODE);
alter table SYSTEM.TRAIN
  add constraint FK_STARTCODE foreign key (STARTCODE)
  references SYSTEM.CITY (CITYCODE);

select * from system.city t;
select * from system.train t;

 

SQL:

1.select (select cityName from system.city where cityCode = t.startcode) as 出发城市名称,
       (select cityName from system.city where cityCode = t.endcode) as 到达城市名称,
       t.startcode as 发车时间
     from system.train t
   where t.startdate >to_date('2010-5-10', 'YYYY-MM-DD')
 order by t.startcode desc;

 

2.select count(*) as 列车数量 from system.train t
  where t.startcode = (select cityCode from system.city where cityName = '北京')
   and
  t.startdate between to_date('2010-4-10', 'YYYY-MM-DD') and to_date('2010-5-10', 'YYYY-MM-DD')
   group by t.startdate;

 

3.select * from system.train
  where  TRAINNUM not in
(select A.trainnum from  system.train a ,system.train b where
a.startCode = b.endCode and a.endCode = b.startCode and a.startDate = b.startDate) and startDate = sysdate;