lianghsun/spider-text2sql-bench huggingface dataset:
### Database Schema
CREATE TABLE `Addresses` (
`address_id` INTEGER PRIMARY KEY,
`line_1_number_building` VARCHAR(80),
`town_city` VARCHAR(50),
`zip_postcode` VARCHAR(20),
`state_province_county` VARCHAR(50),
`country` VARCHAR(50)
)

CREATE TABLE `Services` (
`service_id` INTEGER PRIMARY KEY,
`service_type_code` VARCHAR(15) NOT NULL,
`service_name` VARCHAR(80),
`service_descriptio` VARCHAR(255)
)

CREATE TABLE `Forms` (
`form_id` INTEGER PRIMARY KEY,
`form_type_code` VARCHAR(15) NOT NULL,
`service_id` INTEGER,
`form_number` VARCHAR(50),
`form_name` VARCHAR(80),
`form_description` VARCHAR(255),
FOREIGN KEY (`service_id` ) REFERENCES `Services`(`service_id` )
)

CREATE TABLE `Individuals` (
`individual_id` INTEGER PRIMARY KEY,
`individual_first_name` VARCHAR(80),
`individual_middle_name` VARCHAR(80),
`inidividual_phone` VARCHAR(80),
`individual_email` VARCHAR(80),
`individual_address` VARCHAR(255),
`individual_last_name` VARCHAR(80)
)

CREATE TABLE `Organizations` (
`organization_id` INTEGER PRIMARY KEY,
`date_formed` DATETIME,
`organization_name` VARCHAR(255),
`uk_vat_number` VARCHAR(20)
)

CREATE TABLE `Parties` (
`party_id` INTEGER PRIMARY KEY,
`payment_method_code` VARCHAR(15) NOT NULL,
`party_phone` VARCHAR(80),
`party_email` VARCHAR(80)
)

CREATE TABLE `Organization_Contact_Individuals` (
`individual_id` INTEGER NOT NULL,
`organization_id` INTEGER NOT NULL,
`date_contact_from` DATETIME NOT NULL,
`date_contact_to` DATETIME,
PRIMARY KEY (`individual_id`,`organization_id` ),
FOREIGN KEY (`organization_id` ) REFERENCES `Organizations`(`organization_id` ),
FOREIGN KEY (`individual_id` ) REFERENCES `Individuals`(`individual_id` )
)

CREATE TABLE `Party_Addresses` (
`party_id` INTEGER NOT NULL,
`address_id` INTEGER NOT NULL,
`date_address_from` DATETIME NOT NULL,
`address_type_code` VARCHAR(15) NOT NULL,
`date_address_to` DATETIME,
PRIMARY KEY (`party_id`, `address_id`),
FOREIGN KEY (`address_id` ) REFERENCES `Addresses`(`address_id` ),
FOREIGN KEY (`party_id` ) REFERENCES `Parties`(`party_id` )
)

CREATE TABLE `Party_Forms` (
`party_id` INTEGER NOT NULL,
`form_id` INTEGER NOT NULL,
`date_completion_started` DATETIME NOT NULL,
`form_status_code` VARCHAR(15) NOT NULL,
`date_fully_completed` DATETIME,
PRIMARY KEY (`party_id`, `form_id`),
FOREIGN KEY (`party_id` ) REFERENCES `Parties`(`party_id` ),
FOREIGN KEY (`form_id` ) REFERENCES `Forms`(`form_id` )
)

CREATE TABLE `Party_Services` (
`booking_id` INTEGER NOT NULL ,
`customer_id` INTEGER NOT NULL,
`service_id` INTEGER NOT NULL,
`service_datetime` DATETIME NOT NULL,
`booking_made_date` DATETIME,
FOREIGN KEY (`service_id` ) REFERENCES `Services`(`service_id` ),
FOREIGN KEY (`customer_id` ) REFERENCES `Parties`(`party_id` )
)

### Question
What are the payment method codes that have been used by more than 3 parties?

### SQL
SELECT payment_method_code FROM parties GROUP BY payment_method_code HAVING count(*)  >  3
### Database Schema
CREATE TABLE `Addresses` (
`address_id` INTEGER PRIMARY KEY,
`line_1_number_building` VARCHAR(80),
`town_city` VARCHAR(50),
`zip_postcode` VARCHAR(20),
`state_province_county` VARCHAR(50),
`country` VARCHAR(50)
)

CREATE TABLE `Services` (
`service_id` INTEGER PRIMARY KEY,
`service_type_code` VARCHAR(15) NOT NULL,
`service_name` VARCHAR(80),
`service_descriptio` VARCHAR(255)
)

CREATE TABLE `Forms` (
`form_id` INTEGER PRIMARY KEY,
`form_type_code` VARCHAR(15) NOT NULL,
`service_id` INTEGER,
`form_number` VARCHAR(50),
`form_name` VARCHAR(80),
`form_description` VARCHAR(255),
FOREIGN KEY (`service_id` ) REFERENCES `Services`(`service_id` )
)

CREATE TABLE `Individuals` (
`individual_id` INTEGER PRIMARY KEY,
`individual_first_name` VARCHAR(80),
`individual_middle_name` VARCHAR(80),
`inidividual_phone` VARCHAR(80),
`individual_email` VARCHAR(80),
`individual_address` VARCHAR(255),
`individual_last_name` VARCHAR(80)
)

CREATE TABLE `Organizations` (
`organization_id` INTEGER PRIMARY KEY,
`date_formed` DATETIME,
`organization_name` VARCHAR(255),
`uk_vat_number` VARCHAR(20)
)

CREATE TABLE `Parties` (
`party_id` INTEGER PRIMARY KEY,
`payment_method_code` VARCHAR(15) NOT NULL,
`party_phone` VARCHAR(80),
`party_email` VARCHAR(80)
)

CREATE TABLE `Organization_Contact_Individuals` (
`individual_id` INTEGER NOT NULL,
`organization_id` INTEGER NOT NULL,
`date_contact_from` DATETIME NOT NULL,
`date_contact_to` DATETIME,
PRIMARY KEY (`individual_id`,`organization_id` ),
FOREIGN KEY (`organization_id` ) REFERENCES `Organizations`(`organization_id` ),
FOREIGN KEY (`individual_id` ) REFERENCES `Individuals`(`individual_id` )
)

CREATE TABLE `Party_Addresses` (
`party_id` INTEGER NOT NULL,
`address_id` INTEGER NOT NULL,
`date_address_from` DATETIME NOT NULL,
`address_type_code` VARCHAR(15) NOT NULL,
`date_address_to` DATETIME,
PRIMARY KEY (`party_id`, `address_id`),
FOREIGN KEY (`address_id` ) REFERENCES `Addresses`(`address_id` ),
FOREIGN KEY (`party_id` ) REFERENCES `Parties`(`party_id` )
)

CREATE TABLE `Party_Forms` (
`party_id` INTEGER NOT NULL,
`form_id` INTEGER NOT NULL,
`date_completion_started` DATETIME NOT NULL,
`form_status_code` VARCHAR(15) NOT NULL,
`date_fully_completed` DATETIME,
PRIMARY KEY (`party_id`, `form_id`),
FOREIGN KEY (`party_id` ) REFERENCES `Parties`(`party_id` ),
FOREIGN KEY (`form_id` ) REFERENCES `Forms`(`form_id` )
)

CREATE TABLE `Party_Services` (
`booking_id` INTEGER NOT NULL ,
`customer_id` INTEGER NOT NULL,
`service_id` INTEGER NOT NULL,
`service_datetime` DATETIME NOT NULL,
`booking_made_date` DATETIME,
FOREIGN KEY (`service_id` ) REFERENCES `Services`(`service_id` ),
FOREIGN KEY (`customer_id` ) REFERENCES `Parties`(`party_id` )
)

### Question
Find the name of organizations whose names contain "Party".

### SQL
SELECT organization_name FROM organizations WHERE organization_name LIKE "%Party%"
### Database Schema
CREATE TABLE `Addresses` (
`address_id` INTEGER PRIMARY KEY,
`line_1_number_building` VARCHAR(80),
`town_city` VARCHAR(50),
`zip_postcode` VARCHAR(20),
`state_province_county` VARCHAR(50),
`country` VARCHAR(50)
)

