human
Schema:
Addresses (address_id, line_1_number_building, city, zip_postcode, state_province_county, country)
Staff (staff_id, staff_address_id, nickname, first_name, middle_name, last_name, date_of_birth, date_joined_staff, date_left_staff)
Vehicles (vehicle_id, vehicle_details)
Customers (customer_id, customer_address_id, customer_status_code, date_became_customer, date_of_birth, first_name, last_name, amount_outstanding, email_address, phone_number, cell_mobile_phone_number)
Customer_Payments (customer_id, datetime_payment, payment_method_code, amount_payment)
Lessons (lesson_id, customer_id, lesson_status_code, staff_id, vehicle_id, lesson_date, lesson_time, price)
Please answer with SQL.
assistant
Sure, I understand.
human
What is maximum, minimum and average amount of outstanding of customer?
assistant
SELECT max(amount_outstanding) , min(amount_outstanding) , avg(amount_outstanding) FROM Customers;
human
List the first name and last name of customers have the amount of outstanding between 1000 and 3000.
assistant
SELECT first_name , last_name FROM Customers WHERE amount_outstanding BETWEEN 1000 AND 3000;
human
How many customers in total?
assistant
SELECT count(*) FROM Customers;
human
List the number of customers that did not have any payment history.
assistant
SELECT count(*) FROM Customers WHERE customer_id NOT IN ( SELECT customer_id FROM Customer_Payments );
human
List first name and last name of customers that have more than 2 payments.
assistant
SELECT T2.first_name , T2.last_name FROM Customer_Payments AS T1 JOIN Customers AS T2 ON T1.customer_id = T2.customer_id GROUP BY T1.customer_id HAVING count(*) > 2;
human
For each payment method, how many payments were made?
assistant
SELECT payment_method_code , COUNT( * ) FROM Customer_Payments GROUP BY payment_method_code ;