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 name and number of employees in each of the department.
assistant
SELECT department_name , COUNT(*) FROM employees AS T1 JOIN departments AS T2 ON T1.department_id = T2.department_id GROUP BY department_name
human
display the department name and number of employees in each of the department.
assistant
SELECT T2.department_name , COUNT(*) FROM employees AS T1 JOIN departments AS T2 ON T1.department_id = T2.department_id GROUP BY T2.department_name
human
display all the information of employees whose salary is in the range of 8000 and 12000 and commission is not null or department number does not equal to 40.
assistant
SELECT * FROM employees WHERE salary BETWEEN 8000 AND 12000 AND commission_pct != 'null' OR department_id != 40
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 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
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 ;