lianghsun/spider-text2sql-bench huggingface dataset:
### Database Schema
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
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;
### Database Schema
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 payment methods and number of payments using each payment methods.

### SQL
SELECT payment_method_code ,  count(*) FROM Customer_Payments GROUP BY payment_method_code;
### Database Schema
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
For each payment method, how many payments were made?

### SQL
SELECT payment_method_code ,  count(*) FROM Customer_Payments GROUP BY payment_method_code;
### Database Schema
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
How many lessons were in cancelled state?

### SQL
SELECT count(*) FROM Lessons WHERE lesson_status_code = "Cancelled";
### Database Schema
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
How many lessons have been cancelled?

### SQL
SELECT count(*) FROM Lessons WHERE lesson_status_code = "Cancelled";
### Database Schema
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 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%";
### Database Schema
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
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%";
### Database Schema
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
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%"
### Database Schema
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
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%"
### Database Schema
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
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";
### Database Schema
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
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";
### Database Schema
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
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";
### Database Schema
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
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";
### Database Schema
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
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"
### Database Schema
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
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"
### Database Schema
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
Which last names are both used by customers and by staff?

### SQL
SELECT last_name FROM Customers INTERSECT SELECT last_name FROM Staff
### Database Schema
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
What are the last names that are used by customers and staff?

### SQL
SELECT last_name FROM Customers INTERSECT SELECT last_name FROM Staff
### Database Schema
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
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
### Database Schema
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
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
### Database Schema
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
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
### Database Schema
CREATE TABLE Activity (
  actid INTEGER PRIMARY KEY,
  activity_name varchar(25)
)

CREATE TABLE Participates_in (
  stuid INTEGER,
  actid INTEGER,
  FOREIGN KEY(stuid) REFERENCES Student(StuID),
  FOREIGN KEY(actid) REFERENCES Activity(actid)
)

CREATE TABLE Faculty_Participates_in (
  FacID INTEGER,
  actid INTEGER,
  FOREIGN KEY(FacID) REFERENCES Faculty(FacID),
  FOREIGN KEY(actid) REFERENCES Activity(actid)
)

CREATE TABLE Student (
        StuID        INTEGER PRIMARY KEY,
        LName        VARCHAR(12),
        Fname        VARCHAR(12),
        Age      INTEGER,
        Sex      VARCHAR(1),
        Major        INTEGER,
        Advisor      INTEGER,
        city_code    VARCHAR(3)
 )

CREATE TABLE Faculty (
       FacID 	       INTEGER PRIMARY KEY,
       Lname		VARCHAR(15),
       Fname		VARCHAR(15),
       Rank		VARCHAR(15),
       Sex		VARCHAR(1),
       Phone		INTEGER,
       Room		VARCHAR(5),
       Building		VARCHAR(13)
)

### Question
How many faculty do we have?

### SQL
SELECT count(*) FROM Faculty
### Database Schema
CREATE TABLE Activity (
  actid INTEGER PRIMARY KEY,
  activity_name varchar(25)
)

CREATE TABLE Participates_in (
  stuid INTEGER,
  actid INTEGER,
  FOREIGN KEY(stuid) REFERENCES Student(StuID),
  FOREIGN KEY(actid) REFERENCES Activity(actid)
)

CREATE TABLE Faculty_Participates_in (
  FacID INTEGER,
  actid INTEGER,
  FOREIGN KEY(FacID) REFERENCES Faculty(FacID),
  FOREIGN KEY(actid) REFERENCES Activity(actid)
)

CREATE TABLE Student (
        StuID        INTEGER PRIMARY KEY,
        LName        VARCHAR(12),
        Fname        VARCHAR(12),
        Age      INTEGER,
        Sex      VARCHAR(1),
        Major        INTEGER,
        Advisor      INTEGER,
        city_code    VARCHAR(3)
 )

CREATE TABLE Faculty (
       FacID 	       INTEGER PRIMARY KEY,
       Lname		VARCHAR(15),
       Fname		VARCHAR(15),
       Rank		VARCHAR(15),
       Sex		VARCHAR(1),
       Phone		INTEGER,
       Room		VARCHAR(5),
       Building		VARCHAR(13)
)

### Question
What is the total number of faculty members?

### SQL
SELECT count(*) FROM Faculty
### Database Schema
CREATE TABLE Activity (
  actid INTEGER PRIMARY KEY,
  activity_name varchar(25)
)

CREATE TABLE Participates_in (
  stuid INTEGER,
  actid INTEGER,
  FOREIGN KEY(stuid) REFERENCES Student(StuID),
  FOREIGN KEY(actid) REFERENCES Activity(actid)
)

CREATE TABLE Faculty_Participates_in (
  FacID INTEGER,
  actid INTEGER,
  FOREIGN KEY(FacID) REFERENCES Faculty(FacID),
  FOREIGN KEY(actid) REFERENCES Activity(actid)
)

