Q1.  Write a SQL statement to create the ORDER_RECORD tables above.

 

 

Q2.  Write a SQL statement to add a new column, Calories, which stores the number of calories of the food item, to the FOOD_MENU table.

 

Q3.  Write a SQL statement to remove the ALLEGY_WARNING table (not shown above).

 

Q4.  Write a SQL statement to insert a new record to the CUSTOMER table, with Customer_ID C010, name 'Samuel Lai', Phone '9877-6455', and today as the Join_Date.

 

Q5.  Write a SQL statement to remove the new CUSTOMER record we have just created in Q4.

 

Q6.  Write a SQL statement to change the phone number to be '6622-7788' for the customer with ID C001.

 

Q7.  Write a SQL statement to display the Description of Food Category No 1.

 

Q8.  Write a SQL statement to display the date, food item ID, and special request in the order record for the customer 'Jacky Cheng'. Sort the result by date in descending order.

 

Q9.  Write a SQL statement to find number of food menu items available for each food category. Include food category ID, description and the count as "NumOfItems" in the results. Also include those categories with no food menu items.

 

Q10.  Write a SQL statement to find the famous food category of Foodmerang. Count the number of times each food category being ordered. List the food category number, description, and the count as "freq" with more than 50 times.

 

Q11a. Write a SQL statement to list the food category IDs and descriptions that have been included in the food menu but are not ordered (not included in any order record placed) in November 2020. (Answer this question with a non-correlated subquery)

 

Q11b.  Write a SQL statement to list the food category IDs and descriptions that have been included in the food menu but are not ordered (not included in any order record placed) in November 2020. (Answer this question with a correlated subquery) 

 

Q12. Write a SQL statement to display the customer ID, name, and the food category description he/she ordered most.
Hints: You may use multiple and/or nested subqueries.
 

Refer to the below tables of the online take-away food pre-ordering service offered by Foodmerang: FOOD_MENU Item ID Item Name Price F_Catg FOOD CATEGORY F_Catg Description F2010 Lion King Burger 80.00 1 1 Burger F3210 Aloha Cereal 50.00 7 7 Breakfast F3260 Dream Cake (Warning! Addictive) 128.50 25 9 Rice Bowl F3370 Hakuna matata Chocolate 299.90 25 25 Cake CUSTOMER Customer ID C_Name Phone Join_Date C001 Jacky Cheng 9431-1400 02-Sep-2018 C002 Peter Cheng 9431-1409 25-Dec-2019 C003 Mary Li 9555-1100 25-Dec-2019 C004 Sue Ho (null) 01-Jan-2020 ... ORDER_RECORD Customer ID Item ID Order_Date Order_Price Special_Request C001 F2010 12-OCT-2020 64.00 C001 F3260 13-OCT-2020 Serve with BBQ sauce 128.50 Will ask for refund if no good!!! C002 F3260 12-OCT-2020 100.00 Another shot C003 F3370 14-OCT-2020 250.00 Problem-free philosophy... Assume a customer can order a dish once only within the same day.