Database: driving_school

CREATE TABLE `Addresses` (
`address_id` INTEGER PRIMARY KEY,
`line_1_number_building` VARCHAR(80),
`city` VARCHAR(50),
`zip_postcode` VARCHAR(20),
`state_province_county` VARCHAR(50),
`country` VARCHAR(50)
);
CREATE TABLE `Staff` (
`staff_id` INTEGER PRIMARY KEY,
`staff_address_id` INTEGER NOT NULL,
`nickname` VARCHAR(80),
`first_name` VARCHAR(80),
`middle_name` VARCHAR(80),
`last_name` VARCHAR(80),
`date_of_birth` DATETIME,
`date_joined_staff` DATETIME,
`date_left_staff` DATETIME,
FOREIGN KEY (`staff_address_id` ) REFERENCES `Addresses`(`address_id` )
);
CREATE TABLE `Vehicles` (
`vehicle_id` INTEGER PRIMARY KEY,
`vehicle_details` VARCHAR(255)
);
CREATE TABLE `Customers` (
`customer_id` INTEGER PRIMARY KEY,
`customer_address_id` INTEGER NOT NULL,
`customer_status_code` VARCHAR(15) NOT NULL,
`date_became_customer` DATETIME,
`date_of_birth` DATETIME,
`first_name` VARCHAR(80),
`last_name` VARCHAR(80),
`amount_outstanding` DOUBLE NULL,
`email_address` VARCHAR(250),
`phone_number` VARCHAR(255),
`cell_mobile_phone_number` VARCHAR(255),
FOREIGN KEY (`customer_address_id` ) REFERENCES `Addresses`(`address_id` )
);
CREATE TABLE `Customer_Payments` (
`customer_id` INTEGER NOT NULL,
`datetime_payment` DATETIME NOT NULL,
`payment_method_code` VARCHAR(10) NOT NULL,
`amount_payment` DOUBLE NULL,
PRIMARY KEY (`customer_id`,`datetime_payment`),
FOREIGN KEY (`customer_id` ) REFERENCES `Customers`(`customer_id` )
);
CREATE TABLE `Lessons` (
`lesson_id` INTEGER PRIMARY KEY,
`customer_id` INTEGER NOT NULL,
`lesson_status_code` VARCHAR(15) NOT NULL,
`staff_id` INTEGER,
`vehicle_id` INTEGER NOT NULL,
`lesson_date` DATETIME,
`lesson_time` VARCHAR(10),
`price` DOUBLE NULL,
FOREIGN KEY (`vehicle_id` ) REFERENCES `Vehicles`(`vehicle_id` ),
FOREIGN KEY (`staff_id` ) REFERENCES `Staff`(`staff_id` ),
FOREIGN KEY (`customer_id` ) REFERENCES `Customers`(`customer_id` )
);

Question: List all vehicle id
SQL: SELECT vehicle_id FROM Vehicles;

Question: What are the ids of all vehicles?
SQL: SELECT vehicle_id FROM Vehicles;

Question: How many vehicle in total?
SQL: SELECT count(*) FROM Vehicles;

Question: How many vehicles exist?
SQL: SELECT count(*) FROM Vehicles;

Question: Show the detail of vehicle with id 1.
SQL: SELECT vehicle_details FROM Vehicles WHERE vehicle_id = 1;

Question: What are the details of the car with id 1?
SQL: SELECT vehicle_details FROM Vehicles WHERE vehicle_id = 1;

Question: List the first name middle name and last name of all staff.
SQL: SELECT first_name ,  middle_name ,  last_name FROM Staff;

Question: What are the first, middle, and last names of all staff?
SQL: SELECT first_name ,  middle_name ,  last_name FROM Staff;

Question: What is the birthday of the staff member with first name as Janessa and last name as Sawayn?
SQL: SELECT date_of_birth FROM Staff WHERE first_name = "Janessa" AND last_name = "Sawayn";

Question: What is the date of birth for the staff member named Janessa Sawayn?
SQL: SELECT date_of_birth FROM Staff WHERE first_name = "Janessa" AND last_name = "Sawayn";

Question: When did the staff member with first name as Janessa and last name as Sawayn join the company?
SQL: SELECT date_joined_staff FROM Staff WHERE first_name = "Janessa" AND last_name = "Sawayn";

Question: When did the staff member named Janessa Sawayn join the company?
SQL: SELECT date_joined_staff FROM Staff WHERE first_name = "Janessa" AND last_name = "Sawayn";

Question: When did the staff member with first name as Janessa and last name as Sawayn leave the company?
SQL: SELECT date_left_staff FROM Staff WHERE first_name = "Janessa" AND last_name = "Sawayn";

