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 name of the youngest organization.
assistant
SELECT organization_name FROM organizations ORDER BY date_formed DESC LIMIT 1
human
List all the name of organizations in order of the date formed.
assistant
SELECT organization_name FROM organizations ORDER BY date_formed ASC
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
What is the name of organization that has the greatest number of contact individuals?
assistant
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
human
What are the last names of individuals who have been contact individuals for an organization?
assistant
SELECT DISTINCT t1.individual_last_name FROM individuals AS t1 JOIN organization_contact_individuals AS t2 ON t1.individual_id = t2.individual_id ;