Repository navigation
Expand file tree
/
Copy pathViews
More file actions
135 lines (108 loc) · 4.3 KB
/
Copy pathViews
File metadata and controls
135 lines (108 loc) · 4.3 KB
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
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
Booking Agent View for most recent 30 day commissions and number of tickets
'''
Create View booking_agent_view as
Select sum(price * .1) as commissions, count(ticket_id) as tickets, avg(commissions) as avg_commissions
from flight inner join
(Select *
From booking_agent natural join purchases natural join ticket) as T on flight.flight_num = T.flight_num
Where purchase_date > Date_Sub(curdate(), INTERVAL 30 DAY) and email = {input_email};
'''
#Booking Agent View for top 5 customers
#for 6 month by number
'''
Create View top_5_customer_by_number as
Select customer_email, count(ticket_id) as number_of_tickets
from flight inner join
(Select *
From booking_agent natural join purchases natural join ticket) as T on flight.flight_num = T.flight_num
Where purchase_date > Date_Sub(curdate(), INTERVAL 6 MONTH) and email = {input_email}
group by customer_email
LIMIT 5;
'''
# for 1 year commissions
'''
Create View top_5_customer_by_commission as
Select customer_email, sum(price * .1) as commissions
from flight inner join
(Select *
From booking_agent natural join purchases natural join ticket) as T on flight.flight_num = T.flight_num
Where purchase_date > Date_Sub(curdate(), INTERVAL 1 YEAR) and email = input(email)
group by customer_email
LIMIT 5;
'''
Check for booking agent( if a booking agent does not work for an airline, then they cannot purchase tickets)
Select email, count(airline_name)
from booking_agent_work_for
where email = {input_email}
Checking Available Flights
Create view available_flights as
select airline_name, airplane_id, departure_airport, departure_time, arrival_airport, arrival_time, price, status, T.flight_num, (seats-tickets_purchased) num_of_available_tickets
from (Select * From airplane natural join flight) as S
inner join (select flight_num, count(ticket_id ) as tickets_purchased
FROM reservation_system.ticket
group by flight_num) as T on S.flight_num = T.flight_num
Customer Spending tracking
Select sum(price) as spending
from flight inner join
(Select *
From purchases natural join ticket) as T on flight.flight_num = T.flight_num
Where customer_email = {input_email}
Airline use cases
Airline_Staff queries for checking permissions
#Admin permission query
SELECT permission_type
From permission
WHERE username = {str_username}
#General Staff queries
#View flights from their airline
SELECT *
From flight
WHERE airline_name = session['airline_name'] and status = {input_status}
#List of customers for a certain flight
Select customer_email
From ticket natural join purchases
Where airline_name = session[airline_name] and flight_num = {input_flight_num}
# See all flights taken by a certain customer
Select flight_num
From ticket natural join purchases
Where airline_name = session[airline_name] and customer_email = {input_customer_email}
#Most Frequent customer
Select customer_email, count(ticket_id)
From ticket natural join purchases
Where airline_name = session[airline_name]
group by customer_email
Limit 1;
Amount of tickets sold in a month
Select count(ticket_id)
From ticket natural join purchases
Where airline_name = session[airline_name]
group by customer_email
Where purchase_date > Date_Sub(curdate(), INTERVAL 30 DAY) and airline_name = {airline_name};
Limit 1;
# Top booking agents
Create View top_booking_agent as
Select email, sum(price * .1) as commissions
from flight inner join
(Select *
From booking_agent natural join purchases natural join ticket) as T on flight.flight_num = T.flight_num
Where airline_name = session[airline_name]
group by email
#Admin insert airplane query
Insert into airplane Values(session['airline_name'], airplane_id, seats)
Insert flight into flights
Insert into flight Values(session['airline_name'], flight_number, departure_airport, departure_time, arrival_airport, arrival_time, price, status, airplane_id)
Admin insert airport for airline
#create new table to keep track of information
#adding new airports
create table airline_available_airports as
select airline.airline_name, airport.airport_name
from airline,airports
If airport did not exist
Insert into airports Values(airport_name, airport_city)_
Insert into airline_available_airports Values(session['airline_name'], airport_name)
#Giving people permission
Insert into permissions Values(username, permission_type)
#Operator queries
Update flight
Set status = '{input_status}'
Where flight_num = {input_flight_num)