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 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
What are the full name (first and last name) and salary for all employees who does not have any value for commission?
assistant
SELECT first_name , last_name , salary FROM employees WHERE commission_pct = 'null'
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 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
Find the first name and last name and department id for those employees who earn such amount of salary which is the smallest salary of any of the departments.
assistant
SELECT first_name , last_name , department_id FROM employees WHERE salary IN (SELECT MIN(salary) FROM employees GROUP BY department_id)
human
What is the minimum salary in each department?
assistant
SELECT MIN( salary ) , department_id FROM employees GROUP BY department_id ;