CREATE TABLE `Services` (
`service_id` INTEGER PRIMARY KEY,
`service_type_code` VARCHAR(15) NOT NULL,
`service_name` VARCHAR(80),
`service_descriptio` VARCHAR(255)
)

CREATE TABLE `Forms` (
`form_id` INTEGER PRIMARY KEY,
`form_type_code` VARCHAR(15) NOT NULL,
`service_id` INTEGER,
`form_number` VARCHAR(50),
`form_name` VARCHAR(80),
`form_description` VARCHAR(255),
FOREIGN KEY (`service_id` ) REFERENCES `Services`(`service_id` )
)

CREATE TABLE `Individuals` (
`individual_id` INTEGER PRIMARY KEY,
`individual_first_name` VARCHAR(80),
`individual_middle_name` VARCHAR(80),
`inidividual_phone` VARCHAR(80),
`individual_email` VARCHAR(80),
`individual_address` VARCHAR(255),
`individual_last_name` VARCHAR(80)
)

CREATE TABLE `Organizations` (
`organization_id` INTEGER PRIMARY KEY,
`date_formed` DATETIME,
`organization_name` VARCHAR(255),
`uk_vat_number` VARCHAR(20)
)

CREATE TABLE `Parties` (
`party_id` INTEGER PRIMARY KEY,
`payment_method_code` VARCHAR(15) NOT NULL,
`party_phone` VARCHAR(80),
`party_email` VARCHAR(80)
)

CREATE TABLE `Organization_Contact_Individuals` (
`individual_id` INTEGER NOT NULL,
`organization_id` INTEGER NOT NULL,
`date_contact_from` DATETIME NOT NULL,
`date_contact_to` DATETIME,
PRIMARY KEY (`individual_id`,`organization_id` ),
FOREIGN KEY (`organization_id` ) REFERENCES `Organizations`(`organization_id` ),
FOREIGN KEY (`individual_id` ) REFERENCES `Individuals`(`individual_id` )
)

CREATE TABLE `Party_Addresses` (
`party_id` INTEGER NOT NULL,
`address_id` INTEGER NOT NULL,
`date_address_from` DATETIME NOT NULL,
`address_type_code` VARCHAR(15) NOT NULL,
`date_address_to` DATETIME,
PRIMARY KEY (`party_id`, `address_id`),
FOREIGN KEY (`address_id` ) REFERENCES `Addresses`(`address_id` ),
FOREIGN KEY (`party_id` ) REFERENCES `Parties`(`party_id` )
)

CREATE TABLE `Party_Forms` (
`party_id` INTEGER NOT NULL,
`form_id` INTEGER NOT NULL,
`date_completion_started` DATETIME NOT NULL,
`form_status_code` VARCHAR(15) NOT NULL,
`date_fully_completed` DATETIME,
PRIMARY KEY (`party_id`, `form_id`),
FOREIGN KEY (`party_id` ) REFERENCES `Parties`(`party_id` ),
FOREIGN KEY (`form_id` ) REFERENCES `Forms`(`form_id` )
)

CREATE TABLE `Party_Services` (
`booking_id` INTEGER NOT NULL ,
`customer_id` INTEGER NOT NULL,
`service_id` INTEGER NOT NULL,
`service_datetime` DATETIME NOT NULL,
`booking_made_date` DATETIME,
FOREIGN KEY (`service_id` ) REFERENCES `Services`(`service_id` ),
FOREIGN KEY (`customer_id` ) REFERENCES `Parties`(`party_id` )
)

### Question
What are the names of organizations that contain the word "Party"?

### SQL
SELECT organization_name FROM organizations WHERE organization_name LIKE "%Party%"
### Database Schema
CREATE TABLE `Addresses` (
`address_id` INTEGER PRIMARY KEY,
`line_1_number_building` VARCHAR(80),
`town_city` VARCHAR(50),
`zip_postcode` VARCHAR(20),
`state_province_county` VARCHAR(50),
`country` VARCHAR(50)
)

CREATE TABLE `Services` (
`service_id` INTEGER PRIMARY KEY,
`service_type_code` VARCHAR(15) NOT NULL,
`service_name` VARCHAR(80),
`service_descriptio` VARCHAR(255)
)

CREATE TABLE `Forms` (
`form_id` INTEGER PRIMARY KEY,
`form_type_code` VARCHAR(15) NOT NULL,
`service_id` INTEGER,
`form_number` VARCHAR(50),
`form_name` VARCHAR(80),
`form_description` VARCHAR(255),
FOREIGN KEY (`service_id` ) REFERENCES `Services`(`service_id` )
)

CREATE TABLE `Individuals` (
`individual_id` INTEGER PRIMARY KEY,
`individual_first_name` VARCHAR(80),
`individual_middle_name` VARCHAR(80),
`inidividual_phone` VARCHAR(80),
`individual_email` VARCHAR(80),
`individual_address` VARCHAR(255),
`individual_last_name` VARCHAR(80)
)

CREATE TABLE `Organizations` (
`organization_id` INTEGER PRIMARY KEY,
`date_formed` DATETIME,
`organization_name` VARCHAR(255),
`uk_vat_number` VARCHAR(20)
)

CREATE TABLE `Parties` (
`party_id` INTEGER PRIMARY KEY,
`payment_method_code` VARCHAR(15) NOT NULL,
`party_phone` VARCHAR(80),
`party_email` VARCHAR(80)
)

CREATE TABLE `Organization_Contact_Individuals` (
`individual_id` INTEGER NOT NULL,
`organization_id` INTEGER NOT NULL,
`date_contact_from` DATETIME NOT NULL,
`date_contact_to` DATETIME,
PRIMARY KEY (`individual_id`,`organization_id` ),
FOREIGN KEY (`organization_id` ) REFERENCES `Organizations`(`organization_id` ),
FOREIGN KEY (`individual_id` ) REFERENCES `Individuals`(`individual_id` )
)

CREATE TABLE `Party_Addresses` (
`party_id` INTEGER NOT NULL,
`address_id` INTEGER NOT NULL,
`date_address_from` DATETIME NOT NULL,
`address_type_code` VARCHAR(15) NOT NULL,
`date_address_to` DATETIME,
PRIMARY KEY (`party_id`, `address_id`),
FOREIGN KEY (`address_id` ) REFERENCES `Addresses`(`address_id` ),
FOREIGN KEY (`party_id` ) REFERENCES `Parties`(`party_id` )
)

CREATE TABLE `Party_Forms` (
`party_id` INTEGER NOT NULL,
`form_id` INTEGER NOT NULL,
`date_completion_started` DATETIME NOT NULL,
`form_status_code` VARCHAR(15) NOT NULL,
`date_fully_completed` DATETIME,
PRIMARY KEY (`party_id`, `form_id`),
FOREIGN KEY (`party_id` ) REFERENCES `Parties`(`party_id` ),
FOREIGN KEY (`form_id` ) REFERENCES `Forms`(`form_id` )
)

CREATE TABLE `Party_Services` (
`booking_id` INTEGER NOT NULL ,
`customer_id` INTEGER NOT NULL,
`service_id` INTEGER NOT NULL,
`service_datetime` DATETIME NOT NULL,
`booking_made_date` DATETIME,
FOREIGN KEY (`service_id` ) REFERENCES `Services`(`service_id` ),
FOREIGN KEY (`customer_id` ) REFERENCES `Parties`(`party_id` )
)

### Question
How many distinct payment methods are used by parties?

### SQL
SELECT count(DISTINCT payment_method_code) FROM parties
### Database Schema
CREATE TABLE `Addresses` (
`address_id` INTEGER PRIMARY KEY,
`line_1_number_building` VARCHAR(80),
`town_city` VARCHAR(50),
`zip_postcode` VARCHAR(20),
`state_province_county` VARCHAR(50),
`country` VARCHAR(50)
)

CREATE TABLE `Services` (
`service_id` INTEGER PRIMARY KEY,
`service_type_code` VARCHAR(15) NOT NULL,
`service_name` VARCHAR(80),
`service_descriptio` VARCHAR(255)
)

