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: How many Patent outcomes are generated from all the projects?
SQL: SELECT count(*) FROM Project_outcomes WHERE outcome_code  =  'Patent'

Question: How many patents outcomes were listed for all the projects?
SQL: SELECT count(*) FROM Project_outcomes WHERE outcome_code  =  'Patent'

Question: How many project staff worked as leaders or started working before '1989-04-24 23:51:54'?
SQL: SELECT count(*) FROM Project_Staff WHERE role_code  =  'leader' OR date_from  <  '1989-04-24 23:51:54'

Question: How many project members were leaders or started working before '1989-04-24 23:51:54'?
SQL: SELECT count(*) FROM Project_Staff WHERE role_code  =  'leader' OR date_from  <  '1989-04-24 23:51:54'

Question: What is the last date of the staff leaving the projects?
SQL: SELECT date_to FROM Project_Staff ORDER BY date_to DESC LIMIT 1

Question: What is the last date that a staff member left a project?
SQL: SELECT date_to FROM Project_Staff ORDER BY date_to DESC LIMIT 1

Question: What are the result description of the project whose detail is 'sint'?
SQL: SELECT T1.outcome_description FROM Research_outcomes AS T1 JOIN Project_outcomes AS T2 ON T1.outcome_code  =  T2.outcome_code JOIN Projects AS T3 ON T2.project_id  =  T3.project_id WHERE T3.project_details  =  'sint'

Question: What is the description for the results whose project detail is 'sint'?
SQL: SELECT T1.outcome_description FROM Research_outcomes AS T1 JOIN Project_outcomes AS T2 ON T1.outcome_code  =  T2.outcome_code JOIN Projects AS T3 ON T2.project_id  =  T3.project_id WHERE T3.project_details  =  'sint'

Question: List the organisation id with the maximum outcome count, and the count.
SQL: SELECT T1.organisation_id ,  count(*) FROM Projects AS T1 JOIN Project_outcomes AS T2 ON T1.project_id  =  T2.project_id GROUP BY T1.organisation_id ORDER BY count(*) DESC LIMIT 1

Question: What is the id of the organization with the maximum number of outcomes and how many outcomes are there?
SQL: SELECT T1.organisation_id ,  count(*) FROM Projects AS T1 JOIN Project_outcomes AS T2 ON T1.project_id  =  T2.project_id GROUP BY T1.organisation_id ORDER BY count(*) DESC LIMIT 1

Question: List the project details of the projects launched by the organisation
SQL: SELECT project_details FROM Projects WHERE organisation_id IN ( SELECT organisation_id FROM Projects GROUP BY organisation_id ORDER BY count(*) DESC LIMIT 1 )

Question: What are the details for the projects which were launched by the organization with the most projects?
SQL: SELECT project_details FROM Projects WHERE organisation_id IN ( SELECT organisation_id FROM Projects GROUP BY organisation_id ORDER BY count(*) DESC LIMIT 1 )

Question: List the research staff details, and order in ascending order.
SQL: SELECT staff_details FROM Research_Staff ORDER BY staff_details ASC

Question: What details are there on the research staff? List the result in ascending alphabetical order.
SQL: SELECT staff_details FROM Research_Staff ORDER BY staff_details ASC

Question: How many tasks are there in total?
SQL: SELECT count(*) FROM Tasks

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

Question: How many tasks does each project have? List the task count and the project detail.
SQL: SELECT count(*) ,  T1.project_details FROM Projects AS T1 JOIN Tasks AS T2 ON T1.project_id  =  T2.project_id GROUP BY T1.project_id

Question: For each project id, how many tasks are there?
SQL: SELECT count(*) ,  T1.project_details FROM Projects AS T1 JOIN Tasks AS T2 ON T1.project_id  =  T2.project_id GROUP BY T1.project_id

Question: What are the staff roles of the staff who
SQL: SELECT role_code FROM Project_Staff WHERE date_from  >  '2003-04-19 15:06:20' AND date_to  <  '2016-03-15 00:33:18'

Question: What roles did staff members play between '2003-04-19 15:06:20' and '2016-03-15 00:33:18'?
SQL: SELECT role_code FROM Project_Staff WHERE date_from  >  '2003-04-19 15:06:20' AND date_to  <  '2016-03-15 00:33:18'

Question: What are the descriptions of all the project outcomes?
SQL: SELECT T1.outcome_description FROM Research_outcomes AS T1 JOIN Project_outcomes AS T2 ON T1.outcome_code  =  T2.outcome_code

Question: List the description of the outcomes for all projects.
SQL: SELECT T1.outcome_description FROM Research_outcomes AS T1 JOIN Project_outcomes AS T2 ON T1.outcome_code  =  T2.outcome_code

Question: Which role is most common for the staff?
SQL: SELECT role_code FROM Project_Staff GROUP BY role_code ORDER BY count(*) DESC LIMIT 1

Question: What is the most common role for the staff?
SQL: SELECT role_code FROM Project_Staff GROUP BY role_code ORDER BY count(*) DESC LIMIT 1