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: Which project made the most number of outcomes? List the project details and the project id.
SQL: SELECT T1.project_details ,  T1.project_id FROM Projects AS T1 JOIN Project_outcomes AS T2 ON T1.project_id  =  T2.project_id GROUP BY T1.project_id ORDER BY count(*) DESC LIMIT 1

Question: What are the details and id of the project with the most outcomes?
SQL: SELECT T1.project_details ,  T1.project_id FROM Projects AS T1 JOIN Project_outcomes AS T2 ON T1.project_id  =  T2.project_id GROUP BY T1.project_id ORDER BY count(*) DESC LIMIT 1

Question: Which projects have no outcome? List the project details.
SQL: SELECT project_details FROM Projects WHERE project_id NOT IN ( SELECT project_id FROM Project_outcomes )

Question: What are the details of the project with no outcomes?
SQL: SELECT project_details FROM Projects WHERE project_id NOT IN ( SELECT project_id FROM Project_outcomes )

Question: Which organisation hired the most number of research staff? List the organisation id, type and detail.
SQL: SELECT T1.organisation_id ,  T1.organisation_type ,  T1.organisation_details 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 are the ids, types, and details of the organization with the most research staff?
SQL: SELECT T1.organisation_id ,  T1.organisation_type ,  T1.organisation_details 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: Show the role description and the id of the project staff involved in most number of project outcomes?
SQL: SELECT T1.role_description ,  T2.staff_id FROM Staff_Roles AS T1 JOIN Project_Staff AS T2 ON T1.role_code  =  T2.role_code JOIN Project_outcomes AS T3 ON T2.project_id  =  T3.project_id GROUP BY T2.staff_id ORDER BY count(*) DESC LIMIT 1

Question: For each staff id, what is the description of the role that is involved with the most number of projects?
SQL: SELECT T1.role_description ,  T2.staff_id FROM Staff_Roles AS T1 JOIN Project_Staff AS T2 ON T1.role_code  =  T2.role_code JOIN Project_outcomes AS T3 ON T2.project_id  =  T3.project_id GROUP BY T2.staff_id ORDER BY count(*) DESC LIMIT 1

Question: Which document type is described with the prefix 'Initial'?
SQL: SELECT document_type_code FROM Document_Types WHERE document_description LIKE 'Initial%'

Question: What is the type of the document whose description starts with the word 'Initial'?
SQL: SELECT document_type_code FROM Document_Types WHERE document_description LIKE 'Initial%'

Question: For grants with both documents described as 'Regular' and documents described as 'Initial Application', list its start date.
SQL: SELECT T1.grant_start_date FROM Grants AS T1 JOIN Documents AS T2 ON T1.grant_id  =  T2.grant_id JOIN Document_Types AS T3 ON T2.document_type_code  =  T3.document_type_code WHERE T3.document_description  =  'Regular' INTERSECT SELECT T1.grant_start_date FROM Grants AS T1 JOIN Documents AS T2 ON T1.grant_id  =  T2.grant_id JOIN Document_Types AS T3 ON T2.document_type_code  =  T3.document_type_code WHERE T3.document_description  =  'Initial Application'

Question: For grants that have descriptions of Regular and Initial Applications, what are their start dates?
SQL: SELECT T1.grant_start_date FROM Grants AS T1 JOIN Documents AS T2 ON T1.grant_id  =  T2.grant_id JOIN Document_Types AS T3 ON T2.document_type_code  =  T3.document_type_code WHERE T3.document_description  =  'Regular' INTERSECT SELECT T1.grant_start_date FROM Grants AS T1 JOIN Documents AS T2 ON T1.grant_id  =  T2.grant_id JOIN Document_Types AS T3 ON T2.document_type_code  =  T3.document_type_code WHERE T3.document_description  =  'Initial Application'

Question: How many documents can one grant have at most? List the grant id and number.
SQL: SELECT grant_id ,  count(*) FROM Documents GROUP BY grant_id ORDER BY count(*) DESC LIMIT 1

Question: For each grant id, how many documents does it have, and which one has the most?
SQL: SELECT grant_id ,  count(*) FROM Documents GROUP BY grant_id ORDER BY count(*) DESC LIMIT 1

Question: Find the organisation type description of the organisation detailed as 'quo'.
SQL: SELECT T1.organisation_type_description FROM organisation_Types AS T1 JOIN Organisations AS T2 ON T1.organisation_type  =  T2.organisation_type WHERE T2.organisation_details  =  'quo'

Question: What is the type description of the organization whose detail is listed as 'quo'?
SQL: SELECT T1.organisation_type_description FROM organisation_Types AS T1 JOIN Organisations AS T2 ON T1.organisation_type  =  T2.organisation_type WHERE T2.organisation_details  =  'quo'

Question: What are all the details of the organisations described as 'Sponsor'? Sort the result in an ascending order.
SQL: SELECT organisation_details FROM Organisations AS T1 JOIN organisation_Types AS T2 ON T1.organisation_type  =  T2.organisation_type WHERE T2.organisation_type_description  =  'Sponsor' ORDER BY organisation_details

Question: What are the details of all organizations that are described as Sponsors and sort the results in ascending order?
SQL: SELECT organisation_details FROM Organisations AS T1 JOIN organisation_Types AS T2 ON T1.organisation_type  =  T2.organisation_type WHERE T2.organisation_type_description  =  'Sponsor' ORDER BY organisation_details