CREATE TABLE `Forms` (
`form_id` INTEGER PRIMARY KEY,
`form_type_code` VARCHAR(15) NOT NULL,
`service_id` INTEGER,
`form_number` VARCHAR(50),
`form_name` VARCHAR(80),
`form_description` VARCHAR(255),
FOREIGN KEY (`service_id` ) REFERENCES `Services`(`service_id` )
)

CREATE TABLE `Individuals` (
`individual_id` INTEGER PRIMARY KEY,
`individual_first_name` VARCHAR(80),
`individual_middle_name` VARCHAR(80),
`inidividual_phone` VARCHAR(80),
`individual_email` VARCHAR(80),
`individual_address` VARCHAR(255),
`individual_last_name` VARCHAR(80)
)

CREATE TABLE `Organizations` (
`organization_id` INTEGER PRIMARY KEY,
`date_formed` DATETIME,
`organization_name` VARCHAR(255),
`uk_vat_number` VARCHAR(20)
)

CREATE TABLE `Parties` (
`party_id` INTEGER PRIMARY KEY,
`payment_method_code` VARCHAR(15) NOT NULL,
`party_phone` VARCHAR(80),
`party_email` VARCHAR(80)
)

CREATE TABLE `Organization_Contact_Individuals` (
`individual_id` INTEGER NOT NULL,
`organization_id` INTEGER NOT NULL,
`date_contact_from` DATETIME NOT NULL,
`date_contact_to` DATETIME,
PRIMARY KEY (`individual_id`,`organization_id` ),
FOREIGN KEY (`organization_id` ) REFERENCES `Organizations`(`organization_id` ),
FOREIGN KEY (`individual_id` ) REFERENCES `Individuals`(`individual_id` )
)

CREATE TABLE `Party_Addresses` (
`party_id` INTEGER NOT NULL,
`address_id` INTEGER NOT NULL,
`date_address_from` DATETIME NOT NULL,
`address_type_code` VARCHAR(15) NOT NULL,
`date_address_to` DATETIME,
PRIMARY KEY (`party_id`, `address_id`),
FOREIGN KEY (`address_id` ) REFERENCES `Addresses`(`address_id` ),
FOREIGN KEY (`party_id` ) REFERENCES `Parties`(`party_id` )
)

CREATE TABLE `Party_Forms` (
`party_id` INTEGER NOT NULL,
`form_id` INTEGER NOT NULL,
`date_completion_started` DATETIME NOT NULL,
`form_status_code` VARCHAR(15) NOT NULL,
`date_fully_completed` DATETIME,
PRIMARY KEY (`party_id`, `form_id`),
FOREIGN KEY (`party_id` ) REFERENCES `Parties`(`party_id` ),
FOREIGN KEY (`form_id` ) REFERENCES `Forms`(`form_id` )
)

CREATE TABLE `Party_Services` (
`booking_id` INTEGER NOT NULL ,
`customer_id` INTEGER NOT NULL,
`service_id` INTEGER NOT NULL,
`service_datetime` DATETIME NOT NULL,
`booking_made_date` DATETIME,
FOREIGN KEY (`service_id` ) REFERENCES `Services`(`service_id` ),
FOREIGN KEY (`customer_id` ) REFERENCES `Parties`(`party_id` )
)

### Question
Count the number of different payment method codes used by parties.

### SQL
SELECT count(DISTINCT payment_method_code) FROM parties
### Database Schema
CREATE TABLE `Addresses` (
`address_id` INTEGER PRIMARY KEY,
`line_1_number_building` VARCHAR(80),
`town_city` VARCHAR(50),
`zip_postcode` VARCHAR(20),
`state_province_county` VARCHAR(50),
`country` VARCHAR(50)
)

CREATE TABLE `Services` (
`service_id` INTEGER PRIMARY KEY,
`service_type_code` VARCHAR(15) NOT NULL,
`service_name` VARCHAR(80),
`service_descriptio` VARCHAR(255)
)

CREATE TABLE `Forms` (
`form_id` INTEGER PRIMARY KEY,
`form_type_code` VARCHAR(15) NOT NULL,
`service_id` INTEGER,
`form_number` VARCHAR(50),
`form_name` VARCHAR(80),
`form_description` VARCHAR(255),
FOREIGN KEY (`service_id` ) REFERENCES `Services`(`service_id` )
)

CREATE TABLE `Individuals` (
`individual_id` INTEGER PRIMARY KEY,
`individual_first_name` VARCHAR(80),
`individual_middle_name` VARCHAR(80),
`inidividual_phone` VARCHAR(80),
`individual_email` VARCHAR(80),
`individual_address` VARCHAR(255),
`individual_last_name` VARCHAR(80)
)

CREATE TABLE `Organizations` (
`organization_id` INTEGER PRIMARY KEY,
`date_formed` DATETIME,
`organization_name` VARCHAR(255),
`uk_vat_number` VARCHAR(20)
)

CREATE TABLE `Parties` (
`party_id` INTEGER PRIMARY KEY,
`payment_method_code` VARCHAR(15) NOT NULL,
`party_phone` VARCHAR(80),
`party_email` VARCHAR(80)
)

CREATE TABLE `Organization_Contact_Individuals` (
`individual_id` INTEGER NOT NULL,
`organization_id` INTEGER NOT NULL,
`date_contact_from` DATETIME NOT NULL,
`date_contact_to` DATETIME,
PRIMARY KEY (`individual_id`,`organization_id` ),
FOREIGN KEY (`organization_id` ) REFERENCES `Organizations`(`organization_id` ),
FOREIGN KEY (`individual_id` ) REFERENCES `Individuals`(`individual_id` )
)

CREATE TABLE `Party_Addresses` (
`party_id` INTEGER NOT NULL,
`address_id` INTEGER NOT NULL,
`date_address_from` DATETIME NOT NULL,
`address_type_code` VARCHAR(15) NOT NULL,
`date_address_to` DATETIME,
PRIMARY KEY (`party_id`, `address_id`),
FOREIGN KEY (`address_id` ) REFERENCES `Addresses`(`address_id` ),
FOREIGN KEY (`party_id` ) REFERENCES `Parties`(`party_id` )
)

CREATE TABLE `Party_Forms` (
`party_id` INTEGER NOT NULL,
`form_id` INTEGER NOT NULL,
`date_completion_started` DATETIME NOT NULL,
`form_status_code` VARCHAR(15) NOT NULL,
`date_fully_completed` DATETIME,
PRIMARY KEY (`party_id`, `form_id`),
FOREIGN KEY (`party_id` ) REFERENCES `Parties`(`party_id` ),
FOREIGN KEY (`form_id` ) REFERENCES `Forms`(`form_id` )
)

CREATE TABLE `Party_Services` (
`booking_id` INTEGER NOT NULL ,
`customer_id` INTEGER NOT NULL,
`service_id` INTEGER NOT NULL,
`service_datetime` DATETIME NOT NULL,
`booking_made_date` DATETIME,
FOREIGN KEY (`service_id` ) REFERENCES `Services`(`service_id` ),
FOREIGN KEY (`customer_id` ) REFERENCES `Parties`(`party_id` )
)

### Question
Which is the email of the party that has used the services the most number of times?

### SQL
SELECT t1.party_email FROM parties AS t1 JOIN party_services AS t2 ON t1.party_id  =  t2.customer_id GROUP BY t1.party_email ORDER BY count(*) DESC LIMIT 1
### Database Schema
CREATE TABLE `Addresses` (
`address_id` INTEGER PRIMARY KEY,
`line_1_number_building` VARCHAR(80),
`town_city` VARCHAR(50),
`zip_postcode` VARCHAR(20),
`state_province_county` VARCHAR(50),
`country` VARCHAR(50)
)

CREATE TABLE `Services` (
`service_id` INTEGER PRIMARY KEY,
`service_type_code` VARCHAR(15) NOT NULL,
`service_name` VARCHAR(80),
`service_descriptio` VARCHAR(255)
)

CREATE TABLE `Forms` (
`form_id` INTEGER PRIMARY KEY,
`form_type_code` VARCHAR(15) NOT NULL,
`service_id` INTEGER,
`form_number` VARCHAR(50),
`form_name` VARCHAR(80),
`form_description` VARCHAR(255),
FOREIGN KEY (`service_id` ) REFERENCES `Services`(`service_id` )
)

