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 tasks does each project have? List the task count and the project detail.
assistant
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
human
How many project staff worked as leaders or started working before '1989-04-24 23:51:54'?
assistant
SELECT count(*) FROM Project_Staff WHERE role_code = 'leader' OR date_from < '1989-04-24 23:51:54'
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
List from which date and to which date these staff work: project staff of the project which hires the most staffs
assistant
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'
human
Show the role description and the id of the project staff involved in most number of project outcomes?
assistant
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
human
For each project id, how many staff does it have? List them in increasing order.
assistant
SELECT T1.project_id , COUNT( * ) FROM Project_Staff AS T1 JOIN Projects AS T2 ON T1.project_id = T2.project_id GROUP BY T1.project_id ORDER BY COUNT( * ) ASC ;