human
Schema:
Customers (customer_id, customer_first_name, customer_middle_initial, customer_last_name, gender, email_address, login_name, login_password, phone_number, town_city, state_county_province, country)
Orders (order_id, customer_id, date_order_placed, order_details)
Invoices (invoice_number, order_id, invoice_date)
Accounts (account_id, customer_id, date_account_opened, account_name, other_account_details)
Product_Categories (production_type_code, product_type_description, vat_rating)
Products (product_id, parent_product_id, production_type_code, unit_price, product_name, product_color, product_size)
Financial_Transactions (transaction_id, account_id, invoice_number, transaction_type, transaction_date, transaction_amount, transaction_comment, other_transaction_details)
Order_Items (order_item_id, order_id, product_id, product_quantity, other_order_item_details)
Invoice_Line_Items (order_item_id, invoice_number, product_id, product_title, product_quantity, product_price, derived_product_cost, derived_vat_payable, derived_total_cost)
Please answer with SQL.
assistant
Sure, I understand.
human
What is the order id and order details for the order more than two invoices.
assistant
SELECT T2.order_id , T2.order_details FROM Invoices AS T1 JOIN Orders AS T2 ON T1.order_id = T2.order_id GROUP BY T2.order_id HAVING count(*) > 2
human
Show order ids and the total quantity in each order.
assistant
SELECT order_id , sum(product_quantity) FROM Order_items GROUP BY order_id
human
How many products were not included in any order?
assistant
SELECT count(*) FROM products WHERE product_id NOT IN ( SELECT product_id FROM Order_items )
human
Show the order ids and the number of items in each order.
assistant
SELECT order_id , count(*) FROM Order_items GROUP BY order_id
human
Show the product ids and the number of unique orders containing each product.
assistant
SELECT product_id , count(DISTINCT order_id) FROM Order_items GROUP BY product_id
human
How many different products correspond to each order id?
assistant
SELECT order_id , COUNT( DISTINCT product_id ) FROM Order_items GROUP BY order_id ;