CREATE TABLE `Individuals` (
`individual_id` INTEGER PRIMARY KEY,
`individual_first_name` VARCHAR(80),
`individual_middle_name` VARCHAR(80),
`inidividual_phone` VARCHAR(80),
`individual_email` VARCHAR(80),
`individual_address` VARCHAR(255),
`individual_last_name` VARCHAR(80)
)

CREATE TABLE `Organizations` (
`organization_id` INTEGER PRIMARY KEY,
`date_formed` DATETIME,
`organization_name` VARCHAR(255),
`uk_vat_number` VARCHAR(20)
)

CREATE TABLE `Parties` (
`party_id` INTEGER PRIMARY KEY,
`payment_method_code` VARCHAR(15) NOT NULL,
`party_phone` VARCHAR(80),
`party_email` VARCHAR(80)
)

CREATE TABLE `Organization_Contact_Individuals` (
`individual_id` INTEGER NOT NULL,
`organization_id` INTEGER NOT NULL,
`date_contact_from` DATETIME NOT NULL,
`date_contact_to` DATETIME,
PRIMARY KEY (`individual_id`,`organization_id` ),
FOREIGN KEY (`organization_id` ) REFERENCES `Organizations`(`organization_id` ),
FOREIGN KEY (`individual_id` ) REFERENCES `Individuals`(`individual_id` )
)

CREATE TABLE `Party_Addresses` (
`party_id` INTEGER NOT NULL,
`address_id` INTEGER NOT NULL,
`date_address_from` DATETIME NOT NULL,
`address_type_code` VARCHAR(15) NOT NULL,
`date_address_to` DATETIME,
PRIMARY KEY (`party_id`, `address_id`),
FOREIGN KEY (`address_id` ) REFERENCES `Addresses`(`address_id` ),
FOREIGN KEY (`party_id` ) REFERENCES `Parties`(`party_id` )
)

CREATE TABLE `Party_Forms` (
`party_id` INTEGER NOT NULL,
`form_id` INTEGER NOT NULL,
`date_completion_started` DATETIME NOT NULL,
`form_status_code` VARCHAR(15) NOT NULL,
`date_fully_completed` DATETIME,
PRIMARY KEY (`party_id`, `form_id`),
FOREIGN KEY (`party_id` ) REFERENCES `Parties`(`party_id` ),
FOREIGN KEY (`form_id` ) REFERENCES `Forms`(`form_id` )
)

CREATE TABLE `Party_Services` (
`booking_id` INTEGER NOT NULL ,
`customer_id` INTEGER NOT NULL,
`service_id` INTEGER NOT NULL,
`service_datetime` DATETIME NOT NULL,
`booking_made_date` DATETIME,
FOREIGN KEY (`service_id` ) REFERENCES `Services`(`service_id` ),
FOREIGN KEY (`customer_id` ) REFERENCES `Parties`(`party_id` )
)

### Question
Return the party email that has used party services the greatest number of times.

### SQL
SELECT t1.party_email FROM parties AS t1 JOIN party_services AS t2 ON t1.party_id  =  t2.customer_id GROUP BY t1.party_email ORDER BY count(*) DESC LIMIT 1
### Database Schema
CREATE TABLE `Addresses` (
`address_id` INTEGER PRIMARY KEY,
`line_1_number_building` VARCHAR(80),
`town_city` VARCHAR(50),
`zip_postcode` VARCHAR(20),
`state_province_county` VARCHAR(50),
`country` VARCHAR(50)
)

CREATE TABLE `Services` (
`service_id` INTEGER PRIMARY KEY,
`service_type_code` VARCHAR(15) NOT NULL,
`service_name` VARCHAR(80),
`service_descriptio` VARCHAR(255)
)

CREATE TABLE `Forms` (
`form_id` INTEGER PRIMARY KEY,
`form_type_code` VARCHAR(15) NOT NULL,
`service_id` INTEGER,
`form_number` VARCHAR(50),
`form_name` VARCHAR(80),
`form_description` VARCHAR(255),
FOREIGN KEY (`service_id` ) REFERENCES `Services`(`service_id` )
)

CREATE TABLE `Individuals` (
`individual_id` INTEGER PRIMARY KEY,
`individual_first_name` VARCHAR(80),
`individual_middle_name` VARCHAR(80),
`inidividual_phone` VARCHAR(80),
`individual_email` VARCHAR(80),
`individual_address` VARCHAR(255),
`individual_last_name` VARCHAR(80)
)

CREATE TABLE `Organizations` (
`organization_id` INTEGER PRIMARY KEY,
`date_formed` DATETIME,
`organization_name` VARCHAR(255),
`uk_vat_number` VARCHAR(20)
)

CREATE TABLE `Parties` (
`party_id` INTEGER PRIMARY KEY,
`payment_method_code` VARCHAR(15) NOT NULL,
`party_phone` VARCHAR(80),
`party_email` VARCHAR(80)
)

CREATE TABLE `Organization_Contact_Individuals` (
`individual_id` INTEGER NOT NULL,
`organization_id` INTEGER NOT NULL,
`date_contact_from` DATETIME NOT NULL,
`date_contact_to` DATETIME,
PRIMARY KEY (`individual_id`,`organization_id` ),
FOREIGN KEY (`organization_id` ) REFERENCES `Organizations`(`organization_id` ),
FOREIGN KEY (`individual_id` ) REFERENCES `Individuals`(`individual_id` )
)

CREATE TABLE `Party_Addresses` (
`party_id` INTEGER NOT NULL,
`address_id` INTEGER NOT NULL,
`date_address_from` DATETIME NOT NULL,
`address_type_code` VARCHAR(15) NOT NULL,
`date_address_to` DATETIME,
PRIMARY KEY (`party_id`, `address_id`),
FOREIGN KEY (`address_id` ) REFERENCES `Addresses`(`address_id` ),
FOREIGN KEY (`party_id` ) REFERENCES `Parties`(`party_id` )
)

CREATE TABLE `Party_Forms` (
`party_id` INTEGER NOT NULL,
`form_id` INTEGER NOT NULL,
`date_completion_started` DATETIME NOT NULL,
`form_status_code` VARCHAR(15) NOT NULL,
`date_fully_completed` DATETIME,
PRIMARY KEY (`party_id`, `form_id`),
FOREIGN KEY (`party_id` ) REFERENCES `Parties`(`party_id` ),
FOREIGN KEY (`form_id` ) REFERENCES `Forms`(`form_id` )
)

CREATE TABLE `Party_Services` (
`booking_id` INTEGER NOT NULL ,
`customer_id` INTEGER NOT NULL,
`service_id` INTEGER NOT NULL,
`service_datetime` DATETIME NOT NULL,
`booking_made_date` DATETIME,
FOREIGN KEY (`service_id` ) REFERENCES `Services`(`service_id` ),
FOREIGN KEY (`customer_id` ) REFERENCES `Parties`(`party_id` )
)

### Question
Which state can address "6862 Kaitlyn Knolls" possibly be in?

### SQL
SELECT state_province_county FROM addresses WHERE line_1_number_building LIKE "%6862 Kaitlyn Knolls%"
### Database Schema
CREATE TABLE `Addresses` (
`address_id` INTEGER PRIMARY KEY,
`line_1_number_building` VARCHAR(80),
`town_city` VARCHAR(50),
`zip_postcode` VARCHAR(20),
`state_province_county` VARCHAR(50),
`country` VARCHAR(50)
)

CREATE TABLE `Services` (
`service_id` INTEGER PRIMARY KEY,
`service_type_code` VARCHAR(15) NOT NULL,
`service_name` VARCHAR(80),
`service_descriptio` VARCHAR(255)
)

CREATE TABLE `Forms` (
`form_id` INTEGER PRIMARY KEY,
`form_type_code` VARCHAR(15) NOT NULL,
`service_id` INTEGER,
`form_number` VARCHAR(50),
`form_name` VARCHAR(80),
`form_description` VARCHAR(255),
FOREIGN KEY (`service_id` ) REFERENCES `Services`(`service_id` )
)