Question: When did the staff member Janessa Sawayn leave the company?
SQL: SELECT date_left_staff FROM Staff WHERE first_name = "Janessa" AND last_name = "Sawayn";

Question: How many staff have the first name Ludie?
SQL: SELECT count(*) FROM Staff WHERE first_name = "Ludie";

Question: How many employees have a first name of Ludie?
SQL: SELECT count(*) FROM Staff WHERE first_name = "Ludie";

Question: What is the nickname of staff with first name as Janessa and last name as Sawayn?
SQL: SELECT nickname FROM Staff WHERE first_name = "Janessa" AND last_name = "Sawayn";

Question: What is the nickname of the employee named Janessa Sawayn?
SQL: SELECT nickname FROM Staff WHERE first_name = "Janessa" AND last_name = "Sawayn";

Question: How many staff in total?
SQL: SELECT count(*) FROM Staff;

Question: How many employees are there?
SQL: SELECT count(*) FROM Staff;

Question: Which city does staff with first name as Janessa and last name as Sawayn live?
SQL: SELECT T1.city 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";

Question: In what city does Janessa Sawayn live?
SQL: SELECT T1.city 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";

Question: Which country and state does staff with first name as Janessa and last name as Sawayn lived?
SQL: 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";

Question: In which country and state does Janessa Sawayn live?
SQL: 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";

Question: How long is the total lesson time took by customer with first name as Rylan and last name as Goodwin?
SQL: 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";

Question: How long is the total lesson time took by the customer named Rylan Goodwin?
SQL: 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";

Question: What is the zip code of staff with first name as Janessa and last name as Sawayn lived?
SQL: SELECT T1.zip_postcode 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";

Question: What is the zip code of the hosue of the employee named Janessa Sawayn?
SQL: SELECT T1.zip_postcode 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";

Question: How many staff live in state Georgia?
SQL: SELECT count(*) FROM Addresses WHERE state_province_county = "Georgia";

Question: How many employees live in Georgia?
SQL: SELECT count(*) FROM Addresses WHERE state_province_county = "Georgia";

Question: Find out the first name and last name of staff lived in city Damianfort.
SQL: SELECT T2.first_name ,  T2.last_name FROM Addresses AS T1 JOIN Staff AS T2 ON T1.address_id = T2.staff_address_id WHERE T1.city = "Damianfort";

Question: What is the first and last name of all employees who live in the city Damianfort?
SQL: SELECT T2.first_name ,  T2.last_name FROM Addresses AS T1 JOIN Staff AS T2 ON T1.address_id = T2.staff_address_id WHERE T1.city = "Damianfort";

Question: Which city lives most of staffs? List the city name and number of staffs.
SQL: SELECT T1.city ,  count(*) FROM Addresses AS T1 JOIN Staff AS T2 ON T1.address_id = T2.staff_address_id GROUP BY T1.city ORDER BY count(*) DESC LIMIT 1;

Question: In which city do the most employees live and how many of them live there?
SQL: SELECT T1.city ,  count(*) FROM Addresses AS T1 JOIN Staff AS T2 ON T1.address_id = T2.staff_address_id GROUP BY T1.city ORDER BY count(*) DESC LIMIT 1;

Question: List the states which have between 2 to 4 staffs living there.
SQL: SELECT T1.state_province_county FROM Addresses AS T1 JOIN Staff AS T2 ON T1.address_id = T2.staff_address_id GROUP BY T1.state_province_county HAVING count(*) BETWEEN 2 AND 4;

Question: What are the names of the states that have 2 to 4 employees living there?
SQL: SELECT T1.state_province_county FROM Addresses AS T1 JOIN Staff AS T2 ON T1.address_id = T2.staff_address_id GROUP BY T1.state_province_county HAVING count(*) BETWEEN 2 AND 4;

Question: List the first name and last name of all customers.
SQL: SELECT first_name ,  last_name FROM Customers;

Question: What are the first and last names for all customers?
SQL: SELECT first_name ,  last_name FROM Customers;

Question: List email address and birthday of customer whose first name as Carole.
SQL: SELECT email_address ,  date_of_birth FROM Customers WHERE first_name = "Carole"

Question: What are the email addresses and date of births for all customers who have a first name of Carole?
SQL: SELECT email_address ,  date_of_birth FROM Customers WHERE first_name = "Carole"

Question: List phone number and email address of customer with more than 2000 outstanding balance.
SQL: SELECT phone_number ,  email_address FROM Customers WHERE amount_outstanding  >  2000;

Question: What are the phone numbers and email addresses of all customers who have an outstanding balance of more than 2000?
SQL: SELECT phone_number ,  email_address FROM Customers WHERE amount_outstanding  >  2000;

