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 distinct grant amount for the grants where the documents were sent before '1986-08-26 20:49:27' and grant were ended after '1989-03-16 18:27:16'?
SQL: SELECT T1.grant_amount FROM Grants AS T1 JOIN Documents AS T2 ON T1.grant_id  =  T2.grant_id WHERE T2.sent_date  <  '1986-08-26 20:49:27' INTERSECT SELECT grant_amount FROM grants WHERE grant_end_date  >  '1989-03-16 18:27:16'

Question: What are the different grant amounts for documents sent before '1986-08-26 20:49:27' and after the grant ended on '1989-03-16 18:27:16'?
SQL: SELECT T1.grant_amount FROM Grants AS T1 JOIN Documents AS T2 ON T1.grant_id  =  T2.grant_id WHERE T2.sent_date  <  '1986-08-26 20:49:27' INTERSECT SELECT grant_amount FROM grants WHERE grant_end_date  >  '1989-03-16 18:27:16'

Question: List the project details of the project both producing patent and paper as outcomes.
SQL: SELECT T1.project_details FROM Projects AS T1 JOIN Project_outcomes AS T2 ON T1.project_id  =  T2.project_id WHERE T2.outcome_code  =  'Paper' INTERSECT SELECT T1.project_details FROM Projects AS T1 JOIN Project_outcomes AS T2 ON T1.project_id  =  T2.project_id WHERE T2.outcome_code  =  'Patent'

Question: What are the details of the project that is producing both patents and papers as outcomes?
SQL: SELECT T1.project_details FROM Projects AS T1 JOIN Project_outcomes AS T2 ON T1.project_id  =  T2.project_id WHERE T2.outcome_code  =  'Paper' INTERSECT SELECT T1.project_details FROM Projects AS T1 JOIN Project_outcomes AS T2 ON T1.project_id  =  T2.project_id WHERE T2.outcome_code  =  'Patent'

Question: What is the total grant amount of the organisations described as research?
SQL: SELECT sum(grant_amount) FROM Grants AS T1 JOIN Organisations AS T2 ON T1.organisation_id  =  T2.organisation_id JOIN organisation_Types AS T3 ON T2.organisation_type  =  T3.organisation_type WHERE T3.organisation_type_description  =  'Research'

Question: What is the total amount of grant money for research?
SQL: SELECT sum(grant_amount) FROM Grants AS T1 JOIN Organisations AS T2 ON T1.organisation_id  =  T2.organisation_id JOIN organisation_Types AS T3 ON T2.organisation_type  =  T3.organisation_type WHERE T3.organisation_type_description  =  'Research'

Question: List from which date and to which date these staff work: project staff of the project which hires the most staffs
SQL: SELECT date_from ,  date_to FROM Project_Staff WHERE project_id IN( SELECT project_id FROM Project_Staff GROUP BY project_id ORDER BY count(*) DESC LIMIT 1 ) UNION SELECT date_from ,  date_to FROM Project_Staff WHERE role_code  =  'leader'

Question: From what date and to what date do the staff work on a project that has the most staff and has staff in a leader role?
SQL: SELECT date_from ,  date_to FROM Project_Staff WHERE project_id IN( SELECT project_id FROM Project_Staff GROUP BY project_id ORDER BY count(*) DESC LIMIT 1 ) UNION SELECT date_from ,  date_to FROM Project_Staff WHERE role_code  =  'leader'

Question: Find the organisation ids and details of the organisations which are involved in
SQL: SELECT T2.organisation_id ,  T2.organisation_details FROM Grants AS T1 JOIN Organisations AS T2 ON T1.organisation_id  =  T2.organisation_id GROUP BY T2.organisation_id HAVING sum(T1.grant_amount)  >  6000

Question: What are the ids and details for all organizations that have grants of more than 6000 dollars?
SQL: SELECT T2.organisation_id ,  T2.organisation_details FROM Grants AS T1 JOIN Organisations AS T2 ON T1.organisation_id  =  T2.organisation_id GROUP BY T2.organisation_id HAVING sum(T1.grant_amount)  >  6000

Question: What is the organisation type and id of the organisation which has the most number of research staff?
SQL: SELECT T1.organisation_type ,  T1.organisation_id FROM Organisations AS T1 JOIN Research_Staff AS T2 ON T1.organisation_id  =  T2.employer_organisation_id GROUP BY T1.organisation_id ORDER BY count(*) DESC LIMIT 1

Question: What is the type and id of the organization that has the most research staff?
SQL: SELECT T1.organisation_type ,  T1.organisation_id FROM Organisations AS T1 JOIN Research_Staff AS T2 ON T1.organisation_id  =  T2.employer_organisation_id GROUP BY T1.organisation_id ORDER BY count(*) DESC LIMIT 1

Question: Which organisation type hires most research staff?
SQL: SELECT T1.organisation_type FROM Organisations AS T1 JOIN Research_Staff AS T2 ON T1.organisation_id  =  T2.employer_organisation_id GROUP BY T1.organisation_type ORDER BY count(*) DESC LIMIT 1

Question: What is the type of the organization with the most research staff?
SQL: SELECT T1.organisation_type FROM Organisations AS T1 JOIN Research_Staff AS T2 ON T1.organisation_id  =  T2.employer_organisation_id GROUP BY T1.organisation_type ORDER BY count(*) DESC LIMIT 1

Question: Find out the send dates of the documents with the grant amount of more than 5000 were granted by organisation type described
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'