CREATE TABLE `Individuals` (
`individual_id` INTEGER PRIMARY KEY,
`individual_first_name` VARCHAR(80),
`individual_middle_name` VARCHAR(80),
`inidividual_phone` VARCHAR(80),
`individual_email` VARCHAR(80),
`individual_address` VARCHAR(255),
`individual_last_name` VARCHAR(80)
)

CREATE TABLE `Organizations` (
`organization_id` INTEGER PRIMARY KEY,
`date_formed` DATETIME,
`organization_name` VARCHAR(255),
`uk_vat_number` VARCHAR(20)
)

CREATE TABLE `Parties` (
`party_id` INTEGER PRIMARY KEY,
`payment_method_code` VARCHAR(15) NOT NULL,
`party_phone` VARCHAR(80),
`party_email` VARCHAR(80)
)

CREATE TABLE `Organization_Contact_Individuals` (
`individual_id` INTEGER NOT NULL,
`organization_id` INTEGER NOT NULL,
`date_contact_from` DATETIME NOT NULL,
`date_contact_to` DATETIME,
PRIMARY KEY (`individual_id`,`organization_id` ),
FOREIGN KEY (`organization_id` ) REFERENCES `Organizations`(`organization_id` ),
FOREIGN KEY (`individual_id` ) REFERENCES `Individuals`(`individual_id` )
)

CREATE TABLE `Party_Addresses` (
`party_id` INTEGER NOT NULL,
`address_id` INTEGER NOT NULL,
`date_address_from` DATETIME NOT NULL,
`address_type_code` VARCHAR(15) NOT NULL,
`date_address_to` DATETIME,
PRIMARY KEY (`party_id`, `address_id`),
FOREIGN KEY (`address_id` ) REFERENCES `Addresses`(`address_id` ),
FOREIGN KEY (`party_id` ) REFERENCES `Parties`(`party_id` )
)

CREATE TABLE `Party_Forms` (
`party_id` INTEGER NOT NULL,
`form_id` INTEGER NOT NULL,
`date_completion_started` DATETIME NOT NULL,
`form_status_code` VARCHAR(15) NOT NULL,
`date_fully_completed` DATETIME,
PRIMARY KEY (`party_id`, `form_id`),
FOREIGN KEY (`party_id` ) REFERENCES `Parties`(`party_id` ),
FOREIGN KEY (`form_id` ) REFERENCES `Forms`(`form_id` )
)

CREATE TABLE `Party_Services` (
`booking_id` INTEGER NOT NULL ,
`customer_id` INTEGER NOT NULL,
`service_id` INTEGER NOT NULL,
`service_datetime` DATETIME NOT NULL,
`booking_made_date` DATETIME,
FOREIGN KEY (`service_id` ) REFERENCES `Services`(`service_id` ),
FOREIGN KEY (`customer_id` ) REFERENCES `Parties`(`party_id` )
)

### Question
Give the state corresponding to the line number building "6862 Kaitlyn Knolls".

### SQL
SELECT state_province_county FROM addresses WHERE line_1_number_building LIKE "%6862 Kaitlyn Knolls%"
### Database Schema
CREATE TABLE `Addresses` (
`address_id` INTEGER PRIMARY KEY,
`line_1_number_building` VARCHAR(80),
`town_city` VARCHAR(50),
`zip_postcode` VARCHAR(20),
`state_province_county` VARCHAR(50),
`country` VARCHAR(50)
)

CREATE TABLE `Services` (
`service_id` INTEGER PRIMARY KEY,
`service_type_code` VARCHAR(15) NOT NULL,
`service_name` VARCHAR(80),
`service_descriptio` VARCHAR(255)
)

CREATE TABLE `Forms` (
`form_id` INTEGER PRIMARY KEY,
`form_type_code` VARCHAR(15) NOT NULL,
`service_id` INTEGER,
`form_number` VARCHAR(50),
`form_name` VARCHAR(80),
`form_description` VARCHAR(255),
FOREIGN KEY (`service_id` ) REFERENCES `Services`(`service_id` )
)

CREATE TABLE `Individuals` (
`individual_id` INTEGER PRIMARY KEY,
`individual_first_name` VARCHAR(80),
`individual_middle_name` VARCHAR(80),
`inidividual_phone` VARCHAR(80),
`individual_email` VARCHAR(80),
`individual_address` VARCHAR(255),
`individual_last_name` VARCHAR(80)
)

CREATE TABLE `Organizations` (
`organization_id` INTEGER PRIMARY KEY,
`date_formed` DATETIME,
`organization_name` VARCHAR(255),
`uk_vat_number` VARCHAR(20)
)

CREATE TABLE `Parties` (
`party_id` INTEGER PRIMARY KEY,
`payment_method_code` VARCHAR(15) NOT NULL,
`party_phone` VARCHAR(80),
`party_email` VARCHAR(80)
)

CREATE TABLE `Organization_Contact_Individuals` (
`individual_id` INTEGER NOT NULL,
`organization_id` INTEGER NOT NULL,
`date_contact_from` DATETIME NOT NULL,
`date_contact_to` DATETIME,
PRIMARY KEY (`individual_id`,`organization_id` ),
FOREIGN KEY (`organization_id` ) REFERENCES `Organizations`(`organization_id` ),
FOREIGN KEY (`individual_id` ) REFERENCES `Individuals`(`individual_id` )
)

CREATE TABLE `Party_Addresses` (
`party_id` INTEGER NOT NULL,
`address_id` INTEGER NOT NULL,
`date_address_from` DATETIME NOT NULL,
`address_type_code` VARCHAR(15) NOT NULL,
`date_address_to` DATETIME,
PRIMARY KEY (`party_id`, `address_id`),
FOREIGN KEY (`address_id` ) REFERENCES `Addresses`(`address_id` ),
FOREIGN KEY (`party_id` ) REFERENCES `Parties`(`party_id` )
)

CREATE TABLE `Party_Forms` (
`party_id` INTEGER NOT NULL,
`form_id` INTEGER NOT NULL,
`date_completion_started` DATETIME NOT NULL,
`form_status_code` VARCHAR(15) NOT NULL,
`date_fully_completed` DATETIME,
PRIMARY KEY (`party_id`, `form_id`),
FOREIGN KEY (`party_id` ) REFERENCES `Parties`(`party_id` ),
FOREIGN KEY (`form_id` ) REFERENCES `Forms`(`form_id` )
)

CREATE TABLE `Party_Services` (
`booking_id` INTEGER NOT NULL ,
`customer_id` INTEGER NOT NULL,
`service_id` INTEGER NOT NULL,
`service_datetime` DATETIME NOT NULL,
`booking_made_date` DATETIME,
FOREIGN KEY (`service_id` ) REFERENCES `Services`(`service_id` ),
FOREIGN KEY (`customer_id` ) REFERENCES `Parties`(`party_id` )
)

### Question
What is the name of organization that has the greatest number of contact individuals?

### SQL
SELECT t1.organization_name FROM organizations AS t1 JOIN organization_contact_individuals AS t2 ON t1.organization_id  =  t2.organization_id GROUP BY t1.organization_name ORDER BY count(*) DESC LIMIT 1
### Database Schema
CREATE TABLE `Addresses` (
`address_id` INTEGER PRIMARY KEY,
`line_1_number_building` VARCHAR(80),
`town_city` VARCHAR(50),
`zip_postcode` VARCHAR(20),
`state_province_county` VARCHAR(50),
`country` VARCHAR(50)
)

CREATE TABLE `Services` (
`service_id` INTEGER PRIMARY KEY,
`service_type_code` VARCHAR(15) NOT NULL,
`service_name` VARCHAR(80),
`service_descriptio` VARCHAR(255)
)

CREATE TABLE `Forms` (
`form_id` INTEGER PRIMARY KEY,
`form_type_code` VARCHAR(15) NOT NULL,
`service_id` INTEGER,
`form_number` VARCHAR(50),
`form_name` VARCHAR(80),
`form_description` VARCHAR(255),
FOREIGN KEY (`service_id` ) REFERENCES `Services`(`service_id` )
)

CREATE TABLE `Individuals` (
`individual_id` INTEGER PRIMARY KEY,
`individual_first_name` VARCHAR(80),
`individual_middle_name` VARCHAR(80),
`inidividual_phone` VARCHAR(80),
`individual_email` VARCHAR(80),
`individual_address` VARCHAR(255),
`individual_last_name` VARCHAR(80)
)

