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
Which organisation hired the most number of research staff? List the organisation id, type and detail.
assistant
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
human
List the project details of the projects launched by the organisation
assistant
SELECT project_details FROM Projects WHERE organisation_id IN ( SELECT organisation_id FROM Projects GROUP BY organisation_id ORDER BY count(*) DESC LIMIT 1 )
human
What are all the details of the organisations described as 'Sponsor'? Sort the result in an ascending order.
assistant
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
human
Find the organisation ids and details of the organisations which are involved in
assistant
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
human
What is the organisation type and id of the organisation which has the most number of research staff?
assistant
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
human
What is the type description of the organization whose detail is listed as 'quo'?
assistant
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" ;