Instructions
Refer to the below table of the special meal ordering service for the passengers who travel with Yoshi Airlines:
FLIGHT AIRPORT
Flight_ID Depart_Airport
IS617 KIX
IS658 HKG
IS676 HKG
IS679 OKA
… …
Airport Airport_Name Region
HKG Hong Kong Asia
KIX Kansai, Osaka Asia
NRT Narita, Tokyo Asia
OKA Naha, Okinawa Asia
… … …
PASSENGER MEAL
Passenger_ID P_Name
P5260 Brendon Lee
P5270 Lily So
P5290 Jason Ma
P6260 Sam Tang
… …
Meal Meal_Description
BABY Baby meal
HINDU Hindu meal
LOWFC Low fat or cholesterol
VEG Vegan meal
… …
SPECIAL_MEAL_ORDER
Flight_ID Flight_Date Passenger_ID Seat_Nbr Meal
… … … … …
Q1. Write a SQL statement to create the SPECIAL_MEAL_ORDER table above. (2 marks)
Q2. Write a SQL statement to add a new column, P_DOB to the PASSENGER table for storing their birth dates. (1 mark)
Q3. Write a SQL statement to change the max length of Meal_Description of the MEAL table to be 100. (1 mark)
Q4. Write a SQL statement to drop the Region column from the AIRPORT table. (1 mark)
Q5. Write a SQL statement to remove the FLIGHT table. (1 mark)
Q7. Write a SQL statement to remove the new PASSENGER record we have just created in Q6. (1 mark)
Q8. Write a SQL statement to change the departure airport (Depart_Airport) to be Narita, Tokyo (‘NRT’) for the flight with ID IS600. (1 mark)
Q9. Write a SQL statement to display the name of Passenger P5260. (1 mark)
Q12. Write a SQL statement to list the passengers who have made special orders on two or more different meals. Include the passenger ID and name in the result. (4 marks)
