1. [Link] passenger name who has a 'a' as second letter in their name.
select passname from passenger where passname like '_a%';
2. Display the name of the youngest passenger.
select passname from passenger
where passdob=(select max(passdob) from passenger);
3. . Display the name of the passenger, date of birth and age.
select passname,passdob,
round(datediff(current_date,passdob)/365) as age from passenger;
4. Display the number of flights leaving Kolkata.
select count(*) from flight where flightsource='kol';
5. Display the name of city where the number of flights leaving and reaching is
the same.
select [Link] from flight a
group by [Link] having count([Link])=
(select count([Link]) from flight b
where [Link]=[Link]);
6.
Display the name of the city which has flight source but no destination.
select flightsource from flight where flightsource not in(select flightdest from
flight);
7. Display the dates on which flight 1 and 4 is flying.
select flightdate from flight where flightid in(1,4);
8. Display the number of passenger in each flight.
Use column alias PassCount .
select count([Link]) as PassCount,flightid from booking_details a,booking b wh
ere [Link]=[Link] group by [Link];
9. Display the name and date of birth of passengers who are senior citizen (age>
=60).
select passname,passdob from passenger where round((datediff(current_date,passdo
b)/365))>=60;
10. Display the booking id having the highest number of passengers.
select bookingid from booking_details group by bookingid having count(passid)=(s
elect count(passid) from booking_details group by bookingid order by count(passi
d) desc limit 1);
11. Display the booking id (ticket) and the total cost for the booking. Use col
umn alias Total Fare .
select [Link],sum([Link]) as Total_Fare from booking_details a,flight
b,booking c where [Link]=[Link] and [Link]=[Link] group by a.
bookingid;
12. Display the booking id (ticket) and the total cost for the booking. Use colu
mn alias Total Fare .
Consider giving a rebate of 50% to senior citizen (age>=60).
select [Link],sum(
case
when datediff(current_date,[Link])/365>=60
then [Link]*0.5
else [Link] end) as Total_fare
from booking_details a,flight b,booking c,passenger d
where [Link]=[Link] and [Link]=[Link] and [Link]=[Link] gr
oup by [Link];
13. Display the city receiving the maximum number of flights.
select flightdest from flight group by flightdest having count(flightid)=(select
count(flightid) from flight group by flightdest
order by count(flightid) desc limit 1);
14. Display the passenger s name having more than 1 booking.
select passname from passenger where passid in(select passid from booking_detail
s group by passid having count(bookingid)>1);
15. Display flightid with no of booking.
select [Link],count([Link]) from booking a,booking_details b where [Link]
ngid=[Link] group by [Link];
16. Display the passenger (name only) who booked ticket on the day of flight for
flight no 1.
select passname from passenger where passid in(select passid from booking_detail
s where bookingid in(select bookingid from booking where bookdate in(select date
([Link]) from flight a,booking b where [Link]=[Link] and date([Link]
ightdate)=[Link] and [Link]=1)));
17. Display flights having the same source and destination.
select [Link] from flight a,flight b where [Link]!=[Link] and [Link]
htsource=[Link] and [Link]=[Link];
18. Display the record in the following format.
Column alias Booking Summary
#Hints: Ticket No:1 Flight id: 1 Total Passengers :3 Total Fare:6000
# Ticket No:2 Flight id: 3 Total Passengers :1 Total Fare :2500
select concat('Ticket No:',cast([Link] as char),'Flight id:',cast([Link]
d as char),'Total Passengers:',cast(count([Link]) as char),'Total Fare:',cast(
sum([Link]) as char)) as Booking_Summary from flight a,booking b,booking_d
etails c where [Link]=[Link] and [Link]=[Link] group by [Link]
htid;
19. Flight No: 2 have been delayed for 4 hrs due to fog. Display flight id , fli
ght date and a new column flight new date , which displays the new timing.
select flightid,flightdate,flightdate + interval 4 hour as flight_new_date from
flight where flightid=2;
20. Display passenger name , date of birth sorted by the month of birth .
(Jan ? Dec).
select passname,passdob from passenger order by month(passdob);
