Database Systems SQL DML Statements. help please Provide working SQL DML stateme

Question

Database Systems SQL DML Statements. help please

Provide working SQL DML statements for the following database schema and queries CUSTOMER (D, Type, Firstname, Lastname, Address, City INVENTORY(TD, ItemName, Type, MadeInStore, SupplierName, DailyAverageSold, Price) ORDERS (ID, Customer_FK, Item FK, Quantity,DeliveryDate) 10. Show the lastname of customers that either Have placed at least one order totaling more than $70 Or,live in "Bonita" 11. Show the item names in orders totaling more than $70 12. Find customers that have placed at least one order Do not use JOIN 13. Find customers that have never placed an order Do not use JOIN

Explanation / Answer

Please find the below SQL DML statements for required operations:

Here, it is asumed that ID columns in CUSTOMER and INVENTORY tables will be Foregin keys in ORDERS table

10)
SELECT LASTNAME FROM(
SELECT LASTNAME,ItemName,Price,CITY,CUSTOMER_FK
FROM ORDERS
INNER JOIN INVENTORY ON ORDERS.ITEM_FK=INVENTORY.ID
RIGHT JOIN CUSTOMER ON ORDERS.CUSTOMER_FK=CUSTOMER.ID
)WHERE PRICE>70 OR CITY='Bonita'

11)
SELECT ItemName,Price
FROM ORDERS
INNER JOIN INVENTORY
ON ORDERS.ITEM_FK=INVENTORY.ID
where PRICE>70

12)

SELECT FIRSTNAME,LASTNAME
FROM ORDERS,CUSTOMER
WHERE ORDERS.CUSTOMER_FK=CUSTOMER.ID

13)
SELECT LASTNAME FROM CUSTOMER
where ID NOT IN
(SELECT CUSTOMER_FK FROM ORDERS)

14)
SELECT FIRSTNAME,LASTNAME
FROM ORDERS,CUSTOMER
WHERE ORDERS.CUSTOMER_FK=CUSTOMER.ID
AND CITY='San Diego'

15)
SELECT LASTNAME FROM CUSTOMER
where ID NOT IN
(SELECT CUSTOMER_FK FROM ORDERS)
AND CITY='San Diego'

Related Questions

Navigate

• Browse (All)
• Browse D
• Subjects

---

---

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