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
How much in total does customer with first name as Carole and last name as Bernhard paid?
assistant
SELECT sum(T1.amount_payment) FROM Customer_Payments AS T1 JOIN Customers AS T2 ON T1.customer_id = T2.customer_id WHERE T2.first_name = 'Carole' AND T2.last_name = 'Bernhard'
human
What is the birthday of the staff member with first name as Janessa and last name as Sawayn?
assistant
SELECT date_of_birth FROM Staff WHERE first_name = 'Janessa' AND last_name = 'Sawayn';
human
How many lesson does customer with first name Ray took?
assistant
SELECT count(*) FROM Lessons AS T1 JOIN Customers AS T2 ON T1.customer_id = T2.customer_id WHERE T2.first_name = 'Ray'
human
List lesson id of all lessons taught by staff with first name as Janessa, last name as Sawayn and nickname containing letter 's'.
assistant
SELECT T1.lesson_id FROM Lessons AS T1 JOIN Staff AS T2 ON T1.staff_id = T2.staff_id WHERE T2.first_name = 'Janessa' AND T2.last_name = 'Sawayn' AND nickname LIKE '%s%';
human
How long is the total lesson time taught by staff with first name as Janessa and last name as Sawayn?
assistant
SELECT sum(lesson_time) FROM Lessons AS T1 JOIN Staff AS T2 ON T1.staff_id = T2.staff_id WHERE T2.first_name = 'Janessa' AND T2.last_name = 'Sawayn';
human
What is the average price for a lesson taught by Janessa Sawayn?
assistant
SELECT avg ( price ) FROM Lessons AS T1 JOIN Staff AS T2 ON T1.staff_id = T2.staff_id WHERE T2.first_name = "Janessa" AND T2.last_name = "Sawayn" ;