Provide working SOL DML statements for the following database schema and queries

Question

Provide working SOL DML statements for the following database schema and queries CUSTOMER (ID. Type, Firstname, Lastname, Address, City) INVENTORY (ID, ItemName, Type, MadeInStore, SupplierName, Daily AverageSold, Price) ORDERS (ID, Customer FK, Item FK, Quantity, DeliveryDate) 7. Show how many customers live in each city 8. Find customers that are restaurant owners (type) and live in San Diego For each order, find which items have been ordered, how many of each, and ordered by delivery day . Show: item name and quantity 9. 10. Find orders requesting "focaccia" . Show: order id, deliver date and item name

Explanation / Answer

7.) select city,count(*) as noOfCustomers from customer group by city;

//comment- the above query group customers by city and shows their count ,count(*) is used for counting customers by city

8)select Firstname,LastName,city from customer where city='San Diego' and type='Owner';

//comment-the above query is used to display firstname,lastname and city of customers living in sandiego and are owners .Here type is assumed as owner you can change it if you have any different way to represent owner.

9)select i.itemname,o.quantity,o.deliverydate from orders o inner join inventory i on o.item_fk=i.id order by delivery date asc'

//comment-the above qquery is used to display itemname,quantity and delivery date to get itemname from inventory a join is used and then ordered by delivery date in ascending order if you want descending replace asc with desc

10)select o.id as orderid,o.deliverydate,i.itemname from orders o inner join inventory i on o.item_fk=i.id where i.itemname='focaccia'

//comment-the above query is used to display orderid(alias name),deliverydate and itemname again using inner join for getting itemnames from inventory and itemname as 'focaccia'

Related Questions

Navigate

• Browse (All)
• Browse P
• Subjects

---

---

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