human
Schema:
Addresses (address_id, line_1_number_building, town_city, zip_postcode, state_province_county, country)
Services (service_id, service_type_code, service_name, service_descriptio)
Forms (form_id, form_type_code, service_id, form_number, form_name, form_description)
Individuals (individual_id, individual_first_name, individual_middle_name, inidividual_phone, individual_email, individual_address, individual_last_name)
Organizations (organization_id, date_formed, organization_name, uk_vat_number)
Parties (party_id, payment_method_code, party_phone, party_email)
Organization_Contact_Individuals (individual_id, organization_id, date_contact_from, date_contact_to)
Party_Addresses (party_id, address_id, date_address_from, address_type_code, date_address_to)
Party_Forms (party_id, form_id, date_completion_started, form_status_code, date_fully_completed)
Party_Services (booking_id, customer_id, service_id, service_datetime, booking_made_date)
Please answer with SQL.
assistant
Sure, I understand.
human
Find the last name of the first ever contact person of the organization with the highest UK Vat number.
assistant
SELECT t3.individual_last_name FROM organizations AS t1 JOIN organization_contact_individuals AS t2 ON t1.organization_id = t2.organization_id JOIN individuals AS t3 ON t2.individual_id = t3.individual_id WHERE t1.uk_vat_number = (SELECT max(uk_vat_number) FROM organizations) ORDER BY t2.date_contact_to ASC LIMIT 1
human
Find the last name of the latest contact individual of the organization 'Labour Party'.
assistant
SELECT t3.individual_last_name FROM organizations AS t1 JOIN organization_contact_individuals AS t2 ON t1.organization_id = t2.organization_id JOIN individuals AS t3 ON t2.individual_id = t3.individual_id WHERE t1.organization_name = 'Labour Party' ORDER BY t2.date_contact_to DESC LIMIT 1
human
Which is the email of the party that has used the services the most number of times?
assistant
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
human
Find the name of all the cities and states.
assistant
SELECT town_city FROM addresses UNION SELECT state_province_county FROM addresses
human
How many cities are there in state 'Colorado'?
assistant
SELECT count(*) FROM addresses WHERE state_province_county = 'Colorado'
human
Which state can address '6862 Kaitlyn Knolls' possibly be in?
assistant
SELECT state_province_county FROM addresses WHERE line_1_number_building LIKE "%6862 Kaitlyn Knolls%" ;