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 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 the first and last name, and salary for those employees whose first name is ending with the letter m.
assistant
SELECT first_name , last_name , salary FROM employees WHERE first_name LIKE '%m'
human
display the employee id and salary of all employees who report to Payam (first name).
assistant
SELECT employee_id , salary FROM employees WHERE manager_id = (SELECT employee_id FROM employees WHERE first_name = 'Payam' )
human
display the employee number, name( first name and last name ), and salary for all employees who earn more than the average salary and who work in a department with any employee with a 'J' in their first name.
assistant
SELECT employee_id , first_name , last_name , salary FROM employees WHERE salary > ( SELECT AVG (salary) FROM employees ) AND department_id IN ( SELECT department_id FROM employees WHERE first_name LIKE '%J%')
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 full name (first and last name), and salary of those employees who working in any department located in London.
assistant
SELECT first_name , last_name , salary FROM employees AS T1 JOIN departments AS T2 ON T1.department_id = T2.department_id JOIN locations AS T3 ON T2.location_id = T3.location_id WHERE T3.city = "London" ;