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 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 job title and average salary of employees.
assistant
SELECT job_title , AVG(salary) FROM employees AS T1 JOIN jobs AS T2 ON T1.job_id = T2.job_id GROUP BY T2.job_title
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' )
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 job Title, the difference between minimum and maximum salaries for those jobs which max salary within the range 12000 to 18000.
assistant
SELECT job_title , max_salary - min_salary FROM jobs WHERE max_salary BETWEEN 12000 AND 18000
human
display the job title of jobs which minimum salary is greater than 9000.
assistant
SELECT job_title FROM jobs WHERE min_salary > 9000 ;