question of sql query/database:

2. Refunds! Airlines offer refunds for late departures as follows: For domestic flights (flights within the same country), a 35% refund is given for a departure delay of 4 hours or more, and a 50% refund for 10 hours or more. For international flights (flights between different countries), a 35% refund is given for a departure delay of 7 hours or more, and a 50% refund for 12 hours or more. However, if the pilots manage to make up time during the flight and have an arrival delay that is at most half of the departure delay, then no refund is provided, regardless of how many hours the delay was. For every airline and year that the airline had flights which required refunds, return the total refund money given in that particular year in each seat class. This means there should be at most three records per airline- year combination (you should not include a seat class if no one booked it for any refunded flight). The year of a flight comes from its scheduled departure date. Attribute airline the airline code. the airline name, a specific year. one of the three seat classes (economy, business, first). refund the total money refunded to that seat class that year. Everyone? Every airline-year-seat_class combination should be included in each year the airline had a flight which required refunds, unless a seat class had no bookings that year for a refunded flight, then you should not include that seat class. Duplicates? No duplicates. name year seat class Booking Schema: Id Seat Class 1 2 Pass ID 2 5 Flight ID 5 5 Row 3 3 Letter A B 1 3 5 5 3 1 A CoaWN 4 Date time 2017-08-04 16:05:00 2017-09-14 16:43:00 2017-12-30 21:00:00 2017-10-23 15:57:00 2017-12-30 20:05:00 2019-08-04 16:05:00 2019-08-05 16:05:00 2019-08-06 16:05:00 2019-08-07 16:05:00 2019-08-08 16:05:00 2019-08-09 16:05:00 5 10 10 Price 300 400 600 2300 1200 300 400 600 2300 600 1200 2 5 Economy Economy Economy First Business Economy Economy Economy First Economy Business 2 3 A A B 3 1 | 10 10 3 1 C A 9 3 10 11 6 4 10 10 2 A Airline Schema: Code AC TS UA Name Air Canada Air Transat United Airlines Airport Schema: Code YYZ YTZ YVR LHR HND CDG LGA JFK ORD BUF Name City Person International Airport Toronto Billy Bishop Toronto Airport Toronto Vancouver International Airport Vancouver London Heathrow London Tokyo Haneda Tokyo Paris Charles De Gaulle Paris New York LaGuardia New York New York John F. Kennedy New York Chicago O'Hare Chicago Buffalo Niagara International Airport Buffalo Country Canada Canada Canada UK Japan France USA USA USA USA Arrival Schema: Flight ID 1 Data time 2018-01-02 04:25:00 2 3 4 5 2018-01-01 19:35:00 2018-01-02 17:00:00 2018-01-03 05:45:00 2018-01-05 01:15:00 Departure Schema: Flight ID 1 Data time 2018-01-01 16:15:00 2 3 2018-01-01 13:00:00 2018-01-02 10:30:00 2018-01-02 23:55:00 2018-01-04 20:00:00 5 Flight Schema: Id Airline Outbound Inbound S dep S Arv Flight Plane num 1 FGHIJ 1 AC YYZ HND 2018-01-01 16:05:00 2018-01-01 13:00:00 2018-01-02 04:05:00 2018-01-01 19:35:00 2 AC 862 ACACA YYZ LHR 3 AC 857 ACACA LHR YYZ 4 BA 8 UVWXY HND LHR 2018-01-02 10:30:00 2018-01-02 16:15:00 2018-01-04 07:00:00 2020-04-30 15:00:00 2018-01-02 17:00:00 2018-01-03 03:45:00 2018-01-04 12:15:00 2020-04-30 16:15:00 5 TS 917 TSTST YYZ YVR AC 700 ACACA YYZ LGA 7 UA 211 PQRST YYZ ORD 8 UA 239 PQRST ORD LGA 2020-04-30 10:00:00 2020-04-30 15:00:00 2020-04-30 07:00:00 2020-04-30 14:00:00 2020-04-30 10:45:00 2020-04-30 16:15:00 2020-04-30 12:15:00 2020-04-30 20:15:00 9 TS 917 KLMNO YYZ YVR 10 AC 909 ABCDE YVR LGA Passenger Schema: Id 1 2 3 4 5 6 Plane Schema: FirstName Joseph Hell What Say Amir Joseph Surname McDabber Lo Up No Khan Smith Email joseph.mcDab@gmail.com hellno@gmail.com What@up.com SayNo@gmail.com amir.khan@gmail.com j.smith@gmail.ca Tail Number Airline Model Capacity First Capital Business 2 1 ABCDE TSTST FGHIJ ACACA KLMNO PQRST UVWXY 2 70 AC TS AC AC TS UA Capital Economy 3 3 350 350 120 120 500 D8100 D8100 B767 B767 A320 A320 A380 70 20 20 1 30 30 10 10 BA 100 50 2. Refunds! Airlines offer refunds for late departures as follows: For domestic flights (flights within the same country), a 35% refund is given for a departure delay of 4 hours or more, and a 50% refund for 10 hours or more. For international flights (flights between different countries), a 35% refund is given for a departure delay of 7 hours or more, and a 50% refund for 12 hours or more. However, if the pilots manage to make up time during the flight and have an arrival delay that is at most half of the departure delay, then no refund is provided, regardless of how many hours the delay was. For every airline and year that the airline had flights which required refunds, return the total refund money given in that particular year in each seat class. This means there should be at most three records per airline- year combination (you should not include a seat class if no one booked it for any refunded flight). The year of a flight comes from its scheduled departure date. Attribute airline the airline code. the airline name, a specific year. one of the three seat classes (economy, business, first). refund the total money refunded to that seat class that year. Everyone? Every airline-year-seat_class combination should be included in each year the airline had a flight which required refunds, unless a seat class had no bookings that year for a refunded flight, then you should not include that seat class. Duplicates? No duplicates. name year seat class Booking Schema: Id Seat Class 1 2 Pass ID 2 5 Flight ID 5 5 Row 3 3 Letter A B 1 3 5 5 3 1 A CoaWN 4 Date time 2017-08-04 16:05:00 2017-09-14 16:43:00 2017-12-30 21:00:00 2017-10-23 15:57:00 2017-12-30 20:05:00 2019-08-04 16:05:00 2019-08-05 16:05:00 2019-08-06 16:05:00 2019-08-07 16:05:00 2019-08-08 16:05:00 2019-08-09 16:05:00 5 10 10 Price 300 400 600 2300 1200 300 400 600 2300 600 1200 2 5 Economy Economy Economy First Business Economy Economy Economy First Economy Business 2 3 A A B 3 1 | 10 10 3 1 C A 9 3 10 11 6 4 10 10 2 A Airline Schema: Code AC TS UA Name Air Canada Air Transat United Airlines Airport Schema: Code YYZ YTZ YVR LHR HND CDG LGA JFK ORD BUF Name City Person International Airport Toronto Billy Bishop Toronto Airport Toronto Vancouver International Airport Vancouver London Heathrow London Tokyo Haneda Tokyo Paris Charles De Gaulle Paris New York LaGuardia New York New York John F. Kennedy New York Chicago O'Hare Chicago Buffalo Niagara International Airport Buffalo Country Canada Canada Canada UK Japan France USA USA USA USA Arrival Schema: Flight ID 1 Data time 2018-01-02 04:25:00 2 3 4 5 2018-01-01 19:35:00 2018-01-02 17:00:00 2018-01-03 05:45:00 2018-01-05 01:15:00 Departure Schema: Flight ID 1 Data time 2018-01-01 16:15:00 2 3 2018-01-01 13:00:00 2018-01-02 10:30:00 2018-01-02 23:55:00 2018-01-04 20:00:00 5 Flight Schema: Id Airline Outbound Inbound S dep S Arv Flight Plane num 1 FGHIJ 1 AC YYZ HND 2018-01-01 16:05:00 2018-01-01 13:00:00 2018-01-02 04:05:00 2018-01-01 19:35:00 2 AC 862 ACACA YYZ LHR 3 AC 857 ACACA LHR YYZ 4 BA 8 UVWXY HND LHR 2018-01-02 10:30:00 2018-01-02 16:15:00 2018-01-04 07:00:00 2020-04-30 15:00:00 2018-01-02 17:00:00 2018-01-03 03:45:00 2018-01-04 12:15:00 2020-04-30 16:15:00 5 TS 917 TSTST YYZ YVR AC 700 ACACA YYZ LGA 7 UA 211 PQRST YYZ ORD 8 UA 239 PQRST ORD LGA 2020-04-30 10:00:00 2020-04-30 15:00:00 2020-04-30 07:00:00 2020-04-30 14:00:00 2020-04-30 10:45:00 2020-04-30 16:15:00 2020-04-30 12:15:00 2020-04-30 20:15:00 9 TS 917 KLMNO YYZ YVR 10 AC 909 ABCDE YVR LGA Passenger Schema: Id 1 2 3 4 5 6 Plane Schema: FirstName Joseph Hell What Say Amir Joseph Surname McDabber Lo Up No Khan Smith Email joseph.mcDab@gmail.com hellno@gmail.com What@up.com SayNo@gmail.com amir.khan@gmail.com j.smith@gmail.ca Tail Number Airline Model Capacity First Capital Business 2 1 ABCDE TSTST FGHIJ ACACA KLMNO PQRST UVWXY 2 70 AC TS AC AC TS UA Capital Economy 3 3 350 350 120 120 500 D8100 D8100 B767 B767 A320 A320 A380 70 20 20 1 30 30 10 10 BA 100 50