An airline stores passenger booking information in a table called BOOKING. BookingRef PassengerName FlightCode SeatClass BaseFare BaggageFee BK100 A. Mensah...
Question 1 Report
An airline stores passenger booking information in a table called BOOKING.
| BookingRef | PassengerName | FlightCode | SeatClass | BaseFare | BaggageFee |
|---|---|---|---|---|---|
| BK100 | A. Mensah | FL201 | Economy | 250 | 30 |
| BK101 | C. Dubois | FL201 | Business | 600 | 0 |
| BK102 | R. Tanaka | FL305 | Economy | 180 | 25 |
| BK103 | L. Fischer | FL201 | Economy | 250 | 30 |
| BK104 | S. Obi | FL305 | Business | 520 | 0 |
| BK105 | T. Andersen | FL305 | Economy | 180 | 45 |
(a) Write an SQL query to display the PassengerName and the total cost (BaseFare + BaggageFee) for each booking, ordered by total cost descending. [4]
Answer Details
(a) This question tests your ability to write an SQL query that performs an arithmetic calculation on columns, creates an alias for the result, and sorts the output. [4]
The query needs to:
- Select the PassengerName field
- Calculate the total cost by adding BaseFare and BaggageFee
- Order the results by total cost from highest to lowest
SELECT PassengerName, (BaseFare + BaggageFee) AS TotalCost
FROM BOOKING
ORDER BY TotalCost DESCAn alternative version without the alias is also acceptable:
SELECT PassengerName, BaseFare + BaggageFee
FROM BOOKING
ORDER BY (BaseFare + BaggageFee) DESCMarking points:
- SELECT with both PassengerName and the calculation [1]
- BaseFare + BaggageFee as the computed expression [1]
- ORDER BY clause present [1]
- DESC keyword for descending order [1]
The expected output order is:
| PassengerName | TotalCost |
|---|---|
| C. Dubois | 600 |
| S. Obi | 520 |
| A. Mensah | 280 |
| L. Fischer | 280 |
| T. Andersen | 225 |
| R. Tanaka | 205 |
The key insight is that SQL allows arithmetic expressions in the SELECT clause. The AS keyword creates an alias so the calculated column has a meaningful name in the output. ORDER BY ... DESC sorts from largest to smallest. Business-class passengers have no baggage fee (0), so their total equals their base fare.
Download The App On Google Playstore
Everything you need to excel in your exams
