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 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
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
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 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';
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
What is the first name of all employees who do not give any lessons?
assistant
SELECT first_name FROM Staff EXCEPT SELECT T2.first_name FROM Lessons AS T1 JOIN Staff AS T2 ON T1.staff_id = T2.staff_id ;