human
Schema:
Staff (staff_id, gender, first_name, last_name, email_address, phone_number)
Customers (customer_id, customer_type_code, address_line_1, address_line_2, town_city, state, email_address, phone_number)
Products (product_id, parent_product_id, product_category_code, date_product_first_available, date_product_discontinued, product_name, product_description, product_price)
Complaints (complaint_id, product_id, customer_id, complaint_outcome_code, complaint_status_code, complaint_type_code, date_complaint_raised, date_complaint_closed, staff_id)
Please answer with SQL.
assistant
Sure, I understand.
human
How many customers are there in the customer type with the most customers?
assistant
SELECT count(*) FROM customers GROUP BY customer_type_code ORDER BY count(*) DESC LIMIT 1
human
List the name of all products along with the number of complaints that they have received.
assistant
SELECT t1.product_name , count(*) FROM products AS t1 JOIN complaints AS t2 ON t1.product_id = t2.product_id GROUP BY t1.product_name
human
How many customers are there?
assistant
SELECT count(*) FROM customers
human
Find the prices of products which has never received a single complaint.
assistant
SELECT product_price FROM products WHERE product_id NOT IN (SELECT product_id FROM complaints)
human
Find the name and category of the most expensive product.
assistant
SELECT product_name , product_category_code FROM products ORDER BY product_price DESC LIMIT 1
human
What is the average price of the products for each category?
assistant
SELECT avg ( product_price ) , product_category_code FROM products GROUP BY product_category_code ;