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
List out projects which did not hire staff for researcher role ?
assistant
SELECT project_id FROM Projects WHERE project_id NOT IN ( SELECT project_id FROM Project_Staff WHERE role_code = 'researcher' );
human
what is the role of the project id 5?
assistant
SELECT role_code FROM Project_Staff WHERE project_id = 5;
human
Can you tell me the role of project id 1 ?
assistant
SELECT role_code FROM Project_Staff WHERE project_id = 1;
human
List out projects which has staff for researcher role ?
assistant
SELECT project_id FROM Project_Staff WHERE role_code = 'researcher';
human
Can you tell me the organization id for the project id 1 ?
assistant
SELECT organisation_id FROM Projects WHERE project_id = 1;
human
can you tell me the staff id of the project which did not have researcher role ?
assistant
SELECT T1.staff_id FROM Project_Staff AS T1 JOIN Projects AS T2 WHERE T1.project_id NOT IN ( SELECT project_id FROM Project_Staff WHERE role_code = 'researcher' );
human
tell me project details about the project id 1 ?
assistant
SELECT project_details FROM Projects WHERE project_id = 1;
human
List out total number of projects which has no staff for researcher role ?
assistant
SELECT COUNT( * ) FROM Projects WHERE project_id NOT IN ( SELECT project_id FROM Project_Staff WHERE role_code = 'researcher' );
human
List the project details of the projects which did not hire any staff for a researcher role ?
assistant
SELECT project_details FROM Projects WHERE project_id NOT IN ( SELECT project_id FROM Project_Staff WHERE role_code = "researcher" ) ;