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 average lesson price taught by staff with first name as Janessa and last name as 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';
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
How many lessons taught by staff whose first name has letter 'a' in it?
assistant
SELECT count(*) FROM Lessons AS T1 JOIN Staff AS T2 ON T1.staff_id = T2.staff_id WHERE T2.first_name LIKE '%a%'
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
How long is the total lesson time took by customer with first name as Rylan and last name as Goodwin?
assistant
SELECT sum(T1.lesson_time) FROM Lessons AS T1 JOIN Customers AS T2 ON T1.customer_id = T2.customer_id WHERE T2.first_name = 'Rylan' AND T2.last_name = 'Goodwin';
human
How many lessons taken by customer with first name as Rylan and last name as Goodwin were completed?
assistant
SELECT COUNT( * ) FROM Lessons AS T1 JOIN Customers AS T2 ON T1.customer_id = T2.customer_id WHERE T2.first_name = "Rylan" AND T2.last_name = "Goodwin" AND T1.lesson_status_code = "Completed" ;