SQL Questions:Flight Management system-1

Flight Management system-1

Q./* 1. Display passenger name who has a 'e' as second letter in their name*/

select passname from passenger where passname like "_e%";

Q./* 2.Display the name of the youngest passenger.*/

select passname from passenger where passdob=

  (select max(passdob) max from passenger);

Q./* 3. Display the name of the passenger, date of birth and age.*/

  SELECT PASSNAME,PASSDOB,ROUND((DATEDIFF(NOW(),PASSDOB)/365),2) DATEDIFF FROM PASSENGER;

Q./* 4. Display the number of flights leaving Kolkata.*/

SELECT COUNT(*) NO_OF_FLIGHTS FROM FLIGHT WHERE FLIGHTSOURCE="KOL";

Q./*5. Display the name of city where the number of flights leaving and reaching is the same.*/

SELECT FLIGHTSOURCE FROM 

(SELECT FLIGHTSOURCE,COUNT(*) C1 FROM FLIGHT GROUP BY  FLIGHTSOURCE)A INNER JOIN 

(SELECT FLIGHTDEST,COUNT(*) C2 FROM FLIGHT GROUP BY FLIGHTDEST)B ON A.C1=B.C2 AND  

A.FLIGHTSOURCE=B.FLIGHTDEST;

Q./*6. Display the name of the city which has flight source but no destination.*/

SELECT FLIGHTSOURCE FROM FLIGHT

  NOT IN

  (SELECT FLIGHTDEST FROM FLIGHT);

Q./* 7. Display the dates on which flight 1 and 4 is flying.*/

SELECT FLIGHTDATE FROM FLIGHT WHERE FLIGHTID=1 OR FLIGHTID=4;

Q./* 8. Display the number of passenger in each flight. Use column alias “PassCount”.*/

SELECT FLIGHTID,COUNT(BD.PASSID) PASSCOUNT FROM BOOKING_DETAILS BD INNER

JOIN BOOKING B ON B.BOOKINGID=BD.BOOKINGID GROUP BY FLIGHTID;

Q./* 9. Display the name and date of birth of passengers who are senior citizen (age>=60).*/

SELECT PASSNAME,PASSDOB FROM PASSENGER where (ROUND((DATEDIFF(NOW(),PASSDOB)/365)))>=60;

Q./* 10. Display the booking id having the highest number of passengers.*/

SELECT BOOKINGID FROM BOOKING_DETAILS GROUP BY BOOKINGID HAVING COUNT(PASSID)=

(

SELECT MAX(C) FROM 

(

SELECT BOOKINGID,COUNT(PASSID) C FROM BOOKING_DETAILS GROUP BY BOOKINGID

) a);

 Q./*11. Display the booking id (ticket) and the total cost for the booking. Use column alias “Total Fare”.*/

SELECT B.BOOKINGID,F.TICKETCOST TOTAL_FARE FROM BOOKING B INNER JOIN FLIGHT F ON B.FLIGHTID=F.FLIGHTID;

Q./* 12. Display the booking id (ticket) and the total cost for the booking. 

Use column alias “Total Fare”. Consider giving a rebate of 50% to senior citizen (age>=60).*/

select b.bookingid,case

                       when round(datediff(curdate(),p.passdob)/365)>60 then f.ticketcost/2

                       else f.ticketcost

                   end as 'Total_Fare'

from booking b,flight f,booking_details bd,passenger p

where b.bookingid=bd.bookingid and b.flightid=f.flightid and bd.passid=p.passid;

Q./* 13.Display the city receiving the maximum number of flights.*/

select flightdest 

from flight 

group by flightdest 

having count(*)=(select max(c) 

                 from (select count(*) c 

                       from flight 

                       group by flightdest

                     )a

               );

Q./*14 select passname from passenger where passid in

(select passid,COUNT(BOOKINGID) from booking_details group by BOOKINGID having count(PASSID)>1);*/

SELECT PASSNAME FROM PASSENGER WHERE PASSID IN 

(

SELECT PASSID FROM BOOKING_DETAILS GROUP BY PASSID HAVING COUNT(BOOKINGID)>1

);

Q./* 15. Display flightid with no of booking.*/

select flightid,count(*) NO_OF_BOOKINGS from booking group by flightid;

Q./* 16: Display the passenger (name only) who booked ticket on the day of flight for flight no 1.*/

select p.passname from passenger p,booking b,flight f,booking_details d

where b.flightid="1" and f.flightdate=b.bookdate and d.passid=p.passid and b.bookingid=d.bookingid;

Q./* 17.Display flights having the same source and destination.*/

select flightid from flight where flightdest=flightsource;

Q./*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:",bookingid," Flight id: ",fl.flightid," Total Passengers: ",count(bookingid)," Total Fare:",count(bookingid)*ticketcost) booking_details

from booking 

inner join

flight fl

on 

booking.flightid=fl.flightid

group by 

fl.flightid;

Q./*19:Flight No: 2 have been delayed for 4 hrs due to fog. Display flight id , 

flight date and a new column “flight new date”, which displays the new timing.*/

 

select flightid,flightdate,CASE 

WHEN FLIGHTID=2 THEN adddate(FLIGHTDATE,interval 4 hour)

ELSE FLIGHTDATE

END AS 'FLIGHT NEW DATE'

FROM FLIGHT;

 Q./*Display passenger name , date of birth sorted by the month of birth .(Jan to Dec).*/

select passname,passdob from passenger order by passdob;

SHARE