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
Find the ids of all distinct customers who made order after some orders that were Cancelled.
assistant
SELECT DISTINCT customer_id FROM Customer_Orders WHERE order_date > (SELECT min(order_date) FROM Customer_Orders WHERE order_status_code = 'Cancelled')
human
Return ids of all the products that are supplied by supplier id 2 and are more expensive than the average price of all products.
assistant
SELECT T1.product_id FROM product_suppliers AS T1 JOIN products AS T2 ON T1.product_id = T2.product_id WHERE T1.supplier_id = 2 AND T2.product_price > (SELECT avg(product_price) FROM products)
human
How many department stores does the store chain South have?
assistant
SELECT count(*) FROM department_stores AS T1 JOIN department_store_chain AS T2 ON T1.dept_store_chain_id = T2.dept_store_chain_id WHERE T2.dept_store_chain_name = 'South'
human
Return the ids of all products that were ordered more than three times or supplied more than 80000.
assistant
SELECT product_id FROM Order_Items GROUP BY product_id HAVING count(*) > 3 UNION SELECT product_id FROM Product_Suppliers GROUP BY product_id HAVING sum(total_amount_purchased) > 80000
human
What is the id and name of the department store that has both marketing and managing department?
assistant
SELECT T2.dept_store_id , T2.store_name FROM departments AS T1 JOIN department_stores AS T2 ON T1.dept_store_id = T2.dept_store_id WHERE T1.department_name = 'marketing' INTERSECT SELECT T2.dept_store_id , T2.store_name FROM departments AS T1 JOIN department_stores AS T2 ON T1.dept_store_id = T2.dept_store_id WHERE T1.department_name = 'managing'
human
Return the ids of the two department store chains with the most department stores.
assistant
SELECT dept_store_chain_id FROM department_stores GROUP BY dept_store_chain_id ORDER BY COUNT( * ) DESC LIMIT 2 ;