DATABASE SYSTEM QUESTION/ SQL queries Consider the following relational database

Question

DATABASE SYSTEM QUESTION/ SQL queries

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.

Note: italic words in relations represent primarykeys

Explanation / Answer

(a) Find the airport names that have been destination of at least one flight that has not been reserved by passenger with id 999

In this cant achieve this query because as per this we have to get airport name one flight has not book ticket with id 999

airport (airport-name, city) this is airport table we will assume airport name is unique / primary key we didnt use this table attribute in any other table to map them how can we can we gwt the airport name with id

(b) Find the average number of business-class flights for the passengers

select avg(count(flight-no)) from reservation where class='business' group by flight-no

By the above query we can achieve the target what we are looking for

avg gives the average

count gives count for the specific coloumn

group by it is used for grouping the data in the tables using the flight no because with same flight no so many business class tickets can be booked

where it is used to give the condition

Related Questions

Navigate

• Browse (All)
• Browse D
• Subjects

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.