sql语句.txt

航空订票系统,可实现航空订票的主要功能.

create database flight

delete from flight_schedule
delete from fare
delete from airbus
delete from reservation

drop table flight_schedule
drop table fare
drop table airbus
drop table reservation

create table airbus
(
	airbusNo varchar(5) primary key,
	first_capacity int,
	bus_capacity int,
	eco_capacity int
)

create table fare
(
	route_code varchar(7) primary key,	
	route_desc varchar(25),	
	origin varchar(15),	
	destination varchar(15),	
	first_fare numeric(10,2),	
	bus_fare numeric(10,2),	
	eco_fare numeric(10,2)
)

create table flight_schedule
(
	flightno varchar(5) primary key,	
	airbusNo varchar(5) foreign key references airbus(airbusNo),	
	route_code varchar(7) foreign key references fare(route_code),	
	depart_time varchar(5),	
	journey_hrs varchar(5),	
	first_seat_bk int,	
	bus_seat int,	
	eco_seat_bk int,	
	flight_day1 int,	
	flight_day2 int
)

create table reservation
(
	tickno               int primary key,	
	flightno             varchar(5) foreign key references flight_schedule(flightno),
	pass_name            varchar(15),
	pass_address         varchar(50),
	passport_no          varchar(8),
	credit_card_no       varchar(20),
	flight_date          datetime,
	seatClass            varchar(20),
	reserve_date         datetime
)

alter table flight_schedule
add constraint FK_flight_fare
foreign key(route_code)
references fare(route_code)



insert into airbus values ('A',250,40,30);
insert into airbus values ('B',200,30,30);
insert into airbus values ('C',350,40,30);
insert into airbus values ('D',200,40,30);

insert into fare values('00001','深圳-上海','深圳','上海',30,40,50);
insert into fare values('00002','深圳-北京','武汉','广州',30,40,50);
insert into fare values('00003','北京-广州','北京','广州',30,40,50);
insert into fare values('00004','深圳-重庆','深圳','重庆',30,40,50);
insert into fare values('00005','宁夏-南京','宁夏','南京',30,40,50);


insert into flight_schedule values('1','A','00001','12:00','15:00',30,40,50,20040115,20040115)
insert into flight_schedule values('2','B','00002','12:00','15:00',30,40,50,20040215,20040215)
insert into flight_schedule values('3','C','00003','12:00','15:00',30,40,50,20040315,20040315)
insert into flight_schedule values('4','D','00004','12:00','15:00',30,40,50,20040415,20040415)

insert into reservation values(2001,'1','张三','深圳罗湖区','DESE','43051145874521',2005-09-08,'经济仓',2005-09-07);
insert into reservation values(2002,'2','李四','北京海淀区','DASE','43051145874421',2005-09-06,'商务仓',2005-09-05);
insert into reservation values(2003,'3','王五','深圳福田区','DRSE','43051145874561',2005-09-07,'经济仓',2005-09-07);
insert into reservation values(2004,'4','赵六','深圳盐田区','DGSE','43051145874781',2005-09-08,'经济仓',2005-09-07);

select * from flight_schedule;
select * from reservation;
select * from fare;
select * from airbus;