CREATE TABLE `Organizations` (
`organization_id` INTEGER PRIMARY KEY,
`date_formed` DATETIME,
`organization_name` VARCHAR(255),
`uk_vat_number` VARCHAR(20)
)

CREATE TABLE `Parties` (
`party_id` INTEGER PRIMARY KEY,
`payment_method_code` VARCHAR(15) NOT NULL,
`party_phone` VARCHAR(80),
`party_email` VARCHAR(80)
)

CREATE TABLE `Organization_Contact_Individuals` (
`individual_id` INTEGER NOT NULL,
`organization_id` INTEGER NOT NULL,
`date_contact_from` DATETIME NOT NULL,
`date_contact_to` DATETIME,
PRIMARY KEY (`individual_id`,`organization_id` ),
FOREIGN KEY (`organization_id` ) REFERENCES `Organizations`(`organization_id` ),
FOREIGN KEY (`individual_id` ) REFERENCES `Individuals`(`individual_id` )
)

CREATE TABLE `Party_Addresses` (
`party_id` INTEGER NOT NULL,
`address_id` INTEGER NOT NULL,
`date_address_from` DATETIME NOT NULL,
`address_type_code` VARCHAR(15) NOT NULL,
`date_address_to` DATETIME,
PRIMARY KEY (`party_id`, `address_id`),
FOREIGN KEY (`address_id` ) REFERENCES `Addresses`(`address_id` ),
FOREIGN KEY (`party_id` ) REFERENCES `Parties`(`party_id` )
)

CREATE TABLE `Party_Forms` (
`party_id` INTEGER NOT NULL,
`form_id` INTEGER NOT NULL,
`date_completion_started` DATETIME NOT NULL,
`form_status_code` VARCHAR(15) NOT NULL,
`date_fully_completed` DATETIME,
PRIMARY KEY (`party_id`, `form_id`),
FOREIGN KEY (`party_id` ) REFERENCES `Parties`(`party_id` ),
FOREIGN KEY (`form_id` ) REFERENCES `Forms`(`form_id` )
)

CREATE TABLE `Party_Services` (
`booking_id` INTEGER NOT NULL ,
`customer_id` INTEGER NOT NULL,
`service_id` INTEGER NOT NULL,
`service_datetime` DATETIME NOT NULL,
`booking_made_date` DATETIME,
FOREIGN KEY (`service_id` ) REFERENCES `Services`(`service_id` ),
FOREIGN KEY (`customer_id` ) REFERENCES `Parties`(`party_id` )
)

### Question
Return the name of the organization which has the most contact individuals.

### SQL
SELECT t1.organization_name FROM organizations AS t1 JOIN organization_contact_individuals AS t2 ON t1.organization_id  =  t2.organization_id GROUP BY t1.organization_name ORDER BY count(*) DESC LIMIT 1
### Database Schema
CREATE TABLE `Addresses` (
`address_id` INTEGER PRIMARY KEY,
`line_1_number_building` VARCHAR(80),
`town_city` VARCHAR(50),
`zip_postcode` VARCHAR(20),
`state_province_county` VARCHAR(50),
`country` VARCHAR(50)
)

CREATE TABLE `Services` (
`service_id` INTEGER PRIMARY KEY,
`service_type_code` VARCHAR(15) NOT NULL,
`service_name` VARCHAR(80),
`service_descriptio` VARCHAR(255)
)

CREATE TABLE `Forms` (
`form_id` INTEGER PRIMARY KEY,
`form_type_code` VARCHAR(15) NOT NULL,
`service_id` INTEGER,
`form_number` VARCHAR(50),
`form_name` VARCHAR(80),
`form_description` VARCHAR(255),
FOREIGN KEY (`service_id` ) REFERENCES `Services`(`service_id` )
)

CREATE TABLE `Individuals` (
`individual_id` INTEGER PRIMARY KEY,
`individual_first_name` VARCHAR(80),
`individual_middle_name` VARCHAR(80),
`inidividual_phone` VARCHAR(80),
`individual_email` VARCHAR(80),
`individual_address` VARCHAR(255),
`individual_last_name` VARCHAR(80)
)

CREATE TABLE `Organizations` (
`organization_id` INTEGER PRIMARY KEY,
`date_formed` DATETIME,
`organization_name` VARCHAR(255),
`uk_vat_number` VARCHAR(20)
)

CREATE TABLE `Parties` (
`party_id` INTEGER PRIMARY KEY,
`payment_method_code` VARCHAR(15) NOT NULL,
`party_phone` VARCHAR(80),
`party_email` VARCHAR(80)
)

CREATE TABLE `Organization_Contact_Individuals` (
`individual_id` INTEGER NOT NULL,
`organization_id` INTEGER NOT NULL,
`date_contact_from` DATETIME NOT NULL,
`date_contact_to` DATETIME,
PRIMARY KEY (`individual_id`,`organization_id` ),
FOREIGN KEY (`organization_id` ) REFERENCES `Organizations`(`organization_id` ),
FOREIGN KEY (`individual_id` ) REFERENCES `Individuals`(`individual_id` )
)

CREATE TABLE `Party_Addresses` (
`party_id` INTEGER NOT NULL,
`address_id` INTEGER NOT NULL,
`date_address_from` DATETIME NOT NULL,
`address_type_code` VARCHAR(15) NOT NULL,
`date_address_to` DATETIME,
PRIMARY KEY (`party_id`, `address_id`),
FOREIGN KEY (`address_id` ) REFERENCES `Addresses`(`address_id` ),
FOREIGN KEY (`party_id` ) REFERENCES `Parties`(`party_id` )
)

CREATE TABLE `Party_Forms` (
`party_id` INTEGER NOT NULL,
`form_id` INTEGER NOT NULL,
`date_completion_started` DATETIME NOT NULL,
`form_status_code` VARCHAR(15) NOT NULL,
`date_fully_completed` DATETIME,
PRIMARY KEY (`party_id`, `form_id`),
FOREIGN KEY (`party_id` ) REFERENCES `Parties`(`party_id` ),
FOREIGN KEY (`form_id` ) REFERENCES `Forms`(`form_id` )
)

CREATE TABLE `Party_Services` (
`booking_id` INTEGER NOT NULL ,
`customer_id` INTEGER NOT NULL,
`service_id` INTEGER NOT NULL,
`service_datetime` DATETIME NOT NULL,
`booking_made_date` DATETIME,
FOREIGN KEY (`service_id` ) REFERENCES `Services`(`service_id` ),
FOREIGN KEY (`customer_id` ) REFERENCES `Parties`(`party_id` )
)

### Question
Find the last name of the individuals that have been contact individuals of an organization.

### SQL
SELECT DISTINCT t1.individual_last_name FROM individuals AS t1 JOIN organization_contact_individuals AS t2 ON t1.individual_id  =  t2.individual_id
### Database Schema
CREATE TABLE `Addresses` (
`address_id` INTEGER PRIMARY KEY,
`line_1_number_building` VARCHAR(80),
`town_city` VARCHAR(50),
`zip_postcode` VARCHAR(20),
`state_province_county` VARCHAR(50),
`country` VARCHAR(50)
)

CREATE TABLE `Services` (
`service_id` INTEGER PRIMARY KEY,
`service_type_code` VARCHAR(15) NOT NULL,
`service_name` VARCHAR(80),
`service_descriptio` VARCHAR(255)
)

CREATE TABLE `Forms` (
`form_id` INTEGER PRIMARY KEY,
`form_type_code` VARCHAR(15) NOT NULL,
`service_id` INTEGER,
`form_number` VARCHAR(50),
`form_name` VARCHAR(80),
`form_description` VARCHAR(255),
FOREIGN KEY (`service_id` ) REFERENCES `Services`(`service_id` )
)

CREATE TABLE `Individuals` (
`individual_id` INTEGER PRIMARY KEY,
`individual_first_name` VARCHAR(80),
`individual_middle_name` VARCHAR(80),
`inidividual_phone` VARCHAR(80),
`individual_email` VARCHAR(80),
`individual_address` VARCHAR(255),
`individual_last_name` VARCHAR(80)
)