Question: What is the status code, mobile phone number and email address of customer with last name as Kohler or first name as Marina?
SQL: SELECT customer_status_code ,  cell_mobile_phone_number ,  email_address FROM Customers WHERE first_name = "Marina" OR last_name  =  "Kohler"

Question: What is the status code, phone number, and email address of the customer whose last name is Kohler or whose first name is Marina?
SQL: SELECT customer_status_code ,  cell_mobile_phone_number ,  email_address FROM Customers WHERE first_name = "Marina" OR last_name  =  "Kohler"

Question: When are the birthdays of customer who are classified as 'Good Customer' status?
SQL: SELECT date_of_birth FROM Customers WHERE customer_status_code  =  'Good Customer'

Question: What is the date of birth of every customer whose status code is 'Good Customer'?
SQL: SELECT date_of_birth FROM Customers WHERE customer_status_code  =  'Good Customer'

Question: When did customer with first name as Carole and last name as Bernhard became a customer?
SQL: SELECT date_became_customer FROM Customers WHERE first_name = "Carole" AND last_name = "Bernhard";

Question: When did Carole Bernhard first become a customer?
SQL: SELECT date_became_customer FROM Customers WHERE first_name = "Carole" AND last_name = "Bernhard";

Question: How many customers in total?
SQL: SELECT count(*) FROM Customers;

Question: How many customers are there?
SQL: SELECT count(*) FROM Customers;

Question: List all customer status codes and the number of customers having each status code.
SQL: SELECT customer_status_code , count(*) FROM Customers GROUP BY customer_status_code;

Question: For each customer status code, how many customers are classified that way?
SQL: SELECT customer_status_code , count(*) FROM Customers GROUP BY customer_status_code;

Question: Which customer status code has least number of customers?
SQL: SELECT customer_status_code FROM Customers GROUP BY customer_status_code ORDER BY count(*) ASC LIMIT 1;

Question: What is the status code with the least number of customers?
SQL: SELECT customer_status_code FROM Customers GROUP BY customer_status_code ORDER BY count(*) ASC LIMIT 1;

Question: How many lessons taken by customer with first name as Rylan and last name as Goodwin were completed?
SQL: 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";

Question: How many lessons did the customer Ryan Goodwin complete?
SQL: 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";

Question: What is maximum, minimum and average amount of outstanding of customer?
SQL: SELECT max(amount_outstanding) ,  min(amount_outstanding) ,  avg(amount_outstanding) FROM Customers;

Question: What is the maximum, minimum, and average amount of money outsanding for all customers?
SQL: SELECT max(amount_outstanding) ,  min(amount_outstanding) ,  avg(amount_outstanding) FROM Customers;

Question: List the first name and last name of customers have the amount of outstanding between 1000 and 3000.
SQL: SELECT first_name ,  last_name FROM Customers WHERE amount_outstanding BETWEEN 1000 AND 3000;

Question: What are the first and last names of all customers with between 1000 and 3000 dollars outstanding?
SQL: SELECT first_name ,  last_name FROM Customers WHERE amount_outstanding BETWEEN 1000 AND 3000;

Question: List first name and last name of customers lived in city Lockmanfurt.
SQL: SELECT T1.first_name ,  T1.last_name FROM Customers AS T1 JOIN Addresses AS T2 ON T1.customer_address_id = T2.address_id WHERE T2.city = "Lockmanfurt";

Question: What are the first and last names of all customers who lived in Lockmanfurt?
SQL: SELECT T1.first_name ,  T1.last_name FROM Customers AS T1 JOIN Addresses AS T2 ON T1.customer_address_id = T2.address_id WHERE T2.city = "Lockmanfurt";

Question: Which country does customer with first name as Carole and last name as Bernhard lived in?
SQL: SELECT T2.country FROM Customers AS T1 JOIN Addresses AS T2 ON T1.customer_address_id = T2.address_id WHERE T1.first_name = "Carole" AND T1.last_name = "Bernhard"

Question: What is the country in which the customer Carole Bernhard lived?
SQL: SELECT T2.country FROM Customers AS T1 JOIN Addresses AS T2 ON T1.customer_address_id = T2.address_id WHERE T1.first_name = "Carole" AND T1.last_name = "Bernhard"

Question: What is zip code of customer with first name as Carole and last name as Bernhard?
SQL: SELECT T2.zip_postcode FROM Customers AS T1 JOIN Addresses AS T2 ON T1.customer_address_id = T2.address_id WHERE T1.first_name = "Carole" AND T1.last_name = "Bernhard"

Question: What is the zip code of the customer Carole Bernhard?
SQL: SELECT T2.zip_postcode FROM Customers AS T1 JOIN Addresses AS T2 ON T1.customer_address_id = T2.address_id WHERE T1.first_name = "Carole" AND T1.last_name = "Bernhard"

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