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
Which country and state does staff with first name as Janessa and last name as Sawayn lived?
assistant
SELECT T1.country , T1.state_province_county FROM Addresses AS T1 JOIN Staff AS T2 ON T1.address_id = T2.staff_address_id WHERE T2.first_name = 'Janessa' AND T2.last_name = 'Sawayn';
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
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
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
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
What is the total time for all lessons taught by Janessa 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" ;