CREATE TABLE Document_Types (
    document_type_code TEXT PRIMARY KEY,
    document_description TEXT
);
CREATE TABLE Documents (
    document_id NUMBER PRIMARY KEY,
    document_type_code TEXT,
    grant_id NUMBER,
    sent_date TIME,
    response_received_date TIME,
    other_details TEXT,
    FOREIGN KEY ("grant_id") REFERENCES Grants("grant_id"),
    FOREIGN KEY ("document_type_code") REFERENCES Document_Types("document_type_code")
);
CREATE TABLE Grants (
    grant_id NUMBER PRIMARY KEY,
    organisation_id NUMBER,
    grant_amount NUMBER,
    grant_start_date TIME,
    grant_end_date TIME,
    other_details TEXT,
    FOREIGN KEY ("organisation_id") REFERENCES Organisations("organisation_id")
);
CREATE TABLE Organisation_Types (
    organisation_type TEXT PRIMARY KEY,
    organisation_type_description TEXT
);
CREATE TABLE Organisations (
    organisation_id NUMBER PRIMARY KEY,
    organisation_type TEXT,
    organisation_details TEXT,
    FOREIGN KEY ("organisation_type") REFERENCES Organisation_Types("organisation_type")
);
CREATE TABLE Project_Outcomes (
    project_id NUMBER,
    outcome_code TEXT,
    outcome_details TEXT,
    FOREIGN KEY ("outcome_code") REFERENCES Research_Outcomes("outcome_code"),
    FOREIGN KEY ("project_id") REFERENCES Projects("project_id")
);
CREATE TABLE Project_Staff (
    staff_id NUMBER PRIMARY KEY,
    project_id NUMBER,
    role_code TEXT,
    date_from TIME,
    date_to TIME,
    other_details TEXT,
    FOREIGN KEY ("role_code") REFERENCES Staff_Roles("role_code"),
    FOREIGN KEY ("project_id") REFERENCES Projects("project_id")
);
CREATE TABLE Projects (
    project_id NUMBER PRIMARY KEY,
    organisation_id NUMBER,
    project_details TEXT,
    FOREIGN KEY ("organisation_id") REFERENCES Organisations("organisation_id")
);
CREATE TABLE Research_Outcomes (
    outcome_code TEXT PRIMARY KEY,
    outcome_description TEXT
);
CREATE TABLE Research_Staff (
    staff_id NUMBER PRIMARY KEY,
    employer_organisation_id NUMBER,
    staff_details TEXT,
    FOREIGN KEY ("employer_organisation_id") REFERENCES Organisations("organisation_id")
);
CREATE TABLE Staff_Roles (
    role_code TEXT PRIMARY KEY,
    role_description TEXT
);
CREATE TABLE Tasks (
    task_id NUMBER PRIMARY KEY,
    project_id NUMBER,
    task_details TEXT,
    "eg Agree Objectives" TEXT,
    FOREIGN KEY ("project_id") REFERENCES Projects("project_id")
);

Question: What are the send dates for all documents that have a grant amount of more than 5000 and are involved in research?
SQL: SELECT T1.sent_date FROM documents AS T1 JOIN Grants AS T2 ON T1.grant_id  =  T2.grant_id JOIN Organisations AS T3 ON T2.organisation_id  =  T3.organisation_id JOIN organisation_Types AS T4 ON T3.organisation_type  =  T4.organisation_type WHERE T2.grant_amount  >  5000 AND T4.organisation_type_description  =  'Research'

Question: What are the response received dates for the documents described as 'Regular' or granted with more than 100?
SQL: SELECT T1.response_received_date FROM Documents AS T1 JOIN Document_Types AS T2 ON T1.document_type_code  =  T2.document_type_code JOIN Grants AS T3 ON T1.grant_id  =  T3.grant_id WHERE T2.document_description  =  'Regular' OR T3.grant_amount  >  100

Question: What is the response received date for the document described as Regular that was granted more than 100 dollars?
SQL: SELECT T1.response_received_date FROM Documents AS T1 JOIN Document_Types AS T2 ON T1.document_type_code  =  T2.document_type_code JOIN Grants AS T3 ON T1.grant_id  =  T3.grant_id WHERE T2.document_description  =  'Regular' OR T3.grant_amount  >  100

Question: List the project details of the projects which did not hire any staff for a researcher role.
SQL: SELECT project_details FROM Projects WHERE project_id NOT IN ( SELECT project_id FROM Project_Staff WHERE role_code  =  'researcher' )

