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 those employees who joined after 7th September, 1987.
assistant
SELECT * FROM employees WHERE hire_date > '1987-09-07'
human
when is the hire date for those employees whose first name does not containing the letter M?
assistant
SELECT hire_date FROM employees WHERE first_name NOT LIKE '%M%'
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
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
display all the details from Employees table for those employees who was hired before 2002-06-21.
assistant
SELECT * FROM employees WHERE hire_date < '2002-06-21'
human
Find job id and date of hire for those employees who was hired between November 5th, 2007 and July 5th, 2009.
assistant
SELECT job_id , hire_date FROM employees WHERE hire_date BETWEEN "2007-11-05" AND "2009-07-05" ;