flightreservation/query_reservation.sql at master · HONGWENRU/flightreservation · GitHub

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
SELECT * FROM project.reservation;
/* select year 2014 reservation*/
select * from project.reservation res
where res.rtime between '2013-12-31' and '2015-01-01' and res.rstate='success';

/*select cid that spent the most during 2014*/
select t1.cid, sum(total_fare) spending
from (select * from project.reservation res
	  where res.rtime between '2013-12-31' and '2015-01-01'
      and res.rstate='success') t1
group by t1.cid
order by spending DESC;

/*select top10 cid who spend most 2014*/
select *
from (select t1.cid, sum(total_fare) spending
	  from (select * from project.reservation res
			where res.rtime between '2013-12-31' and '2015-01-01'
			and res.rstate='success') t1
	  group by t1.cid
	  order by spending DESC) t2
limit 10;

/* select recent 7 days reservation*/
select *
from project.reservation res
where res.rtime between current_date()-interval 100 day and current_date();

/* slect representative who has the most reservation of last 1 yaer*/

select *
from project.reservation res
where res.rtime between current_date()+interval 1 day-interval 1 year and current_date()
	  and res.rstate='success';

select t1.repre_id, sum(total_fare) total
from (select *
		from project.reservation res
		where res.rtime between current_date()+interval 1 day-interval 1 year and current_date()
			  and res.rstate='success') t1
group by t1.repre_id
order by total DESC
limit 10;

/*Produce a list of all customers who have seats reserved on a given flight*/
select cid
from reservation re
	 join reservetaking rt using(r_num)
     join accounts ac using(anum)
     where re.rstate='success'
     and fid= and fdate= ;

select cid,fid,fdate
from reservation re
	 join reservetaking rt using(r_num)
     join accounts ac using(anum)
     where re.rstate='success'

/* num of cid on each reserved flight instance*/
select count(*) num_cid,
	   t1.fid, t1.fdate
from (select *
		from reservation re
			 join reservetaking rt using(r_num)
             join accounts ac using(anum)
			 where re.rstate='success') t1
group by t1.fid,t1.fdate;

/*9. Produce a list of all flights for a given airport*/
select fl.fid
from flights fl,
	 airport ap
where fl.start_apid=1218 or fl.end_apid=1218;

/* 10.Produce a list of all flights whose arrival and departure times are on-time/delayed */
select *
from flightinstance fi
where fi.status='on-time';

/* ● Best-Seller list of flights of the most recent year */
select *
from reservetaking rt
	 join reservation re using(r_num)
     join flightinstance fi using (fid)
where rstate='success'
	  and rtime between current_timestamp()-interval 1 year and current_timestamp();

select current_timestamp();

select t1.fid, count(*) re_num
from (select rt.fid, rt.r_num,rt.p_id,ac.cid,re.repre_id,re.rtime,rt.fdate
		from reservetaking rt
			 join reservation re using(r_num)
			 join flightinstance fi using(fid)
             join accounts ac using(anum)
		where rstate='success'
			  and rtime between current_timestamp()-interval 1 year and current_timestamp()) t1
group by t1.fid
order by re_num DESC
limit 100;