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 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 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
Find the salary and manager number for those employees who is working under a manager.
assistant
SELECT salary , manager_id FROM employees WHERE manager_id != 'null'
human
What is all the information regarding employees who are managers?
assistant
SELECT DISTINCT * FROM employees AS T1 JOIN departments AS T2 ON T1.department_id = T2.department_id WHERE T1.employee_id = T2.manager_id ;