CREATE TABLE Addresses (
    address_id NUMBER PRIMARY KEY,
    line_1_number_building TEXT,
    town_city TEXT,
    zip_postcode TEXT,
    state_province_county TEXT,
    country TEXT
);
CREATE TABLE Services (
    service_id NUMBER PRIMARY KEY,
    service_type_code TEXT,
    service_name TEXT,
    service_descriptio TEXT
);
CREATE TABLE Forms (
    form_id NUMBER PRIMARY KEY,
    form_type_code TEXT,
    service_id NUMBER,
    form_number TEXT,
    form_name TEXT,
    form_description TEXT,
    FOREIGN KEY ("service_id") REFERENCES Services("service_id")
);
CREATE TABLE Individuals (
    individual_id NUMBER PRIMARY KEY,
    individual_first_name TEXT,
    individual_middle_name TEXT,
    inidividual_phone TEXT,
    individual_email TEXT,
    individual_address TEXT,
    individual_last_name TEXT
);
CREATE TABLE Organizations (
    organization_id NUMBER PRIMARY KEY,
    date_formed TIME,
    organization_name TEXT,
    uk_vat_number TEXT
);
CREATE TABLE Parties (
    party_id NUMBER PRIMARY KEY,
    payment_method_code TEXT,
    party_phone TEXT,
    party_email TEXT
);
CREATE TABLE Organization_Contact_Individuals (
    individual_id NUMBER PRIMARY KEY,
    organization_id NUMBER,
    date_contact_from TIME,
    date_contact_to TIME,
    FOREIGN KEY ("individual_id") REFERENCES Individuals("individual_id"),
    FOREIGN KEY ("organization_id") REFERENCES Organizations("organization_id")
);
CREATE TABLE Party_Addresses (
    party_id NUMBER PRIMARY KEY,
    address_id NUMBER,
    date_address_from TIME,
    address_type_code TEXT,
    date_address_to TIME,
    FOREIGN KEY ("party_id") REFERENCES Parties("party_id"),
    FOREIGN KEY ("address_id") REFERENCES Addresses("address_id")
);
CREATE TABLE Party_Forms (
    party_id NUMBER PRIMARY KEY,
    form_id NUMBER,
    date_completion_started TIME,
    form_status_code TEXT,
    date_fully_completed TIME,
    FOREIGN KEY ("form_id") REFERENCES Forms("form_id"),
    FOREIGN KEY ("party_id") REFERENCES Parties("party_id")
);
CREATE TABLE Party_Services (
    booking_id NUMBER,
    customer_id NUMBER,
    service_id NUMBER,
    service_datetime TIME,
    booking_made_date TIME,
    FOREIGN KEY ("customer_id") REFERENCES Parties("party_id"),
    FOREIGN KEY ("service_id") REFERENCES Services("service_id")
);

Question: List every individual's first name, middle name and last name in alphabetical order by last name.
SQL: SELECT individual_first_name ,  individual_middle_name ,  individual_last_name FROM individuals ORDER BY individual_last_name

Question: What are the first, middle, and last names of all individuals, ordered by last name?
SQL: SELECT individual_first_name ,  individual_middle_name ,  individual_last_name FROM individuals ORDER BY individual_last_name

Question: List all the types of forms.
SQL: SELECT DISTINCT form_type_code FROM forms

Question: What are the different types of forms?
SQL: SELECT DISTINCT form_type_code FROM forms

Question: Find the name of the most popular party form.
SQL: SELECT t1.form_name FROM forms AS t1 JOIN party_forms AS t2 ON t1.form_id  =  t2.form_id GROUP BY t2.form_id ORDER BY count(*) DESC LIMIT 1

Question: What is the name of the party form that is most common?
SQL: SELECT t1.form_name FROM forms AS t1 JOIN party_forms AS t2 ON t1.form_id  =  t2.form_id GROUP BY t2.form_id ORDER BY count(*) DESC LIMIT 1

Question: Find the payment method and phone of the party with email "enrico09@example.com".
SQL: SELECT payment_method_code ,  party_phone FROM parties WHERE party_email  =  "enrico09@example.com"

Question: What is the payment method code and party phone of the party with the email 'enrico09@example.com'?
SQL: SELECT payment_method_code ,  party_phone FROM parties WHERE party_email  =  "enrico09@example.com"

Question: Find the emails of parties with the most popular party form.
SQL: SELECT t1.party_email FROM parties AS t1 JOIN party_forms AS t2 ON t1.party_id  =  t2.party_id WHERE t2.form_id  =  (SELECT form_id FROM party_forms GROUP BY form_id ORDER BY count(*) DESC LIMIT 1)

Question: What are the party emails associated with parties that used the party form that is the most common?
SQL: SELECT t1.party_email FROM parties AS t1 JOIN party_forms AS t2 ON t1.party_id  =  t2.party_id WHERE t2.form_id  =  (SELECT form_id FROM party_forms GROUP BY form_id ORDER BY count(*) DESC LIMIT 1)

Question: List all the name of organizations in order of the date formed.
SQL: SELECT organization_name FROM organizations ORDER BY date_formed ASC

Question: What are the names of organizations, ordered by the date they were formed, ascending?
SQL: SELECT organization_name FROM organizations ORDER BY date_formed ASC

Question: Find the name of the youngest organization.
SQL: SELECT organization_name FROM organizations ORDER BY date_formed DESC LIMIT 1

Question: What is the name of the organization that was formed most recently?
SQL: SELECT organization_name FROM organizations ORDER BY date_formed DESC LIMIT 1

Question: Find the last name of the latest contact individual of the organization "Labour Party".
SQL: 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

Question: What is the last name of the contact individual from the Labour party organization who was contacted most recently?
SQL: 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

Question: Find the last name of the first ever contact person of the organization with the highest UK Vat number.
SQL: 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

Question: What is the last name of the first individual contacted from the organization with the maximum UK Vat number across all organizations?
SQL: 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

Question: How many services are there?
SQL: SELECT count(*) FROM services

Question: Count the number of services.
SQL: SELECT count(*) FROM services

Question: Find name of the services that has never been used.
SQL: SELECT service_name FROM services EXCEPT SELECT t1.service_name FROM services AS t1 JOIN party_services AS t2 ON t1.service_id  =  t2.service_id

Question: What are the names of the services that have never been used?
SQL: SELECT service_name FROM services EXCEPT SELECT t1.service_name FROM services AS t1 JOIN party_services AS t2 ON t1.service_id  =  t2.service_id

Question: Find the name of all the cities and states.
SQL: SELECT town_city FROM addresses UNION SELECT state_province_county FROM addresses

Question: What are the names of all cities and states?
SQL: SELECT town_city FROM addresses UNION SELECT state_province_county FROM addresses

Question: How many cities are there in state "Colorado"?
SQL: SELECT count(*) FROM addresses WHERE state_province_county  =  "Colorado"

Question: Count the number of cities in the state of Colorado.
SQL: SELECT count(*) FROM addresses WHERE state_province_county  =  "Colorado"