CREATE TABLE `Organizations` (
`organization_id` INTEGER PRIMARY KEY,
`date_formed` DATETIME,
`organization_name` VARCHAR(255),
`uk_vat_number` VARCHAR(20)
)

CREATE TABLE `Parties` (
`party_id` INTEGER PRIMARY KEY,
`payment_method_code` VARCHAR(15) NOT NULL,
`party_phone` VARCHAR(80),
`party_email` VARCHAR(80)
)

CREATE TABLE `Organization_Contact_Individuals` (
`individual_id` INTEGER NOT NULL,
`organization_id` INTEGER NOT NULL,
`date_contact_from` DATETIME NOT NULL,
`date_contact_to` DATETIME,
PRIMARY KEY (`individual_id`,`organization_id` ),
FOREIGN KEY (`organization_id` ) REFERENCES `Organizations`(`organization_id` ),
FOREIGN KEY (`individual_id` ) REFERENCES `Individuals`(`individual_id` )
)

CREATE TABLE `Party_Addresses` (
`party_id` INTEGER NOT NULL,
`address_id` INTEGER NOT NULL,
`date_address_from` DATETIME NOT NULL,
`address_type_code` VARCHAR(15) NOT NULL,
`date_address_to` DATETIME,
PRIMARY KEY (`party_id`, `address_id`),
FOREIGN KEY (`address_id` ) REFERENCES `Addresses`(`address_id` ),
FOREIGN KEY (`party_id` ) REFERENCES `Parties`(`party_id` )
)

CREATE TABLE `Party_Forms` (
`party_id` INTEGER NOT NULL,
`form_id` INTEGER NOT NULL,
`date_completion_started` DATETIME NOT NULL,
`form_status_code` VARCHAR(15) NOT NULL,
`date_fully_completed` DATETIME,
PRIMARY KEY (`party_id`, `form_id`),
FOREIGN KEY (`party_id` ) REFERENCES `Parties`(`party_id` ),
FOREIGN KEY (`form_id` ) REFERENCES `Forms`(`form_id` )
)

CREATE TABLE `Party_Services` (
`booking_id` INTEGER NOT NULL ,
`customer_id` INTEGER NOT NULL,
`service_id` INTEGER NOT NULL,
`service_datetime` DATETIME NOT NULL,
`booking_made_date` DATETIME,
FOREIGN KEY (`service_id` ) REFERENCES `Services`(`service_id` ),
FOREIGN KEY (`customer_id` ) REFERENCES `Parties`(`party_id` )
)

### Question
What are the last names of individuals who have been contact individuals for an organization?

### SQL
SELECT DISTINCT t1.individual_last_name FROM individuals AS t1 JOIN organization_contact_individuals AS t2 ON t1.individual_id  =  t2.individual_id
### Database Schema
CREATE TABLE "driver" (
"Driver_ID" int,
"Name" text,
"Party" text,
"Home_city" text,
"Age" int,
PRIMARY KEY ("Driver_ID")
)

CREATE TABLE "school" (
"School_ID" int,
"Grade" text,
"School" text,
"Location" text,
"Type" text,
PRIMARY KEY ("School_ID")
)

CREATE TABLE "school_bus" (
"School_ID" int,
"Driver_ID" int,
"Years_Working" int,
"If_full_time" bool,
PRIMARY KEY ("School_ID","Driver_ID"),
FOREIGN KEY ("School_ID") REFERENCES `school`("School_ID"),
FOREIGN KEY ("Driver_ID") REFERENCES `driver`("Driver_ID")
)

### Question
How many drivers are there?

### SQL
SELECT count(*) FROM driver
### Database Schema
CREATE TABLE "driver" (
"Driver_ID" int,
"Name" text,
"Party" text,
"Home_city" text,
"Age" int,
PRIMARY KEY ("Driver_ID")
)

CREATE TABLE "school" (
"School_ID" int,
"Grade" text,
"School" text,
"Location" text,
"Type" text,
PRIMARY KEY ("School_ID")
)

CREATE TABLE "school_bus" (
"School_ID" int,
"Driver_ID" int,
"Years_Working" int,
"If_full_time" bool,
PRIMARY KEY ("School_ID","Driver_ID"),
FOREIGN KEY ("School_ID") REFERENCES `school`("School_ID"),
FOREIGN KEY ("Driver_ID") REFERENCES `driver`("Driver_ID")
)

### Question
Show the name, home city, and age for all drivers.

### SQL
SELECT name ,  home_city ,  age FROM driver
### Database Schema
CREATE TABLE "driver" (
"Driver_ID" int,
"Name" text,
"Party" text,
"Home_city" text,
"Age" int,
PRIMARY KEY ("Driver_ID")
)

CREATE TABLE "school" (
"School_ID" int,
"Grade" text,
"School" text,
"Location" text,
"Type" text,
PRIMARY KEY ("School_ID")
)

CREATE TABLE "school_bus" (
"School_ID" int,
"Driver_ID" int,
"Years_Working" int,
"If_full_time" bool,
PRIMARY KEY ("School_ID","Driver_ID"),
FOREIGN KEY ("School_ID") REFERENCES `school`("School_ID"),
FOREIGN KEY ("Driver_ID") REFERENCES `driver`("Driver_ID")
)

### Question
Show the party and the number of drivers in each party.

### SQL
SELECT party ,  count(*) FROM driver GROUP BY party
### Database Schema
CREATE TABLE "driver" (
"Driver_ID" int,
"Name" text,
"Party" text,
"Home_city" text,
"Age" int,
PRIMARY KEY ("Driver_ID")
)

CREATE TABLE "school" (
"School_ID" int,
"Grade" text,
"School" text,
"Location" text,
"Type" text,
PRIMARY KEY ("School_ID")
)

CREATE TABLE "school_bus" (
"School_ID" int,
"Driver_ID" int,
"Years_Working" int,
"If_full_time" bool,
PRIMARY KEY ("School_ID","Driver_ID"),
FOREIGN KEY ("School_ID") REFERENCES `school`("School_ID"),
FOREIGN KEY ("Driver_ID") REFERENCES `driver`("Driver_ID")
)

### Question
Show the name of drivers in descending order of age.

### SQL
SELECT name FROM driver ORDER BY age DESC
### Database Schema
CREATE TABLE "driver" (
"Driver_ID" int,
"Name" text,
"Party" text,
"Home_city" text,
"Age" int,
PRIMARY KEY ("Driver_ID")
)

CREATE TABLE "school" (
"School_ID" int,
"Grade" text,
"School" text,
"Location" text,
"Type" text,
PRIMARY KEY ("School_ID")
)

CREATE TABLE "school_bus" (
"School_ID" int,
"Driver_ID" int,
"Years_Working" int,
"If_full_time" bool,
PRIMARY KEY ("School_ID","Driver_ID"),
FOREIGN KEY ("School_ID") REFERENCES `school`("School_ID"),
FOREIGN KEY ("Driver_ID") REFERENCES `driver`("Driver_ID")
)

### Question
Show all different home cities.

### SQL
SELECT DISTINCT home_city FROM driver
### Database Schema
CREATE TABLE "driver" (
"Driver_ID" int,
"Name" text,
"Party" text,
"Home_city" text,
"Age" int,
PRIMARY KEY ("Driver_ID")
)

CREATE TABLE "school" (
"School_ID" int,
"Grade" text,
"School" text,
"Location" text,
"Type" text,
PRIMARY KEY ("School_ID")
)

CREATE TABLE "school_bus" (
"School_ID" int,
"Driver_ID" int,
"Years_Working" int,
"If_full_time" bool,
PRIMARY KEY ("School_ID","Driver_ID"),
FOREIGN KEY ("School_ID") REFERENCES `school`("School_ID"),
FOREIGN KEY ("Driver_ID") REFERENCES `driver`("Driver_ID")
)

### Question
Show the home city with the most number of drivers.

### SQL
SELECT home_city FROM driver GROUP BY home_city ORDER BY count(*) DESC LIMIT 1
### Database Schema
CREATE TABLE "driver" (
"Driver_ID" int,
"Name" text,
"Party" text,
"Home_city" text,
"Age" int,
PRIMARY KEY ("Driver_ID")
)

CREATE TABLE "school" (
"School_ID" int,
"Grade" text,
"School" text,
"Location" text,
"Type" text,
PRIMARY KEY ("School_ID")
)

