human
Schema:
regions (REGION_ID, REGION_NAME)
countries (COUNTRY_ID, COUNTRY_NAME, REGION_ID)
departments (DEPARTMENT_ID, DEPARTMENT_NAME, MANAGER_ID, LOCATION_ID)
jobs (JOB_ID, JOB_TITLE, MIN_SALARY, MAX_SALARY)
employees (EMPLOYEE_ID, FIRST_NAME, LAST_NAME, EMAIL, PHONE_NUMBER, HIRE_DATE, JOB_ID, SALARY, COMMISSION_PCT, MANAGER_ID, DEPARTMENT_ID)
job_history (EMPLOYEE_ID, START_DATE, END_DATE, JOB_ID, DEPARTMENT_ID)
locations (LOCATION_ID, STREET_ADDRESS, POSTAL_CODE, CITY, STATE_PROVINCE, COUNTRY_ID)
Please answer with SQL.
assistant
Sure, I understand.
human
display the employee ID for each employee and the date on which he ended his previous job.
assistant
SELECT employee_id , MAX(end_date) FROM job_history GROUP BY employee_id
human
Find the ids of the departments where any manager is managing 4 or more employees.
assistant
SELECT DISTINCT department_id FROM employees GROUP BY department_id , manager_id HAVING COUNT(employee_id) >= 4
human
display the employee ID and job name for all those jobs in department 80.
assistant
SELECT T1.employee_id , T2.job_title FROM employees AS T1 JOIN jobs AS T2 ON T1.job_id = T2.job_id WHERE T1.department_id = 80
human
What is the first name and job id for all employees in the Finance department?
assistant
SELECT T1.first_name , T1.job_id FROM employees AS T1 JOIN departments AS T2 ON T1.department_id = T2.department_id WHERE T2.department_name = 'Finance'
human
display the ID for those employees who did two or more jobs in the past.
assistant
SELECT employee_id FROM job_history GROUP BY employee_id HAVING COUNT(*) >= 2
human
What are the job ids for jobs done more than once for a period of more than 300 days?
assistant
SELECT job_id FROM job_history WHERE end_date - start_date > 300 GROUP BY job_id HAVING COUNT( * ) >= 2 ;