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 department ID, full name (first and last name), salary for those employees who is highest salary in every department.
assistant
SELECT first_name , last_name , salary , department_id , MAX(salary) FROM employees GROUP BY department_id
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 emails of the employees who have no commission percentage and salary within the range 7000 to 12000 and works in that department which number is 50.
assistant
SELECT email FROM employees WHERE commission_pct = 'null' AND salary BETWEEN 7000 AND 12000 AND department_id = 50
human
display the department id and the total salary for those departments which contains at least two employees.
assistant
SELECT department_id , SUM(salary) FROM employees GROUP BY department_id HAVING count(*) >= 2
human
display those departments where more than ten employees work who got a commission percentage.
assistant
SELECT department_id FROM employees GROUP BY department_id HAVING COUNT(commission_pct) > 10
human
display the average salary of employees for each department who gets a commission percentage.
assistant
SELECT department_id , AVG ( salary ) FROM employees WHERE commission_pct != "null" GROUP BY department_id ;