Question

Exercise 3 [12.5 marks]

Given the Restaurant management system database that contains the following tables (primary keys are underlined and foreign keys are preceded with #):

Customer (customerID,customerFirstName,customerLastName,customerAddress)

Oreder (orderID,orderDate, #customerID,#menuItemID,#staffID)

MenuItem(menuItemID, menuItemName,ingredients,type,availability)

Staff(staffID, staffName, staffPhoneNumber, staffRole )

OrderPayment(paymentID,paymentAmount,#orderID,#staffID)

1) Without using DISTINCT, write the SQL query equivalent to the following one:[1.5 marks]

SELECT DISTINCT menuItemName

FROM MenuItem

WHERE type = ‘Vegetarian’ OR availability= ‘Yes’;

2) Express the following queries in SQL:

a) Find the number of orders placed by a customer with name ‘Marten Smith’. [1.5 marks]

b) List the menu item details (menuItemName,ingredients,type) which is highly ordered by the customers . ​​​​​​​​ [1.5 marks]

c) Find the average of payments amountcollected by each staff that’s the average amount is greater than 1000. The resulted table should have the columns staffID and amount_Average. ​​​​​​​​ [1.5 marks]

3) Create a view named ‘Non-Vegi’ to hold the details of all menu items with type ‘Non vegetarian’ and ordered on ’01-01-2020’.​​ ​​​ [1.5 marks]

4) Write a PL / SQL block (cursor) to display the details of customers who made payment with an amount more than 100. This block should display customer First Name, Last Name, Address along with the amount of the payment.

Answer 1

Please ask only 2 questions at a time.

1.) SELECT menuItemName FROM MenuItem WHERE type ='VEGETARIAN' or availability ='YES';

2.) SELECT CustomerID, COUNT(CustomerID) as TotalOrders FROM Oreder GROUP BY CustomerID;

SELECT top (10) menuItemName,sum(OrderID), ingredients, type as TotalMenu FROM MenuItem GROUP BY menuItemID ORDER BY sum(orderID) DESC.

Please up vote if you get any help from the content. Your likes motivate us to help others too. Thank You.