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 number, name( first name and last name ), and salary for all employees who earn more than the average salary and who work in a department with any employee with a 'J' in their first name.
assistant
SELECT employee_id , first_name , last_name , salary FROM employees WHERE salary > ( SELECT AVG (salary) FROM employees ) AND department_id IN ( SELECT department_id FROM employees WHERE first_name LIKE '%J%')
human
Find the employee id for all employees who earn more than the average salary.
assistant
SELECT employee_id FROM employees WHERE salary > (SELECT AVG(salary) FROM employees)
human
display the job title of jobs which minimum salary is greater than 9000.
assistant
SELECT job_title FROM jobs WHERE min_salary > 9000
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
display the employee number, name( first name and last name ) and job title for all employees whose salary is more than any salary of those employees whose job title is PU_MAN.
assistant
SELECT employee_id , first_name , last_name , job_id FROM employees WHERE salary > ( SELECT max(salary) FROM employees WHERE job_id = 'PU_MAN' )
human
display the employee number and job id for all employees whose salary is smaller than any salary of those employees whose job title is MK_MAN.
assistant
SELECT employee_id , job_id FROM employees WHERE salary < ( SELECT MIN( salary ) FROM employees WHERE job_id = "MK_MAN" ) ;