SQL Skills
Consider the following ER diagram below:
The diagram shows 3 tables:
· Booking: Contains information regarding bookings made by customers. Include attributes such as the booking status (“confirmed”, “cancelled”) and the point of sale (Country) where the booking was made (eg. “AU”, “NZ”). Each booking contains a unique Id called “BookingID”
· BookingPassenger: Contains information about the passenger within each booking. Please bear in mind that each booking can have more than one passenger. Each passenger contains a unique Id called “PassengerID”
· PassengerSegments: Contains information about the segments bought by each passenger (eg. A customer that bought a $80 ticket to fly from Melbourne to Sydney). Please bear in mind that each passenger can have one or more segments (eg. Flying from Melbourne (MEL) to Sydney (SYD) and then Sydney (SYD) to Gold Coast (OOL) ). Each segment is specified by a unique Id called “SegmentID”.
Considering the information above, please specify the SQL query for answering the following questions (if something is unclear, please feel free to make assumptions):
a. How many bookings sold in Point of Sale “NZ” had their status as “confirmed”?
. What is the full name (first name / last name) of the passenger with the highest ticket fare that is flying between ‘PER’ and ‘OOL’?
c. What was the average ticket fare of segments sold in Point of Sale “AU”?