CREATE TABLE Student (
        StuID        INTEGER PRIMARY KEY,
        LName        VARCHAR(12),
        Fname        VARCHAR(12),
        Age      INTEGER,
        Sex      VARCHAR(1),
        Major        INTEGER,
        Advisor      INTEGER,
        city_code    VARCHAR(3)
 )

CREATE TABLE Faculty (
       FacID 	       INTEGER PRIMARY KEY,
       Lname		VARCHAR(15),
       Fname		VARCHAR(15),
       Rank		VARCHAR(15),
       Sex		VARCHAR(1),
       Phone		INTEGER,
       Room		VARCHAR(5),
       Building		VARCHAR(13)
)

### Question
What ranks do we have for faculty?

### SQL
SELECT DISTINCT rank FROM Faculty
### Database Schema
CREATE TABLE Activity (
  actid INTEGER PRIMARY KEY,
  activity_name varchar(25)
)

CREATE TABLE Participates_in (
  stuid INTEGER,
  actid INTEGER,
  FOREIGN KEY(stuid) REFERENCES Student(StuID),
  FOREIGN KEY(actid) REFERENCES Activity(actid)
)

CREATE TABLE Faculty_Participates_in (
  FacID INTEGER,
  actid INTEGER,
  FOREIGN KEY(FacID) REFERENCES Faculty(FacID),
  FOREIGN KEY(actid) REFERENCES Activity(actid)
)

CREATE TABLE Student (
        StuID        INTEGER PRIMARY KEY,
        LName        VARCHAR(12),
        Fname        VARCHAR(12),
        Age      INTEGER,
        Sex      VARCHAR(1),
        Major        INTEGER,
        Advisor      INTEGER,
        city_code    VARCHAR(3)
 )

CREATE TABLE Faculty (
       FacID 	       INTEGER PRIMARY KEY,
       Lname		VARCHAR(15),
       Fname		VARCHAR(15),
       Rank		VARCHAR(15),
       Sex		VARCHAR(1),
       Phone		INTEGER,
       Room		VARCHAR(5),
       Building		VARCHAR(13)
)

### Question
Find the list of distinct ranks for faculty.

### SQL
SELECT DISTINCT rank FROM Faculty
### Database Schema
CREATE TABLE Activity (
  actid INTEGER PRIMARY KEY,
  activity_name varchar(25)
)

CREATE TABLE Participates_in (
  stuid INTEGER,
  actid INTEGER,
  FOREIGN KEY(stuid) REFERENCES Student(StuID),
  FOREIGN KEY(actid) REFERENCES Activity(actid)
)

CREATE TABLE Faculty_Participates_in (
  FacID INTEGER,
  actid INTEGER,
  FOREIGN KEY(FacID) REFERENCES Faculty(FacID),
  FOREIGN KEY(actid) REFERENCES Activity(actid)
)

CREATE TABLE Student (
        StuID        INTEGER PRIMARY KEY,
        LName        VARCHAR(12),
        Fname        VARCHAR(12),
        Age      INTEGER,
        Sex      VARCHAR(1),
        Major        INTEGER,
        Advisor      INTEGER,
        city_code    VARCHAR(3)
 )

CREATE TABLE Faculty (
       FacID 	       INTEGER PRIMARY KEY,
       Lname		VARCHAR(15),
       Fname		VARCHAR(15),
       Rank		VARCHAR(15),
       Sex		VARCHAR(1),
       Phone		INTEGER,
       Room		VARCHAR(5),
       Building		VARCHAR(13)
)

### Question
Show all the distinct buildings that have faculty rooms.

### SQL
SELECT DISTINCT building FROM Faculty
### Database Schema
CREATE TABLE Activity (
  actid INTEGER PRIMARY KEY,
  activity_name varchar(25)
)

CREATE TABLE Participates_in (
  stuid INTEGER,
  actid INTEGER,
  FOREIGN KEY(stuid) REFERENCES Student(StuID),
  FOREIGN KEY(actid) REFERENCES Activity(actid)
)

CREATE TABLE Faculty_Participates_in (
  FacID INTEGER,
  actid INTEGER,
  FOREIGN KEY(FacID) REFERENCES Faculty(FacID),
  FOREIGN KEY(actid) REFERENCES Activity(actid)
)

CREATE TABLE Student (
        StuID        INTEGER PRIMARY KEY,
        LName        VARCHAR(12),
        Fname        VARCHAR(12),
        Age      INTEGER,
        Sex      VARCHAR(1),
        Major        INTEGER,
        Advisor      INTEGER,
        city_code    VARCHAR(3)
 )

CREATE TABLE Faculty (
       FacID 	       INTEGER PRIMARY KEY,
       Lname		VARCHAR(15),
       Fname		VARCHAR(15),
       Rank		VARCHAR(15),
       Sex		VARCHAR(1),
       Phone		INTEGER,
       Room		VARCHAR(5),
       Building		VARCHAR(13)
)

### Question
What buildings have faculty offices?

### SQL
SELECT DISTINCT building FROM Faculty