human
Schema:
Customers (customer_id, customer_name, customer_details)
Invoices (invoice_number, invoice_date, invoice_details)
Orders (order_id, customer_id, order_status, date_order_placed, order_details)
Products (product_id, product_name, product_details)
Order_Items (order_item_id, product_id, order_id, order_item_status, order_item_details)
Shipments (shipment_id, order_id, invoice_number, shipment_tracking_number, shipment_date, other_shipment_details)
Shipment_Items (shipment_id, order_item_id)
Please answer with SQL.
assistant
Sure, I understand.
human
List the names of the customers who have once bought product 'food'.
assistant
SELECT T1.customer_name FROM customers AS T1 JOIN orders AS T2 JOIN order_items AS T3 JOIN products AS T4 ON T1.customer_id = T2.customer_id AND T2.order_id = T3.order_id AND T3.product_id = T4.product_id WHERE T4.product_name = 'food' GROUP BY T1.customer_id HAVING count(*) >= 1
human
For each customer who has at least two orders, find the customer name and number of orders made.
assistant
SELECT T2.customer_name , count(*) FROM orders AS T1 JOIN customers AS T2 ON T1.customer_id = T2.customer_id GROUP BY T2.customer_id HAVING count(*) >= 2
human
Find the name of the customers who have at most two orders.
assistant
SELECT T2.customer_name FROM orders AS T1 JOIN customers AS T2 ON T1.customer_id = T2.customer_id GROUP BY T2.customer_id HAVING count(*) <= 2
human
List the names of all distinct products in alphabetical order.
assistant
SELECT DISTINCT product_name FROM products ORDER BY product_name
human
List the count and id of each product in all the orders.
assistant
SELECT count(*) , T3.product_id FROM orders AS T1 JOIN order_items AS T2 JOIN products AS T3 ON T1.order_id = T2.order_id AND T2.product_id = T3.product_id GROUP BY T3.product_id
human
For each product, show its name and the number of times it was ordered.
assistant
SELECT T3.product_name , COUNT( * ) FROM orders AS T1 JOIN order_items AS T2 JOIN products AS T3 ON T1.order_id = T2.order_id AND T2.product_id = T3.product_id GROUP BY T3.product_id ;