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 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 the job ID for those jobs which average salary is above 8000.
assistant
SELECT job_id FROM employees GROUP BY job_id HAVING AVG(salary) > 8000
human
display all the information of the employees whose salary if within the range of smallest salary and 2500.
assistant
SELECT * FROM employees WHERE salary BETWEEN (SELECT MIN(salary) FROM employees) AND 2500
human
List the full name (first and last name), and salary for those employees who earn below 6000.
assistant
SELECT first_name , last_name , salary FROM employees WHERE salary < 6000
human
display all the information of those employees who did not have any job in the past.
assistant
SELECT * FROM employees WHERE employee_id NOT IN (SELECT employee_id FROM job_history)
human
Can you return all detailed info of jobs which was done by any of the employees who is presently earning a salary on and above 12000?
assistant
SELECT * FROM job_history AS T1 JOIN employees AS T2 ON T1.employee_id = T2.employee_id WHERE T2.salary >= 12000 ;