CREATE TABLE "school_bus" (
"School_ID" int,
"Driver_ID" int,
"Years_Working" int,
"If_full_time" bool,
PRIMARY KEY ("School_ID","Driver_ID"),
FOREIGN KEY ("School_ID") REFERENCES `school`("School_ID"),
FOREIGN KEY ("Driver_ID") REFERENCES `driver`("Driver_ID")
)

### Question
Show the party with drivers from Hartford and drivers older than 40.

### SQL
SELECT party FROM driver WHERE home_city  =  'Hartford' AND age  >  40
### Database Schema
CREATE TABLE "driver" (
"Driver_ID" int,
"Name" text,
"Party" text,
"Home_city" text,
"Age" int,
PRIMARY KEY ("Driver_ID")
)

CREATE TABLE "school" (
"School_ID" int,
"Grade" text,
"School" text,
"Location" text,
"Type" text,
PRIMARY KEY ("School_ID")
)

CREATE TABLE "school_bus" (
"School_ID" int,
"Driver_ID" int,
"Years_Working" int,
"If_full_time" bool,
PRIMARY KEY ("School_ID","Driver_ID"),
FOREIGN KEY ("School_ID") REFERENCES `school`("School_ID"),
FOREIGN KEY ("Driver_ID") REFERENCES `driver`("Driver_ID")
)

### Question
Show home city where at least two drivers older than 40 are from.

### SQL
SELECT home_city FROM driver WHERE age  >  40 GROUP BY home_city HAVING count(*)  >=  2
### Database Schema
CREATE TABLE "driver" (
"Driver_ID" int,
"Name" text,
"Party" text,
"Home_city" text,
"Age" int,
PRIMARY KEY ("Driver_ID")
)

CREATE TABLE "school" (
"School_ID" int,
"Grade" text,
"School" text,
"Location" text,
"Type" text,
PRIMARY KEY ("School_ID")
)

CREATE TABLE "school_bus" (
"School_ID" int,
"Driver_ID" int,
"Years_Working" int,
"If_full_time" bool,
PRIMARY KEY ("School_ID","Driver_ID"),
FOREIGN KEY ("School_ID") REFERENCES `school`("School_ID"),
FOREIGN KEY ("Driver_ID") REFERENCES `driver`("Driver_ID")
)

### Question
Show all home cities except for those having a driver older than 40.

### SQL
SELECT home_city FROM driver EXCEPT SELECT home_city FROM driver WHERE age  >  40
### Database Schema
CREATE TABLE "driver" (
"Driver_ID" int,
"Name" text,
"Party" text,
"Home_city" text,
"Age" int,
PRIMARY KEY ("Driver_ID")
)

CREATE TABLE "school" (
"School_ID" int,
"Grade" text,
"School" text,
"Location" text,
"Type" text,
PRIMARY KEY ("School_ID")
)

CREATE TABLE "school_bus" (
"School_ID" int,
"Driver_ID" int,
"Years_Working" int,
"If_full_time" bool,
PRIMARY KEY ("School_ID","Driver_ID"),
FOREIGN KEY ("School_ID") REFERENCES `school`("School_ID"),
FOREIGN KEY ("Driver_ID") REFERENCES `driver`("Driver_ID")
)

### Question
Show the names of the drivers without a school bus.

### SQL
SELECT name FROM driver WHERE driver_id NOT IN (SELECT driver_id FROM school_bus)
### Database Schema
CREATE TABLE "driver" (
"Driver_ID" int,
"Name" text,
"Party" text,
"Home_city" text,
"Age" int,
PRIMARY KEY ("Driver_ID")
)

CREATE TABLE "school" (
"School_ID" int,
"Grade" text,
"School" text,
"Location" text,
"Type" text,
PRIMARY KEY ("School_ID")
)

CREATE TABLE "school_bus" (
"School_ID" int,
"Driver_ID" int,
"Years_Working" int,
"If_full_time" bool,
PRIMARY KEY ("School_ID","Driver_ID"),
FOREIGN KEY ("School_ID") REFERENCES `school`("School_ID"),
FOREIGN KEY ("Driver_ID") REFERENCES `driver`("Driver_ID")
)

### Question
Show the types of schools that have two schools.

### SQL
SELECT TYPE FROM school GROUP BY TYPE HAVING count(*)  =  2
### Database Schema
CREATE TABLE "driver" (
"Driver_ID" int,
"Name" text,
"Party" text,
"Home_city" text,
"Age" int,
PRIMARY KEY ("Driver_ID")
)

CREATE TABLE "school" (
"School_ID" int,
"Grade" text,
"School" text,
"Location" text,
"Type" text,
PRIMARY KEY ("School_ID")
)

CREATE TABLE "school_bus" (
"School_ID" int,
"Driver_ID" int,
"Years_Working" int,
"If_full_time" bool,
PRIMARY KEY ("School_ID","Driver_ID"),
FOREIGN KEY ("School_ID") REFERENCES `school`("School_ID"),
FOREIGN KEY ("Driver_ID") REFERENCES `driver`("Driver_ID")
)

### Question
Show the school name and driver name for all school buses.

### SQL
SELECT T2.school ,  T3.name FROM school_bus AS T1 JOIN school AS T2 ON T1.school_id  =  T2.school_id JOIN driver AS T3 ON T1.driver_id  =  T3.driver_id
### Database Schema
CREATE TABLE "driver" (
"Driver_ID" int,
"Name" text,
"Party" text,
"Home_city" text,
"Age" int,
PRIMARY KEY ("Driver_ID")
)

CREATE TABLE "school" (
"School_ID" int,
"Grade" text,
"School" text,
"Location" text,
"Type" text,
PRIMARY KEY ("School_ID")
)

CREATE TABLE "school_bus" (
"School_ID" int,
"Driver_ID" int,
"Years_Working" int,
"If_full_time" bool,
PRIMARY KEY ("School_ID","Driver_ID"),
FOREIGN KEY ("School_ID") REFERENCES `school`("School_ID"),
FOREIGN KEY ("Driver_ID") REFERENCES `driver`("Driver_ID")
)

### Question
What is the maximum, minimum and average years spent working on a school bus?

### SQL
SELECT max(years_working) ,  min(years_working) ,  avg(years_working) FROM school_bus
### Database Schema
CREATE TABLE "driver" (
"Driver_ID" int,
"Name" text,
"Party" text,
"Home_city" text,
"Age" int,
PRIMARY KEY ("Driver_ID")
)

CREATE TABLE "school" (
"School_ID" int,
"Grade" text,
"School" text,
"Location" text,
"Type" text,
PRIMARY KEY ("School_ID")
)

CREATE TABLE "school_bus" (
"School_ID" int,
"Driver_ID" int,
"Years_Working" int,
"If_full_time" bool,
PRIMARY KEY ("School_ID","Driver_ID"),
FOREIGN KEY ("School_ID") REFERENCES `school`("School_ID"),
FOREIGN KEY ("Driver_ID") REFERENCES `driver`("Driver_ID")
)

### Question
Show the school name and type for schools without a school bus.

### SQL
SELECT school ,  TYPE FROM school WHERE school_id NOT IN (SELECT school_id FROM school_bus)
### Database Schema
CREATE TABLE "driver" (
"Driver_ID" int,
"Name" text,
"Party" text,
"Home_city" text,
"Age" int,
PRIMARY KEY ("Driver_ID")
)

CREATE TABLE "school" (
"School_ID" int,
"Grade" text,
"School" text,
"Location" text,
"Type" text,
PRIMARY KEY ("School_ID")
)

CREATE TABLE "school_bus" (
"School_ID" int,
"Driver_ID" int,
"Years_Working" int,
"If_full_time" bool,
PRIMARY KEY ("School_ID","Driver_ID"),
FOREIGN KEY ("School_ID") REFERENCES `school`("School_ID"),
FOREIGN KEY ("Driver_ID") REFERENCES `driver`("Driver_ID")
)

### Question
Show the type of school and the number of buses for each type.

### SQL
SELECT T2.type ,  count(*) FROM school_bus AS T1 JOIN school AS T2 ON T1.school_id  =  T2.school_id GROUP BY T2.type