Consider the following relational database: flight (flight-no, source-airport, d
ID: 3729186 • Letter: C
Question
Consider the following relational database:
flight (flight-no, source-airport, destination-airport)
reservation ( passenger-ssn, flight-no, class)
airport (airport-name, city)
Give the corresponding SQL queries for each of the following.
(a) Find the airport names that have been destination of at least one flight that has not been reserved by passenger with id 999.
(b) Find the average number of business-class flights for the passengers.
(c) Find the passengers that have more number of flights than the number of Ankara-Berlin flights of each passenger.
Explanation / Answer
Answer is as follows:
a) Find the airport names that have been destination of at least one flight that has not been reserved by passenger with id 999
SELECT destination_aitport AS AirportName FROM flight JOIN reservation ON flight.flight_no = reservation.flight_no WHERE passenger_ssn = SOME( SELECT passenger_ssn FROM reservation WHERE passenger_ssn = '999') ;
b) Find the average number of business-class flights for the passengers.
SELECT AVG(flight_no) FROM reservation WHERE class = "Business" ;
c) We can't achive this in SQL without knowing the Flight Company and number of flights they have. If you provide me such data that I will update it within sometime.
In question (b) , I think it is not average, it is the count of flights. if so than the query is :
SELECT COUNT(flight_no) FROM reservation WHERE class = "Business" ;
It is just my thinking according to question, please don't take it serious.
if there is any qyery please ask in comments...