Database Systems Provide working SQL DML statements for the following database s

Question

Database Systems

Provide working SQL DML statements for the following database schema and queries EXERCISE CUSTOMER ID, Type, Firstname, Lastname, Address, City) INVENTORY(ID, ItemName, Type, MadeInStore, SupplierName, DailyAverageSold, Price) ORDERS (D, customer-FI, 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

Explanation / Answer

16 Answer :

select o.ID, c.Lastname, (o.Quantity * i.Price) as totalAmount from ORDERS o
left outer join CUSTOMER c on o.Customer_FK = c.ID
left outer join INVENTORY i on o.Item_fk = i.ID
where (o.Quantity * i.Price ) >= 70

o - order table

c - customer table

i - inventory table

for total amount i have multiplied price with quantity.

17. Answer :

select * from (
select o.ID, c.Lastname, (o.Quantity * i.Price) as totalAmount,
count(i.Type) as no_of_types from ORDERS o
left outer join CUSTOMER c on o.Customer_FK = c.ID
left outer join INVENTORY i on o.Item_fk = i.ID
where c.type = "Restaurent owner" and (o.Quantity * i.Price ) >= 70 group by i.Types )
where no_of_types < 3

Subquery to fetch the aggregated types. Outer query to apply the where condition on types

Related Questions

Navigate

• Browse (All)
• Browse D
• Subjects

---

---

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.