given the schema and table above, Execute SQL statements to answer the following

Question

given the schema and table above, Execute SQL statements to answer the following questions:

a. Who are the customers who have placed 0 orders?

b. Who are the customers who have placed between 1 and 3 orders?

c. Who are the customers who have placed 4 or more orders?

E ...EEE CUSTOMERS CUSTOMER ID CUSTOMER FIRST NAME CUSTOMER LAST NAME CUSTOMER ADDRESS CUSTOMER CITY CUSTOMER STATE CUSTOMER ZIP CUSTOMER PHONE CUSTOMER FAX CUSTOMER STATUS ID EEE GENERAL LEDGER ACCOUNTS INVOICE LINE ITEMS INVOICES ITEMS E E ORDER DETAILS ORDER ID ITEM ID EEE ORDER QTY E ...EEE ORDERS ORDER-ID CUSTOMER ID ORDER DATE SHIPPED DATE

Explanation / Answer

a.
SELECT CUSTOMERS.CUSTOMER_FIRST_NAME, CUSTOMERS.CUSTOMER_LAST_NAME
FROM CUSTOMERS
LEFT JOIN ORDERS ON CUSTOMERS.CUSTOMER_ID = ORDERS.CUSTOMER_ID
WHERE ORDERS.CUSTOMER_ID IS NULL;

b.
SELECT CUSTOMER_FIRST_NAME, COUNT(DISTINCT ORDER_ID) as products_count FROM CUSTOMERS
INNER JOIN ORDERS ON CUSTOMERS.CUSTOMER_ID = ORDERS.CUSTOMER_ID
GROUP BY CUSTOMERS.CUSTOMER_ID,CUSTOMERS.CUSTOMER_FIRST_NAME
HAVING COUNT(DISTINCT ORDER_ID) BETWEEN 1 AND 3

c.
SELECT CUSTOMER_FIRST_NAME, COUNT(DISTINCT ORDER_ID) as products_count FROM CUSTOMERS
INNER JOIN ORDERS ON CUSTOMERS.CUSTOMER_ID = ORDERS.CUSTOMER_ID
GROUP BY CUSTOMERS.CUSTOMER_ID,CUSTOMERS.CUSTOMER_FIRST_NAME
HAVING COUNT(DISTINCT ORDER_ID) >= 4

Related Questions

Navigate

• Browse (All)
• Browse G
• Subjects

---

---

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