Q1. Write a SQL statement to create the ORDER_RECORD tables above. Q2. Write a SQL
Question:
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.
Accounting Information Systems
ISBN: 978-1260153156
2nd edition
Authors: Vernon Richardson, Chengyee Chang, Rod Smith