Question

EXERCISE Provide working SQL DML statements for the following database schema and queries CUSTOMER (ID, Type, Firstname, Lastname, Address, City) INVENTORY(ID, ItemName, Type, MadeInStore, SupplierName, DailyAverageSold, Price) ORDERS(ID, Customer_FK, Item_FK, Quantity, DeliveryDate) 16. Find the total amount due for each order, where the total is at least $70 . Show: order id, lastname, and total-amount . Hint: variation of previous query 17. Find the total amount due for each order placed by a customer that is a restaurant owner, total is at least $70 and the number of different item types (not the units) is less than 3 . Show: order id, lastname, and total-amount, number of different types of items in the order Hint: variation of previous query

Answer 1

SQL Query 16 :

select Orders.ID,lastname,sum(Quantity*Price) TotalAmount
from orders,customer,Inventory
where
Orders.Customer_FK=Customer.ID and
Orders.Item_FK=Inventory.ID
group by Orders.ID,lastname
having TotalAmount>=70;

• This SQL query joins three tables orders,customer,Inventory and will return the result

***********************************

SQL query 17 :

select Orders.ID,lastname,sum(Quantity*Price) TotalAmount,
count(distinct(Item_FK)) NumberOfDifferentItems
from orders,customer,Inventory
where
Orders.Customer_FK=Customer.ID and
Orders.Item_FK=Inventory.ID and customer.type like 'Restaurant Owner'
group by Orders.ID,lastname
having TotalAmount>=70 and NumberOfDifferentItems<3;

• This SQL query joins three tables orders,customer,Inventory and will return the result
• Note :Here customer type value is taken as Restaurant Owner