human
Schema:
Addresses (address_id, address_details)
Staff (staff_id, staff_gender, staff_name)
Suppliers (supplier_id, supplier_name, supplier_phone)
Department_Store_Chain (dept_store_chain_id, dept_store_chain_name)
Customers (customer_id, payment_method_code, customer_code, customer_name, customer_address, customer_phone, customer_email)
Products (product_id, product_type_code, product_name, product_price)
Supplier_Addresses (supplier_id, address_id, date_from, date_to)
Customer_Addresses (customer_id, address_id, date_from, date_to)
Customer_Orders (order_id, customer_id, order_status_code, order_date)
Department_Stores (dept_store_id, dept_store_chain_id, store_name, store_address, store_phone, store_email)
Departments (department_id, dept_store_id, department_name)
Order_Items (order_item_id, order_id, product_id)
Product_Suppliers (product_id, supplier_id, date_supplied_from, date_supplied_to, total_amount_purchased, total_value_purchased)
Staff_Department_Assignments (staff_id, department_id, date_assigned_from, job_title_code, date_assigned_to)
Please answer with SQL.
assistant
Sure, I understand.
human
What is the largest and smallest customer codes?
assistant
SELECT max(customer_code) , min(customer_code) FROM Customers
human
What are the average amount purchased and value purchased for the supplier who supplies the most products.
assistant
SELECT avg(total_amount_purchased) , avg(total_value_purchased) FROM Product_Suppliers WHERE supplier_id = (SELECT supplier_id FROM Product_Suppliers GROUP BY supplier_id ORDER BY count(*) DESC LIMIT 1)
human
Find the names of products that were bought by at least two distinct customers.
assistant
SELECT DISTINCT T3.product_name FROM customer_orders AS T1 JOIN order_items AS T2 ON T1.order_id = T2.order_id JOIN products AS T3 ON T2.product_id = T3.product_id GROUP BY T3.product_id HAVING COUNT (DISTINCT T1.customer_id) >= 2
human
What are the phone numbers of all customers and suppliers.
assistant
SELECT customer_phone FROM customers UNION SELECT supplier_phone FROM suppliers
human
Find the names of customers who have bought by at least three distinct products.
assistant
SELECT DISTINCT T1.customer_name FROM customers AS T1 JOIN customer_orders AS T2 ON T1.customer_id = T2.customer_id JOIN order_items AS T3 ON T2.order_id = T3.order_id GROUP BY T1.customer_id HAVING COUNT (DISTINCT T3.product_id) >= 3
human
For each payment method, return how many customers use it.
assistant
SELECT payment_method_code , COUNT( * ) FROM customers GROUP BY payment_method_code ;