human
Schema:
Addresses (address_id, line_1_number_building, city, zip_postcode, state_province_county, country)
Products (product_id, product_type_code, product_name, product_price)
Customers (customer_id, payment_method_code, customer_number, customer_name, customer_address, customer_phone, customer_email)
Contacts (contact_id, customer_id, gender, first_name, last_name, contact_phone)
Customer_Address_History (customer_id, address_id, date_from, date_to)
Customer_Orders (order_id, customer_id, order_date, order_status_code)
Order_Items (order_item_id, order_id, product_id, order_quantity)
Please answer with SQL.
assistant
Sure, I understand.
human
Show the product type codes which have at least two products.
assistant
SELECT product_type_code FROM products GROUP BY product_type_code HAVING count(*) >= 2
human
Show the names of customers who have both an order in completed status and an order in part status.
assistant
SELECT T1.customer_name FROM customers AS T1 JOIN customer_orders AS T2 ON T1.customer_id = T2.customer_id WHERE T2.order_status_code = 'Completed' INTERSECT SELECT T1.customer_name FROM customers AS T1 JOIN customer_orders AS T2 ON T1.customer_id = T2.customer_id WHERE T2.order_status_code = 'Part'
human
Show the product type and name for the products with price higher than 1000 or lower than 500.
assistant
SELECT product_type_code , product_name FROM products WHERE product_price > 1000 OR product_price < 500
human
Show the name of the customer who has the most orders.
assistant
SELECT T1.customer_name FROM customers AS T1 JOIN customer_orders AS T2 ON T1.customer_id = T2.customer_id GROUP BY T1.customer_id ORDER BY count(*) DESC LIMIT 1
human
What are the name and phone of the customer with the most ordered product quantity?
assistant
SELECT T1.customer_name , T1.customer_phone FROM customers AS T1 JOIN customer_orders AS T2 ON T1.customer_id = T2.customer_id JOIN order_items AS T3 ON T3.order_id = T2.order_id GROUP BY T1.customer_id ORDER BY sum(T3.order_quantity) DESC LIMIT 1
human
Show the product name and total order quantity for each product.
assistant
SELECT T1.product_name , SUM( T2.order_quantity ) FROM products AS T1 JOIN order_items AS T2 ON T1.product_id = T2.product_id GROUP BY T1.product_id ;