human
Schema:
Document_Types (document_type_code, document_description)
Documents (document_id, document_type_code, grant_id, sent_date, response_received_date, other_details)
Grants (grant_id, organisation_id, grant_amount, grant_start_date, grant_end_date, other_details)
Organisation_Types (organisation_type, organisation_type_description)
Organisations (organisation_id, organisation_type, organisation_details)
Project_Outcomes (project_id, outcome_code, outcome_details)
Project_Staff (staff_id, project_id, role_code, date_from, date_to, other_details)
Projects (project_id, organisation_id, project_details)
Research_Outcomes (outcome_code, outcome_description)
Research_Staff (staff_id, employer_organisation_id, staff_details)
Staff_Roles (role_code, role_description)
Tasks (task_id, project_id, task_details, eg Agree Objectives)
Please answer with SQL.
assistant
Sure, I understand.
human
How many Patent outcomes are generated from all the projects?
assistant
SELECT count(*) FROM Project_outcomes WHERE outcome_code = 'Patent'
human
Find out the send dates of the documents with the grant amount of more than 5000 were granted by organisation type described
assistant
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'
human
How many documents can one grant have at most? List the grant id and number.
assistant
SELECT grant_id , count(*) FROM Documents GROUP BY grant_id ORDER BY count(*) DESC LIMIT 1
human
What is the total amount of grants given by each organisations? Also list the organisation id.
assistant
SELECT sum(grant_amount) , organisation_id FROM Grants GROUP BY organisation_id
human
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'?
assistant
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'
human
What is the total amount of grant money for research?
assistant
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" ;