CREATE TABLE Addresses (
    address_id NUMBER PRIMARY KEY,
    line_1_number_building TEXT,
    city TEXT,
    zip_postcode TEXT,
    state_province_county TEXT,
    country TEXT
);
CREATE TABLE Staff (
    staff_id NUMBER PRIMARY KEY,
    staff_address_id NUMBER,
    nickname TEXT,
    first_name TEXT,
    middle_name TEXT,
    last_name TEXT,
    date_of_birth TIME,
    date_joined_staff TIME,
    date_left_staff TIME,
    FOREIGN KEY ("staff_address_id") REFERENCES Addresses("address_id")
);
CREATE TABLE Vehicles (
    vehicle_id NUMBER PRIMARY KEY,
    vehicle_details TEXT
);
CREATE TABLE Customers (
    customer_id NUMBER PRIMARY KEY,
    customer_address_id NUMBER,
    customer_status_code TEXT,
    date_became_customer TIME,
    date_of_birth TIME,
    first_name TEXT,
    last_name TEXT,
    amount_outstanding NUMBER,
    email_address TEXT,
    phone_number TEXT,
    cell_mobile_phone_number TEXT,
    FOREIGN KEY ("customer_address_id") REFERENCES Addresses("address_id")
);
CREATE TABLE Customer_Payments (
    customer_id NUMBER PRIMARY KEY,
    datetime_payment TIME,
    payment_method_code TEXT,
    amount_payment NUMBER,
    FOREIGN KEY ("customer_id") REFERENCES Customers("customer_id")
);
CREATE TABLE Lessons (
    lesson_id NUMBER PRIMARY KEY,
    customer_id NUMBER,
    lesson_status_code TEXT,
    staff_id NUMBER,
    vehicle_id NUMBER,
    lesson_date TIME,
    lesson_time TEXT,
    price NUMBER,
    FOREIGN KEY ("customer_id") REFERENCES Customers("customer_id"),
    FOREIGN KEY ("staff_id") REFERENCES Staff("staff_id"),
    FOREIGN KEY ("vehicle_id") REFERENCES Vehicles("vehicle_id")
);

Question: Which city does has most number of customers?
SQL: SELECT T2.city FROM Customers AS T1 JOIN Addresses AS T2 ON T1.customer_address_id = T2.address_id GROUP BY T2.city ORDER BY count(*) DESC LIMIT 1;

Question: What is the city with the most customers?
SQL: SELECT T2.city FROM Customers AS T1 JOIN Addresses AS T2 ON T1.customer_address_id = T2.address_id GROUP BY T2.city ORDER BY count(*) DESC LIMIT 1;

Question: How much in total does customer with first name as Carole and last name as Bernhard paid?
SQL: 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"

Question: What is the total amount of moeny paid by the customer Carole Bernhard?
SQL: 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"

Question: List the number of customers that did not have any payment history.
SQL: SELECT count(*) FROM Customers WHERE customer_id NOT IN ( SELECT customer_id FROM Customer_Payments );

Question: How many customers have no payment histories?
SQL: SELECT count(*) FROM Customers WHERE customer_id NOT IN ( SELECT customer_id FROM Customer_Payments );

Question: List first name and last name of customers that have more than 2 payments.
SQL: 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;

Question: What are the first and last names of all customers with more than 2 payments?
SQL: 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;

Question: List all payment methods and number of payments using each payment methods.
SQL: SELECT payment_method_code ,  count(*) FROM Customer_Payments GROUP BY payment_method_code;

Question: For each payment method, how many payments were made?
SQL: SELECT payment_method_code ,  count(*) FROM Customer_Payments GROUP BY payment_method_code;

Question: How many lessons were in cancelled state?
SQL: SELECT count(*) FROM Lessons WHERE lesson_status_code = "Cancelled";

Question: How many lessons have been cancelled?
SQL: SELECT count(*) FROM Lessons WHERE lesson_status_code = "Cancelled";

Question: List lesson id of all lessons taught by staff with first name as Janessa, last name as Sawayn and nickname containing letter 's'.
SQL: 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%";

Question: What are the the lesson ids of all staff taught by Janessa Sawayn whose nickname has the letter s?
SQL: 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%";

Question: How many lessons taught by staff whose first name has letter 'a' in it?
SQL: SELECT count(*) FROM Lessons AS T1 JOIN Staff AS T2 ON T1.staff_id = T2.staff_id WHERE T2.first_name LIKE "%a%"

Question: How many lessons were taught by a staff member whose first name has the letter 'a' in it?
SQL: SELECT count(*) FROM Lessons AS T1 JOIN Staff AS T2 ON T1.staff_id = T2.staff_id WHERE T2.first_name LIKE "%a%"

Question: How long is the total lesson time taught by staff with first name as Janessa and last name as Sawayn?
SQL: 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";

Question: What is the total time for all lessons taught by Janessa Sawayn?
SQL: 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";

Question: What is average lesson price taught by staff with first name as Janessa and last name as Sawayn?
SQL: 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";

Question: What is the average price for a lesson taught by Janessa Sawayn?
SQL: 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";

Question: How many lesson does customer with first name Ray took?
SQL: SELECT count(*) FROM Lessons AS T1 JOIN Customers AS T2 ON T1.customer_id  = T2.customer_id WHERE T2.first_name = "Ray"

Question: How many lessons did the customer with the first name Ray take?
SQL: SELECT count(*) FROM Lessons AS T1 JOIN Customers AS T2 ON T1.customer_id  = T2.customer_id WHERE T2.first_name = "Ray"

Question: Which last names are both used by customers and by staff?
SQL: SELECT last_name FROM Customers INTERSECT SELECT last_name FROM Staff

Question: What are the last names that are used by customers and staff?
SQL: SELECT last_name FROM Customers INTERSECT SELECT last_name FROM Staff

Question: What is the first name of the staff who did not give any lesson?
SQL: 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

Question: What is the first name of all employees who do not give any lessons?
SQL: 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

Question: What is the id and detail of the vehicle used in lessons for most of the times?
SQL: SELECT T1.vehicle_id ,  T1.vehicle_details FROM Vehicles AS T1 JOIN Lessons AS T2 ON T1.vehicle_id  =  T2.vehicle_id GROUP BY T1.vehicle_id ORDER BY count(*) DESC LIMIT 1