human
Schema:
Premises (premise_id, premises_type, premise_details)
Products (product_id, product_category, product_name)
Customers (customer_id, payment_method, customer_name, customer_phone, customer_email, customer_address, customer_login, customer_password)
Mailshot_Campaigns (mailshot_id, product_category, mailshot_name, mailshot_start_date, mailshot_end_date)
Customer_Addresses (customer_id, premise_id, date_address_from, address_type_code, date_address_to)
Customer_Orders (order_id, customer_id, order_status_code, shipping_method_code, order_placed_datetime, order_delivered_datetime, order_shipping_charges)
Mailshot_Customers (mailshot_id, customer_id, outcome_code, mailshot_customer_date)
Order_Items (item_id, order_item_status_code, order_id, product_id, item_status_code, item_delivered_datetime, item_order_quantity)
Please answer with SQL.
assistant
Sure, I understand.
human
Show the names of customers having an order with shipping method FedEx and order status Paid.
assistant
SELECT T1.customer_name FROM customers AS T1 JOIN customer_orders AS T2 ON T1.customer_id = T2.customer_id WHERE shipping_method_code = 'FedEx' AND order_status_code = 'Paid'
human
Show the premise type and address type code for all customer addresses.
assistant
SELECT T2.premises_type , T1.address_type_code FROM customer_addresses AS T1 JOIN premises AS T2 ON T1.premise_id = T2.premise_id
human
How many premises are there?
assistant
SELECT count(*) FROM premises
human
What are all the distinct premise types?
assistant
SELECT DISTINCT premises_type FROM premises
human
Find the types and details for all premises and order by the premise type.
assistant
SELECT premises_type , premise_details FROM premises ORDER BY premises_type
human
Show each premise type and the number of premises in that type.
assistant
SELECT premises_type , COUNT( * ) FROM premises GROUP BY premises_type ;