Question: What are the details for all projects that did not hire any staff in a research role?
SQL: SELECT project_details FROM Projects WHERE project_id NOT IN ( SELECT project_id FROM Project_Staff WHERE role_code  =  'researcher' )

Question: What are the task details, task id and project id for the projects which are detailed as 'omnis' or  have more than 2 outcomes?
SQL: SELECT T1.task_details ,  T1.task_id ,  T2.project_id FROM Tasks AS T1 JOIN Projects AS T2 ON T1.project_id  =  T2.project_id WHERE T2.project_details  =  'omnis' UNION SELECT T1.task_details ,  T1.task_id ,  T2.project_id FROM Tasks AS T1 JOIN Projects AS T2 ON T1.project_id  =  T2.project_id JOIN Project_outcomes AS T3 ON T2.project_id  =  T3.project_id GROUP BY T2.project_id HAVING count(*)  >  2

Question: What are the task details, task ids, and project ids for the progrects that are detailed as 'omnis' or have at least 3 outcomes?
SQL: SELECT T1.task_details ,  T1.task_id ,  T2.project_id FROM Tasks AS T1 JOIN Projects AS T2 ON T1.project_id  =  T2.project_id WHERE T2.project_details  =  'omnis' UNION SELECT T1.task_details ,  T1.task_id ,  T2.project_id FROM Tasks AS T1 JOIN Projects AS T2 ON T1.project_id  =  T2.project_id JOIN Project_outcomes AS T3 ON T2.project_id  =  T3.project_id GROUP BY T2.project_id HAVING count(*)  >  2

Question: When do all the researcher role staff start to work, and when do they stop working?
SQL: SELECT date_from ,  date_to FROM Project_Staff WHERE role_code  =  'researcher'

Question: When did researchers start and stop working?
SQL: SELECT date_from ,  date_to FROM Project_Staff WHERE role_code  =  'researcher'

Question: How many kinds of roles are there for the staff?
SQL: SELECT count(DISTINCT role_code) FROM Project_Staff

Question: How many different roles are there on the project staff?
SQL: SELECT count(DISTINCT role_code) FROM Project_Staff

Question: What is the total amount of grants given by each organisations? Also list the organisation id.
SQL: SELECT sum(grant_amount) ,  organisation_id FROM Grants GROUP BY organisation_id

Question: What is the total amount of grant money given to each organization and what is its id?
SQL: SELECT sum(grant_amount) ,  organisation_id FROM Grants GROUP BY organisation_id

Question: List the project details of the projects with the research outcome described with the substring 'Published'.
SQL: SELECT T1.project_details FROM Projects AS T1 JOIN Project_outcomes AS T2 ON T1.project_id  =  T2.project_id JOIN Research_outcomes AS T3 ON T2.outcome_code  =  T3.outcome_code WHERE T3.outcome_description LIKE '%Published%'

Question: What are the details for the project whose research has been published?
SQL: SELECT T1.project_details FROM Projects AS T1 JOIN Project_outcomes AS T2 ON T1.project_id  =  T2.project_id JOIN Research_outcomes AS T3 ON T2.outcome_code  =  T3.outcome_code WHERE T3.outcome_description LIKE '%Published%'

Question: How many staff does each project has? List the project id and the number in an ascending order.
SQL: SELECT T1.project_id ,  count(*) FROM Project_Staff AS T1 JOIN Projects AS T2 ON T1.project_id  =  T2.project_id GROUP BY T1.project_id ORDER BY count(*) ASC

Question: For each project id, how many staff does it have? List them in increasing order.
SQL: SELECT T1.project_id ,  count(*) FROM Project_Staff AS T1 JOIN Projects AS T2 ON T1.project_id  =  T2.project_id GROUP BY T1.project_id ORDER BY count(*) ASC

Question: What is the complete description of the researcher role.
SQL: SELECT role_description FROM Staff_Roles WHERE role_code  =  'researcher'

Question: What is the complete description of the job of a researcher?
SQL: SELECT role_description FROM Staff_Roles WHERE role_code  =  'researcher'

Question: When did the first staff for the projects started working?
SQL: SELECT date_from FROM Project_Staff ORDER BY date_from ASC LIMIT 1

Question: When did the first staff member start working?
SQL: SELECT date_from FROM Project_Staff ORDER BY date_from ASC LIMIT 1