SQL Questions: Flight Management System
( AIRLINES MANAGEMENT SYSTEM)
@MRProgrammer89
1.Display profile id of passenger who booked minimum number of tickets.
Ans:
select profile_id from air_ticket_info
group by profile_id
havingcount(ticket_id) in
(select min(t.top) from
(selectcount(ticket_id) as top from air_ticket_info group by profile_id) as t);
2. Write a query to intimate the passengers who are boarding Chennai to Hyderabad Flight on 6th May 2013 stating
the delay of 1hr in the departure time. The Query should display the passenger’s profile_id, first_name,
last_name, flight_id, flight_departure_date, actual departure time as "Actual_Departure_Time", actual arrival
time as"Actual_Arrival_Time", delayed departure time as "Delayed_Departure_time", delayed arrival time as
"Delayed_Arrival_Time" Hint: Distinct Profile ID should be displayed irrespective of multiple tickets booked by
the same profile.
Ans:
select distinct app.profile_id, app.first_name, app.last_name, ati.flight_id, ati.flight_departure_date,
af.arrival_time as Actual_arrival_time, af.departure_time as Actual_departure_time,
addtime(af.departure_time,'1:00:00') as Delayed_departure_time,
addtime(af.arrival_time,'1:00:00') as Delayed_arrival_time from
air_passenger_profile app join air_flight af join air_ticket_info ati on
app.profile_id=ati.profile_id and ati.flight_id=af.flight_id
where af.from_location='CHN' and af.to_location='HYD'
group by app.profile_id
having ati.flight_departure_date='2014-05-06';
3. Display number of flights between 6.00 AM and 6.00 PM from Chennai. Hint Use FLIGHT_COUNT as alias name
Ans:
select count(flight_id) as Flight_count from
air_flight where
departure_time between '6:00:00' and '18:00:00' and from_location='chn';
4. Display flight id,departure date,flight type of all flights. Flight type can be identified if ticket price is less than 3000
then 'AIR PASSENGER',ticket price between 3000 and less than 4000 'AIR BUS' and ticket price between 4000 and
greater than 4000 then 'EXECUTIVE PASSENGER'. Hint use FLIGHT_TYPE as alias name.
Ans:
selectflight_id, flight_departure_date,
case when price <3000 then 'Air_passenger'
when price between 3000 and 4000 then 'Air_bus'
when price >4000 then 'Executive_passenger'
else 'n/a'
end Flight_Tme
from air_flight_details
group by flight_id;
@MRProgrammer89
5. Display flight id,from location, to location and price of flights whose departure is in the month of April
Ans:
select afd.flight_id, af.from_location, af.to_location,afd.price from
air_flight_details afd join air_flight af on
af.flight_id=afd.flight_id
where month(afd.flight_departure_date)=5
group by afd.flight_id;
6.Write a query to display the Flight_Id, Flight_Departure_Date, From_Location,To_Location and Duration
which has duration of travel less than 1 Hour, 10 Minutes
Ans:
select af.Flight_Id, afd.Flight_Departure_Date, af.From_Location, af.To_Location,Duration from
air_flight af join air_flight_details afd
where afd.flight_id=af.flight_id and af.duration < '01:10:00'
group by af.flight_id;
@MRProgrammer89
7. Write a query to display the credit card type and no of credit cards used on the same type. Hint: Use
CARD_COUNT AS Alias name for no of cards.
Ans:
select card_type, count(card_number) as card_count from
air_credit_card_details
group by card_type;
9. Write a query to display profile id of passenger who booked maximum number of tickets
Ans:
select profile_id from air_ticket_info
group by profile_id
having count(ticket_id) in
(select min(t.top) from
(select count(ticket_id) as top from air_ticket_info group by profile_id) as t);
@MRProgrammer89
10. Write a query to display unique passenger profile id,first name,and mobile number and email address of
passengers who booked ticket to travel from HYDERABAD to CHENNAI.
Ans:
select distinct(app.profile_id),app.first_name,app.mobile_number,app.email_id from
air_passenger_profile app join air_flight af join air_ticket_info ati on
app.profile_id=ati.profile_id and ati.flight_id=af.flight_id
where af.from_location='HYD' and af.to_location='KOL';
11. Write a query to find the number of tickets as “No_of_Tickets” (alias name) booked by Kochi Customers. It
should display the Profile_Id, First_Name, Base_Location and number of tickets booked.
Hint: Use String functions to get the base location of customer from Address and give alias name as Base_Location
Ans:
select app.profile_id, app.first_name, app.address as base_location, count(ati.ticket_id)
from air_passenger_profile app join air_ticket_info ati on
app.profile_id=ati.profile_id
where app.address like '%kolkata%'
group by app.profile_id;
12.Write a query to display the flight_id, from_location, to_location, number of Services as “No_of_Services” (alias
name) offered in the month of May. Hint:The number of services can be calculated from the number of scheduled
departure dates of the flight
Ans:
select ati.flight_id, af.from_location, af.to_location,
count(ati.flight_departure_date) as No_of_Services from
air_ticket_info ati join air_flight af on
af.flight_id=ati.flight_id
where month(ati.flight_departure_date)=5
group by ati.flight_id;
@MRProgrammer89
13. Write a query to display unique profile id,first name, email address and contact number of passengers who
traveled on flight with id 3148.
Ans:
select distinct app.profile_id,app.first_name, app.address, app.mobile_number, app.email_id from
air_passenger_profile app join air_ticket_info ati on
ati.profile_id=app.profile_id
where ati.flight_id='2';
14. Write a query to find the no of services offered by each flight and the total price of the services. The Query should
display flight_id, number of services as “No_of_Services” (alias name)and the cost as “Total_Price” (alias name).
Order the result by Highest Total Price
Hint:The number of services can be calculated from the number of scheduled departure dates of the flight
Ans:
select af.flight_id, count(afd.flight_departure_date) as No_of_services,
sum(afd.price) as Total_price
from air_flight af join air_flight_details afd join air_ticket_info ati
on afd.flight_id=af.flight_id and afd.flight_id=ati.flight_id
where status='on time'
group by af.flight_id
order by Total_price desc;
15. Write a query to find the number of passengers traveled in each flight in each scheduled date. The Query should
display flight_id, flight_departure_date and the number of passengers as “No_of_Passengers” (alias name).
Hint: The Number of passengers inclusive of all the tickets booked with single profile id.
Ans:
select flight_id,flight_departure_date,
count(ticket_id) as no_of_passengers
from air_ticket_info
where status='on time'
group by flight_id, flight_departure_date;
@MRProgrammer89
16. Write a query to find the customer who has booked the ticket with the From_Location different from their
Base_Location. The Query should display flight_id,profile_id, customer_name, base_location and from_location
Hint: Use string functions to get the city value as Base_location from customer address. Use alias name as
“Base_Location”.Combine First Name and Last Name with a comma in between and give alias name as
“Customer_Name”. Use distinct to display unique records
Ans:
select distinct app.profile_id, af.flight_id,
concat(app.first_name,',',app.last_name) as customer_name,app.address as base_location,af.from_location
from air_ticket_info ati join air_passenger_profile app join air_flight af
on ati.flight_id=af.flight_id and app.profile_id=ati.profile_id
where app.address<>af.from_location
order by profile_id,flight_id;
18. Write a query to find the number of flights flying from each location. The Query should display the starting
location as "Location", number of flights to other locations as “No_of_Flights”. Hint: Get the distinct starting
location from the From_Location & To_Location.
Ans:
select from_location, count(flight_id) as no_of_flights
from air_flight
group by from_location;
@MRProgrammer89
19. Write a query to find the average cost of the tickets in each flight on all scheduled dates. The query should display
flight_id, from_location, to_location and Average price as “Price” (alias name).
Ans:
select af.flight_id, af.from_location, af.to_location,
round((avg(afd.price)),2) as average_price
from air_flight af join air_flight_details afd
where af.flight_id=afd.flight_id
group by af.flight_id;
20. Write a query to find the customers who have booked tickets from Chennai to Hyderabad. The query should
display profile_id, customer_name (combine first_name, last_name with comma in b/w), and address of the
customer
Hint: Query should fetch unique customers irrespective of multiple tickets booked
Ans:
select distinct(app.profile_id),concat(app.first_name,’,’,app.last_name),
app.mobile_number, app.address, app.email_id from
air_passenger_profile app join air_flight af join air_ticket_info ati on
app.profile_id=ati.profile_id and ati.flight_id=af.flight_id
where af.from_location='CHN' and af.to_location='HYD'
group by app.profile_id;
@MRProgrammer89
21.Write a query to find the customers who has booked maximum number of tickets in ABC Airlines. The Query
should display profile_id, customer's first_name, Address and Number of tickets booked as “No_of_Tickets” (alias
name).
Ans:
Select distinct(app.profile_id),ati.ticket_id,app.first_name,app.address,count(ati.ticket_id) as no_of_tickets
From air_flight af join air_passenger_profile app join air_ticket_info ati
on ati.flight_id=af.flight_id and ati.profile_id=app.profile_id
where af.airline_name='Malaysian Airlines' group by app.profile_id
having count(ati.ticket_id) in (select max(t.top) from
(select count(ati.ticket_id) as top
from air_flight af join air_passenger_profile app join air_ticket_info ati
on ati.flight_id=af.flight_id and ati.profile_id=app.profile_id
where af.airline_name='Malaysian Airlines' group by app.profile_id)as t);
24. Write a query to find the number of passengers traveled on each flight on each scheduled date and total price of
the tickets booked. The Query should display flight_id, from_location, to_location,flight_departure_date,number
of passengers as “No_of_Passengers” (alias name), Total Price of the tickets booked as “Total_Price(alias
name).
Hint: The Number of passengers inclusive of all the tickets booked with single profile id. "Total_Price" should be
calculated as (No_of_Passengers * price of single ticket
Ans:
select af.flight_id,af.from_location,af.to_location,afd.flight_departure_date,
count(ticket_id) as No_of_Passengers,count(ticket_id)*price as total_price
from air_ticket_info ati join air_flight af join air_flight_details afd
on ati.flight_id=af.flight_id and af.flight_id=afd.flight_id
and ati.status='on time'
group by af.flight_id;
25. Write a query to find the customers who are travelling together on the same flight. The Query should display
profile_id, first_name,last_name,flight_id, flight_departure_date ,from_location and to_location. The result
should be sorted based on the flight id and flight departure date. Hint: Distinct profile id should be displayed
irrespective of multiple tickets booked.Display the result only if more than one customers travelling on the same
flight.
For Eg: If the profile ID P1 & P2 booked the ticket on flight F1 on May 3rd and 10th respectively then do not
display P1 & P2. If both P1 & P2 booked the ticket on flight F1 on May 12th (same day) then display P1
P2 as they will travel together
Ans:
select distinct(TI.profile_id), first_name, last_name,
TI.flight_id, TI.flight_departure_date, from_location, to_location
from air_ticket_info TI, air_passenger_profile PP, air_flight F
whereF.flight_id=TI.flight_id AND PP.profile_id=TI.profile_id AND
EXISTS(select profile_id from air_ticket_info
whereTI.profile_id<>profile_id AND TI.flight_departure_date=flight_departure_date AND TI.flight_id=flight_id)
order by TI.flight_id, TI.flight_departure_date;
@MRProgrammer89
26. Write a query to find the flights available in Morning, After Noon, Evening & Night. The Query should display
the Flight_Id, From_Location, To_Location , Departure_Time, time of service as "Time_of_Service". Time of
Service should be calculated as: From 05:00:01 Hrs to 12:00:00 Hrs - Morning, 12:00:01 to 18:00:00 Hrs -
Afternoon, 18:00:01 to 24:00:00 - Evening and 00:00:01 to 05:00:00 – Night
Ans:
select flight_id,from_location,to_location,departure_time,
case when departure_time between '5:00:01' and '12:00:00' then 'morning'
when departure_time between '12:00:01' and '18:00:00' then 'afternoon'
when departure_time between '18:00:01' and '24:00:00' then 'evening'
else 'n/a'
end 'Time_of_service'
from air_flight
group by flight_id;
@MRProgrammer89
27. Write a query to find the flight which has least number of services in the month of May. The Query should fetch
flight_id, from_location, to_location, least number of Services as “No_of_Services”
Hint: Number of services offered can be calculated from the number of scheduled departure dates of a flight.
Ans:
Select af.flight_id, af.from_location, af.to_location, count(ati.flight_departure_date) as No_of_services
from air_flight af join air_passenger_profile app join air_ticket_info ati
on ati.flight_id=af.flight_id and ati.profile_id=app.profile_id
where month(ati.flight_departure_date)=5 group by app.profile_id
having count(ati.flight_departure_date) in (select max(t.top) from
(select count(ati.flight_departure_date) as top
from air_flight af join air_passenger_profile app join air_ticket_info ati
on ati.flight_id=af.flight_id and ati.profile_id=app.profile_id
where month(ati.flight_departure_date)=5 group by app.profile_id)as t);
@MRProgrammer89
MOTIVATION :
“The way we spend our time defines who we are ”
Although we may not see each other as often as we’d like, distance is no match for the bond that we share. Thank you for coming to visit. It was fantastic to catch up.
VISITE MORE BLOGS
THANK YOU....😊
Tags
SQL